Nuxt + an external API. That's how I've always used Nuxt in my projects, and that's how it was used at the various clients I've worked for. But recently I worked on a project where the constraint was to do everything inside Nuxt, and we ended up with Nuxt + Drizzle + internal API routes.

So I'm taking the opportunity to write up a walkthrough of setting up a fullstack application with Nuxt. We'll base it on a very simple little project: lightweight astronomy gear management with a list and CRUD. We keep it simple, but that will still let us look at a few interesting bits of Drizzle ORM, like relations, for example.

One prerequisite only: having a local database (PostgreSQL, MySQL, etc.).

You can also grab the sources from the public GitLab.

Setup

The stack will be the following:

• Nuxt with:
  • NuxtUi
  • NuxtHub
  • Drizzle

We won't linger on the UI side, it'll stay basic.

Installing Nuxt

We can now install our environment.

npm create nuxt@latest tuto-nuxt
npm install @nuxt/ui tailwindcss
npx nuxi module add hub
npm i drizzle-orm@beta -D
# If PostgreSQL
npm install drizzle-orm drizzle-kit postgres @electric-sql/pglite
# If MySQL
npm install drizzle-orm drizzle-kit mysql2
# If SQLite
npm install drizzle-orm drizzle-kit @libsql/client

We check in nuxt.config.ts that the modules are loaded properly and we adjust the configuration.

// https://nuxt.com/docs/api/configuration/nuxt-config
export default defineNuxtConfig({
  ...
  modules: ['@nuxt/ui', '@nuxthub/core'],
  hub: {
    db: 'postgresql' // or 'mysql' / 'sqlite'
  }
})

We'll throw together a small UI base (nothing fancy).

<template>
  <div>
    <header class="topbar">
      <NuxtLink to="/" class="brand">Astronomy Demo</NuxtLink>
      <nav class="nav">
        <NuxtLink to="/manufacturers">Manufacturers</NuxtLink>
        <NuxtLink to="/stars">Stars</NuxtLink>
        <NuxtLink to="/mounts">Mounts</NuxtLink>
        <NuxtLink to="/telescopes">Telescopes</NuxtLink>
        <NuxtLink to="/sessions">Sessions</NuxtLink>
      </nav>
    </header>
    <NuxtPage />
  </div>
</template>

