New drizzle-kit config and Plugins API

[AndriiSherman]

While working on v1, we realized that this would be a great time to upgrade the drizzle-kit config, remove legacy commands, and introduce plugins. These plugins would extend drizzle-kit by hooking into kit lifecycles and controlling how the generate, migrate, pull, and push commands work

This discussion aims to showcase our thoughts on the updated config and plugin api, along with all the possible lifecycle hooks we can think of as needed. We’d love to hear feedback and get ideas on how to improve it

It’s really important to provide real-world scenarios for a plugin or lifecycle hook feature, so we can add functionality that developers truly need, rather than adding features just for the sake of adding them

Old drizzle-kit config options

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  dialect: "postgresql",
  schema: "./src/schema.ts",
  driver: "pglite",
  dbCredentials: {
    url: "./database/",
  },
  extensionsFilters: ["postgis"],
  schemaFilter: "public",
  tablesFilter: "*",
  introspect: {
    casing: "camel",
  },
  migrations: {
    prefix: "timestamp",
    table: "__drizzle_migrations__",
    schema: "public",
  },
  entities: {
    roles: {
      provider: '',
      exclude: [],
      include: []
    }
  },
  breakpoints: true,
  strict: true,
  verbose: true,
});

New drizzle-kit config options

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  dialect: "postgresql",
  schema: "./src/schema.ts",
  driver: "pglite",
  connection: {
    url: "./database/",
  },
  verbose: true,
  migrations: {
    table: "__drizzle_migrations__",
    schema: "public",
    provider: ""
  },
  
  // New API
  plugins: [
     introspect({ casing: 'camel' }),
  ],
  entities: {
    schemas: "public",
    schemas: (name) => true,
    //
    tables: "*", 
    tables: (type, schema, name) => false,
    //
    extensions: ["postgis", "..."]
    roles: {
      provider: '',
      exclude: [],
      include: []
    },
  },
})

So we will get a set of changes in the config:

1. "strict": true will be enabled by default and removed from the config.
2. The strangely named extensionsFilters, tablesFilter, and schemaFilter will be extended and moved under entities. This API will reproduce different settings for the database entities you may have

entities: {
    schemas: "public",
    schemas: (name) => true,
    //
    tables: "*", 
    tables: (type, schema, name) => false,
    //
    extensions: ["postgis", "..."]
    roles: {
      provider: '',
      exclude: [],
      include: []
    },
  },
  // functions, triggers, etc. may appear here in the future

3. schemas and tables will get 1 more api option - callback. You can add custom logic for filtering specific tables, views or schemas.

schemas: (name: string) => true,
tables: (type: "table" | "view", schema: string, name: string) => false,

4. We will deprecate introspect casing and move it to our first plugin to showcase an example of usage

  plugins: [
     introspect({ casing: 'camel' }),
  ],

Plugins API

This API is inspired by Astro integrations, and we’ve used the main API ideas from there. Here is an example of how you would define the introspect implementation.

// The hook shape is an example and will be complete in terms of data in the callback
type PullHook = { entity: string, data: { key: string, dbName: string, ... }  }

function introspect({ casing }: { casing: "camel" | "preserve" }): DrizzlePlugin {
  return {
    name: "introspect",
    hooks: {
      // hook shape is an example
      'pull:entities': async (hook: PullHook) => {
        if (hook.entity === 'column') {
          hook.key = toCamelCase(hook.data.key)
        }
      }, 
    }
  }
}

DrizzlePlugin will have plugin name and different hooks implementations you want to define

Here is a list of possible hooks we want to expose (together with their use cases)

interface DrizzleKitHooks {
  // changing entities config on introspection 
  // use-case: introspect casing plugin
  "pull:entities": ({ type, column }:{ type: 'columns', column: {} }) => Promise<void>,

  // changing a row of code outputted to the ts file
  // use-case: adding some specific types or ts only features that are not available on db level
  // for example adding .$type<>() to some specific column, so on each introspection you can get a schema a need
  "pull:strings": (row: string) => Promise<string>,

  // output a custom string on top of the pulled ts file 
  // use-case: We output some types as unknown because it's not supported by orm and kit
  // you can define a customType implementation so it can be outputted to introspected file
  "pull:custom:code": () => Promise<string>,

