Drizzle ORM SQLite Trigger migrations

example.ts

// has to be exported - triggers should be stored in the same folder as table schemas
export const insertTrigger = new Trigger({
  name: "update_product_meta_on_new_review",
  type: "INSERT",
  on: review,
  when: ({ newRow }) => eq(newRow.isPrivate, false),
  do: ({ newRow }) =>
    db
      .insert(productMeta)
      .values({
        barcode: sql`${newRow.barcode}`,
        publicReviewCount: 1,
        publicTotalRating: sql`${newRow.rating}`,
      })
      .onConflictDoUpdate({
        target: productMeta.barcode,
        set: {
          publicReviewCount: sql`${productMeta.publicReviewCount} + 1`,
          publicTotalRating: sql`${productMeta.publicTotalRating} + ${alias(productMeta, "excluded").publicTotalRating}`,
        },
      }),
});

trigger-migration.ts

// This is the file which you can run as a script to generate a migration for your triggers

import { Trigger, breakpoint, createDropTriggerStatement } from "@/server/database/schema/trigger";
import type { Query, SQL } from "drizzle-orm";
import { sql } from "drizzle-orm";
import { SQLiteSyncDialect } from "drizzle-orm/sqlite-core";
import { exec } from "node:child_process";
import { readFile, readdir, writeFile } from "node:fs/promises";
import { resolve } from "node:path";
import { promisify } from "node:util";

function execAsync(command: string) {
  return promisify(exec)(command).then(({ stderr, stdout }) => {
    if (stderr) {
      console.error(stderr);
    }
    return stdout;
  });
}

export default function main() {
  const triggers = getTriggers();
  const dropPreviousTriggers = getPreviousTriggers().then((triggers) =>
    triggers.map(createDropTriggerStatement),
  );
  const migrationSql = Promise.all([dropPreviousTriggers, triggers]).then(
    ([prevTriggers, triggers]) =>
      createMigration(...prevTriggers, ...triggers.map((trigger) => trigger.statement)),
  );
  const migration = migrationSql.then(serializeQuery);
  const migrationFilePath = generateCustomMigrationFile();
  return Promise.all([migrationFilePath, migration]).then(([path, migration]) =>
    writeFile(path, migration),
  );
}

const schemaDirectory = "./src/server/database/schema";
function getTriggers() {
  return readdir(schemaDirectory, { recursive: true })
    .then((files) =>
      files
        .filter((file) => file.endsWith(".ts") || file.endsWith(".js"))
        .map((file) => resolve(schemaDirectory, file)),
    )
    .then((files) => Promise.all(files.map((file) => import(file))))
    .then((modules: unknown[]) =>
      modules
        .flatMap((module) => module && Object.values(module))
        .filter((value): value is Trigger => value instanceof Trigger),
    );
}

