Database

NuxtPro uses Drizzle ORM as the primary ORM. It supports common databases such as MySQL and PostgreSQL.

This page uses MySQL as an example and explains the setup flow in NuxtPro.

Configuration

1) MySQL connection in the project

For both the client and admin apps, you only need to set one env variable to connect to MySQL:

DATABASE_URL=mysql://USERNAME:PASSWORD@MYSQL_HOST:PORT/DB_NAME # MySQL connection string

2) Drizzle configuration for MySQL

In the config folder you’ll find the Drizzle configuration. If you want to switch databases later, update the dialect accordingly.

import type { Config } from 'drizzle-kit';

export default {
    dialect: 'mysql',
    schema: './server/database/schema.ts',
    out: './server/migrations',
    dbCredentials: {
      url: process.env.DATABASE_URL!
    },
  } satisfies Config;

Then in server/database, adjust database.ts to use the correct MySQL driver (other DBs follow the same pattern):

import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

const connection = mysql.createPool(process.env.DATABASE_URL!);
export const db = drizzle(connection, { schema, mode: 'default' });

3) Create DB and tables with Navicat

For simplicity, this project does not rely on migrations here. Instead, it uses an SQL script executed via Navicat.

a) Create the database

b) Execute the table creation script

Copy the contents of server/migrations/db.sql and execute it in Navicat (or import db.sql into your new nuxtpro database).

c) Basic CRUD examples

Below are basic Drizzle ORM examples. For full details, see the official docs: Drizzle ORM

import { category } from "@/db/schema";
import { db } from "@/db";
import { and, desc, eq } from "drizzle-orm";

export enum CategoryStatus {
  Created = "created",
  Deleted = "deleted",
  Online = "online",
  Offline = "offline",
}

export async function insertCategory(
  data: typeof category.$inferInsert
): Promise<typeof category.$inferSelect | undefined> {
  const [category] = await db().insert(category).values(data).returning();

  return category;
}

export async function updatecategory(
  uuid: string,
  data: Partial<typeof category.$inferInsert>
): Promise<typeof category.$inferSelect | undefined> {
  const [category] = await db()
    .update(category)
    .set(data)
    .where(eq(category.uuid, uuid))
    .returning();

  return category;
}

export async function findCategoryByUuid(
  uuid: string
): Promise<typeof category.$inferSelect | undefined> {
  const [category] = await db()
    .select()
    .from(category)
    .where(eq(category.uuid, uuid))
    .limit(1);

  return category;
}

export async function findCategoryBySlug(
  slug: string,
  locale: string
): Promise<typeof category.$inferSelect | undefined> {
  const [category] = await db()
    .select()
    .from(category)
    .where(and(eq(category.slug, slug), eq(category.locale, locale)))
    .limit(1);

  return category;
}

export async function getAllCategory(
  page: number = 1,
  limit: number = 50
): Promise<(typeof category.$inferSelect)[] | undefined> {
  const offset = (page - 1) * limit;

  const data = await db()
    .select()
    .from(category)
    .orderBy(desc(category.created_at))
    .limit(limit)
    .offset(offset);

  return data;
}

export async function getCategoryByLocale(
  locale: string,
  page: number = 1,
  limit: number = 50
): Promise<(typeof category.$inferSelect)[] | undefined> {
  const offset = (page - 1) * limit;

  const data = await db()
    .select()
    .from(category)
    .where(and(eq(category.locale, locale), eq(category.status, CategoryStatus.Online)))
    .orderBy(desc(category.created_at))
    .limit(limit)
    .offset(offset);

  return data;
}

export async function getCategoryTotal(): Promise<number> {
  const total = await db().$count(category);

  return total;
}