Drizzle Team•9mo ago

Close SQLite connection in Next.js API routes for `next build` command to work?

So I got Database is locked error when I'm doing next build like this when I'm building docker image. the command underneath is next build which builds for production. this command calls all api routes:

46.40 ahoy!!
46.43 ahoy!!
46.46 ahoy!!
46.47 SqliteError: database is locked

46.40 ahoy!!
46.43 ahoy!!
46.46 ahoy!!
46.47 SqliteError: database is locked

my file is:

import sqlite from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import { isProduction } from 'std-env'

import { env } from '@/app/lib/env'

console.log(`ahoy!!`)

const url = isProduction
  ? `/data/${env.SQLITE_DATABASE_NAME}`
  : `${env.SQLITE_DATABASE_NAME}`

const client = sqlite(url)
client.pragma('journal_mode = WAL') // see https://github.com/WiseLibs/better-sqlite3/blob/master/docs/performance.md

export const db = drizzle(client)

import sqlite from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import { isProduction } from 'std-env'

import { env } from '@/app/lib/env'

console.log(`ahoy!!`)

const url = isProduction
  ? `/data/${env.SQLITE_DATABASE_NAME}`
  : `${env.SQLITE_DATABASE_NAME}`

const client = sqlite(url)
client.pragma('journal_mode = WAL') // see https://github.com/WiseLibs/better-sqlite3/blob/master/docs/performance.md

export const db = drizzle(client)

how do i solve this? there is a .close() on client but not on db? there's a singleton pattern which i haven't tried for sqlite. what would you do? i believe this should work & it does locally at least but not with docker. the problem is it sometimes builds perfectly & randomly fails when i delete previously build. so its hard to debug.

1 Reply

Startup Spells 🪄 Newsletter Guy•9mo ago

i think i managed to fix this issue i guess. its either because of me using default export export default db (i use this now) instead of named export export const db like above or singleton pattern using https://github.com/epicweb-dev/remember i'll have to debug but this essentially solved it for my docker version.

import sqlite from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import { isProduction } from 'std-env'
import { remember } from '@epic-web/remember'

import { env } from '@/app/lib/env'

console.log(`ahoy!!`)

const url = isProduction
  ? `/data/${env.SQLITE_DATABASE_NAME}`
  : `${env.SQLITE_DATABASE_NAME}`

const db = remember('drizzle', () => {
  const client = sqlite(url, { verbose: console.log })
  client.pragma('journal_mode = WAL') // see https://github.com/WiseLibs/better-sqlite3/blob/master/docs/performance.md
  return drizzle(client)
})

export default db

import sqlite from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import { isProduction } from 'std-env'
import { remember } from '@epic-web/remember'

import { env } from '@/app/lib/env'

console.log(`ahoy!!`)

const url = isProduction
  ? `/data/${env.SQLITE_DATABASE_NAME}`
  : `${env.SQLITE_DATABASE_NAME}`

const db = remember('drizzle', () => {
  const client = sqlite(url, { verbose: console.log })
  client.pragma('journal_mode = WAL') // see https://github.com/WiseLibs/better-sqlite3/blob/master/docs/performance.md
  return drizzle(client)
})

export default db

i'll push the whole working code on https://github.com/deadcoder0904/easypanel-nextjs-sqlite soon my current problem is only to fix the drizzle migrations copying in the dockerfile which is a big hard to debug even with docker exec -it 12141 sh i removed export default db & @epic-web/remember and it still runs fine. i guess the issue was something else entirely but you can find it on my repo.

Gaming

Programming

Close SQLite connection in Next.js API routes for `next build` command to work?