How to setup a DB connection in a server plugin

Question

I want to setup a connection to a PostgreDB in a server plugin so that I can use it whenever needed (I use pg-promise because I find named parameters more convenient).

In ~/server/plugins/connectdb.ts:

import pgPromise from 'pg-promise'
     
export default defineNitroPlugin(() => {
    console.log('Initializing DB connection')
    const pgp = pgPromise({})
    try {       
        const db = pgp('postgres://user:pwd@localhost:5432/mydb')
    } catch (error) {
        console.error("Error: "+error);
        return process.exit(1) 
    }
})

Then in ~/api/getlist.ts:

export default defineEventHandler((event) => {
    console.log(db);
})

But here db is unknown. If I run a query just after the initialization, I can see the connection in Postgres' logs. I tried adding:

export let db;

between import and export default (and change const db to let db in the code) but that doesn't change anything. I guess what I want is kind of a global variable to store the connection.

Answer 1

Using a server utility

I defined a server utility and it works (but better solution below):

~/server/utils/dbconnect.js

import pgPromise from 'pg-promise'

const pgp = pgPromise({})
export let usePg = pgp('postgres://user:pwd@localhost:5432/mydb')

Then the auto-import feature does its magic and usePg is available everywhere.

---

Using a server plugin

It is also possible (and preferable as it seems) to do it in a server plugin, extending the Nitro context:

in /server/plugins/dbconnect.ts:

import pgPromise from 'pg-promise'

console.log('Init from server plugin');

export default defineNitroPlugin((nitroApp) => {
    const pgp = pgPromise({/* Initialization Options */})
    const db = pgp('postgres://user:pwd@localhost:5432/mydb')
    nitroApp.hooks.hook('request', (event) => {
        event.context.$pg = db
    })
})

in /server/api/whatever.ts:

export default defineEventHandler(async event => {
    const pg = event.context.$pg

    const res = await pg.one(`
        SELECT id
        FROM passwd
        WHERE id=$(id)
        `,{id: 12})
    console.log(res);
    return res;
})