energistic Comments - Answer Overflow

@Louis I suggest using env variables with dot-env: https://www.npmjs.com/package/dotenv#-deploying I also have a script that creates a local proxy to my prod db, then I run my migrations locally against the prod db

5 replies

DTDrizzle Team

•Created by pedro77 on 12/19/2023 in #help

Can I use drizzle + mysql2 driver + middleware.ts (next.js)?

You can use that driver: https://orm.drizzle.team/docs/quick-mysql/planetscale and edge is possible too: https://orm.drizzle.team/docs/perf-serverless

4 replies

DTDrizzle Team

•Created by pdina on 5/18/2023 in #help

Create GIN index in Postgres

I wrote a little script to remove the quotes and add a GIN index

import fs from 'node:fs/promises'
import path from 'node:path'

const migrationsFolder = path.join('.', 'drizzle', 'migrations')
const files = await fs.readdir(migrationsFolder)
const sqlFiles = files
    .filter(file => file.endsWith('.sql'))
    .map(file => path.join(migrationsFolder, file))

for (const file of sqlFiles) {
    const fileContents = await fs.readFile(file, 'utf8')

    const tablesWithRawGins = findTableNameWithVectorText(fileContents)

    const gins = tablesWithRawGins
        .map(
            tableName =>
                `CREATE INDEX content_search_idx ON ${tableName} USING GIN (vector_text);`,
        )
        .join('\n')

    const regex =
        /"vector_text" "tsvector GENERATED ALWAYS AS \(to_tsvector\('english', name\)\) STORED"/g
    const newFileContents = fileContents.replace(
        regex,
        `"vector_text" tsvector GENERATED ALWAYS AS (to_tsvector('english', name)) STORED`,
    )
    await fs.writeFile(file, newFileContents + '\n' + gins)
}

function findTableNameWithVectorText(input: string): string[] {
    const lines = input.split('\n')
    let tableName: string | null = null
    let foundTableNames = []

    for (let i = 0; i < lines.length; i++) {
        const line = lines[i].trim()

        // Check for CREATE TABLE line and capture table name
        if (line.startsWith('CREATE TABLE IF NOT EXISTS') && line.endsWith('(')) {
            const match = line.match(/"([^"]+)"/)
            if (match) {
                tableName = match[1]
            }
        }

        // Check for vector_text line
        if (
            line ===
            '"vector_text" "tsvector GENERATED ALWAYS AS (to_tsvector(\'english\', name)) STORED"'
        ) {
            foundTableNames.push(tableName as string)
        }
    }

    return foundTableNames
}

import fs from 'node:fs/promises'
import path from 'node:path'

const migrationsFolder = path.join('.', 'drizzle', 'migrations')
const files = await fs.readdir(migrationsFolder)
const sqlFiles = files
    .filter(file => file.endsWith('.sql'))
    .map(file => path.join(migrationsFolder, file))

for (const file of sqlFiles) {
    const fileContents = await fs.readFile(file, 'utf8')

    const tablesWithRawGins = findTableNameWithVectorText(fileContents)

    const gins = tablesWithRawGins
        .map(
            tableName =>
                `CREATE INDEX content_search_idx ON ${tableName} USING GIN (vector_text);`,
        )
        .join('\n')

    const regex =
        /"vector_text" "tsvector GENERATED ALWAYS AS \(to_tsvector\('english', name\)\) STORED"/g
    const newFileContents = fileContents.replace(
        regex,
        `"vector_text" tsvector GENERATED ALWAYS AS (to_tsvector('english', name)) STORED`,
    )
    await fs.writeFile(file, newFileContents + '\n' + gins)
}

function findTableNameWithVectorText(input: string): string[] {
    const lines = input.split('\n')
    let tableName: string | null = null
    let foundTableNames = []

    for (let i = 0; i < lines.length; i++) {
        const line = lines[i].trim()

        // Check for CREATE TABLE line and capture table name
        if (line.startsWith('CREATE TABLE IF NOT EXISTS') && line.endsWith('(')) {
            const match = line.match(/"([^"]+)"/)
            if (match) {
                tableName = match[1]
            }
        }

        // Check for vector_text line
        if (
            line ===
            '"vector_text" "tsvector GENERATED ALWAYS AS (to_tsvector(\'english\', name)) STORED"'
        ) {
            foundTableNames.push(tableName as string)
        }
    }

    return foundTableNames
}

10 replies

DTDrizzle Team

•Created by Bardia on 12/11/2023 in #help

Migration timing out in Next.js Route

here's my migration file:

import 'dotenv/config'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import postgres from 'postgres'
import { db } from './db2.server.ts'
export const connection = postgres(process.env.DATABASE_URL)
// This will run migrations on the database, skipping the ones already applied
await migrate(db, { migrationsFolder: './drizzle/migrations' })
connection.end()
process.exit(0)

import 'dotenv/config'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import postgres from 'postgres'
import { db } from './db2.server.ts'
export const connection = postgres(process.env.DATABASE_URL)
// This will run migrations on the database, skipping the ones already applied
await migrate(db, { migrationsFolder: './drizzle/migrations' })
connection.end()
process.exit(0)

5 replies

DTDrizzle Team

•Created by Wabathur on 12/10/2023 in #help

[pg] How do I make subquery in select()?

Maybe use with? https://orm.drizzle.team/docs/select/#with-clause

5 replies

DTDrizzle Team

•Created by Bardia on 12/11/2023 in #help

Migration timing out in Next.js Route

I find my migrations succeed but never really end. I added a process.exit(0) at the end and that seems to work. I also close the db connection but not sure if that's really necessary

5 replies

Gaming

Programming