Database

Flexible database setup with Prisma ORM. Support for PostgreSQL, SQLite, and other databases with type-safe queries.

The boilerplate uses Prisma ORM, supporting multiple database engines including PostgreSQL, SQLite, MySQL, and MongoDB.

PostgreSQL is the recommended setup, but I've provided guides for SQLite as well.

Note: While the boilerplate supports both PostgreSQL and SQLite, switching between them after starting development is not recommended. I switch between them for testing purposes, but managing separate migration histories is quite a hassle.

What's Included

  • Prisma ORM setup and configuration
  • Type-safe database client
  • Database migration system
  • Connection pooling for serverless
  • Database management tools

Database Connection

Configure your database connection in .env:

# For serverless deployments, use pooling URL
DATABASE_URL="postgres://postgres:mysecretpassword@localhost:5432/postgres"
# For migrations and direct access
DIRECT_DATABASE_URL="postgres://postgres:mysecretpassword@localhost:5432/postgres"

Connection string format:

# PostgreSQL
postgres://<username>:<password>@<host>:<port>/<database>

# SQLite
file:<path-to-sqlite-file>

PostgreSQL Setup

Using Docker

Quick start with Docker (development or production):

docker run --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 -d postgres \
  -v saas_data:/var/lib/postgresql/data

Using Neon.tech

Neon is a fully managed serverless PostgreSQL platform with:

  • Generous free tier
  • Automatic scaling
  • Connection pooling
  • Branching capability
  1. Create a Neon account
  2. Create a database
  3. Copy the connection string (enable "Pooled connections" for serverless)

AWS PostgreSQL

Coming soon

SQLite Setup

The boilerplate comes with PostgreSQL by default. Due to SQLite's limitations with Prisma (no support for enums, JSON, etc.), you'll need to apply some changes to use SQLite.

I've created a patch that handles these modifications:

  1. Apply the SQLite patch:
    git apply patches/sqlite.patch
  2. Configure your database URL:
    DATABASE_URL=file:./dev.db

Additional SQLite Options

Prisma Tooling

Client Generation

Generate type-safe Prisma client:

pnpm prisma generate

Note: When deploying to serverless environments (AWS Lambda, Vercel, etc.), we need to ensure the Prisma client is generated and accessible during runtime.

Database Migrations

Development migrations:

pnpm prisma migrate dev

Production migrations (ideally run in a CI/CD environment):

pnpm prisma db push

Note: The boilerplate excludes the migrations folder from source control by default. Add it to version control once you choose your database.

Prisma Studio

Launch the database management UI:

pnpm prisma studio

Key Features

  1. Type Safety: Full TypeScript integration
  2. Migration System: Version-controlled schema changes
  3. Connection Pooling: Optimized for serverless
  4. Visual Editor: Built-in database management UI

Best Practices

  1. Choose database type early in development
  2. Use connection pooling for serverless deployments
  3. Version control your migration files
  4. Test migrations in staging environment

Need Help?