Prisma & Your Database

What Prisma is

Prisma lets you talk to a database using JavaScript instead of writing SQL. You describe your tables in one file, and Prisma generates a client with autocomplete for every table and column.

The schema file

generator client {
  provider = "prisma-client-js"     // generate a JS client
}

datasource db {
  provider = "mysql"                // or "postgresql"
  url      = env("DATABASE_URL")    // read from .env
}

model Widget {
  id        Int      @id @default(autoincrement())
  title     String
  enabled   Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

That model creates a Widget table and gives you db.widget in code — the model name, lowercased.

The template you start from uses provider = "sqlite" instead — one local file, no server to set up. Its README notes that SQLite works in production only if your app runs as a single instance, and suggests MySQL or PostgreSQL otherwise. This note's examples use MySQL.

Field types and modifiers

imageUrl  String                // breaks on real URLs
imageUrl  String?  @db.Text     // correct

Connecting tables

In a Shopify app nearly everything belongs to a shop, so nearly every table gets a shop column holding the shop's domain — the same value your code reads from session.shop. When one of your tables belongs to another of your tables, you link them with a relation.

model Widget {
  id       Int            @id @default(autoincrement())
  shop     String                            // e.g. "my-store.myshopify.com"
  title    String
  options  WidgetOption[]                    // one widget has many options
}

model WidgetOption {
  id       Int     @id @default(autoincrement())
  widgetId Int                               // the link
  label    String

  widget Widget @relation(fields: [widgetId], references: [id], onDelete: Cascade)
}

Three pieces make a relation work:

1. widgetId — a normal column holding the parent's id.
2. widget Widget @relation(...) — tells Prisma how they connect.
3. options WidgetOption[] on the parent — the other direction.

Only the first is a real column. The other two exist so you can write option.widget or widget.options in code. onDelete: Cascade is explained in the next section.

Unique combinations

Sometimes a value must be unique per shop, not globally:

model Widget {
  shop String
  slug String

  @@unique([shop, slug])    // two shops may both use "blue"
}

onDelete — the setting that deletes your data

This decides what happens to child rows when the parent is deleted.

Migrations

A migration is a saved instruction such as "add a column called enabled". Prisma writes them for you and keeps them in order, so your laptop, a teammate's laptop and production all end up with identical tables.

# after editing schema.prisma — development only
npx prisma migrate dev --name add_enabled_flag

# on production — applies pending migrations, nothing else
npx prisma migrate deploy

# rebuild the client after pulling someone else's changes
npx prisma generate

Reading data

import db from "../db.server";

// many rows
const widgets = await db.widget.findMany({
  where:   { shop: session.shop, enabled: true },
  orderBy: { createdAt: "desc" },
  take: 20,          // limit
  skip: 0,           // offset — page 2 would be skip: 20
});

// one row by a unique field
const widget = await db.widget.findUnique({ where: { id: 42 } });

// one row by any conditions
const mine = await db.widget.findFirst({
  where: { id: 42, shop: session.shop },
});

// how many
const total = await db.widget.count({ where: { shop: session.shop } });

Picking columns, and following relations

// only the fields you need (faster, and avoids leaking extra data)
await db.widget.findMany({
  select: { id: true, title: true },
});

// bring the children along
await db.widget.findFirst({
  where:   { id: 42, shop: session.shop },
  include: { options: true },
});

// filter by something on the parent
await db.widgetOption.findMany({
  where: { widget: { shop: session.shop } },
});

Useful conditions

where: {
  title:     { contains: "blue" },     // search
  id:        { in: [1, 2, 3] },        // any of these
  deletedAt: null,                     // is empty
  createdAt: { gte: someDate },        // on or after
  OR: [{ title: { contains: "a" } }, { slug: { contains: "a" } }],
}

Writing data

// create
await db.widget.create({
  data: { title: "New widget", shop: session.shop },
});

// update one row
await db.widget.update({
  where: { id: 42 },
  data:  { title: "Renamed" },
});

// update it if it exists, otherwise create it
// (Setting has shop String @unique — one settings row per shop)
await db.setting.upsert({
  where:  { shop: session.shop },
  update: { enabled: false },
  create: { shop: session.shop, enabled: false },
});

// many at once
await db.widget.createMany({ data: [{ ... }, { ... }] });
await db.widget.updateMany({ where: { shop: session.shop }, data: { enabled: true } });

upsert is worth knowing early. It removes the "check if it exists, then decide" dance you would otherwise write constantly.

Deleting safely

// WRONG — any shop can delete any row by guessing an id
await db.widget.delete({ where: { id: Number(form.get("id")) } });

// RIGHT — scoped to the shop that asked
await db.widget.deleteMany({
  where: { id: Number(form.get("id")), shop: session.shop },
});

JSON columns: useful, with one catch

A Json field stores any shape without designing new tables. Good for flexible settings and structures that vary.

// Store the object directly — recommended
await db.widget.create({ data: { config: { color: "blue" } } });
widget.config.color;                // "blue" — an object

// Stringify first, and you get a string back
await db.widget.create({ data: { config: JSON.stringify({ color: "blue" }) } });
widget.config.color;                // undefined!

const config = typeof widget.config === "string"
  ? JSON.parse(widget.config)
  : widget.config ?? {};

JSON columns cannot be searched or indexed like normal columns. If you find yourself filtering by something inside the JSON, that something deserves its own column.

Designing a table: five questions

• Does it belong to a shop? Then it needs a shop column — not a relation to Session.
• Does it belong to another of your tables? Then it needs a relation — choose onDelete deliberately.
• Could any field hold a URL or long text? Give it @db.Text.
• Must anything be unique — globally, or only per shop (@@unique)?
• Will you ever filter by it? Then it is a column, not a key inside a JSON blob.

Add createdAt and updatedAt to everything. They cost nothing and you will want them the first time you debug.

Common errors

Cheat sheet

# schema
String @db.Text        long text and URLs
Json                   flexible structure (can't be filtered well)
@@unique([a, b])       unique per combination
onDelete: Cascade      deletes children with the parent

# commands
npx prisma migrate dev --name x   after editing the schema (dev)
npx prisma migrate deploy         production
npx prisma generate               client undefined? run this
npx prisma studio                 browse the data

# read
findMany   many rows      findUnique  one, by unique field
findFirst  one, any where count       how many

# write
create  update  upsert  createMany  updateMany  deleteMany

# always
where: { shop: session.shop }      on every shop-data query
deleteMany / updateMany            for anything user-triggered