Nuxt3 - 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.

score -1 · Answer 1 · edited May 23 '23 at 07:26

-1

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.

edited May 23 '23 at 07:26

rink.attendant.6

44,500
61
101
156

answered Mar 22 '23 at 12:35

harley81

190
2
12

With this solution, a new connection to the DB is being created for each request – Leonardo Raele Jun 23 '23 at 03:58
@LeonardoRaele Do you know the correct solution? – harley81 Jun 26 '23 at 10:29
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. – Leonardo Raele Jul 01 '23 at 17:43

Nuxt3 - Establish database connection in server middleware

1 Answers1