Make simple migration statements available on table objects

[0xOlias]

I have a use case where it would be really nice if a table object had a method similar to query.toSQL() that returned (or executed) the SQL query required to create that table and any indices.

From SQLite example:

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  fullName: text('full_name'),
})

This API would return or execute the query:

CREATE TABLE IF NOT EXISTS users (
  'id' INTEGER PRIMARY KEY,
  'full_name' TEXT
);

Would also appreciate any ideas for workarounds. My use case involves programmatically creating a temporary table in a SQLite database (cannot use a CLI for migrations like drizzle-kit).

In an ideal world, this API would also somehow support constraints/statements that reference multiple tables as demonstrated here.

[205g0]

Did you already check Drizzle's migrations table in the DB? You could query that table.

[kmerfeld]

Is there no way to create a sqliteTable() without a migration?

[tonyxiao]

@0xOlias did you figure out a solution to this?

[0xOlias]

No, not with Drizzle. Was able to solve my use case with Kysely's support for programmatic migrations.

[tonyxiao]

I see. could maintainers such as @AlexBlokh or others provide a workaround or a wont-fix for this issue?

[kevinswarner]

It would be nice to be able to use the Drizzle API to create and delete tables programmatically. Our use case is for testing. For example, we have many different test files that need to operate on a table, and these test files run concurrently. It would be nice for us to be able to create a table per test file (e.g. test_file_1_widgets, test_file_2_widgets). Then, we can easily add data per test file and delete the data in the tables per test file without conflicts between the two test files.

I think we can potentially use raw sql to create and delete files in the setup and teardown of tests, but because we define the tables using Drizzle, it would be much more convenient to be able to simply use the API to do this.

We also can run out tests serially, but this impacts our overall test performance.

If anyone has any better solution, please let me know.

Thanks.

[jkazimierczak]

This 👆 - this feature is essential for testing purposes.

I've built a custom generic DAO that I would like to test before employing it into the codebase. Currently I have to test it with one of the existing tables, but I would prefer to test it on some temporary table too.

For now I'm working around this issue with use of test containers, and running migrations from within the setup function:

import {
  PostgreSqlContainer,
  type StartedPostgreSqlContainer,
} from "@testcontainers/postgresql";
import { Client } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import * as schema from "@/core/database/schema";

// then in the describe block:
beforeEach(async () => {
  databaseContainer = await new PostgreSqlContainer().start();
  databaseClient = new Client({
    connectionString: databaseContainer.getConnectionUri(),
  });
  await databaseClient.connect();

  db = drizzle(databaseClient, { schema });

  await migrate(db, {
    migrationsFolder: "./database/migrations",
  });

  dao = new TestDao(db);

  // Seed with necessary data
  await db.insert(tables.userProfiles).values(testUser);
});

Unfortunately, this still depends on migrations being pre-generated. But from how I understand your use-case, you already have a schema defined for widgets table, and only need to have a fresh and clean table for each test file.

The use of test containers might be suitable for you, as for each file (or even for each test case if you prefer), you can spin-up a new database container that's provides an independent environment.

[kevinswarner]

Thanks for the suggestion. I am currently just going to run my tests sequentially. But good option!

[schpet]

i'm interested in using drizzle in a cli tool, and would be nice in that context to intialize sqlite databases with a schema, and without needing to generate migration files