Drizzle Team•8mo ago

`no such table: user` in production with drizzle + sqlite + next.js?

i have 2 sqlite databases: 1. example.dev.sqlite - for development 2. example.prod.sqlite - for production i know how to use drizzle in development but i'm having a problem in production. in development, i do pnpm db:generate to generate migrations & pnpm db:push to create tables. what should i do in production to generate tables? do i have to run the same pnpm db:push script? my sqlite file is in docker swarm so the location is a bit different. my drizzle.config.ts file looks like:

import * as dotenv from "dotenv"
import type { Config } from "drizzle-kit"
// bug in drizzle: see https://github.com/drizzle-team/drizzle-orm/issues/1228
// import { DATABASE_PATH } from '@/app/lib/env/server'

dotenv.config({ path: ".env.development" })

const DATABASE_PATH = process.env.DATABASE_PATH

export default {
    schema: "./src/app/db/schema.ts",
    out: "./src/app/db/migrations",
    driver: "better-sqlite",
    dbCredentials: {
        url: DATABASE_PATH,
    },
    verbose: true,
} satisfies Config

import * as dotenv from "dotenv"
import type { Config } from "drizzle-kit"
// bug in drizzle: see https://github.com/drizzle-team/drizzle-orm/issues/1228
// import { DATABASE_PATH } from '@/app/lib/env/server'

dotenv.config({ path: ".env.development" })

const DATABASE_PATH = process.env.DATABASE_PATH

export default {
    schema: "./src/app/db/schema.ts",
    out: "./src/app/db/migrations",
    driver: "better-sqlite",
    dbCredentials: {
        url: DATABASE_PATH,
    },
    verbose: true,
} satisfies Config

would i need to change DATABASE_PATH. im confused here as i am unable to get it to work in production? my goal is to simply get the production sqlite database working. if anyone has done it before, lmk what's the process? google didn't help much.

3 Replies

Startup Spells 🪄 Newsletter Guy•8mo ago

alright, i was able to make it work through my code search skills. i searched for context:global migrate AND drizzle lang:TypeScript -repo:^github\.com/drizzle-team/drizzle-orm$ on https://sourcegraph.com this repo answered it -> https://sourcegraph.com/github.com/ixartz/Next-js-Boilerplate@9845272ef4ea729f0101e035262b4be8c0082303/-/blob/scripts/dbMigrate.ts i've only managed to get it working locally on production build ,i.e, next build & next start (which builds for production in case you don't use next.js) but i think i can manage it on vps too. so i made a scripts/drizzle-migrate.ts file:

import sqlite from "better-sqlite3"
import consola from "consola"
import { drizzle } from "drizzle-orm/better-sqlite3"
import { migrate } from "drizzle-orm/better-sqlite3/migrator"
import path from "node:path"

async function main() {
    const client = sqlite(process.env.DATABASE_PATH)
    const db = drizzle(client)

    consola.info("Running migrations")
    migrate(db, { migrationsFolder: path.join(__dirname, "..", "/app/db/migrations") })
    consola.success("Migrated successfully")
    process.exit(0)
}

main().catch((e) => {
    consola.error("Migration failed")
    consola.error(e)
    process.exit(1)
})

import sqlite from "better-sqlite3"
import consola from "consola"
import { drizzle } from "drizzle-orm/better-sqlite3"
import { migrate } from "drizzle-orm/better-sqlite3/migrator"
import path from "node:path"

async function main() {
    const client = sqlite(process.env.DATABASE_PATH)
    const db = drizzle(client)

    consola.info("Running migrations")
    migrate(db, { migrationsFolder: path.join(__dirname, "..", "/app/db/migrations") })
    consola.success("Migrated successfully")
    process.exit(0)
}

main().catch((e) => {
    consola.error("Migration failed")
    consola.error(e)
    process.exit(1)
})

i added that to my package.json:

