Generating migration commands programmatically

[tonyxiao]

We have some sync code that dynamically write tables & columns into our database based on shape of the incoming object. Therefore are not able to use a static schemas.ts file with the existing drizzle:kit based approach. Would it be possible to get the migration command drizzle would have run? Or at least be able to get a create (ideally create if not exists) statement for a given table specified in Drizzle syntax?

[WoetDev]

Would also be in need of this!

I'm programmatically provisioning a database on Neon with their API and I'd like to run the schema migration directly from my code on that newly created database.

[jmls]

100% yes - came here to ask the question. I need to be able to create / apply the migrations programmatically . At the moment we're using typeorm and db-migrate but was hoping to move to drizzle and go all in.

[AimForNaN]

Looking for this, too. Forcing us to use drizzle-kit generate is fairly opinionated.

[tonyxiao]

Drizzle team any updates here? And did anyone find workaround?
i saw a getSQL() on table but alas it only gets the table name... Need drizzle kit

[leptonite]

We have a similar use case where we need to create the DDL statements for our drizzle schema programmatically. We don’t need migrations from an old schema to the current one, we just need to create the current schema. drizzle-kit exports some functions that help building a solution:

import { generateDrizzleJson, generateMigration } from 'drizzle-kit/api'
import { date, integer, pgTable, timestamp } from 'drizzle-orm/pg-core'

// [...]

// the drizzle schema we want to create DDL statements for
const schema = {
    testTable1: pgTable('testtable1', {
        id: integer('id').primaryKey(),
        testdate: date('testdate').notNull(),
    }),
    testTable2: pgTable('testtable2', {
        id: integer('id').primaryKey(),
        testtimestamp: timestamp('testtimestamp').notNull(),
    }),
}

// drizzle-kit seems to always migrate from one schema to another
// initially creating a schema seems to be equivalent to migrating from an empty schema
const prevSchema = {}

const migrationStatements = await generateMigration(
    generateDrizzleJson(prevSchema),
    generateDrizzleJson(schema),
)

console.log(migrationStatements.join('\n'))

// prints this:
//
// CREATE TABLE IF NOT EXISTS "testtable1" (
//     "id" integer PRIMARY KEY NOT NULL,
//     "testdate" date NOT NULL
// );
// 
// CREATE TABLE IF NOT EXISTS "testtable2" (
//     "id" integer PRIMARY KEY NOT NULL,
//     "testtimestamp" timestamp NOT NULL
// );

I didn’t find any documentation for drizzle-kit/api, instead I figured this out by inspecting drizzle-kit’s source code. There is more to discover in drizzle-kit/api, so you may be able to solve some other tasks with it.

[pdenapo]

This should be documented. I found it very useful!

[jack3898]

Super useful thanks!

[jrmarquard]

Incredible, very helpful. Thank you.
Using to spin up test containers with our schema.

[rkofman]

I'm hoping to do something similar; but need the full flow including generating a diff migration from an existing state. I believe the CLI does it using sqlitePushIntrospect; which is unfortunately not exported in the api package :/

Anybody have a workaround; would it be useful to open an issue that asks for more power in api?

[meredian]

Second that! All operations drizzke-kit does should be available programmatically to be injected & scaffolded properly with supporting code for production use 💪

[pdenapo]

I have found that the correct way of doing this is doing something like:

  import { db } from '../src/lib/db_postgresql/db';
  import { cuentas } from '../src/lib/db_postgresql/schema';
  import { generateDrizzleJson, generateMigration } from 'drizzle-kit/api';
  
  const prevSchema = {};
  const schema = { cuentas };
  
  const migrationStatements = await generateMigration(
	  generateDrizzleJson(prevSchema),
	  generateDrizzleJson(schema)
  );
  
await db.execute('drop table if exists cuentas');
  
  await db.execute(migrationStatements.join('\n'));

[tonyxiao]

Is this a new capability introduced into drizzlekit not so long ago?

[pstevovski]

@pdenapo Is this example that you've shown being executed from a .js file? I'm trying to achieve something like this when running tests. So before starting my tests, I'd like to drop my tables, then generate new ones based on the schema that I have defined, and then eventually seed the testing database tables with some fake data.

But when I run code from your example, specifically when I call the generateDrizzleJson on my schema definition I get an error stating Dynamic require of "fs" is not supported.

Any workaround for this? I'm using TS (trough ts-node) and perhaps something with that causes this issue?

[pdenapo]

Yes, I've been using bun for doing that. But now I have migrated to another solution without drizzle (so I am not sure if it still works).

[alexnorton]

I got this working a little while ago so I don't remember if I was having the exact same issue, but this is what works for me in TypeScript inside an Astro project (albeit using SQLite):

import { createRequire } from "module";
import * as schema from "./schema";

global.require = createRequire(import.meta.url);

const { generateSQLiteDrizzleJson, generateSQLiteMigration } = await import(
  "drizzle-kit/api"
);

const [previous, current] = await Promise.all(
  [{}, schema].map((schemaObject) => generateSQLiteDrizzleJson(schemaObject))
);

const statements = await generateSQLiteMigration(previous, current);
const migration = statements.join("\n");

export { migration };

[nbbaier]

Has anyone tried this approach for sqlite?

[hugochinchilla]

There is a simpler way:

import * as schema from '@myapp/persistence/db/schema';
import {  pushSQLiteSchema } from "drizzle-kit/api";

export async function createSchema(db: Drizzle) {
  const {apply} = await pushSQLiteSchema(schema, db);
  await apply();
}

[wsw70]

Unfortunately I get an error:

Dynamic require of "fs" is not supported

[marques-gabriel]

If you want to apply migrations programmatically using Drizzle:

import { migrate } from "drizzle-orm/node-postgres/migrator";
import { drizzle } from "drizzle-orm/node-postgres"

// Migration Function
async function runMigrations(db, migrationConfig) {

  // Pass your Drizzle instance (db) and the migration configuration.
  // This configuration should align with your drizzle config file.
    await migrate(db, migrationConfig); 
}

// Migration Configuration and Database Connection
const db = drizzle(process.env.DATABASE_URL);
const migrationConfig = {
  migrationsFolder: "src/database/migrations",
  migrationsTable: "pgmigrations", // Optional: The table name for migrations
  migrationsSchema: "public",      // Optional: The schema for the migrations table (PostgreSQL specific)
};

//Execute Migrations
runMigrations(db, migrationConfig)

Under the hood, Drizzle applies migrations using the migrate function from the respective driver, which in turn uses the migrate function of the database dialect associated with your Drizzle instance:

// (from drizzle-orm/node-postgres/migrator.js)

import { readMigrationFiles } from "../migrator.js";
async function migrate(db, config) {
  const migrations = readMigrationFiles(config);
    // The db.dialect.migrate method executes the migrations
  await db.dialect.migrate(migrations, db.session, config);
}
export {
  migrate
};

I hope Drizzle ORM will expose a more generalized programmatic API in the future, similar to what node-pg-migrate offers.