Skip to content
Tutorials Center
My learning

Topics

Lesson 45 of 55 ยท Testing, Deployment and Operations

Databases and Session Storage in Production

Move a Shopify app from SQLite to Postgres or MySQL: Prisma schema, session storage, multi-tenant data keyed by shop, migrations and safe uninstall cleanup.

A freshly generated Shopify app comes with a small SQLite file. That is perfect for your laptop: no server to install, nothing to configure. It is not what you want on the day a hundred merchants install your app, because a file on one machine cannot be shared by several servers and can vanish when a container restarts.

This lesson explains what your app must store, how the session table works, how to design every table around the shop, and how to change the database safely over time. The Prisma and SQL snippets are read-only: they need a real database. The runnable examples are stand-ins written in plain JavaScript that show the design rules (isolation between shops, migrations, cleanup) without any database. Version numbers and package names change, so check the current template before copying anything. The ideas here build on Token Exchange and Access Tokens and Authenticated Requests and Route Protection.

What an app has to store

Shopify holds the merchant's products, orders and customers. Your app stores only what Shopify does not:

  • Sessions and access tokens, so a background job can call the Admin API later.
  • Your own settings per shop (thresholds, plan, feature flags).
  • Work state: queued jobs, processed webhook ids, import progress.
  • Derived data you chose to copy for speed, such as a nightly stock snapshot.

A good rule from Webhooks in Shopify and the privacy lesson applies here: store as little of the merchant's data as you can. Every extra column is something you must secure, export on request and delete on uninstall.

flowchart TB
  subgraph Shopify
    A["Products, orders, customers"]
  end
  subgraph "Your database"
    S["Session<br/>(tokens per shop)"]
    C["ShopSettings"]
    J["Jobs and processed webhooks"]
    D["Optional copies"]
  end
  A -. "fetched when needed" .-> D
  S --> C
  S --> J

Shopify stays the source of truth; your database holds credentials, settings and work state.

The session table

The app template uses a Prisma adapter for session storage. The adapter reads and writes one table, usually named Session. A simplified Prisma schema looks like this (read-only; copy the real one from the template you generated, because columns have been added over time, for example for expiring tokens and user details):

PRISMA
datasource db {
  provider = "sqlite"
  url      = "file:dev.sqlite"
}

model Session {
  id          String    @id
  shop        String
  state       String
  isOnline    Boolean   @default(false)
  scope       String?
  expires     DateTime?
  accessToken String
  userId      BigInt?
}