<style scoped>
.topbar { display:flex; align-items:center; justify-content:space-between; padding:.75rem 1rem; border-bottom:1px solid #eee; position:sticky; top:0; background:#fff; z-index:10; }
.brand { font-weight:700; text-decoration:none; color:#222; }
.nav { display:flex; gap:10px; }
.nav a { text-decoration:none; color:#0b5ed7; }
.nav a.router-link-exact-active { text-decoration:underline; }
</style>

Database configuration

In the .env, we add the database configuration.

#PostgreSQL
DATABASE_URL='postgres://<user>:<password>@<host>:<port>/<database>'
#MySQL
DATABASE_URL='mysql://<user>:<password>@<host>:<port>/<database>'

We run the first migration (empty for now) and start the server to check.

npx nuxt db generate
npm run dev

npx nuxt db generate will generate the migration SQL (equivalent to bin/console make\:migration with Doctrine and Symfony). These migrations will be executed either by starting the dev server with npm run dev, or by building with npx nuxt build, or via npx nuxt db migrate (equivalent to bin/console do:mi:mi).

The module comes with a DrizzleStudio plugin, which lets you access your database from Nuxt DevTools.

You can view your tables and manipulate them, pretty handy!

The schemas

Creating the schemas

Now that the environment is ready, we can move on to creating the schemas.

Schemas are roughly the equivalent of entities with Symfony and Doctrine. They let us define our different entities.

For our mini CRUD, we'll define the following entities:

• Telescope: each telescope has an identifier, a manufacturer, an aperture and a focal length.
• Manufacturer: each manufacturer has an identifier and a name.
• Mount: each mount has an identifier, a type and a maximum payload.
• Observation session: each session has an identifier, a date, the telescope and mount used, as well as the targeted celestial objects.
• Celestial object: each object is identified by an id and has a type.

The relations between these entities are as follows:

Each telescope is linked to a manufacturer and can be used in several sessions. Each manufacturer can make several telescopes. Each observation session uses a telescope and a mount, and can target several celestial objects. Finally, each mount can be used in several sessions.

We'll now translate this structure into a Drizzle schema. For clarity, all our schemas will live in a single schema.ts file, but nothing stops you from creating one file per table if you prefer.

Either
server/
|-db/
|--schema.ts

Or
server/
|-db
|--schema/
|----sessions.ts
|----manufacturers.ts
...

Our schema will therefore look like this:

import {pgEnum, pgTable, serial, text, integer, date, primaryKey} from "drizzle-orm/pg-core";
import {defineRelations} from "drizzle-orm";

export const manufacturers = pgTable('manufacturers', {
    id: serial().primaryKey(),
    name: text().notNull()
})

export const stars = pgTable('stars', {
    id: serial().primaryKey(),
    name: text().notNull()
})

export const mountTypeEnum = pgEnum('mount_type', ['Altazimutal', 'Equatorial'])
export const mounts = pgTable('mounts', {
    id: serial().primaryKey(),
    mount_type: mountTypeEnum().default('Altazimutal'),
    maxPayload: integer('max_payload').notNull()
})

export const telescopes = pgTable('telescopes', {
    id: serial().primaryKey(),
    manufacturerId: integer('manufacturer_id').references(() => manufacturers.id),
    focale: integer().notNull(),
    apperture: integer().notNull()
})

export const sessions = pgTable('sessions', {
    id: serial().primaryKey(),
    telescopeId: integer('telescope_id').references(() => telescopes.id),
    mountId: integer('mount_id').references(() => mounts.id),
    date: date().notNull()
})

export const starsToSessions = pgTable('stars_to_sessions', {
    starId: integer('star_id').notNull().references(() => stars.id),
    sessionId: integer('session_id').notNull().references(() => sessions.id)
},
    (t) => [primaryKey({columns: [t.starId, t.sessionId]})]
)

const relations = defineRelations(
    { telescopes, manufacturers, sessions, stars, mounts, starsToSessions},
    (r) => ({
    telescopes: {
        manufacturer: r.one.manufacturers({
            from: r.telescopes.manufacturerId,
            to: r.manufacturers.id
        }),
        sessions: r.many.sessions()
    },
    manufacturers: {
        telescopes: r.many.telescopes(
        )
    },
    sessions: {
        telescope: r.one.telescopes({
            from: r.sessions.telescopeId,
            to: r.telescopes.id
        }),
        stars: r.many.stars({
            from: r.sessions.id.through(r.starsToSessions.sessionId),
            to: r.stars.id.through(r.starsToSessions.starId)
        }),
        mount: r.one.mounts({
            from: r.sessions.mountId,
            to: r.mounts.id
        })
    },
    stars: {
        sessions: r.many.sessions()
    }
}));

Don't panic, we'll explain all of this.

Explanations

Defining a table

Each table will be represented by a TypeScript constant, here manufacturers, stars, and so on. We declare the table with pgTable (or mysqlTable / sqliteTable depending on your database), we name it and we configure the fields.

export const maTable = pgTable('ma_table', {/** field definitions **/})

IDs are defined with serial().primaryKey(). For the other fields, we define their type (text, integer, etc., based on what your database dialect supports) and whether they are nullable or not, and optionally a default value.

name: text().default('hello')

You can add more constraints via check(). For that, I'll point you to the official documentation.

export const mountTypeEnum = pgEnum('mount_type', ['Altazimutal', 'Equatorial'])

will create an object_type in PostgreSQL that we can then use on other fields

mount_type: mountTypeEnum().default('Altazimutal'),

It's a bit different on MySQL and SQLite

// Mysql
mount_type: t.mysqlEnum(["Altazimutal", "Equatorial"]).default("Altazimutal"),
//SQLite
mount_type: t.text().$type<"Altazimutal" | "Equatorial">().default("Altazimutal"),

Last point: if you want a different name between TypeScript and the database, you can rename the field directly in the type:

manufacturerId: integer('manufacturer_id') 

You can also configure Drizzle globally to automatically map your camelCase definitions to snake_case:

const db = drizzle({ connection: process.env.DATABASE_URL, casing: 'snake_case' })

Relations

You'll notice in my example that for some schemas we reference other IDs: manufacturerId in the telescope schema, for example. That's the simplest case, the one we use for One-To-One, Many-To-One and One-To-Many. In our schema, a telescope is linked to a manufacturer so we store the manufacturer's ID on the telescope, same thing in sessions: a session will store the ID of the telescope and of the mount used.

But what about Many-To-Many relations? We said a session can involve several celestial objects, and the same object can be observed in several sessions. Technically, behind the scenes, we have a join table that stores session IDs and celestial-object IDs. In ORMs like Doctrine, that's defined directly on the entity.

<?php

class Session
{
    public function __construct(
        #[ORM\ManyToMany(targetEntity: Star::class, inversedBy: 'sessions')]
        private Collection $stars
    ){}
}

<?php

class Star
{
    public function __construct(
        #[ORM\ManyToMany(targetEntity: Session::class, mappedBy: 'stars')]
        private Collection $sessions
    ){}
}

With Drizzle, it's more verbose: you have to declare that join table yourself, and that's what we do in:

export const starsToSessions = pgTable('stars_to_sessions', {
    starId: integer('star_id').notNull().references(() => stars.id),
    sessionId: integer('session_id').notNull().references(() => sessions.id)
},
    (t) => [primaryKey({columns: [t.starId, t.sessionId]})]
)

We define our join table like any other table, and we put a key on the IDs.

That's all well and good, but just saying that in telescope we put an integer field called manufacturerId isn't enough to create the link. Fair enough, and here too we have to declare the relations between our schemas by hand. It's verbose, but in the end it's very readable and logical. In our example, that's this whole part:

const relations = defineRelations(
    { telescopes, manufacturers, sessions, stars, mounts, starsToSessions},
    (r) => ({
    telescopes: {
        manufacturer: r.one.manufacturers({
            from: r.telescopes.manufacturerId,
            to: r.manufacturers.id
        }),
        sessions: r.many.sessions()
    },
    manufacturers: {
        telescopes: r.many.telescopes(
        )
    },
    sessions: {
        telescope: r.one.telescopes({
            from: r.sessions.telescopeId,
            to: r.telescopes.id
        }),
        stars: r.many.stars({
            from: r.sessions.id.through(r.starsToSessions.sessionId),
            to: r.stars.id.through(r.starsToSessions.starId)
        }),
        mount: r.one.mounts({
            from: r.sessions.mountId,
            to: r.mounts.id
        })
    },
    stars: {
        sessions: r.many.sessions()
    }
}));

The { telescopes, manufacturers, sessions, stars, mounts, starsToSessions} part lets us pass all the tables for which we'll have relations, then in (r) => {} we define those relations in an almost literal way.

// telescopes tables
telescopes: {
        manufacturer: r.one.manufacturers({ // linked to 1 manufacturer
            from: r.telescopes.manufacturerId, // the manufacturer id is stored in manufacturerId
            to: r.manufacturers.id // and it references a manufacturer's id
        }),
        sessions: r.many.sessions() // linked to several sessions
    },

// we have the inverse on the manufacturer side
manufacturers: {
        telescopes: r.many.telescopes() // 1 manufacturer makes several telescopes
    },

// and on the sessions side
sessions: {
        // 1 session is linked to 1 telescope
        telescope: r.one.telescopes({
            from: r.sessions.telescopeId, // id stored in telescopeId
            to: r.telescopes.id // references telescope.id
        }),
    },

It's actually pretty logical. 

Where it gets more "tricky" is Many-To-Many: we have to go through the join table, and no longer directly through the two tables. As a reminder, a session can involve several celestial objects, and celestial objects are linked to several sessions.

sessions: {
        ...
        // One session, several celestial objects
        stars: r.many.stars({
            // session id linked to sessionId of the join table
            from: r.sessions.id.through(r.starsToSessions.sessionId),
            // same for the celestial object's id
            to: r.stars.id.through(r.starsToSessions.starId)
        }),
        ...
    },
stars: {
        // The simplest case, a celestial object linked to several sessions
        sessions: r.many.sessions()
    }

Running the migration

Now that our schema is good, we can run the migration

npx nuxt db generate
npx nuxt db migrate

The first command will generate the migration scripts in server/db/migrations/postgresql/ and the second will run the migration. As mentioned above, npm run dev or npx nuxt build will also run the migrations.

Just like Doctrine, migrations are stored in a _hub_migrations table.

We have our database, we can now move on to our little CRUD.

Setting up the API

We'll start by setting up our API routes to manage our few entities. We won't cover all of them here (you'll have the full set in the GitHub repo), but we'll go through one of each type: manufacturers for a simple entity with no relation, telescopes for a One-To-Many entity, and we'll finish with Sessions which handles Many-To-Many. First we'll do a general recap of setting up APIs with Nuxt.

APIs with Nuxt

All of the server side is managed in the server/ directory. A quick look at the docs and we find this structure:

-| server/
---| api/
-----| hello.ts      # /api/hello
---| routes/
-----| bonjour.ts    # /bonjour
---| middleware/
-----| log.ts        # log all requests

We'll therefore focus on the api/ part. The api/ directory will contain our various endpoints for our entities. Note that everything that lives in api/ will be autoloaded by Nuxt as a route prefixed with /api.

Several options for organizing your files:

• Either you put everything at the root of api/ as <entity>.<verb>.ts (e.g. manufacturers.get.ts, etc.).
• Or in subdirectories, one per entity. That's the option I'm keeping, so for manufacturers we end up with the following tree:

-| server/
---| api/
-----| manufacturers/
-------| [id].delete.ts # delete a manufacturer
-------| [id].get.ts # fetch a manufacturer
-------| [id].put.ts # update a manufacturer
-------| index.get.ts # fetch all manufacturers
-------| index.post.ts # create a manufacturer

Since we use a dedicated subdirectory, endpoints that don't target a specific unique item will be index.<verb>.ts and [id].<verb>.ts for the others. Even though we could just as well have [name].<verb>.ts, for example, the point is to declare the route parameters.

So [id].delete.ts will automatically create a /api/manufacturers/id route and will be triggered automatically via a DELETE.

Each file will export a defineEventHandler that returns your JSON, a Promise, and so on.

export default defineEventHandler((event) => {
  return {
    hello: 'world',
  }
})

Querying the database

Now that we have our structure, we can start querying our database. Database access is done through SELECT, UPDATE, INSERT and DELETE. Query construction is very close to basic SQL: you'll have to dust off your college notes, no magic methods like with Doctrine.

import { db, schema } from '@nuxthub/db'

await db.select().from(schema.manufacturers) // select
await db.update(schema.manufacturers).set().where() // update
await db.insert(schema.manufacturers).values() // insert
await db.delete(schema.manufacturers).where() // delete

There are of course other functions to complete queries, do joins, filters, and so on. I'd invite you to check the official documentation to see what's possible.

Back to our manufacturer management.

Simple example: managing manufacturers

This is the simplest one, no complicated relations.

import { db, schema } from '@nuxthub/db'
import {eq} from "drizzle-orm";

export default eventHandler(async (event) => {
    const { id } = getRouterParams(event) // gets the id from the route parameters, also possible to do const id = getRouterParam(event, 'id')
    const deletedManufacturer = await db
        .delete(schema.manufacturers)
        .where(eq(schema.manufacturers.id, Number(id)))

    if (!deletedManufacturer) {
        throw createError({
            status: 404,
            message: `Manufacturer with id ${id} not found`
        })
    }

    return { deleted: true }
})

import { db, schema } from '@nuxthub/db'
import { eq } from 'drizzle-orm'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)

  const rows = await db
    .select()
    .from(schema.manufacturers)
    .where(eq(schema.manufacturers.id, Number(id)))
    .limit(1)

  const manufacturer = rows?.[0]
  if (!manufacturer) {
    throw createError({ status: 404, message: `Manufacturer with id ${id} not found` })
  }

  return manufacturer
})

import { db, schema } from '@nuxthub/db'
import {eq} from "drizzle-orm";

export default eventHandler(async (event) => {
    const { name } = await readBody(event)
    const { id } = getRouterParams(event)

    await db
        .update(schema.manufacturers)
        .set({ name })
        .where(eq(schema.manufacturers.id, Number(id)))
})

import { db, schema } from '@nuxthub/db'

export default eventHandler(async (event) => {
    return await db.select().from(schema.manufacturers)
})

import { db, schema } from '@nuxthub/db'

export default eventHandler(async (event) => {
    const { name } = await readBody(event)
    await db
        .insert(schema.manufacturers)
        .values({
            name
        })
})

Worth noting the use of:

• getRouterParams(event) to get the route parameters
• readBody(event) to get the payload

Many-To-One / One-To-Many example: managing telescopes

After a simple warm-up, we'll crank it up a notch with a One-To-Many: the telescopes <> manufacturer relation. We keep the same folder structure as for manufacturers. The delete, post and update being in the same vein as for manufacturers, we won't go back over them.

On the other hand, for the gets (list and edit) we can play with joins.

The goal is to fetch all telescopes and the associated manufacturer's name. With Doctrine for example we could just have done $telescope->getManufacturer()->getName() and that would have been it, but no such convenience here. Open your manuals to the joins page, we'll have to write some again.

So we want to fetch the telescopes and the associated manufacturer's name, in SQL that would give

SELECT 
    t.id,
    t.apperture,
    t.focale,
    m.name as manufacturerName
FROM telescope t
INNER JOIN manufacturer m 
    ON t.manufacturer_id = m.id;

Alright, that's a pretty light join. Now we have to translate this query into Drizzle

import { db, schema } from '@nuxthub/db'
import {eq, getColumns} from "drizzle-orm";

export default eventHandler(async (event) => {
    const telescopes = schema.telescopes
    const manufacturers = schema.manufacturers

    return await db
        .select({
            ...getColumns(telescopes),
            manufacturerName: manufacturers.name
        })
        .from(telescopes)
        .leftJoin(manufacturers, eq(telescopes.manufacturerId, manufacturers.id))
        .orderBy(telescopes.id)
})

Pretty similar, right? I did warn you that writing Drizzle queries was more or less like writing SQL directly.

...getColumns() simply avoids having to re-specify every field we want to fetch when we want the whole lot.

To fetch one particular telescope, we would have gone with the classic SQL query:

SELECT 
    t.id,
    t.apperture,
    t.focale,
    m.name as manufacturerName
FROM telescope t
INNER JOIN manufacturer m 
    ON t.manufacturer_id = m.id
WHERE t.id = <id_telescope>;

With Drizzle, we translate it almost literally:

import { db, schema } from '@nuxthub/db'
import {eq, getColumns} from 'drizzle-orm'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)
  const telescopes = schema.telescopes
  const manufacturers = schema.manufacturers

  const rows = await db
    .select({
        ...getColumns(telescopes),
        manufacturerName: manufacturers.name,
    })
    .from(telescopes)
    .innerJoin(manufacturers, eq(telescopes.manufacturerId, manufacturers.id))
    .where(eq(telescopes.id, Number(id)))
    .limit(1)

  const telescope = rows?.[0]
  if (!telescope) {
    throw createError({ status: 404, message: `Telescope with id ${id} not found` })
  }

  return telescope
})

