Running SQL statement manually for in-memory database

I have an in-memory sqlite database:

import { readdir } from "node:fs/promises"
import path from "path"
import { createClient } from "@libsql/client/node"
import { drizzle } from "drizzle-orm/libsql"

const client = createClient({
  url: ":memory:",
})

export const db = drizzle(client, { logger: true })

migrate()

export async function migrate() {
  const migrations = path.resolve(__dirname, "migrations")

  const files = await readdir(migrations)

  for (const file of files) {
    if (file.endsWith(".sql")) {
      const read = Bun.file(path.resolve(migrations, file))

      const sql = await read.text()

      const result = await db.run(sql)

      console.log("result", result)
    }
  }
}

import { readdir } from "node:fs/promises"
import path from "path"
import { createClient } from "@libsql/client/node"
import { drizzle } from "drizzle-orm/libsql"

const client = createClient({
  url: ":memory:",
})

export const db = drizzle(client, { logger: true })

migrate()

export async function migrate() {
  const migrations = path.resolve(__dirname, "migrations")

  const files = await readdir(migrations)

  for (const file of files) {
    if (file.endsWith(".sql")) {
      const read = Bun.file(path.resolve(migrations, file))

      const sql = await read.text()

      const result = await db.run(sql)

      console.log("result", result)
    }
  }
}

and I'm having trouble running migrations on that database

14 Replies

Gabriel LucenaOP•3w ago

this is the output from await db.run(sql):

@app/server:dev: result {
@app/server:dev:   columns: [],
@app/server:dev:   columnTypes: [],
@app/server:dev:   rows: [],
@app/server:dev:   rowsAffected: 0,
@app/server:dev:   lastInsertRowid: "0",
@app/server:dev: }

@app/server:dev: result {
@app/server:dev:   columns: [],
@app/server:dev:   columnTypes: [],
@app/server:dev:   rows: [],
@app/server:dev:   rowsAffected: 0,
@app/server:dev:   lastInsertRowid: "0",
@app/server:dev: }

this is my only migration file, created by drizzle:

