drizzle ormのメモ

DATABASE_URL="./data"
DRIVER="pglite"

import { defineConfig } from 'drizzle-kit';
import { env } from 'std-env';

const { DATABASE_URL, DRIVER } = env;
if (!DATABASE_URL) {
  throw new Error('DATABASE_URL is not set');
}

const config = {
  // migrationファイルの出力先
  // pushだと使わないしdrizzleは初期値なのでここに書く意味は薄いな…
  out: './drizzle',

  // schemaファイル これは単ファイルの場合の書き方
  schema: './schema.ts',

  // 接続先 ローカルのpgliteなら保存先ファイル
  dbCredentials: {
    url: DATABASE_URL,
  },

  // push時に実行を確認
  strict: true,

  // push時にSQLを表示
  verbose: true,

  // postgresqlを使用
  dialect: 'postgresql' as const,

  // DBカラム名をsnake_caseにする 重要
  casing: 'snake_case' as const,

  // pgliteを使用
  driver: DRIVER,
};

// 変数に入れないで直接defineConfig()に渡せば良いのだけど
// console.logしたかったので一旦変数化した
console.log({ userDB: config });

export default defineConfig(config);

import { PGlite } from '@electric-sql/pglite';
import { resolve } from '@std/path/resolve';
import { config } from 'dotenv';
import { drizzle } from 'drizzle-orm/pglite';
import { env } from 'std-env';
import * as schema from './schema';

// なんかstd-envだけではmonorepoの相対パスのenvの取得が上手くいかなかった
config({ path: resolve(import.meta.dirname, '.env') });

// DATABASE_URLの取得はdrizzle.config.tsと同じ
const { DATABASE_URL } = env;
if (!DATABASE_URL) {
  throw new Error('DATABASE_URL is not set');
}

// ローカルのurlを相対パスに変換
const url = DATABASE_URL.startsWith('.')
  ? resolve(import.meta.dirname, './data')
  : DATABASE_URL;

console.log(`DB URL: ${url}`);

// オプションの適用
const client = new PGlite(url);
const casing = 'snake_case';
const logger = true;
export const db = drizzle({ schema, client, casing, logger });

import { createId } from '@paralleldrive/cuid2';
import { relations } from 'drizzle-orm';
import { char, integer, pgEnum, pgTable, primaryKey, text, timestamp } from 'drizzle-orm/pg-core';

/***** Helper *****/

// wrapper function for cuid2
function cuid(opts?: { needGenerate: boolean }) {
  if (opts?.needGenerate) {
    return char({ length: 24 }).$defaultFn(() => createId());
  }
  return char({ length: 24 });
}

// wrapper function for timestamp with zone
function tsz() {
  return timestamp({ withTimezone: true, mode: 'date', precision: 3 }).notNull();
}

// wrapper function for cratedAt & updatedAt
const timestamps = {
  createdAt: tsz().$defaultFn(() => new Date()),
  updatedAt: tsz().$onUpdate(() => new Date()),
};

/***** Table *****/

export const adminLevel = pgEnum('admin_level', ['limited', 'normal', 'super']);
export const admin = pgTable('admin', {
  id: cuid({ needGenerate: true }).primaryKey(),
  email: text().notNull().unique(),
  name: text().notNull(),
  level: adminLevel().notNull().default('limited'),
  passwordHash: text().notNull(),
  ...timestamps,
});

export const adminSession = pgTable('admin_session', {
  encodedToken: text().primaryKey(),
  adminId: cuid().notNull().references(() => admin.id),
  expiresAt: tsz(),
});

/***** Relation *****/

export const adminRelations = relations(admin, ({ many }) => ({
  adminSessions: many(adminSession),
}));

export const adminSessionRelations = relations(adminSession, ({ one }) => ({
  admin: one(admin, {
    fields: [adminSession.adminId],
    references: [admin.id],
  }),
}));

// casing: 'snake_case'の設定が

// あるときー
passwordHash: text().notNull(),

// ないときー
passwordHash: text('password_hash').notNull(),

// hashPasswordの実装はここでは省略
import { hashPassword } from '@shared/logic/password';

import { reset } from 'drizzle-seed';
import { db } from './index';
import * as schema from './schema';

async function insertAdmin(email: string, level: string) {
  // super easy password for dev
  const password = 'hello123';
  const passwordHash = await hashPassword(password);
  const name = email.replace(/@.+$/, '');

  await db
    .insert(schema.admin)
    .values({ email, passwordHash, name, level });
}

async function main() {
  console.log('seed start');

  console.log('reset local db');
  await reset(db, schema);

  const arr = ['A', 'B', 'C', 'D', 'E', 'F', 'G'];

  console.log('insert data');
  await Promise.all([
    insertAdmin('admin@example.com', 'super'),
    ...arr.map(n => insertAdmin(`member${n}@example.com`, 'normal')),
    ...arr.map(n => insertAdmin(`supporter${n}@example.com`, 'limited')),
  ]);

  console.log('seed completed');
}

main();

export const author = pgTable('author', {
  id: cuid({ needGenerate: true }).primaryKey(),
  name: text().notNull(),
});

export const book = pgTable('book', {
  id: cuid({ needGenerate: true }).primaryKey(),
  title: text().notNull(),
});

export const bookAuthor = pgTable('book_author', {
  bookId: cuid().notNull().references(() => book.id),
  authorId: cuid().notNull().references(() => author.id),
}, t => ({
  pk: primaryKey({ columns: [t.bookId, t.authorId] }),
}));

export const authorRelations = relations(author, ({ many }) => ({
  bookAuthors: many(bookAuthor),
}));

export const bookRelations = relations(book, ({ many }) => ({
  bookAuthors: many(bookAuthor),
}));

export const bookAuthorRelations = relations(bookAuthor, ({ one }) => ({
  author: one(author, {
    fields: [bookAuthor.authorId],
    references: [author.id],
  }),
  book: one(book, {
    fields: [bookAuthor.bookId],
    references: [book.id],
  }),
}));

const books = await db.query.book.findMany({
  with: { bookAuthors: { with: { author: true } } },
});

const books = [
  {
    bookAuthors: [{ author: {...} }, { author: {...} }, ...]
  },
  {
    bookAuthors: [{ author: {...} }, { author: {...} }, ...]
  },
  ...,
]

> bun repl

> import { db } from '@shared/db'
> import { sql } from 'drizzle-orm'
> await db.execute(sql`select * from information_schema.tables where table_schema = 'public'`)
> await db.execute(sql`select column_name, data_type from information_schema.columns where table_name = 'user'`)

// この辺はパラメータで指定
const per = 10;
const page = 2;

const books = await db.query.book.findMany({
  limit: per,
  offset: (page - 1) * per,
  extras: (book, { sql }) => ({
    count: (sql<number>`count(${book.id}) over()`).as('count'),
  }),
});

await db.query.posts.findMany({
	where: (posts, { eq }) => (eq(posts.id, 1)),
	with: {
		comments: {
			where: (comments, { lt }) => lt(comments.createdAt, new Date()),
		},
	},
});