What's the best way to query my db?

BBrucey4/2/2024
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! Bumping this, thank you.
HHuilen4/2/2024
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
BBrucey4/8/2024
Thanks -- I suspect the same.

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
How to query many-many with mysqlI setup a simple example to test if many-many query and ran again into the same issue. ```ts Error:How do I access config in fromDriver and toDriver when defining a custom type using customType?None of the examples do this so it's not clear how/if this is possible?Cannot get user roleI'm using Next.js with next auth and the drizzle adapter. I must be doing something wrong because th`drizzle-kit generate:sqlite` doesn't work`lib/drizzle.ts` ```ts import { text, blob, sqliteTable } from "drizzle-orm/sqlite-core"; export coStudio not enough info to infer relationI'm getting this error: ``` throw new Error( ^ Error: There is not enough information toTurso DB reads (alot)Using turso for a project with drizzle ( I usually use mysql ), and i'm getting way more database reAppending to array OnConflictUpdate()? (Postgres)**full question on stack overflow: **https://stackoverflow.com/questions/78246191/error-appending-toTypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys')I'm getting this error when I want to rename a table (there are no other changes). Old table: ``` ebetter way to check truthy / falsey from selectHey all, currently checking if the length > 0 and <1 to check truthy / falsey is there a better way Relation join conditionsHey - let's say I have the following SQL: ```sql SELECT blocks.id, blocks.type FROM inHow do I use the sql operator with better-sqlite3?Hello. I'm trying to build a custom query with the sql operator in drizzle-orm, and to run it againsIs there a way in drizzle to return the count before limit and offset?i have a table that i need to limit and offset that also needs to return the count. is there a way torder by dynamic columi have a table and i want the user to be able to order by different columns. how do i write the drizDynamic query columns projectionHey, i'm trying to do a dynamic selection of columns in my queries, having before hand an array of tUnable to use drizzle-orm migrator with drizzle-kitSo. I'm running into something pretty wild. I've got a bunch of migrations generated by dizzle kit, How to use findMany?I'm using supabase. I'm going to show a sample which doesnt necessarily need findMany to work but I