const migrationsDirectory = "./database/migrations";
const triggerNameRegExp = /CREATE TRIGGER\s*['"`]?(\w+)['"`]?/g;
function getPreviousTriggers() {
  return readdir(migrationsDirectory)
    .then((files) =>
      files
        .filter((file) => file.endsWith(".sql"))
        .map((file) => resolve(migrationsDirectory, file)),
    )
    .then((files) => Promise.all(files.map((file) => readFile(file, "utf-8"))))
    .then((migrations) =>
      migrations.flatMap((migration) =>
        [...migration.matchAll(triggerNameRegExp)]
          .map((match) => match[1])
          .filter((match): match is NonNullable<typeof match> => !!match),
      ),
    );
}

function generateCustomMigrationFile() {
  return execAsync("pnpm drizzle-kit generate:sqlite --custom").then((stdour) => {
    const file = stdour.match(/Your SQL migration file.+migrations\/(.+\.sql)/)?.[1];
    if (!file) throw Error("Could not resolve migration file path");
    return resolve(migrationsDirectory, file);
  });
}

const sqlite = new SQLiteSyncDialect();
function createMigration(...statements: Array<SQL>) {
  return sqlite.sqlToQuery(sql.join(statements, breakpoint));
}

function serializeQuery(query: Query) {
  return `${query.sql
    .split("?")
    .map((chunk, i) => {
      if (!chunk) return "";

      if (!(i in query.params)) return chunk;
      const param = query.params[i];

      const stringified = typeof param === "string" ? `"${param}"` : param;
      return `${chunk}${String(stringified)}`;
    })
    .join("")}`;
}

trigger.ts

// This is the file which mainly exports Trigger - a class to create new Triggers

import type { SQL, SQLWrapper } from "drizzle-orm";
import { sql } from "drizzle-orm";
import type { BuildAliasTable, SQLiteTable } from "drizzle-orm/sqlite-core";
import { alias } from "drizzle-orm/sqlite-core";

type TableColumn<TTable extends SQLiteTable> = TTable["_"]["columns"][keyof TTable["_"]["columns"]];

type TriggerType = "INSERT" | "UPDATE" | "DELETE";
type TriggerRow<TType extends TriggerType, TTable extends SQLiteTable> = Record<
  TType extends Exclude<TriggerType, "DELETE"> ? "newRow" : never,
  BuildAliasTable<TTable, "new">
> &
  Record<
    TType extends Exclude<TriggerType, "INSERT"> ? "oldRow" : never,
    BuildAliasTable<TTable, "old">
  >;

type BaseTriggerData<TType extends TriggerType, TTable extends SQLiteTable> = {
  /** Trigger name */
  name: string;
  /** On which operations should trigger activate */
  type: TType;
  /** On which tables should trigger activate */
  on: TTable;
  /** Condition when trigger should activate */
  when?: (row: TriggerRow<TType, TTable>) => SQLWrapper;
  /** WHat operation to perform when trigger activates */
  do: (row: TriggerRow<TType, TTable>) => SQLWrapper;
};

interface UpdateTriggerData<TTable extends SQLiteTable> extends BaseTriggerData<"UPDATE", TTable> {
  /** On which column of `on` table should trigger activate */
  of?: TableColumn<TTable>;
}

type TriggerData<
  TType extends TriggerType = TriggerType,
  TTable extends SQLiteTable = SQLiteTable,
> =
  TType extends Extract<TriggerType, "UPDATE">
    ? UpdateTriggerData<TTable>
    : BaseTriggerData<TType, TTable>;

const endLine = sql`;`;
const newLine = sql`\n`;
const tab = sql`\t`;
const space = sql` `;
export const breakpoint = sql`--> statement-breakpoint`.append(newLine);

export class Trigger<
  TType extends TriggerType = TriggerType,
  TTable extends SQLiteTable = SQLiteTable,
> {
  statement: SQL;

  constructor(data: TriggerData<TType, TTable>) {
    const triggerTable = join(
      [
        sql`AFTER`,
        sql.raw(data.type),
        data.type === "UPDATE" && data.of
          ? join([sql`OF`, sql.identifier(data.of.name)], space)
          : undefined,
        sql`ON`,
        data.on,
      ],
      space,
    );

    const newTable = alias(data.on, "new");
    const oldTable = alias(data.on, "old");
    const triggerCondition = data.when
      ? join([
          tab,
          join(
            [sql`FOR EACH ROW WHEN`, data.when({ newRow: newTable, oldRow: oldTable }).getSQL()],
            space,
          ),
        ])
      : undefined;

    const createStatement = join(
      [
        join([sql`CREATE TRIGGER`, sql.identifier(data.name)], space),
        join([tab, triggerTable]),
        triggerCondition,
        sql`BEGIN`,
        join([tab, data.do({ newRow: newTable, oldRow: oldTable }).getSQL(), endLine]),
        sql`END`,
      ],
      newLine,
    ).append(endLine);

    this.statement = join([createDropTriggerStatement(data.name), createStatement], breakpoint);
  }
}

export function createDropTriggerStatement(name: string) {
  return join([sql`DROP TRIGGER IF EXISTS`, sql.identifier(name)], space).append(endLine);
}

const join: typeof sql.join = function (chunks, separator) {
  return sql.join(chunks.filter(Boolean), separator);
};

This is for SQLite only - pretty sure it's easy to convert these ideas to other flavors I think.

Hopefully you're not generating triggers from user input because this is not safe against SQL injections