Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 32 ยท Server and API

Databases and ORMs

Connect Nuxt server routes to a database: the Nitro database layer, Drizzle and Prisma overview, SQLite for development, migrations and avoiding N+1 queries.

Almost every real app stores something: users, orders, notes, posts. In Nuxt the database is always reached from the server side, from handlers in server/api or helpers in server/utils, never directly from a Vue component. The browser asks your API, and your API asks the database. That keeps connection strings private and lets you validate every request.

This lesson maps the options (Nitro's built-in database layer, Drizzle, Prisma), explains why SQLite is a good start, and teaches the habits that matter in any stack: migrations, parameterized queries and avoiding the N+1 problem. The database tools and Nuxt code are read-only here, because they need packages and files this site cannot run. Two stand-ins run live on Node with its built-in node:sqlite module: migrations plus a small repository, and an N+1 demonstration with two fixes. SQL itself is taught in the SQL tutorial, if you want a refresher.

The path of a query

Page Calls your API with useFetch.
Server route Validates input, calls a helper.
Helper The only code that writes SQL.
Database SQLite, Postgres, MySQL.
1 / 2

Choosing a database

Pick the database like you would for any web app:

  • SQLite is a database in a single file. It needs no server, so it is perfect for development, prototypes, tests and many small production sites that run on one machine.
  • PostgreSQL and MySQL are server databases. Choose them when several app instances need to share data, when you need strong concurrency, or when your host provides one.
  • Serverless and edge databases (hosted SQLite variants, key-value stores) fit deployments where your code runs on many short-lived instances and cannot keep a local file.

A good starting plan: develop on SQLite, keep your SQL and schema portable, and move to Postgres when you outgrow one machine. Your Nuxt server routes do not need to change much, because they only call the helper layer.

Where the connection lives

Create the connection once, in a file under server/utils/, and import its helpers from routes. Never open a new connection on every request. Utils are auto-imported in server code (see Server middleware, plugins and utils).

TypeScript
// server/utils/db.ts (read-only; the driver depends on your choice)
import Database from 'better-sqlite3';

export const db = new Database('data/app.db');
db.pragma('journal_mode = WAL');
TypeScript
// server/api/users/[id].get.ts (read-only)
export default defineEventHandler((event) => {
  const id = Number(getRouterParam(event, 'id'));
  const user = db.prepare('SELECT id, name, email FROM users WHERE id = ?').get(id);

  if (!user) {
    throw createError({ statusCode: 404, statusMessage: 'User not found' });
  }
  return user;
});

Notice the ? placeholder. Always pass values as parameters, never by gluing strings into SQL. Parameters keep user input as data, so input such as x' OR '1'='1 cannot change the meaning of your query. That single habit stops the most common web database attack, SQL injection.

The Nitro database layer

Nitro ships an optional, experimental database layer based on a small library called db0. You switch it on in nuxt.config.ts and then use useDatabase() in server code:

TypeScript
// nuxt.config.ts (read-only; experimental, check the current docs)
export default defineNuxtConfig({
  nitro: {
    experimental: { database: true },
  },
});
TypeScript
// server/api/users.get.ts (read-only)
export default defineEventHandler(async () => {
  const db = useDatabase();
  const { rows } = await db.sql`SELECT id, name FROM users ORDER BY name`;
  return rows;
});

In development it uses a local SQLite file under .data/; you can configure other connectors for other databases. The tagged template (db.sql followed by a backtick string) turns interpolated values into parameters for you. Because the feature is marked experimental, its option names and behavior may change, so treat the docs of your Nuxt version as the source of truth. It is a convenient start for small projects; larger projects usually add an ORM.

Drizzle and Prisma

An ORM (object-relational mapper) or query builder lets you describe tables in code and write queries with typed functions instead of raw strings. Two popular choices in the Nuxt world:

Drizzle keeps the SQL shape. You declare tables in TypeScript, and queries read like SQL:

TypeScript
// server/database/schema.ts (read-only)
import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});
TypeScript
// in a handler (read-only)
const [user] = await db.select().from(users).where(eq(users.id, id));

Prisma uses a separate schema.prisma file and generates a client with a friendly object API:

TypeScript
// in a handler (read-only)
const user = await prisma.user.findUnique({ where: { id } });
const withPosts = await prisma.user.findMany({ include: { posts: true } });

Both give you type safety (a typo in a column name is a compile error) and both generate migrations from your schema. Their setup steps change between versions, so follow the current guide for the version you install. How to choose: prefer Drizzle if you like SQL and want a light layer, prefer Prisma if you like a schema-first workflow and a high-level API, and use raw SQL with a tiny helper layer if your app is small. None of them changes the architecture: routes call helpers, helpers talk to the database.

