DBはMySQLを使用してみます。
npm install drizzle-orm mysql2
npm install -D drizzle-kit
npm install --save-dev dotenv dotenv-cli
npm install ts-node
├── drizzle/
├── drizzle.config.ts
├── tsconfig.cli.json
├── src/
│ ├── db/
│ │ ├── database.ts
│ │ ├── migrate.ts
│ │ ├── schema.ts
│ │ └── seed.ts
├── tsconfig.cli.json
"target": "esnext" に変更します
{
"compilerOptions": {
"target": "esnext",
src/db/schema.ts
import {
json,
serial,
text,
boolean,
datetime,
timestamp,
mysqlTable,
varchar,
} from "drizzle-orm/mysql-core";
type Permission = {
code: string;
description: string;
};
export const users = mysqlTable("users", {
// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
id: serial("id").primaryKey(),
name: text("name").notNull(),
role: varchar("varchar", { length: 16, enum: ["admin", "user"] }),
verified: boolean("verified").notNull().default(false),
permissionJson: json("relatedJson").$type<Permission[]>(),
createdAt: datetime("createdAt"),
updatedAt: timestamp("updatedAt"),
});
package.json へ 次のコマンドを追加します
NODE_ENV=development を設定して、src/db/database.ts 内に
.env.development ファイルから接続情報を読み込むよう定義します
"scripts": {
"db:migrate:generate": "NODE_ENV=development drizzle-kit generate:mysql",
"db:migrate:execute": "NODE_ENV=development ts-node --project tsconfig.cli.json ./src/db/migrate.ts",
"db:seed": "NODE_ENV=development ts-node --project tsconfig.cli.json ./src/db/seed.ts",
"db:studio": "NODE_ENV=development npx drizzle-kit studio"
},
tsconfig.cli.json
{
"compilerOptions": {
"module": "nodenext"
},
// ts-node
"ts-node": {
"esm": true,
"experimentalSpecifierResolution": "node"
}
}
.env.development
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=drizzle_sample_db
DB_USERNAME=root
DB_PASSWORD=
src/db/database.ts
NODE_ENV == "development" の場合は .env.development を読み込むようにしています
import { drizzle } from "drizzle-orm/mysql2";
import mysql, { Connection } from "mysql2/promise";
import * as dotenv from "dotenv";
if (process.env.NODE_ENV == "development") {
dotenv.config({ path: ".env.development" });
} else {
dotenv.config();
}
if (!("DB_HOST" in process.env)) throw new Error("DB_HOST not found on env");
export const dbCredentials = {
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT, 10),
user: process.env.DB_USERNAME,
database: process.env.DB_DATABASE,
password: process.env.DB_PASSWORD,
multipleStatements: true,
};
console.log(dbCredentials);
export const getConnection = async (): Promise<Connection> => {
return mysql.createConnection({
...dbCredentials,
});
};
export const getDb = async (connection: Connection) => {
return drizzle(connection);
};
drizzle.config.ts
database.ts の接続情報を呼び出しています
import type { Config } from "drizzle-kit";
import { dbCredentials } from "./src/db/database";
export default {
schema: "./src/db/schema.ts",
out: "./drizzle/migrations",
driver: "mysql2", // 'pg' | 'mysql2' | 'better-sqlite' | 'libsql' | 'turso'
dbCredentials: {
...dbCredentials,
},
} satisfies Config;
npm run db:migrate:generate
src/db/migrate.ts
import "dotenv/config";
import { migrate } from "drizzle-orm/mysql2/migrator";
import { getDb, getConnection } from "./database";
const migrateAsync = async () => {
const connection = await getConnection();
const db = await getDb(connection);
await migrate(db, {
migrationsFolder: "./drizzle/migrations",
});
await connection.end();
};
void migrateAsync();
npm run db:migrate:execute
drizzle-kit に push というコマンドもありますが、条件によってはマイグレーションとの併用はうまくいかないようです。
drizzle-kit push:mysql
npm i -D @faker-js/faker
src/db/seed.ts
import { users } from "./schema";
import { faker } from "@faker-js/faker";
import { getConnection, getDb } from "./database";
const seedAsync = async () => {
const connection = await getConnection();
const db = await getDb(connection);
const data: (typeof users.$inferInsert)[] = [];
for (let i = 0; i < 20; i++) {
const zeroOrOne = i % 1;
data.push({
name: faker.internet.userName(),
role: ["admin", "user"][zeroOrOne] as (typeof users.$inferInsert)["role"],
permissionJson: [
{
code: "READ",
description: "read data",
},
{
code: "WRITE",
description: "write data",
},
],
verified: true,
});
}
await db.insert(users).values(data);
await connection.end();
console.log("✅ seed done");
};
void seedAsync();
シードの実行
npm run db:seed
src/trpc/server/database.ts
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
const sqlite = new Database("sqlite.db");
export const db = drizzle(sqlite);