Drizzle Team•4mo ago

What's the best way to query my db?

I'm running Supabase Postgres locally with Drizzle. In production, I'd use the connection pooler which would ensure I don't have too many concurrent connections. I can't locally however and Supabase often gives this error: PostgresError: remaining connection slots are reserved for non-replication superuser connections. I'm initiating the db as such:

import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import * as schema from "../schemas/drizzleSchema"

export default function db() {
    const connectionString = process.env.DATABASE_URL

    if (!connectionString) {
        throw new Error("LOCAL_DATABASE_URL is undefined")
    }

    const client = postgres(connectionString)

    return drizzle(client, { schema })
}

import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import * as schema from "../schemas/drizzleSchema"

export default function db() {
    const connectionString = process.env.DATABASE_URL

    if (!connectionString) {
        throw new Error("LOCAL_DATABASE_URL is undefined")
    }

    const client = postgres(connectionString)

    return drizzle(client, { schema })
}

And then in the necessary functions I import db and call it, like so: const database = db(). I have many helper functions which each create a db connection for the query, for example:

export async function getPostById(postId: number) {
    try {
        const database = db()
        return await database.query.posts.findFirst({ where: eq(posts.id, postId) })
    } catch (err) {
        error(400, "Couldn't get post")
    }
}

export async function getPostById(postId: number) {
    try {
        const database = db()
        return await database.query.posts.findFirst({ where: eq(posts.id, postId) })
    } catch (err) {
        error(400, "Couldn't get post")
    }
}

Is this the right way of doing things? Or should I pass the database to the helper functions, instead of initiating a connection there? I want to ensure I'm not creating unnecessary connections to the DB. Are there any best practices I should be aware of? Thanks!

3 Replies

Brucey•4mo ago

Bumping this, thank you.

Huilen•4mo ago

hi i think a client is in the case youre doing something with a socket idk that many about dbs so dont tame for serious but i believe a pool is better

Brucey•4mo ago

Thanks -- I suspect the same.

Gaming

Programming

What's the best way to query my db?