"dev": "npm run db:migrate:dev && next dev",
"build": "npm run db:migrate:prod && next build",
"db:migrate:dev": "node --import tsx --env-file .env.development ./src/scripts/drizzle-migrate.ts",
"db:migrate:prod": "node --import tsx --env-file .env.production ./src/scripts/drizzle-migrate.ts",

"dev": "npm run db:migrate:dev && next dev",
"build": "npm run db:migrate:prod && next build",
"db:migrate:dev": "node --import tsx --env-file .env.development ./src/scripts/drizzle-migrate.ts",
"db:migrate:prod": "node --import tsx --env-file .env.production ./src/scripts/drizzle-migrate.ts",

in development, i run db:migrate:dev script & i run db:migrate:prod in production. maybe i can consolidate it with NODE_ENV but then i'll have to hardcode database name in code. one way i can think of is using:

const db = process.env.DATABASE_NAME
const client = sqlite(process.env.NODE_ENV === 'development' ? `${db}.dev.sqlite` : `${db}.prod.sqlite`)

const db = process.env.DATABASE_NAME
const client = sqlite(process.env.NODE_ENV === 'development' ? `${db}.dev.sqlite` : `${db}.prod.sqlite`)

Sourcegraph

Sourcegraph | Code Intelligence Platform

Sourcegraph’s code intelligence platform makes it easy for devs to write, fix, and maintain code with Cody, the AI coding assistant, and Code Search.

dbMigrate.ts

Startup Spells 🪄 Newsletter Guy•8mo ago

i tried this method:

const db = process.env.DATABASE_NAME
const client = sqlite(process.env.NODE_ENV === 'development' ? `${db}.dev.sqlite` : `${db}.prod.sqlite`)

const db = process.env.DATABASE_NAME
const client = sqlite(process.env.NODE_ENV === 'development' ? `${db}.dev.sqlite` : `${db}.prod.sqlite`)

but it was not better than my original answer. here's my scripts:

"scripts": {
    "dev": "npm run db:migrate:dev && next dev",
    "turbo": "next dev --turbo",
    "build": "npm run db:migrate:prod && next build",
    "start": "next start",
    "lint": "next lint",
    "clean": "rimraf .next",
    "db:push": "drizzle-kit push:sqlite --config drizzle.config.ts",
    "db:generate": "drizzle-kit generate:sqlite --config drizzle.config.ts",
    "db:migrate:dev": "node --import tsx --env-file .env.development ./src/scripts/drizzle-migrate.ts",
    "db:migrate:prod": "node --import tsx --env-file .env.production ./src/scripts/drizzle-migrate.ts",
    "db:studio": "drizzle-kit studio --host localhost --port 3002 --verbose --config drizzle.config.ts",
    "db:seed": "node --import tsx --env-file .env.development ./src/app/seed/insert.ts",
    "db:delete": "node --import tsx --env-file .env.development ./src/app/seed/delete.ts",
    "rate-limit:login": "tsx ./src/scripts/rate-limit-login.ts",
    "redis:getall": "node --import tsx --env-file .env.development ./src/scripts/redis-get-all.ts",
}

"scripts": {
    "dev": "npm run db:migrate:dev && next dev",
    "turbo": "next dev --turbo",
    "build": "npm run db:migrate:prod && next build",
    "start": "next start",
    "lint": "next lint",
    "clean": "rimraf .next",
    "db:push": "drizzle-kit push:sqlite --config drizzle.config.ts",
    "db:generate": "drizzle-kit generate:sqlite --config drizzle.config.ts",
    "db:migrate:dev": "node --import tsx --env-file .env.development ./src/scripts/drizzle-migrate.ts",
    "db:migrate:prod": "node --import tsx --env-file .env.production ./src/scripts/drizzle-migrate.ts",
    "db:studio": "drizzle-kit studio --host localhost --port 3002 --verbose --config drizzle.config.ts",
    "db:seed": "node --import tsx --env-file .env.development ./src/app/seed/insert.ts",
    "db:delete": "node --import tsx --env-file .env.development ./src/app/seed/delete.ts",
    "rate-limit:login": "tsx ./src/scripts/rate-limit-login.ts",
    "redis:getall": "node --import tsx --env-file .env.development ./src/scripts/redis-get-all.ts",
}