Many-To-Many example: sessions

Saving the best for last! Managing Many-To-Many is a bit more complex and will take a bit more "gymnastics".

Delete / create / update

Forget the conveniences Doctrine offers, for example where a delete automatically goes and removes the join, here everything is done by hand.

import { db, schema } from '@nuxthub/db'
import { eq } from 'drizzle-orm'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)
  const sessionId = Number(id)

  // We delete the sessions <=> stars link first
  await db
    .delete(schema.starsToSessions)
    .where(eq(schema.starsToSessions.sessionId, sessionId))

  // We delete the session
  await db
    .delete(schema.sessions)
    .where(eq(schema.sessions.id, sessionId))
})

Yep, here we have to manage the join table manually, so delete the link "by hand".

It'll be the same for POST and UPDATE: we'll manually add the sessions <=> stars link, or we'll update it.

import { db, schema } from '@nuxthub/db'
import { eq } from 'drizzle-orm'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)
  const sessionId = Number(id)
  const { telescopeId, mountId, date, starIds } = await readBody(event)

  await db
    .update(schema.sessions)
    .set({
      telescopeId,
      mountId,
      date
    })
    .where(eq(schema.sessions.id, sessionId))

  // We update the join table
  // 1. We delete the old ones
  await db
    .delete(schema.starsToSessions)
    .where(eq(schema.starsToSessions.sessionId, sessionId))

  // 2. We add the new ones
  if (starIds && starIds.length > 0) {
    await db.insert(schema.starsToSessions).values(
      starIds.map(starId => ({
        sessionId,
        starId
      }))
    )
  }
})

