Drizzle ORM: How to generate "CREATE TABLE" SQL from pgTable definition?

Question

Is there a built-in way in Drizzle ORM to generate the CREATE TABLE IF NOT EXISTS SQL directly from the pgTable schema definition?

Here’s an example of how I’m currently setting up an in-memory pglite database for testing:

import {lower} from '@/drizzle/db.utils';
import {pgTable, serial, text, timestamp, uniqueIndex} from 'drizzle-orm/pg-core';

export const emailSubmissionTable = pgTable(
  'email_submission',
  {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    email: text('email').notNull().unique(),
    createdAt: timestamp('created_at').notNull().defaultNow(),
  },
  (table) => [uniqueIndex('unique_email_idx').on(lower(table.email))]
);

And here’s how I’m currently setting up the in-memory database:

import {emailSubmissionTable} from '@/drizzle/db';
import {PGlite} from '@electric-sql/pglite';
import {drizzle} from 'drizzle-orm/pglite';
import {beforeAll, describe} from 'vitest';

describe('Email Submission', () => {
  let db;

  beforeAll(async () => {
    const inMemoryDbClient = new PGlite();

    await inMemoryDbClient.exec(`
          CREATE TABLE IF NOT EXISTS "email_submission" (
            "id" serial PRIMARY KEY NOT NULL,
            "name" text NOT NULL,
            "email" text NOT NULL,
            "created_at" timestamp DEFAULT now() NOT NULL,
            CONSTRAINT "email_submission_email_unique" UNIQUE("email")
          );
      `);

    db = drizzle(inMemoryDbClient, {schema: {emailSubmissionTable}});
  });

  // ...
});

Problem:

I want to automatically generate the raw CREATE TABLE IF NOT EXISTS SQL string from the pgTable schema definition, which would look like this:

CREATE TABLE IF NOT EXISTS "email_submission" (
  "id" serial PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "email" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "unique_email_idx" UNIQUE("email")
);

The reason I need this is so I can pass the generated SQL string to the in-memory database for testing my queries. I am using diffTestSchemas from the Drizzle ORM repository to generate this, but it's not convenient.

What It Could Look Like:

Ideally, Drizzle ORM would provide a helper function that could generate the CREATE TABLE IF NOT EXISTS SQL directly from the pgTable schema definition, something like this:

import {generateCreateTableSQL} from 'drizzle-orm/utils'; // Hypothetical helper function

const inMemoryDbClient = new PGlite();

// Generate the SQL string from the pgTable schema
const sql = generateCreateTableSQL(emailSubmissionTable);

// Execute the generated SQL in the in-memory database
await inMemoryDbClient.exec(sql);

// Log the SQL string to see it
console.log(sql);
/* Output:
  CREATE TABLE IF NOT EXISTS "email_submission" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text NOT NULL,
    "email" text NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    CONSTRAINT "unique_email_idx" UNIQUE("email")
  );
*/

This would make it much easier to generate the SQL string directly from the schema definition to test my queries, selects, insets, etc.

Maia Yeager · Accepted Answer · 2025-01-16 20:24:21Z

I had a similar issue, which I resolved using drizzle-kit's programmatic API to push my schema to the test database — specifically the pushSchema function.

Using your test case above as the basis, here's what your test case could look like instead (not tested directly):

import {emailSubmissionTable} from '@/drizzle/db';
import {PGlite} from '@electric-sql/pglite';
import {drizzle} from 'drizzle-orm/pglite';
import {pushSchema} from 'drizzle-kit/api'; // New import
import {beforeAll, describe} from 'vitest';

describe('Email Submission', () => {
  let db;

  beforeAll(async () => {
    const inMemoryDbClient = new PGlite();
    const schema = {emailSubmissionTable}; // Re-usable schema definition.

    // Create a separate, inline Drizzle instance without the schema.
    const { apply } = await pushSchema(schema, drizzle(inMemoryDbClient));
    await apply();

    db = drizzle(inMemoryDbClient, {schema});
  });

  // ...
});

If you really want the SQL statements as strings to inspect or apply manually, you can also do the following:

import {sql} from 'drizzle-orm';

// ...

const { statementsToExecute } = await pushSchema(schema, drizzle(inMemoryDbClient));
for (const statement of statementsToExecute) {
  console.log(statement)
  await db.execute(sql.raw(statement))
}

// ...

Additional notes

Not a requirement, but in general I'd highly recommend using the same database implementation for testing that you'll be using in production. If you're using PGlite in production as well, great! No need to change anything.

Otherwise, you may want to consider switching to something like @testcontainers/postgresql to use a containerized version of your production database implementation (e.g. postgres:16-alpine). That way, any potential discrepancies that would exist (i.e. between the PGlite Postgres implementation and your production Postgres implementation) are eliminated entirely.

Collectives™ on Stack Overflow

Drizzle ORM: How to generate "CREATE TABLE" SQL from pgTable definition?

Problem:

What It Could Look Like:

1 Answer 1

Additional notes

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem:

What It Could Look Like:

1 Answer 1

Additional notes

Comments

Your Answer

Sign up or log in

Post as a guest

Related