  // Get each object that drizzle-kit read from schema file
  // use-case: maybe a good case to add debug mode to see all the entities that kit reads
  // for example to catch that some not exported tables, enums were not caught by drizzle-kit
  "generate:entities": (entity: any) => Promise<void>,

  // Need to think about a use-case
  // Maybe we don't need it
  "generate:statements": (statement: DDL) => Promise<void>,

  // all sql statements that will be outputted to the generated migration file
  // Need to think about a use-case
  // Maybe we don't need it
  "generate:strings": (sql: string[]) => Promise<string>,

  // what to write, where to write, how to name
  // will explain where additionally to output sql file and how to name it
  // use-case: supabase migrations
  "generate:file": (filename: string, path: string, sql: string) => Promise<{filename: string, path: string, sql: string}>
  
  // maybe you can do custom ts code between migration that should be applied? You can check by date or name
  "migrate:files": (sql: string[], date: string, name: string, path: string) => Promise<string>

  // check on which file rollback was fired or error appeared
  // may be good for debug plugins for kit commands
  "migrate:status": (file: string, status: 'success' | 'rollback', error?: string) => Promise<void>

  // control each entity you read from a schema
  // maybe we will reuse generate:entities
  "push:entities:schema": (entity: any) => Promise<any>

  // control each entity you read from a db
  // maybe we will reuse pull:entities
  "push:entities:db": (entity: any) => Promise<any>

  // all sql statements that will be executed on the database
  // Need to think about a use-case
  // Maybe we don't need it
  "push:statements": (statement: DDL) => Promise<void>

  // all sql strings that will be executed on the database
  // Need to think about a use-case
  // Maybe we don't need it
  "push:strings": (sql: string) => Promise<string>

  // TODO
  // hook for destructive statements detection with info and type about each?
  // need a use-case here
}

[marcospgp]

A real-world scenario for migrate:files, and one capability it would need: a veto.

We generate migrations in agent sessions and apply them unattended, in a pre-deploy step, with the server refusing to start while any migration is pending. The only gate between a generated file and production is that a session reviewed the SQL, and nothing in the toolchain ties applying to that review having happened.

What we built: a migration is a draft until it ends with a review line, -- Reviewed-By: <reviewer>. A wrapper around migrate() reads the folder with readMigrationFiles and throws before touching the database if any file lacks the line. The gate and the generate command repeat the check.

What the plugin API would need for this to live in a hook rather than a wrapper: migrate:files able to refuse, by throwing or returning a refusal, so the whole run aborts before any statement executes, with the hook receiving path, name and SQL.

A read-only pending list beside it, roughly getPendingMigrations({ migrationsFolder, db }), would let the boot guard and the review gate both be written without reimplementing getMigrationsToRun, see #2791.

On the destructive-statement TODO: the same hook is where we would attach a rows-touched report, replaying the batch inside a rolled-back transaction, so a data-losing statement is seen before the deploy applies it.

[NickCrews]

I'm not quite sure if I understand the scope of this, but I want some way of extending the sorts of entities that drizzle-kit can manage. Particularly I'm interested in funcitons, triggers, comments, roles and permissions. eg I want to declare functions in my schema, and have the migration lifecycle be automated:

// schema/functions.ts
import { sql } from 'drizzle-orm';
import { pgFunction } from 'drizzle-funcs';

export const orderTotal = pgFunction('order_total', {
  args: [{ name: 'order_id', type: 'uuid' }],
  returns: 'numeric',
  language: 'sql',
  volatility: 'stable',
  security: 'invoker',
  body: sql`select sum(price * qty) from order_items where order_id = $1`,
});

// queries.ts
import { desc, eq, gt } from 'drizzle-orm';
import { orders } from './schema/tables';
import { orderTotal } from './schema/functions';

const total = orderTotal(orders.id).as('total');
const rows = await db
  .select({ id: orders.id, status: orders.status, total })
  .from(orders)
  .where(gt(orderTotal(orders.id), '100'))
  .orderBy(desc(total));

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
import { definePlugin } from 'drizzle-funcs';

export default defineConfig({
  dialect: 'postgresql',
  schema: './schema',
  out: './drizzle',
  plugins: [
    definePlugin(),
  ],
});

The drizzle-funcs package is reponsible for diffing (desired, current) database states and emitting the required SQL, which will then be included in migrations.