Startup Spells 🪄 Newsletter Guy Posts

Startup Spells 🪄 Newsletter Guy

•Created by Startup Spells 🪄 Newsletter Guy on 3/22/2024 in #help

How to set timestamp for `created_at` in Drizzle on SQLite?

I found SQLite using https://stackoverflow.com/questions/14461851/how-to-have-an-automatic-timestamp-in-sqlite but not using Drizzle

2 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 2/14/2024 in #help

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.

3 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 2/13/2024 in #help

SQLite Error: Database is locked with Docker + Next.js + Drizzle

My docker-compose.yml is:

version: '3'

services:
  docker-production:
    build:
      context: .
      dockerfile: Dockerfile
    image: docker-production
    env_file:
      - .env.production
    ports:
      - '3001:3000'
    volumes:
      - ./data:/data

version: '3'

services:
  docker-production:
    build:
      context: .
      dockerfile: Dockerfile
    image: docker-production
    env_file:
      - .env.production
    ports:
      - '3001:3000'
    volumes:
      - ./data:/data

my Dockerfile is huge but the relevant bits are:

RUN mkdir -p /data
RUN npm run db:generate
RUN npm run db:migrate:vps
RUN npm run build

RUN mkdir -p /data
RUN npm run db:generate
RUN npm run db:migrate:vps
RUN npm run build

i do it in production just like i do it in development. i don't think i need migrate at all in production so i sometimes comment it out. i probably need to copy migrations folder into production i guess. can someone correct me here? anyhow, i get database locked error at the npm run build command that next.js needs to build production builds. i've detailed my question on stackoverflow as discord doesn't allow long-form code without going over character limit. https://stackoverflow.com/q/77986435/6141587 please take a look if you got sqlite + drizzle working on docker? there are few resources on this. many are complicated af.

1 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 2/8/2024 in #help

`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.

7 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 1/25/2024 in #help

How to run generate & migrate in production on a VPS for SQLite Database?

So I have a database in development that I run db:generate & db:migrate on to create the database & then run my app that uses it. But how do I do that in production when I can't run direct commands. I'm using https://easypanel.io on a self-hosted VPS. Do I just use pre-scripts or post-scripts? What is the recommended way to go about it?

5 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 7/20/2023 in #help

Confused about Relationships in Drizzle?

i have relationships like this in https://lucia-auth.com

users -> sessions, keys, email_verification_tokens (1:many)

sessions, keys, email_verification_tokens -> users (many:1)

users -> sessions, keys, email_verification_tokens (1:many)

sessions, keys, email_verification_tokens -> users (many:1)

and my code in drizzle looks like:

export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  keys: many(keys),
  emailVerificationTokens: many(emailVerificationTokens),
}))

export const sessionsRelations = relations(sessions, ({ one }) => ({
  users: one(users),
}))

export const keysRelations = relations(keys, ({ one }) => ({
  users: one(users),
}))

export const emailVerificationTokensRelations = relations(
  emailVerificationTokens,
  ({ one }) => ({
    users: one(users),
  })
)

export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  keys: many(keys),
  emailVerificationTokens: many(emailVerificationTokens),
}))

export const sessionsRelations = relations(sessions, ({ one }) => ({
  users: one(users),
}))

export const keysRelations = relations(keys, ({ one }) => ({
  users: one(users),
}))

export const emailVerificationTokensRelations = relations(
  emailVerificationTokens,
  ({ one }) => ({
    users: one(users),
  })
)

i would love to know if it's correct or wrong? idk coding relationships was a bit confusing in drizzle.

11 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 6/22/2023 in #help

Drizzle Kit Push & Generate gives weird error

I get the following error:

{
      detail: undefined,
      id: '',
      location: [Object],
      notes: [],
      pluginName: '',
      text: 'Transforming destructuring to the configured target environment ("es5") is not supported yet'
    }

{
      detail: undefined,
      id: '',
      location: [Object],
      notes: [],
      pluginName: '',
      text: 'Transforming destructuring to the configured target environment ("es5") is not supported yet'
    }

the full code is here -> https://github.com/deadcoder0904/next-13-lucia-auth-drizzle-turso-sqlite-magic-link/tree/main pnpm db:push & pnpm db:generate don't work for some reason.

5 replies

DTDrizzle Team

•Created by Startup Spells 🪄 Newsletter Guy on 6/21/2023 in #help

Unable to delete all rows from SQLite using Transactions?

i have a route api/delete-all that looks like:

import { NextResponse } from 'next/server'

import { users, sessions, keys, emailVerificationTokens } from '@/app/db/schema'
import { db } from '@/app/db/index'

export const DELETE = async () => {
  console.log('🏁 /api/delete')
  try {
    await db.transaction(async (tx) => {
      const deletedUsers = tx.delete(users)
      const deletedSessions = tx.delete(sessions)
      const deletedKeys = tx.delete(keys)
      const deletedEmailVerificationTokens = tx.delete(emailVerificationTokens)

      console.log({
        deletedUsers,
        deletedSessions,
        deletedKeys,
        deletedEmailVerificationTokens,
      })
    })
  } catch (error) {
    return NextResponse.json({ error, success: false })
  }
  return NextResponse.json({ success: true })
}

import { NextResponse } from 'next/server'

import { users, sessions, keys, emailVerificationTokens } from '@/app/db/schema'
import { db } from '@/app/db/index'

export const DELETE = async () => {
  console.log('🏁 /api/delete')
  try {
    await db.transaction(async (tx) => {
      const deletedUsers = tx.delete(users)
      const deletedSessions = tx.delete(sessions)
      const deletedKeys = tx.delete(keys)
      const deletedEmailVerificationTokens = tx.delete(emailVerificationTokens)

      console.log({
        deletedUsers,
        deletedSessions,
        deletedKeys,
        deletedEmailVerificationTokens,
      })
    })
  } catch (error) {
    return NextResponse.json({ error, success: false })
  }
  return NextResponse.json({ success: true })
}

i want to delete all values but i get a SQLITE_CONSTRAINT_ERROR:

error: {code: "SQLITE_CONSTRAINT", name: "LibsqlError"}

error: {code: "SQLITE_CONSTRAINT", name: "LibsqlError"}

how do i delete all rows in a table? i tried without transactions as well as using await tx.delete(users).all() but it doesn't delete from the database. i'm using https://turso.tech for sqlite on the edge.

3 replies

Gaming

Programming