Drizzle Kit

When we design tables in our code, the tables don’t actually exist in the database. Therefore, we need Drizzle Kit to synchronize the table structure from our code to the database.

Configuration

We create a configuration file /drizzle.config.ts for Drizzle Kit.

import type { Config } from "drizzle-kit";
import { env } from "node:process";

const drizzleConfig = {
  schema: "./generated/database-schema.ts",
  out: "./drizzle",
  dialect: "mysql", // "postgresql" | "mysql"
  dbCredentials: {
    host: env.DB_HOST ?? "your-default-database-url",
    port: Number(env.DB_PORT) ?? 3306,
    user: env.DB_USERNAME ?? "your-default-username",
    password: env.DB_PASSWORD ?? "your-default-password",
    database: env.DB_DATABASE ?? "your-default-database",
  },
} satisfies Config;

export default drizzleConfig;

You can modify the highlighted lines above to specify the database you want to use. Once the configuration is created, we need to write a script that uses Drizzle Kit to generate the synchronization SQL statements and execute them every time the script is run.

Migration

Drizzle Kit supports two modes: push mode and generate mode. Generally, we use the push mode during development and the generate mode when deploying to a server.

Push Mode

Push mode: directly adjusts the database to match the local table structure. Tables and fields that have been removed will not be deleted.

#!/usr/bin/env bun
import { $ } from "bun";
import { exit } from "node:process";

// --- Run Drizzle Kit in push mode ---
await bun x drizzle-kit push`;

exit(0);

The above code assumes that you are using MySQL. If you are using PostgreSQL, replace mysql with pg in the code.

Generate Mode

Generate mode: analyzes the differences between the current table structure and the table structure from the last run, and generates SQL files for the differences.

Generate mode is safer because you can explicitly track every change made to the database to prevent unforeseen consequences caused by human errors.

#!/usr/bin/env bun
import { $ } from "bun";
import { exit } from "node:process";

console.log("Drizzle Migrating..");

const config = await import("./drizzle.config.ts");
const schema = await import("./generated/database-schema.ts");

// --- Run Drizzle Kit in generate mode ---
const { execSync } = await import("node:child_process");
await bun x drizzle-kit generate`;

// --- Load Drizzle and execute migration files ---
// In fact, before this step, we only generated migration SQL files in the /drizzle directory, and we still need to run them
const mysql = await import("mysql2/promise");
const { drizzle } = await import("drizzle-orm/mysql2");
const { migrate } = await import("drizzle-orm/mysql2/migrator");
const connection = await mysql.createConnection(config.default.dbCredentials);
const db = drizzle(connection, { schema, mode: "default" });
await migrate(db, { migrationsFolder: "drizzle" });

console.log("Drizzle Migration Completed!");
exit(0);

The above code assumes that you are using MySQL. If you are using PostgreSQL, adjust the parts related to MySQL in the code.

Running Drizzle Kit

Once you’re done, you can give it a try. Go ahead and run it! Before running, make sure you have at least one table created.

bun run ./drizzle.migrate.push.ts

If you find it cumbersome to enter the command every time, you can add this command to the Milkio menubar in the bottom-left corner of VS Code.

[menubar]
commands = [
  { name = 'Drizzle Kit', script = 'bun run ./drizzle.migrate.push.ts' },
  { name = 'Test All APIs', script = 'bun run ./index.ts --run-mode:API_TEST' },
]

Deployment

Usually, the database used in local development is different from the one used in the actual server. Therefore, when you update your server code, remember to run bun run ./drizzle.migrate.push.ts to keep the database up to date.

Drizzle Studio

Drizzle Kit comes with Drizzle Studio, a bundled database browser that allows you to start it locally with a single command.

bun x drizzle-kit studio --port 3000

If you find it cumbersome to enter the command every time, you can add this command to the Milkio menubar in the bottom-left corner of VS Code.

[menubar]
commands = [
  { name = 'Drizzle Studio', script = 'bun x drizzle-kit studio --port 3000' },
  { name = 'Test All APIs', script = 'bun run ./index.ts --run-mode:API_TEST' },
]