import { db, schema } from '@nuxthub/db'

export default eventHandler(async (event) => {
    const { telescopeId, mountId, date, starIds } = await readBody(event)

    // We insert into sessions
    const result = await db
        .insert(schema.sessions)
        .values({
            telescopeId,
            mountId,
            date
        })
        .returning({ insertedId: schema.sessions.id })

    const sessionId = result[0]?.insertedId
    
    // We insert into the join table
    if (starIds && starIds.length > 0) {
        await db.insert(schema.starsToSessions).values(
            starIds.map((starId: any) => ({
                sessionId,
                starId
            }))
        )
    }

    return { id: sessionId }
})

Fetching

The collection GET

This is where it gets fun! For our sessions listing, we want something fairly readable:

• The session's basic info: id and date
• Readable labels for the telescope and the mount (e.g.: Skywatcher 127/1500 and Equatorial (10kg))
• The list of stars (e.g.: "M42", "M45" )

SELECT
  s.*,
  mfr.name || ' ' || t.apperture || '/' || t.focale AS telescopeLabel,
  mt.mount_type || ' (' || mt.maxPayload || 'kg)' AS mountLabel,
  COALESCE(stars_per_session.stars, '[]'::json) AS starNames
FROM sessions s
LEFT JOIN telescopes t ON s.telescopeId = t.id
LEFT JOIN manufacturers mfr ON t.manufacturerId = mfr.id
LEFT JOIN mounts mt ON s.mountId = mt.id
LEFT JOIN (
  SELECT sts.sessionId,
         COALESCE(JSON_AGG(st.name) FILTER (WHERE st.name IS NOT NULL), '[]'::json) AS stars
  FROM starsToSessions sts
  LEFT JOIN stars st ON sts.starId = st.id
  GROUP BY sts.sessionId
) stars_per_session ON s.id = stars_per_session.sessionId
ORDER BY s.date;

