Nuxt3 x MySQL (& Drizzle ORM)

The Nuxt ecosystem is very promising but also very badly documented.

Not sure that is the best way to do it but it's the simplest and working I found.

If you have a better way please share with us.

mysql2 and raw SQL

Install mysql2

npm i mysql2

Setup Nuxt3 app

// /.env.dev

DB_HOSTNAME="localhost"
DB_PORT=3306
DB_USERNAME=""
DB_PASSWORD=""
DB_DATABASE=""

// /nuxt.config.ts

runtimeConfig: {
    db: {
        hostname: process.env.DB_HOSTNAME,
        port: process.env.DB_PORT,
        username: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_DATABASE
    }
},

// /server/plugins/db.ts

import mysql from 'mysql2'

export default defineNitroPlugin(async nitroApp => {
    const cfg = useRuntimeConfig()
    const pool = mysql
        .createPool({
            host: cfg.db.hostname,
            user: cfg.db.username,
            port: cfg.db.port,
            password: cfg.db.password,
            database: cfg.db.database,
            timezone: '+01:00',
            supportBigNumbers: true,
            bigNumberStrings: true
        })
        .promise()

    nitroApp.db = pool
})

Usage

// /server/api/dbtest.ts

export default defineEventHandler(async event => {
    const [r] = await useNitroApp().db.query(`SELECT * FROM user`)
    return r
})

// /pages/tests/dbtest.vue

<script setup lang="ts">
const r = await $fetch('/api/dbtest')
</script>

<template></template>

With Drizzle ORM

Next to previous steps

Install Drizzle ORM

npm i drizzle-orm
npm i -D drizzle-kit

Define DB schema

// /server/utils/dbschema.vue

import { mysqlTable, int, text, varchar } from 'drizzle-orm/mysql-core'

export const Post = mysqlTable('post', {
    id: int('id').primaryKey(),
    title: varchar('title', { length: 255 }).notNull(),
    text: text('text').notNull(),
    user: int('user_id').references(() => User.id),
})

export type PostSelect = typeof Post.$inferSelect
export type PostInsert = typeof Post.$inferInsert

export const User = mysqlTable('user', {
    id: int('id').primaryKey(),
    email: varchar('email', { length: 255 }).notNull().unique()
})

export type UserSelect = typeof User.$inferSelect
export type UserInsert = typeof User.$inferInsert

Usage

// /server/api/dbtest.ts

export default defineEventHandler(async event => {
    const r = await useNitroApp().drizzle.select().from(Post).where(eq(Post.id, 1)).limit(1)
    return r[0] ?? null
})