Declarative database modelling
Tags: nuxt3, prisma, esm
Published: 2022-02-23 13:34:29

Easily defining our app's data model in a declarative way requires some kind of ORM. Among the many solutions available, [Prisma](https://www.prisma.io/) 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:
```shell
yarn add -D @prisma/client prisma
```

We can now initialize the Prisma configuration with the following:
```shell
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`:
```shell
DATABASE_URL=mysql://dbuser:dbpassword@localhost:3306/dbname
```

As for the [Prisma schema](https://www.prisma.io/docs/concepts/components/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](https://marketplace.visualstudio.com/items?itemName=Prisma.prisma) 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`:
```json
"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:
```shell
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):
```shell
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:
```shell
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`:
```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):
```typescript
import { PrismaClient, UserRole } from "@prisma/client";

export async function seedUsers(prisma: PrismaClient) {
  // Default admin user
  const admin = {
    email: process.env.SEED_ADMIN_EMAIL || "admin@example.com",
    // 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`:
```typescript
export * from "./users";
```

Finally, we create our main seeding script `prisma/seed.ts`:
```typescript
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:
```shell
yarn prisma db seed
```

Resetting the database will also execute the seeding script:
```shell
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:
```shell
yarn add -D dotenv
```

Also, until `@prisma/client` supports proper ES modules exports (see this [issue](https://github.com/prisma/prisma/issues/5030)), we can re-export it from a helper file in `prisma/client.ts` like so: 
```typescript
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](https://www.prisma.io/docs/concepts/components/prisma-studio) allows us to explore and edit the data visually by running:
```shell
yarn prisma studio
```