...
--> statement-breakpoint
CREATE TABLE `customers` (
    `id` text PRIMARY KEY NOT NULL,
    `workspace_id` text NOT NULL,
    `created_at` integer NOT NULL,
    `deleted_at` integer,
    `name` text(100) NOT NULL,
    `email` text(100) NOT NULL,
    `phone_number` text(20),
    `phone_country_code` text(10),
    `document` text(100),
    `status` text(255) NOT NULL,
    `deleted` integer NOT NULL,
    FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
...

...
--> statement-breakpoint
CREATE TABLE `customers` (
    `id` text PRIMARY KEY NOT NULL,
    `workspace_id` text NOT NULL,
    `created_at` integer NOT NULL,
    `deleted_at` integer,
    `name` text(100) NOT NULL,
    `email` text(100) NOT NULL,
    `phone_number` text(20),
    `phone_country_code` text(10),
    `document` text(100),
    `status` text(255) NOT NULL,
    `deleted` integer NOT NULL,
    FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
...

other tables are omitted the drizzle error:

Query: select "id", "created_at", "confirmed_email", "confirmed_phone_number", "name", "email", "phone_country", "phone_country_code", "phone_number", "otp", "blocked" from "users" where "users"."email" like ? -- params: [""]
 6 |     rawCode;
 7 |     constructor(message, code, rawCode, cause) {
 8 |         if (code !== undefined) {
 9 |             message = `${code}: ${message}`;
10 |         }
11 |         super(message, { cause });
             ^
LibsqlError: SQLITE_ERROR: no such table: users

Query: select "id", "created_at", "confirmed_email", "confirmed_phone_number", "name", "email", "phone_country", "phone_country_code", "phone_number", "otp", "blocked" from "users" where "users"."email" like ? -- params: [""]
 6 |     rawCode;
 7 |     constructor(message, code, rawCode, cause) {
 8 |         if (code !== undefined) {
 9 |             message = `${code}: ${message}`;
10 |         }
11 |         super(message, { cause });
             ^
LibsqlError: SQLITE_ERROR: no such table: users

@opaldraggy so, the output from this:

      const result = await db.run(sql)

      console.log("result", result.toJSON())

      const result = await db.run(sql)

      console.log("result", result.toJSON())

is:

@app/server:dev: result {
@app/server:dev:   columns: [],
@app/server:dev:   columnTypes: [],
@app/server:dev:   rows: [],
@app/server:dev:   rowsAffected: 0,
@app/server:dev:   lastInsertRowid: "0",
@app/server:dev: }

@app/server:dev: result {
@app/server:dev:   columns: [],
@app/server:dev:   columnTypes: [],
@app/server:dev:   rows: [],
@app/server:dev:   rowsAffected: 0,
@app/server:dev:   lastInsertRowid: "0",
@app/server:dev: }

I'm not sure if DDL statements are included on the output, but it doesn't seem to be failing... so, I'm not sure what's going on

opaldraggy•3w ago

I suspect that you need to split on --> statement-breakpoint and run any non-empty results as separate statements.

Gabriel LucenaOP•3w ago

got it. I'll try running only the users table creation to see what happens

opaldraggy•3w ago

also: drizzle can make it a little hard to track down the actual sql errors; so if you can find a lower-level interface to run the sql, you might get better debugging info (like directly using libsql) Aside: you may want to remove your email from being shared above. Depending on how much you like being spammed by bots that harvest emails from various public places.

Gabriel LucenaOP•3w ago

yep, that's exactly the issue, removing everything but the user table creation from the migration did the trick... now, how can I split those statements into multiple files? or... do you have a better idea how to tackle this problem? duh... I literally just:

sql.split("--> statement-breakpoint").forEach(async (sql) => {
        const result = await db.run(sql)

        console.log("result", result.toJSON())
      })

sql.split("--> statement-breakpoint").forEach(async (sql) => {
        const result = await db.run(sql)

        console.log("result", result.toJSON())
      })

thank you so much for the help @opaldraggy

opaldraggy•3w ago

I recently found out you can also "push" the entire schema to the DB at once instead of running individual migrations -- but I'm not sure whether that's exposed as its own API; and tbh, I personally trust it less. But mentioning in case it's useful.

Gabriel LucenaOP•3w ago

I've seem something like that in here: import { pushSQLiteSchema } from "drizzle-kit/api" but I did not find any docs on that

opaldraggy•3w ago

I think it's ~ documented here: https://orm.drizzle.team/docs/drizzle-kit-push

Drizzle ORM - push

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gabriel LucenaOP•3w ago

I had some issues with just splitting the migration file by --> statement-breakpoint, some table creation scripts are out of order, like the first image (creating first the n:n table than the 1:n table). this is the new alg:

export async function migrate() {
  const dir = path.resolve(__dirname, "migrations")

  const migrations = await readdir(dir)

  for (const migration of migrations) {
    if (migration.endsWith(".sql")) {
      const file = Bun.file(path.resolve(dir, migration))

      const text = await file.text()

      const statements = text.split("--> statement-breakpoint")

      const ddl = []
      const dml = []
      const dependencies = new Map()

      for (const statement of statements) {
        const trimmed = statement.trim()

        if (trimmed.startsWith("CREATE TABLE")) {
          ddl.push(trimmed)

          const table = trimmed.match(/CREATE TABLE `?(\w+)`?/i)?.[1] || ""

          const fks = [
            ...trimmed.matchAll(/FOREIGN KEY.*REFERENCES `?(\w+)`?/gi),
          ].map((match) => match[1])

          dependencies.set(table, fks)
        } else {
          dml.push(trimmed)
        }
      }

      const sorted = [...topologicalSort(ddl, dependencies), ...dml.sort()]

      console.log("inserting", sorted)

      for (const sql of sorted) {
        const trimmedSql = sql.trim()

        if (trimmedSql) {
          try {
            const result = await db.run(trimmedSql)

            console.log(`Executed SQL from ${migration}:`, trimmedSql)
            console.log("Result:", result)
            console.log("=====================================")
          } catch (error) {
            console.error(`Error executing SQL from ${migration}:`, trimmedSql)
            console.error("Error:", error)

            throw error
          }
        }
      }
    }
  }
}

export async function migrate() {
  const dir = path.resolve(__dirname, "migrations")

  const migrations = await readdir(dir)

  for (const migration of migrations) {
    if (migration.endsWith(".sql")) {
      const file = Bun.file(path.resolve(dir, migration))

      const text = await file.text()

      const statements = text.split("--> statement-breakpoint")

      const ddl = []
      const dml = []
      const dependencies = new Map()

      for (const statement of statements) {
        const trimmed = statement.trim()

        if (trimmed.startsWith("CREATE TABLE")) {
          ddl.push(trimmed)

          const table = trimmed.match(/CREATE TABLE `?(\w+)`?/i)?.[1] || ""

          const fks = [
            ...trimmed.matchAll(/FOREIGN KEY.*REFERENCES `?(\w+)`?/gi),
          ].map((match) => match[1])

          dependencies.set(table, fks)
        } else {
          dml.push(trimmed)
        }
      }

      const sorted = [...topologicalSort(ddl, dependencies), ...dml.sort()]

      console.log("inserting", sorted)

      for (const sql of sorted) {
        const trimmedSql = sql.trim()

        if (trimmedSql) {
          try {
            const result = await db.run(trimmedSql)

            console.log(`Executed SQL from ${migration}:`, trimmedSql)
            console.log("Result:", result)
            console.log("=====================================")
          } catch (error) {
            console.error(`Error executing SQL from ${migration}:`, trimmedSql)
            console.error("Error:", error)

            throw error
          }
        }
      }
    }
  }
}

it mostly works, I'm able to insert data into most tables, but even so, there are still some tables that are not being created:

Gabriel LucenaOP•3w ago

migrate() function creating the session table:

Gabriel LucenaOP•3w ago

not sure what to do anymore to fix this, any ideas @opaldraggy?

opaldraggy•3w ago

you can turn off foreign key constraints while you're doing the migration, and turn them back on after. I believe that's what some of the migration batching scripts do in drizzle. If you cant find an implementation that's exposed publicly by drizzle itself, try:

PRAGMA foreign_keys = OFF;

PRAGMA foreign_keys = OFF;

before your migrations, and then:

PRAGMA foreign_keys = ON;

PRAGMA foreign_keys = ON;

after.

Gaming

Programming

Running SQL statement manually for in-memory database