Schema Migrations with Drizzle

Drizzle is a TypeScript ORM that supports Postgres, MySQL, and SQLite. It also has a CLI, drizzle-kit, for managing migrations and few other things. This guide will show you how to use Drizzle Kit CLI to manage your schema migrations. We are going to assume that you already have a project set up with your favorite Typescript framework.

Start from example project

Clone our example project and install dependencies:

git clone https://github.com/niledatabase/niledatabase.git
cd examples/migrations/drizzle
npm i

This will install drizzle-kit, drizzle-orm, dotenv, and pg-node - all of which are needed for this guide. pg-node can be replaced with another postgres client like postgres.js.To run this example, you’ll need a .env file with a DATABASE_URL environment variable set to a postgres database. You can copy the connection string from your Nile database home page.

Configure Drizzle

Drizzle kit is configured via a drizzle.config.ts file, which you can find in the root of the example project.Here’s an example drizzle.config.ts file. You’ll need to set:

The schema field to the path to your schema file
The out field to the path where you want to store your migrations
The dialect field to postgresql for Nile databases
The dbCredentials field with your database credentials

import { defineConfig } from 'drizzle-kit';
import dotenv from 'dotenv';

dotenv.config();

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './db/out',
  dialect: 'postgresql',
  dbCredentials: {
    url:
      process.env.DATABASE_URL ||
      'postgresql://username:password@db.thenile.dev:5432/db',
  },
});

Define Your Schema

In code-first schema management, you define your schema as Typescript objects, and then use the Drizzle Kit CLI to generate migrations.Create your schema in src/db/schema.ts. Note that we include the built-in tenants table that Nile automatically provisions:

import { sql } from 'drizzle-orm';
import {
  pgTable,
  primaryKey,
  uuid,
  text,
  timestamp,
  varchar,
  boolean,
  vector,
} from 'drizzle-orm/pg-core';

export const tenants = pgTable('tenants', {
  id: uuid('id')
    .default(sql`gen_random_uuid()`)
    .primaryKey(),
  name: text('name'),
  created: timestamp('created'),
  updated: timestamp('updated'),
  deleted: timestamp('deleted'),
});

export const todos = pgTable(
  'todos',
  {
    id: uuid('id').default(sql`gen_random_uuid()`),
    tenantId: uuid('tenant_id'),
    title: varchar('title', { length: 256 }),
    estimate: varchar('estimate', { length: 256 }),
    embedding: vector('embedding', { dimensions: 768 }),
    complete: boolean('complete'),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.tenantId, table.id] }),
    };
  },
);

Generate and Run Migrations

Generate your first migration using Drizzle Kit:

npx drizzle-kit generate

You should see output like:

2 tables
tenants 5 columns 0 indexes 0 fks
todos 6 columns 0 indexes 0 fks

[✓] Your SQL migration file ➜ db/out/0000_absurd_captain_britain.sql 🚀

Then run the migration:

npx drizzle-kit migrate

Seed and Query Data

Now you can write code to insert and query data. Here’s an example (src/index.ts):

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';
import { todos, tenants } from './db/schema';

const db = drizzle(process.env.DATABASE_URL!);
async function main() {
  const tenant: typeof tenants.$inferInsert = {
    name: 'My New Tenant',
  };

  const tenantId = await db.insert(tenants).values(tenant).returning({ id: tenants.id });
  console.log('New tenant created!')

  const todo: typeof todos.$inferInsert = {
    title: 'My New Todo',
    tenantId: tenantId[0].id,
  };

  await db.insert(todos).values(todo);
  console.log('New todo created!')

  const rows = await db.select().from(todos);
  console.log('Getting all todos from the database: ', rows)
}

main();

Run the example:

npx tsx src/index.ts

You should see output showing the created tenant and todo:

New tenant created!
New todo created!
Getting all todos from the database:  [
  {
    id: 'd8896674-a7eb-4405-a4de-4ad6fbd2f5fc',
    tenantId: '01929704-3250-70bf-9568-0a6858dfd4e9',
    title: 'My New Todo',
    estimate: null,
    embedding: null,
    complete: null
  }
]

Make Schema Changes

To add new columns, update your schema file. For example, to add a due date:

// ...
    complete: boolean("complete"),
    dueDate: timestamp("due_date"), // new column!
// ...

Generate and run a new migration:

npx drizzle-kit generate
npx drizzle-kit migrate

This will generate a migration file like:

ALTER TABLE "todos" ADD COLUMN "due_date" timestamp;

Get Started

Tenant Virtualization

Postgres Concepts

Extensions