Yes, I deliberately went a bit heavy-handed so the query returns everything already formatted, to show you a more complex example.

If we take this query and transpose it into Drizzle

import {db, schema} from '@nuxthub/db'
import {eq, getColumns, sql} from 'drizzle-orm'

export default eventHandler(async () => {
    const { sessions, telescopes, mounts, starsToSessions, stars, manufacturers } = schema

    // Subquery to fetch the stars per session
    const starsSubquery = db
        .select({
            sessionId: starsToSessions.sessionId,
            stars: sql<string[]>`
        COALESCE(
          JSON_AGG(${stars.name}) FILTER (WHERE ${stars.name} IS NOT NULL),
          '[]'::json
        )
      `.as('stars') // <- important, alias is required to use it in the main query
        })
        .from(starsToSessions)
        .leftJoin(stars, eq(starsToSessions.starId, stars.id))
        .groupBy(starsToSessions.sessionId)
        .as('stars_per_session')

    // Main query
    return await db
        .select({
            ...getColumns(sessions),
            telescopeLabel: sql<string>`
                    (${manufacturers.name} || ' ' || ${telescopes.focale} || '/' || ${telescopes.apperture})
            `,
            mountLabel: sql<string>`
                    (${mounts.mount_type} || ' (' || ${mounts.maxPayload} || 'kg)')
            `,
            starNames: starsSubquery.stars
        })
        .from(sessions)
        .leftJoin(telescopes, eq(sessions.telescopeId, telescopes.id))
        .leftJoin(manufacturers, eq(telescopes.manufacturerId, manufacturers.id))
        .leftJoin(mounts, eq(sessions.mountId, mounts.id))
        .leftJoin(starsSubquery, eq(sessions.id, starsSubquery.sessionId))
        .orderBy(sessions.date)
})

