Data is a critical part of every web app. Whether you're building a SvelteKit app to capture form inputs, visualize some data, or provide a dashboard to your users.

But dealing with databases can be challenging. Each database has it's own slightly different APIs. You need to write a lot of database access code and keep database schemas up to date. That work adds up.

That's why I like Prisma, it's a developer friendly solution that simplifies many of the pain points of working with databases.

What is Prisma?

Prisma is an ORM (object-relational mapper). It helps apps talk to databases. Kind of like a middleman between your JavaScript code and the underlying database.

It can read rows from the database and turns them into JavaScript objects (hydration). And vice-versa, it can take JavaScript objects and update the database or create new records. And it supports many types of databases.

Code generator

In Prisma, models (aka tables) are defined in a file called schema.prisma. Then you run prisma generate to generate a custom client for your app.

So you can think of Prisma as a kind of code generator.

Schema syncing

Prisma keeps the database schema up-to-date by syncing changes.

It supports two styles:

• push: In this style, the schema.prisma is the source-of-truth. Additive changes, like adding a new table or a new column can be handled easily. It's the recommended approach during the prototype phase. But it's not ideal for more complex changes, like changing a data type.
• migrate: In this approach, the migration scripts are considered the source-of-truth. It provides a fine-grained control over schema changes. This is recommended for production apps.

Setup

To install the Prisma in a SvelteKit app, run:

pnpm install -D prisma @prisma/client

Then initialize the project:

pnpm prisma init

This does 2 things

1. It creates a prisma/schema.prisma where models can be defined.
2. It adds an environment variable DATABASE_URL to .env.

The DATABASE_URL is the connection information Prisma needs to connect to the database.

For example, for Postgres, it would look like postgresql://USER:PASSWORD@HOST:PORT/DATABASE

For a full list of examples, see Supported Connection URLs.

Starting points

For new apps, the Prisma schema is written from scratch, but Prisma also supports importing an existing legacy database's schema too.

To start with an existing database, run prisma db pull to pull the existing schema into schema/prisma.schema.

To start with a new database, define models in prisma/schema.prisma and Prisma will sync the database schema for you.

Modeling

Here are some examples of models and indexes.

To define a basic model:

// in prisma/schema.prisma
model Product {
}

And fields can be added in the format name DataType:

model Product {
  // auto-incrementing primary key
  id Int @id @default(autoincrement())
  barcode String
  name String
}

To add an index:

model Product {
  // ...

  // define a unique index
  @@unique([barcode])

  // define a non-unique index
  @@index([name])
}

For more examples, see the docs on modelling or my course on Svelte + Prisma.

Syncing the database

To update the database schema, run:

pnpm prisma db push

Note: In some cases, a conflict can occur. For example, if a field's data type is changed from String to Int, there is no automatic way to reconcile that conversion. Prisma will notify you that there is a conflict and give you the option to reset the table (causing data loss), or you can use migrations for more control.

Browsing your database

Prisma comes with a handy database viewer that runs in the browser. It's a useful tool for inspecting database tables during development.

Here's a screenshot:

To view it, run:

pnpm prisma studio

Then visit https://localhost:5555

Accessing the database

It's likely that many places in your app will need to access the database. So, it's a good idea to centralize the db client.

To do that, create a file called src/lib/db.server.js and instantiate the client:

import { PrismaClient } from '@prisma/client'

// expose a singleton
export const db = new PrismaClient()

Now, anytime a connection is needed, import lib/db.server.js:

// import the centralized connection
import { db } from '$lib/db.server'

// then do stuff with the db connection
db.model.create(...)
db.model.update(...)
db.model.findMany(...)
// etc...

Note: Manual disconnection is rarely needed because Prisma automatically closes connections when the process ends.

Seeding data

It's much easier to set up developer machines when the app ships with a seed dataset. It also makes it easier to reset the environment when if it ever gets hosed.

In Prisma, seed data is defined in prisma/seed.js.

Here's an example:

// in prisma/seed.js

// import db client from previous step
import { db } from '$lib/db.server'

// create one record
await db.product.create({
  data: { barcode: '1234', name: 'Shirt' }
})

// or create records in bulk
await db.product.createMany({
  data: [
    { barcode: '5678', name: 'Pants' },
    { barcode: '91011', name: 'Socks' }
  ]
})

Then add a prisma section to package.json:

"prisma": {
  "seed": "vite-node ./prisma/seed.js"
}

Note: vite-node is used instead of node because our seed.js imports a vite alias $lib. So make sure to install vite-node too:

pnpm install -D vite-node

Then to load the seed data:

pnpm prisma db seed

Conclusion

Prisma is a great option for connecting SvelteKit projects with a database.

Prisma handles all the tricky stuff, like syncing your database schema and generating SQL queries, so you don't have to do that by hand.

It frees up developer time to focus on building features.

P.S. For a SvelteKit/Prisma repo with more examples, see joshnuss/markdown-mail.