Establish database connection in server middleware

Question

I'm learning to use Nuxt, and currently trying nuxt-bridge which comes with most of Nuxt3 functions, while keeping nuxt-auth-next module compatibily which I need. As of now I'm working on my API, using Nuxt3 /server/api and /server/middleware directories. Everything runs on nuxi/nitro.

This is a small example of API route (/server/api/me.get.ts : gets user info from JWT token, code has been simplified here) :

// /server/api/me.get.ts
import mysql, { RowDataPacket } from 'mysql2/promise'
import { defineEventHandler, getRequestHeader } from 'h3' // Needed since nuxt-bridge wont auto import dependencies in /server/...
import { useRuntimeConfig } from '#imports' // fails but code still works... ESM absolute path needed

export default defineEventHandler(async (event) => {
  const config = useRuntimeConfig()

  try {
    const db = await mysql.createPool(config.mysql)
    // ... Core route logic : getting user info from token and sending it back
  } catch (e) {
    // Managing error
}
})

This actually works as intended. My problem is that i'm using the same code for establishing MySQL connection in every route (login.post.ts, register.post.ts...) which is redundent and not quite elegant. I would then like to use a serverMiddleware to establish the connection for every route. So first of all, is this correct practice/use of serverMiddleware ?

Then, I have trouble finding how to properly achieve it. I created a /server/middleware/database.ts which is fired every time an API call is made. But I have no clue how to establish mysql connection from it and passing it to the actual called route. I tried fiddling with request/response as follow (seen in a tutorial) :

// /server/middleware/database.ts
import type { IncomingMessage, ServerResponse } from 'http'
import mysql from 'mysql2/promise'
import { defineHandler } from 'h3' // Needed since nuxt-bridge wont auto import dependencies
import { useRuntimeConfig } from '#imports' // fails but code still works... ESM absolute path needed

export default defineHandler(
  async (req: IncomingMessage, res: ServerResponse) => {
    const config = useRuntimeConfig()

    try {
      req['db'] = await mysql.createPool(config.mysql)
    } catch (e) {
      console.log('error')
    }
  }
)

And then using it like so :

// /server/api/test.get.ts
import type { IncomingMessage, ServerResponse } from 'http'
import { defineHandler } from 'h3' // Needed since nuxt-bridge wont auto import dependencies
import { useRuntimeConfig } from '#imports' // fails but code still works... ESM absolute path needed

export default defineHandler(
  async (req: IncomingMessage, res: ServerResponse) => {
    const [test] = req['db'].query('SELECT * FROM users')
    // Core logic
  }
)

But it does not work. I'm stuck :). Please note that it's been quite some time since I used Javascript and that it's my first steps en Typescript.

Any help would be greatly appreciated.

Almatadema

Answer 1

Inside the server folder you will create a folder called plugins In the plugins folder, create a file with the code below

//server/plugins/connectDb.js

export default defineNitroPlugin(() => {

  //your code...

})

https://nuxt.com/docs/guide/directory-structure/server#server-plugins

Here is a more detailed example

// sever/db/index.js
import { drizzle } from "drizzle-orm/node-postgres";
import Client from "pg/lib/client.js";

const client = new Client({
  host: "127.0.0.1",
  port: 5432,
  user: "postgres",
  password: "1111",
  database: "abc",
});

let db;
const connectDB = async () => {
  if (db) return db;
  try {
    await client.connect();
    console.log("Connected successfully to server");
    db = drizzle(client);
    return db;
  } catch (error) {
    console.error(error);
  }
}

export { connectDB, db }

// server/plugins/connectDb.js
import { connectDB } from 'server/db/index.js';

export default defineNitroPlugin(async (nitroApp) => {
  await connectDB();
})

Bakke

But how do you make the variable visible everywhere? If I take your suggestion and insert const db = "test" (instead of //your code...) then in /api/test.ts console.log(db) the db variable is unknown. – Addis

I edited and wrote a more detailed example for you Look again at what I wrote – Bakke

I don't know how /server/db/index.js is being called. I have since solved the problem in 2 different ways : see here – Addis

??? I specifically specified the file – Bakke

sorry, didn't see it properly because of the formatting. Thanks for your help. – Addis

Answer 2

I have the same problem and here is my first working solution:

Create a file /server/middleware/database.ts

import mysql from 'mysql2/promise'

export default defineEventHandler(async (event) => {
   event.context.db = await mysql.createConnection({
      host: '127.0.0.1',
      port: 3306,
      user: 'root',
      password: 'xxxx',
      database: 'STATS'
   })
});

And the api-endpoint:

export default defineEventHandler(async (event) => {

   const [rows, fields] = await event.context.db.execute('select * from tests');
   return {
        rows,
        fields
   }
})

I hope it solves your problem.

Gravitate

With this solution, a new connection to the DB is being created for each request – Retake

@LeonardoRaele Do you know the correct solution? – Gravitate

Afaik., Nuxt 3 has no proper way of doing that (which is stupid, but that's how it is), so you have to manage connections by yourself if you aren't using a lib like mongoose or prisma that already does that for you. e.g. you could connect to the db in the middleware once and store the connection in a module-scoped variable to reuse it in later requests. – Retake

Do this in a server module or plugin in Nuxt 3. – Smail