Drizzle Team•12mo ago

How can I close connections after a query is done

When Hot-Reloading I get a bunch of extra connections, and eventually I run out of connections. This is on dev btw This may not be cause by hot reload, i am unsure when running SELECT * FROM pg_stat_activity; in Supabase I see a bunch of connections that are running the same queries all from postgres.js even after the api call is done. The connections are then stuck on idle

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

// Define the type for the drizzle database instance
type PostgresJsDatabase = ReturnType<typeof drizzle> & {
    // Include the types or methods that drizzle provides, based on your schema
}

// Initialize the database connection
function initialize(): PostgresJsDatabase {
    if (!process.env.DATABASE_URL) {
        throw new Error('DATABASE_URL not set')
    }

    const connectionString = process.env.DATABASE_URL
    const sql = postgres(connectionString)
    return drizzle(sql, { schema }) as PostgresJsDatabase
}

// Singleton instance
let dbInstance: PostgresJsDatabase | null = null

// Singleton accessor function
function singleton(): PostgresJsDatabase {
    if (!dbInstance) {
        dbInstance = initialize()
    }
    return dbInstance
}

// Use the singleton pattern for both development and production
const db = singleton()

export default db

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

// Define the type for the drizzle database instance
type PostgresJsDatabase = ReturnType<typeof drizzle> & {
    // Include the types or methods that drizzle provides, based on your schema
}

// Initialize the database connection
function initialize(): PostgresJsDatabase {
    if (!process.env.DATABASE_URL) {
        throw new Error('DATABASE_URL not set')
    }

    const connectionString = process.env.DATABASE_URL
    const sql = postgres(connectionString)
    return drizzle(sql, { schema }) as PostgresJsDatabase
}

// Singleton instance
let dbInstance: PostgresJsDatabase | null = null

// Singleton accessor function
function singleton(): PostgresJsDatabase {
    if (!dbInstance) {
        dbInstance = initialize()
    }
    return dbInstance
}

// Use the singleton pattern for both development and production
const db = singleton()

export default db

3 Replies

Angelelz•12mo ago

Hot reloading causes this issue. The singleton is the solution Doesn't the function postgres(connectionString) returns a pool? In that case it's normal to have idle connections

zamachnoiOP•12mo ago

oh alright so if i just use the pool it should be good?

Angelelz•12mo ago

That is my understanding, it has several connections available to make your queries start faster

Gaming

Programming

How can I close connections after a query is done