A thing of beauty, right?

• The starsSubquery subquery will:
  • aggregate all stars of a session into a JSON array thanks to JSON_AGG
  • provide an empty array by default if there are no stars thanks to COALESCE(..., '[]'::json)
  • create an alias via .as() so we can use it in our main query
• The main query will:
  • fetch all columns of sessions via getColumns() (which we already saw above)
  • create readable labels for the telescope and the mount with sql<T> (we'll come back to that just after)
  • associate all stars of the session via leftJoin(starsSubquery)

The other joins are classic, we won't linger on them, but let's come back for a moment to the use of sql<T>.

The sql<T> operator lets you write native SQL and type the return: sql<number[]> indicates that the native query will return an array of numbers, for example. That lets you run PostgreSQL functions that have no equivalent on the Drizzle side. Typically here, we use it to create our labels and our array of stars. We can also inject variables via ${}. This operator can be combined with .as() (used in our example so we can reuse it), .mapWith(), and others you'll find in the Drizzle documentation.

The GET /id

Back to something a bit lighter. To fetch one particular session, we have less formatting, but we still want to keep our array of stars.

SELECT
  s.*,
  COALESCE(JSON_AGG(sts.starId) FILTER (WHERE sts.starId IS NOT NULL), '[]'::json) AS starIds
FROM sessions s
LEFT JOIN starsToSessions sts ON s.id = sts.sessionId
WHERE s.id = <id_session>
GROUP BY s.id
LIMIT 1;

Much simpler to wrap up, I'm being nice.

import { db, schema } from '@nuxthub/db'
import {eq, getColumns, sql} from 'drizzle-orm'

export default eventHandler(async (event) => {
    const { id } = getRouterParams(event)
    const sessionId = Number(id)

    // Single query with JSON aggregation for the stars
    const rows = await db
        .select({
            ...getColumns(schema.sessions),
            starIds: sql<number[]>`
        COALESCE(
          JSON_AGG(${schema.starsToSessions.starId}) FILTER (WHERE ${schema.starsToSessions.starId} IS NOT NULL),
          '[]'::json
        )
      `
        })
        .from(schema.sessions)
        .leftJoin(schema.starsToSessions, eq(schema.sessions.id, schema.starsToSessions.sessionId))
        .where(eq(schema.sessions.id, sessionId))
        .groupBy(schema.sessions.id)
        .limit(1)

    const session = rows?.[0]
    if (!session) {
        throw createError({ status: 404, message: `Session with id ${id} not found` })
    }

    return session
})

We find a few elements in common with GET collection:

• aggregating the stars with JSON_AGG
• the empty array by default thanks to COALESCE(..., '[]'::json)
• the ever-present getColumns()
• .as()

The frontend

We'll go through this very quickly: the frontend stays Nuxt/VueJS and classic data display. You can have a look in the repo for more details, but we'll just briefly explain how to call our routes.

Nuxt provides 3 methods to fetch data:

• $fetch: the most basic one
• useFetch: which is a wrapper around $fetch
• useAsyncData: similar to useFetch, but offering more control

We'll look at the first 2, which are the ones used in my example (see repo). When to use one rather than the other?

$fetch is better used for POST, DELETE, PUT requests, and so on. Basically, everything that isn't a GET. Why? Because $fetch is a simple HTTP wrapper that doesn't include Nuxt's SSR/Hydration part, and if it's used to fetch data in onMounted for example, the request can run twice:

• A first time on the server (SSR)
• A second time on the client

So we rather use it for non-GETs, because those are generally executed after an action, like submitting a form, so executed once on the client.

useFetch and useAsyncData, on the other hand, are wrappers around $fetch that avoid these double calls: the API call is made on the server, and the data is sent to the client via the payload, which won't have to redo the request.

Back to our APIs, we'll therefore just need to call useFetch to fetch our values and $fetch for write access.

const id = computed(() => Number(route.params.id)) // We get the id from the route
const { data: manufacturer } = await useFetch(`/api/manufacturers/${id.value}`)

And to send

async function submit() {
  await $fetch('/api/manufacturers', {
    method: 'POST',
    body: {name: name.value},
  })
  await router.push('/manufacturers')
}

Conclusion

I hope I was able to give you a glimpse of what Nuxt offers for going fullstack. I won't hide that I expected something closer to Doctrine when people told me about Drizzle (coming from the PHP and Symfony world originally), but it had the upside of making me write SQL in a bit more depth again (with Doctrine, it had been a while since I'd written any SQL).

And coming from Doctrine, it also lets you see the different philosophies: where Doctrine is more high-level (even if you can still write native SQL and handle complexity when needed) and more object-oriented, Drizzle clearly puts more emphasis on SQL, with typing, but nothing is hidden behind magic methods that do all the work. So yes, from my point of view it's less pleasant to use, but on the other hand it also leaves you more control.