and some explanation i wrote: 1. pnpm db:generate (dev) - Generate src/app/db/migrations 2. pnpm db:push (dev) - Pushes the schema to database & creates example.dev.sqlite file. Only use it when developing schema locally as it is never to be used in production. 3. pmpm db:migrate (dev/prod) - Works in both environments 4. pnpm dev - Runs pnpm db:migrate with NODE_ENV=development 5. pnpm build - Runs pnpm db:migrate with NODE_ENV=production Any script that contains drizzle.config.ts file is only for development. drizzle.config.ts

import * as dotenv from "dotenv"
import type { Config } from "drizzle-kit"

// bug in drizzle: see https://github.com/drizzle-team/drizzle-orm/issues/1228
// import { DATABASE_PATH } from '@/app/lib/env/server'

dotenv.config({ path: ".env.development" })

export default {
    schema: "./src/app/db/schema.ts",
    out: "./src/app/db/migrations",
    driver: "better-sqlite",
    dbCredentials: {
        url: process.env.DATABASE_PATH,
    },
    verbose: true,
} satisfies Config

import * as dotenv from "dotenv"
import type { Config } from "drizzle-kit"

// bug in drizzle: see https://github.com/drizzle-team/drizzle-orm/issues/1228
// import { DATABASE_PATH } from '@/app/lib/env/server'

dotenv.config({ path: ".env.development" })

export default {
    schema: "./src/app/db/schema.ts",
    out: "./src/app/db/migrations",
    driver: "better-sqlite",
    dbCredentials: {
        url: process.env.DATABASE_PATH,
    },
    verbose: true,
} satisfies Config

scripts/drizzle-migrate.ts

import sqlite from "better-sqlite3"
import consola from "consola"
import { drizzle } from "drizzle-orm/better-sqlite3"
import { migrate } from "drizzle-orm/better-sqlite3/migrator"
import path from "node:path"

async function main() {
    const client = sqlite(process.env.DATABASE_PATH)
    const db = drizzle(client)

    consola.info("Running migrations")
    migrate(db, { migrationsFolder: path.join(__dirname, "..", "/app/db/migrations") })
    consola.success("Migrated successfully")
    process.exit(0)
}

main().catch((e) => {
    consola.error("Migration failed")
    consola.error(e)
    process.exit(1)
})

import sqlite from "better-sqlite3"
import consola from "consola"
import { drizzle } from "drizzle-orm/better-sqlite3"
import { migrate } from "drizzle-orm/better-sqlite3/migrator"
import path from "node:path"

async function main() {
    const client = sqlite(process.env.DATABASE_PATH)
    const db = drizzle(client)

    consola.info("Running migrations")
    migrate(db, { migrationsFolder: path.join(__dirname, "..", "/app/db/migrations") })
    consola.success("Migrated successfully")
    process.exit(0)
}

main().catch((e) => {
    consola.error("Migration failed")
    consola.error(e)
    process.exit(1)
})

@Andrew Sherman i think this should be in the docs of running sqlite in development & production with drizzle orm. lmk if i made any mistakes.

Startup Spells 🪄 Newsletter Guy•7mo ago

better solution using docker -> https://stackoverflow.com/a/78034626/6141587

Stack Overflow

Cannot find package 'drizzle-orm' in Docker Container even though I...

I install all my dependencies in Dockerfile using npm ci below. Dockerfile FROM node:20-alpine AS base 1. Install dependencies only when needed FROM base AS deps Check https://github.com/nodejs/

Gaming

Programming

`no such table: user` in production with drizzle + sqlite + next.js?