Migrations

Your database schema changes over time: a new table, a new column, an index. A migration is a small, numbered, versioned script that changes the schema from one state to the next. The rules are simple:

  1. Every change is a new migration file; you never edit one that has already run anywhere.
  2. Migrations run in order, once each, and the database records which ones have run.
  3. The same migrations run on your laptop, in tests and in production, so every environment has the same shape.

With an ORM you rarely write them by hand: Drizzle's and Prisma's command-line tools generate migration files by comparing your schema to the previous state. Without one, you keep numbered .sql files and a small runner.

The first stand-in does exactly that on an in-memory SQLite database from Node: it keeps a schema_migrations table, applies each pending migration once, and wraps the SQL in a tiny repository, which is the one place that "knows" the tables. It also shows parameters blocking a hostile string, and a UNIQUE constraint doing its job.

Node.js Migrations and a repository with node:sqlite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The first run prints applied 001_create_users, applied 002_create_posts and applied 003_add_posts_index. On the second run nothing is applied, which is the whole point: migrations are safe to run again. The repository then prints the new user and null for a missing one, the hostile string finds 0 rows, and the duplicate email is rejected with UNIQUE constraint failed: users.email. In your own handler you would turn null into a 404 error and the constraint failure into a 409.

The N+1 problem

The most common performance bug in database code has a catchy name. Imagine a page that lists authors with their posts. A natural first version:

  1. Run one query to get the authors (1).
  2. For each author, run another query to get that author's posts (N).

With 4 authors that is 5 queries; with 400 authors it is 401. Each query has a cost, especially when the database is on another machine, so the page gets slower as the data grows. It also hides well: the code looks innocent, and ORMs can produce it behind your back when you access a relation inside a loop.

There are two standard cures. A join fetches everything in one query and you group the rows in code. A batched lookup runs two queries: the list, then one query for all related rows using IN (...). ORMs offer the same ideas under names like include (Prisma) or with (Drizzle's relational queries).

Node.js Counting queries: N+1 versus JOIN and IN

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The output reads N+1 queries: 5, JOIN queries: 1, IN queries: 2, then same result: true and the third author as {"id":3,"name":"Grace","posts":["Compilers","COBOL"]}. All three strategies return the same data; they differ in how many round trips they cost. The IN approach is often the best compromise, because a join repeats the parent columns on every row.

Habits that save you later

  • Keep SQL in one layer. Routes call functions such as users.byId(id); only that layer imports the database. It makes tests and a future database switch much easier.
  • Select only what you need. Never return a whole user row from an API; passwords, hashes and internal flags must not leave the server.
  • Add indexes for columns you filter or join on (like posts.user_id above). Measure with your database's explain tooling before and after.
  • Map errors on purpose. A missing row is a 404, a constraint violation is usually a 409 or 400, and everything unexpected is a 500 without database details in the message.
  • Know your host. A local SQLite file does not survive on platforms with a read-only or temporary file system. Check where your deployment can write (Building and deploying), and use a hosted database when it cannot.
  • Keep secrets in runtime config. The connection string belongs in a private runtimeConfig key (Runtime config and environment variables).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where should database queries run in a Nuxt app?

    Database access is server-side only. Components call your API, and the API runs the queries, so credentials never reach the browser.
  2. 2. What is the main reason to pass values as query parameters instead of building SQL strings?

    With parameters, input such as x' OR '1'='1 is treated as a plain value, so it cannot change the structure of the query.
  3. 3. A page loads 50 authors and then runs one query per author for their posts. How many queries is that?

    That is the N+1 problem: one query for the list plus one for each of the 50 rows. A join or a single IN lookup fixes it.
  4. 4. What is a migration?

    Migrations make schema changes repeatable: every environment applies the same ordered scripts, and the database records which ones have run.

Summary

  • Query the database only from the server, through helpers in server/utils that routes call.
  • SQLite is an excellent development database; move to Postgres or MySQL when several instances need to share data.
  • Nitro's database layer is experimental and convenient; Drizzle and Prisma add typed schemas and generated migrations.
  • Always use parameters, never string-built SQL.
  • Migrations are ordered, run-once schema changes recorded in the database.
  • Avoid N+1 with a join or an IN batch, return only the fields you need, and keep credentials in private runtime config.

Next, learn how Nuxt turns all of this into HTML in Rendering modes: SSR, SPA, SSG, hybrid.