Each row is one session. An offline session (one per shop) holds the token your background jobs use; an online session is tied to one staff member and expires. If you have forgotten the difference, review Online vs Offline Tokens. Two practical points:

  • Session ids are strings built by the library, so never parse them yourself; query by shop and isOnline instead.
  • The accessToken column is a secret. Anyone who can read your database can act as your app on every store. Restrict database access, use TLS to the database, and consider encrypting the column (your own field-level encryption, or the database provider's) so a leaked backup is not a leaked token.

Moving off SQLite

SQLite is a single file written by one process. That breaks down in production for three reasons: several app servers cannot share it, many hosts have ephemeral disks, and heavy parallel writes (webhook bursts) make it queue up. Move to a managed Postgres or MySQL database. With Prisma the change is small:

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

and in the environment:

Terminal
DATABASE_URL="postgresql://app_user:[email protected]:5432/myapp?sslmode=require"

Then:

  1. Generate the Prisma client again for the new provider.
  2. Create migrations against the new database (do not reuse SQLite migration files; their SQL is specific to SQLite, so start a fresh migration history for the new provider).
  3. Deploy migrations as a release step with prisma migrate deploy, which applies existing migration files and never invents new ones.
  4. Point DATABASE_URL at the production database through your host's secret store, never through a committed file (Deploying Your App covers environment variables).

During development you can keep SQLite for speed, but run your tests against the same engine as production at least in CI, because types, case sensitivity and constraints differ between engines.

Do not copy a development SQLite file into production. It contains test shops, test tokens and probably credentials you do not want in a real database.

Every table is keyed by shop

An app is multi-tenant: many merchants share one database. The most important design rule is that every row that belongs to a merchant carries the shop's domain (or an internal shop id) and every query filters by it. Forgetting one filter lets one merchant read another's data, and that is the kind of bug that gets an app removed from the App Store.

PRISMA
model ShopSettings {
  shop             String   @id
  lowStockLimit    Int      @default(5)
  plan             String   @default("free")
  updatedAt        DateTime @updatedAt
}

model ProcessedWebhook {
  webhookId String   @id
  shop      String
  topic     String
  createdAt DateTime @default(now())

  @@index([shop])
}

model Job {
  id        Int      @id @default(autoincrement())
  shop      String
  kind      String
  payload   Json
  runAt     DateTime
  attempts  Int      @default(0)

  @@index([shop, runAt])
}

Notes on this design:

  • ShopSettings uses shop as the primary key: one row per merchant, impossible to duplicate.
  • ProcessedWebhook.webhookId is the primary key, which is exactly the idempotency key from Reliable Webhook Handlers. A second insert of the same id fails, so the database itself enforces "process once".
  • Indexes follow the queries. If your job runner asks "what is due for this shop?", index (shop, runAt).

Where does the shop value come from? Only from the authenticated request: the verified session token or the verified webhook, never from a query string or body field the browser sent. The helper from authenticate.admin(request) returns the session whose shop you use for every query. A stand-in below enforces this rule by making it impossible to query without a shop.

Node.js A tenant-scoped store that cannot forget the shop

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

This is a stand-in: a real app would do the same with Prisma, either by always writing where: { shop } or by wrapping the client in a function that adds it. The pattern is the same: make the safe path the only path. A request for another shop's id returns "not found", which also avoids telling an attacker the row exists.

Migrations: change the schema without downtime

Your schema will change: a new setting, a new index. A migration is a numbered, reviewed script that moves the schema from one version to the next, and a table records which ones ran. Rules that save you from outages:

  1. Never edit a migration that has already shipped. Add a new one.
  2. Make changes backward compatible. During a deploy, old and new code run at the same time for a short while. Add a column as nullable (or with a default) first, deploy code that writes it, backfill, and only later make it required. To remove a column, stop reading it, deploy, then drop it.
  3. Run migrations before the new code starts, as a release step, and only once (not in every instance).
  4. Test the migration on a copy of production-sized data. Adding an index to a very large table can lock it.
  5. Keep a backup and know how to restore it before you need to.

The stand-in below applies a list of migrations in order, remembers what ran, refuses to apply an edited migration (it compares checksums) and is safe to run twice. Real tools such as Prisma Migrate do the same job against a real database.

Node.js A tiny migration runner with checksums

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Cleaning up on uninstall

When a merchant uninstalls, Shopify sends app/uninstalled and invalidates your tokens. You should delete the shop's sessions right away, since they are now useless secrets. Settings and job rows should go too, except anything you are required or allowed to keep for a short period (for example billing records). Shopify later sends the mandatory shop redaction webhook; see Privacy, GDPR and Mandatory Webhooks for what it requires. A handler in the template style (read-only):

JavaScript
// app/routes/webhooks.app.uninstalled.jsx
export const action = async ({ request }) => {
  const { shop, session } = await authenticate.webhook(request);

  if (session) {
    await db.session.deleteMany({ where: { shop } });
  }
  await db.job.deleteMany({ where: { shop } });
  await db.shopSettings.deleteMany({ where: { shop } });

  return new Response();
};

Use deleteMany rather than delete: the webhook can arrive twice, or the rows may already be gone, and an idempotent handler must not fail. A merchant can reinstall later, so your install path should create fresh rows without assuming old ones exist.

Other production habits

  • Connection limits. Serverless and many small instances each open connections. Use a connection pooler (or the provider's pooled connection string) so you do not run out.
  • Backups and restore drills. A backup you never restored is a hope, not a backup.
  • Least privilege. The app's database user needs to read and write its tables, not to drop the database. Use a separate, more powerful user only for migrations.
  • Time zones. Store timestamps in UTC and format them in the browser.
  • Money. If you store amounts, store integers in minor units plus a currency code, as in Money as Integers.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is a SQLite file a poor choice for a production app with several servers?

    SQLite is excellent for local development, but production needs a shared, durable, network-accessible database such as Postgres or MySQL.
  2. 2. Where should the shop value for a database query come from?

    Anything the browser sends can be forged. The authenticated request tells you which shop is calling, and that value must scope every query.
  3. 3. What does it mean that the ProcessedWebhook table uses webhookId as its primary key?

    A primary key must be unique. If the same webhook is delivered twice, the second insert fails, so you can detect and skip the duplicate.
  4. 4. Which is the safest way to remove a column that old code still reads?

    Old and new code overlap during a deploy. Removing a column in steps keeps both versions working, and shipped migrations are never edited.
  5. 5. Why use deleteMany in the uninstall handler?

    Webhooks can be delivered more than once. deleteMany succeeds with zero matches, so a repeat is harmless.

Summary

  • Shopify is the source of truth; your database holds sessions, settings, work state and a few derived copies.
  • The accessToken in the session table is a secret: protect, restrict and ideally encrypt it.
  • Move from SQLite to managed Postgres or MySQL, create new migrations for the new provider and deploy them with prisma migrate deploy.
  • Key every table by shop, take the shop only from authenticated requests and make unscoped queries impossible.
  • Never edit shipped migrations; make schema changes backward compatible and run them once per release.
  • Delete sessions and shop data on uninstall with idempotent handlers.

Next, put the app on a real server in Deploying Your App.