Declarative database modelling

Easily defining our app's data model in a declarative way requires some kind of ORM. Among the many solutions available, Prisma seems like a solid choice for many reasons: it's actively maintained, stable and performant, has lots of supported databases, etc.

Setting up Prisma

Let's add the Prisma client and CLI packages as development dependencies:

yarn add -D @prisma/client prisma

We can now initialize the Prisma configuration with the following:

yarn prisma init

This creates the .env and prisma/schema.prisma files which will respectively hold our database URL and Prisma schema definition.

Assuming a MySQL database, DATABASE_URL would look like this in .env:

DATABASE_URL=mysql://dbuser:dbpassword@localhost:3306/dbname

As for the Prisma schema file, this is where we'll define our data sources, generators and data models.

If using VS Code, which I strongly recommend, installing the official Prisma extension will give us syntax highlighting, linting, code completion, formatting, jump-to-definition and more when editing the schema file.

Data sources

A data source determines how Prisma connects to the database, in our case MySQL. While the corresponding definition block is already present in the freshly initialized prisma/schema.prisma, we'll need to adjust the provider value to match our database type like so:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

Generators

A generator determines which assets are created when we execute prisma generate. For now, we only need the default definition block for the JavaScript client:

generator client {
  provider = "prisma-client-js"
}

Data models

Finally, we can declare our data model by adding definition blocks for our entities and enums. In our case, we'll pave the way for authentication by defining a User model and UserRole enum:

model User {
  id       Int      @id @default(autoincrement())
  email    String   @unique
  password String
  role     UserRole @default(UNVERIFIED)
}

enum UserRole {
  UNVERIFIED
  GUEST
  EDITOR
  ADMIN
}

Generating the Prisma client

We're now ready to generate the Prisma client from our schema file, which will create the type-safe query engine runtime in node_modules/.prisma/client (which in turn is exposed via the @prisma/client package).

Although I did successfully use nodemon in the past to watch for schema changes and regenerate the Prisma client automatically, having multiple watchers for different purposes seemed a little bloated. Using a Nuxt module to achieve this would be a lot cleaner IMHO.

That being said, I now prefer hooking up the postinstall script in package.json to generate on each yarn install:

"postinstall": "prisma generate"

Database migration workflow

Having generated the client, we still need the actual database to reflect our schema with the proper tables and columns. This process is referred to as migrating the database.

The Prisma CLI provides two commands to achieve this: db push and migrate.

As our data model evolves, we can apply any pending changes to the database with the first method:

yarn prisma db push

Once we're ready to commit these changes to source control, we use the second method, which requires naming the migration with the name parameter (prompted if missing):

yarn prisma migrate dev --name initial-user-model

Seeding the database

In order to consistently populate our database with some default data (for example, an administrator user), we'll create a prisma/seed.ts seeding script which will be executed with ts-node, so let's add it to our project:

yarn add -D ts-node

We can instruct Prisma's integrated seeding functionality to execute prisma/seed.ts by adding the following section in package.json:

"prisma": {
  "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
}

Note the --compiler-options needed to import the @prisma/client package properly (more on this later).

In this case, the data we want to seed corresponds to the default user's email, password and role. Since we want our seeding scripts to be modular, let's create prisma/seeds/users.ts (yes, the password will eventually be encrypted):

import { PrismaClient, UserRole } from "@prisma/client";

export async function seedUsers(prisma: PrismaClient) {
  // Default admin user
  const admin = {
    email: process.env.SEED_ADMIN_EMAIL || "[e-mail]",
    // TODO: Encrypt password
    password: process.env.SEED_ADMIN_PASSWORD || "changeme",
    role: UserRole.ADMIN,
  };
  return await prisma.user.upsert({
    where: { email: admin.email },
    create: admin,
    update: admin,
  });
}

In order to import all of scripts at once, we re-export every file in prisma/seeds/index.ts:

export * from "./users";

Finally, we create our main seeding script prisma/seed.ts:

import { PrismaClient } from "@prisma/client";
import * as seeds from "./seeds";

const prisma = new PrismaClient();

async function main() {
  for (const seed of Object.values(seeds)) {
    console.log(await seed(prisma));
  }
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

We can now seed the database with the following:

yarn prisma db seed

Resetting the database will also execute the seeding script:

yarn prisma migrate reset

Importing the Prisma client in Nuxt3

In production, Nitro bundles the environment variables at build time, but the Prisma runtime doesn't understand that for DATABASE_URL. As a workaround, we can use dotenv to make sure our .env file is always loaded no matter what:

yarn add -D dotenv

Also, until @prisma/client supports proper ES modules exports (see this issue), we can re-export it from a helper file in prisma/client.ts like so:

import { config } from "dotenv";
import Prisma, * as PrismaScope from "@prisma/client";
const PrismaClient = Prisma?.PrismaClient || PrismaScope?.PrismaClient;

// Load process.env.DATABASE_URL from .env
config();

export const prisma = new PrismaClient();

Please note that the current setup probably won't work in a serverless environment as this would require configuring Prisma Data Proxy. I plan on adding support for serverless deployments in the future but I wanted to get things working nicely in NodeJS first.

Exploring our data

Prisma Studio allows us to explore and edit the data visually by running:

yarn prisma studio