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!

[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 };