Skip to main content
The Shopify Subscriptions Reference App uses Prisma ORM for database management. This guide covers database setup for development and production environments.

​
Database Schema Overview

The application uses three main database tables:

​
Session Table

Stores Shopify OAuth session data including access tokens and shop information.

​
BillingSchedule Table

Manages billing schedules for subscription processing with timezone and timing configuration.

​
DunningTracker Table

Tracks failed billing attempts and dunning processes for subscription recovery.

​
Database Provider Options

SQLite is configured by default but is NOT recommended for production. Use PostgreSQL or MySQL for production deployments.

​
SQLite (Development Only)

The default configuration uses SQLite: 
datasource db {
  provider = "sqlite"
  url      = "file:./data.db"
}
Use case: Local development and testing only Limitations:
  • Single file-based database
  • No concurrent write support
  • Not suitable for production workloads
  • Limited scalability
PostgreSQL is the recommended database for production deployments.
1

Update Prisma Schema

Edit prisma/schema.prisma:
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
2

Set Database URL

Configure your PostgreSQL connection string in .env:
DATABASE_URL="postgresql://username:password@hostname:5432/database_name?schema=public"
Include ?schema=public to specify the PostgreSQL schema. For connection pooling, see the Connection Pooling section.
3

Run Migrations

Apply database migrations:
pnpm prisma migrate deploy

​
MySQL

MySQL is also supported for production deployments.
1

Update Prisma Schema

Edit prisma/schema.prisma:
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}
2

Set Database URL

Configure your MySQL connection string:
DATABASE_URL="mysql://username:password@hostname:3306/database_name"
3

Run Migrations

Apply database migrations:
pnpm prisma migrate deploy

​
Prisma Schema Details

The complete Prisma schema from prisma/schema.prisma: 
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["tracing"]
}

datasource db {
  provider = "sqlite"
  url      = "file:./data.db"
}

model Session {
  id            String    @id
  shop          String
  state         String
  isOnline      Boolean   @default(false)
  scope         String?
  expires       DateTime?
  accessToken   String
  userId        BigInt?
  firstName     String?
  lastName      String?
  email         String?
  accountOwner  Boolean   @default(false)
  locale        String?
  collaborator  Boolean?  @default(false)
  emailVerified Boolean?  @default(false)
}

model BillingSchedule {
  id        Int      @id @default(autoincrement())
  shop      String   @unique
  hour      Int      @default(10)
  timezone  String   @default("America/Toronto")
  active    Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model DunningTracker {
  id                Int       @id @default(autoincrement())
  shop              String
  contractId        String
  billingCycleIndex Int
  failureReason     String
  completedAt       DateTime?
  completedReason   String?

  @@unique([shop, contractId, billingCycleIndex, failureReason], name: "uniqueBillingCycleFailure")
  @@index([completedAt])
}

​
Database Setup Commands

​
Initialize Database

Run the setup script to generate Prisma Client and deploy migrations: 
pnpm run setup
This command executes:
  1. prisma generate - Generates TypeScript types and Prisma Client
  2. prisma migrate deploy - Runs all pending migrations

​
Run Migrations

Apply database migrations in production: 
pnpm run db:migrate
Always run migrations before starting your application server.

​
Generate Prisma Client

Regenerate Prisma Client after schema changes: 
pnpm prisma generate

​
Create New Migration (Development)

Create a new migration after modifying the schema: 
pnpm prisma migrate dev --name migration_name

​
View Database in Prisma Studio

Open Prisma Studio to view and edit database records: 
pnpm prisma studio

​
Connection Configuration

​
Connection String Format

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public"

​
Connection Pooling

For production deployments with connection pooling (e.g., PgBouncer): 
# Direct connection for migrations
DATABASE_URL="postgresql://user:password@host:5432/database?schema=public"

# Pooled connection for application
DATABASE_POOL_URL="postgresql://user:password@pooler:6543/database?schema=public&pgbouncer=true"
Update app/db.server.ts to use the pooled connection: 
const databaseUrl = process.env.DATABASE_POOL_URL || process.env.DATABASE_URL;

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: databaseUrl,
    },
  },
});

​
Database Hosting Options

​
Managed Database Services

PostgreSQL Options: MySQL Options:
Choose a database provider in the same region as your application server to minimize latency.

​
Production Database Best Practices

1

Enable Connection Pooling

Use connection pooling to handle multiple concurrent requests efficiently:
  • PgBouncer for PostgreSQL
  • ProxySQL for MySQL
  • Built-in pooling from managed providers
2

Set Up Backups

Configure automated database backups:
  • Daily automated backups (minimum)
  • Point-in-time recovery enabled
  • Backup retention policy (30+ days recommended)
  • Test backup restoration regularly
3

Monitor Database Performance

Implement database monitoring:
  • Query performance tracking
  • Connection pool metrics
  • Slow query logs
  • Database resource utilization
4

Secure Database Access

Follow security best practices:
  • Use strong passwords
  • Enable SSL/TLS connections
  • Restrict network access (IP whitelist)
  • Use environment variables for credentials
  • Never commit credentials to version control
Always use SSL connections in production. Add ?sslmode=require to your connection string.
5

Set Connection Limits

Configure appropriate connection limits:
// In app/db.server.ts
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  // Connection pool configuration
  // Adjust based on your hosting environment
});

​
Session Storage Configuration

The app supports two session storage modes:

​
Prisma Session Storage (Production)

Stores sessions in your database using Prisma: 
// Configured in app/shopify.server.ts
import {PrismaSessionStorage} from '@shopify/shopify-app-session-storage-prisma';
import prisma from '~/db.server';

const shopify = shopifyApp({
  sessionStorage: new PrismaSessionStorage(prisma),
  // ... other config
});

​
Memory Session Storage (Development/Testing)

Stores sessions in memory (data lost on restart): 
import {MemorySessionStorage} from '@shopify/shopify-app-session-storage-memory';

const shopify = shopifyApp({
  sessionStorage: new MemorySessionStorage(),
  // ... other config
});
Never use MemorySessionStorage in production. Sessions will be lost when the server restarts.

​
Troubleshooting

​
Migration Failures

If migrations fail: 
# Reset database (development only - destroys all data)
pnpm prisma migrate reset

# Resolve migration issues
pnpm prisma migrate resolve --rolled-back "<migration_name>"

​
Connection Issues

Test database connectivity: 
# Verify connection
pnpm prisma db pull
Common issues:
  • Incorrect connection string format
  • Firewall blocking database port
  • Invalid credentials
  • SSL certificate issues

​
Prisma Client Generation

If TypeScript cannot find Prisma types: 
# Regenerate Prisma Client
pnpm prisma generate

# Restart TypeScript server in your editor

​
Next Steps

After setting up your database:
  1. Configure environment variables
  2. Deploy to production

Build docs developers (and LLMs) love

Get started for freeTalk to us