Database Setup - Minimal

Minimal uses Prisma ORM with SQLite as the database engine. You can use either local SQLite files for development or Turso (distributed SQLite) for production.

Database Architecture

Minimal’s database is built on:

ORM: Prisma 7.0.1
Database: SQLite (via libSQL)
Adapter: @prisma/adapter-libsql with @libsql/client
Production: Turso (distributed SQLite)

Database Schema

Minimal uses the following database models:

User Model

Stores user account information:

model user {
  id              String   @id @default(cuid())
  name            String
  email           String   @unique
  emailVerified   Boolean  @default(false)
  image           String?
  username        String?  @unique
  bio             String?
  github          String?
  twitter         String?
  website         String?
  isProfilePublic Boolean  @default(false)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  sessions  session[]
  accounts  account[]
  bookmarks bookmark[]
  groups    group[]
}

Session Model

Manages user authentication sessions:

model session {
  id        String   @id @default(cuid())
  expiresAt DateTime
  token     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  ipAddress String?
  userAgent String?
  userId    String

  user user @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Account Model

Stores OAuth and password authentication data:

model account {
  id                    String    @id @default(cuid())
  accountId             String
  providerId            String
  userId                String
  accessToken           String?
  refreshToken          String?
  idToken               String?
  accessTokenExpiresAt  DateTime?
  refreshTokenExpiresAt DateTime?
  scope                 String?
  password              String?
  createdAt             DateTime  @default(now())
  updatedAt             DateTime  @updatedAt

  user user @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Group Model

Organizes bookmarks into groups:

model group {
  id        String   @id @default(cuid())
  name      String
  color     String
  isPublic  Boolean  @default(false)
  userId    String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user      user       @relation(fields: [userId], references: [id], onDelete: Cascade)
  bookmarks bookmark[]
}

Bookmark Model

Stores individual bookmarks:

model bookmark {
  id             String    @id @default(cuid())
  title          String
  url            String?
  normalizedUrl  String?
  favicon        String?
  type           String    @default("link")
  color          String?
  isPublic       Boolean?
  primarySource  String?
  sourceHistory  String?
  lastCapturedAt DateTime?
  groupId        String
  userId         String
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt

  group group @relation(fields: [groupId], references: [id], onDelete: Cascade)
  user  user  @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Verification Model

Handles email verification and password reset tokens:

model verification {
  id         String   @id @default(cuid())
  identifier String
  value      String
  expiresAt  DateTime
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
}

Local SQLite Setup

Local SQLite is perfect for development and small deployments. For production, consider using Turso.

Configuration

Set Database URL

Configure DATABASE_URL in your .env file:

.env

DATABASE_URL="file:./dev.db"

This creates a SQLite database file named dev.db in your project root.

Generate Prisma Client

Generate the Prisma client from your schema:

bun run db:generate

This creates type-safe database client in ./prisma/generated/client/.

Push Schema to Database

Create database tables from your schema:

bun run db:push

This creates all tables, indexes, and constraints in your SQLite database.

Verify Setup

Open Prisma Studio to view your database:

bun run db:studio

This opens a web interface at http://localhost:5555 to browse your data.

Database Location

By default, the SQLite database is created at ./dev.db relative to your project root. You can customize this:

.env

# Relative path
DATABASE_URL="file:./database/minimal.db"

# Absolute path
DATABASE_URL="file:/var/lib/minimal/minimal.db"

Ensure the application has write permissions to the database directory.

Turso Setup (Production)

Turso is a distributed SQLite database perfect for production deployments. It provides:

Global edge replicas
Automatic backups
Point-in-time recovery
Better scalability than local SQLite

Installation

Install Turso CLI

curl -sSfL https://get.tur.so/install.sh | bash

Authenticate

turso auth login

This opens a browser window to authenticate with GitHub.

Create Database

turso db create minimal-production

For better performance, create the database in a region close to your users:

turso db create minimal-production --location iad

Available locations: iad (US East), sjc (US West), fra (Europe), nrt (Asia), etc.

Get Connection Details

turso db show minimal-production

This displays your database URL and other details.

Create Auth Token

turso db tokens create minimal-production

This generates an authentication token for your application.

Configure Environment

Add to your .env file:

.env

TURSO_DATABASE_URL="libsql://minimal-production-your-org.turso.io"
TURSO_AUTH_TOKEN="eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9..."

Push Schema

bunx prisma db push

This creates all tables in your Turso database.

Database Connection

The database connection logic in lib/db.ts automatically selects the correct database:

const databaseUrl =
  process.env.TURSO_DATABASE_URL ??
  process.env.DATABASE_URL ??
  "file:./dev.db";

const adapter = new PrismaLibSql({
  url: databaseUrl,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

Priority order:

TURSO_DATABASE_URL + TURSO_AUTH_TOKEN (production)
DATABASE_URL (local SQLite)
file:./dev.db (fallback)

Database Migrations

Development Workflow

For development, use db:push for rapid iteration:

bun run db:push

This synchronizes your schema without creating migration files.

Production Migrations

For production, use proper migrations:

Create Migration

bunx prisma migrate dev --name your_migration_name

This creates a migration file in prisma/migrations/.

Review Migration

Check the generated SQL in prisma/migrations/[timestamp]_your_migration_name/migration.sql.

Deploy to Turso

turso db shell minimal-production < prisma/migrations/[timestamp]_your_migration_name/migration.sql

Or use the npm script:

bun run db:migrate:deploy [timestamp]_your_migration_name

Database Management

View Data

Open Prisma Studio to browse and edit data:

bun run db:studio

Backup Local Database

cp dev.db dev.db.backup

Backup Turso Database

Turso provides automatic backups and point-in-time recovery:

# Create manual backup
turso db dump minimal-production > backup.sql

# Restore from backup
turso db shell minimal-production < backup.sql

Reset Database

This will delete all data. Only use in development.

# Delete database file
rm dev.db

# Recreate schema
bun run db:push

Performance Optimization

Indexes

Minimal includes optimized indexes for common queries:

@@index([userId])
@@index([userId, isPublic])
@@index([groupId])
@@index([createdAt])
@@index([userId, normalizedUrl])

Connection Pooling

Prisma automatically manages connection pooling. For Turso, connections are multiplexed over HTTP.

Query Optimization

Use Prisma’s built-in query optimization:

// Include relations efficiently
const bookmarks = await db.bookmark.findMany({
  include: {
    group: true,
    user: true,
  },
});

// Use select for specific fields
const bookmarks = await db.bookmark.findMany({
  select: {
    id: true,
    title: true,
    url: true,
  },
});

Troubleshooting

”No database URL configured for production”

This error occurs when DATABASE_URL or TURSO_DATABASE_URL is not set in production:

.env

# Add one of these:
DATABASE_URL="file:./minimal.db"
# OR
TURSO_DATABASE_URL="libsql://your-db.turso.io"
TURSO_AUTH_TOKEN="your-token"

“Table does not exist”

Run database push to create tables:

bun run db:push

“Permission denied” on SQLite file

Ensure the application has write access:

chmod 644 dev.db
chmod 755 .

Turso connection timeout

Verify TURSO_DATABASE_URL is correct
Check TURSO_AUTH_TOKEN is valid
Ensure network connectivity to Turso

Database Schema Changes

When modifying the schema:

Edit Schema

Modify prisma/schema.prisma

Format Schema

bunx prisma format

Generate Client

bun run db:generate

Apply Changes

Development:

bun run db:push

Production:

bunx prisma migrate dev
bun run db:migrate:deploy [migration-name]

Next Steps

Authentication Setup - Configure Better Auth
Configuration - Environment variables
Installation - Complete installation guide

​Database Architecture

​Database Schema

​User Model

​Session Model

​Account Model

​Group Model

​Bookmark Model

​Verification Model

​Local SQLite Setup

​Configuration

​Database Location

​Turso Setup (Production)

​Installation

​Database Connection

​Database Migrations

​Development Workflow

​Production Migrations

​Database Management

​View Data

​Backup Local Database

​Backup Turso Database

​Reset Database

​Performance Optimization

​Indexes

​Connection Pooling

​Query Optimization

​Troubleshooting

​”No database URL configured for production”

​“Table does not exist”

​“Permission denied” on SQLite file

​Turso connection timeout

​Database Schema Changes

​Next Steps

Database Architecture

Database Schema

User Model

Session Model

Account Model

Group Model

Bookmark Model

Verification Model

Local SQLite Setup

Configuration

Database Location

Turso Setup (Production)

Installation

Database Connection

Database Migrations

Development Workflow

Production Migrations

Database Management

View Data

Backup Local Database

Backup Turso Database

Reset Database

Performance Optimization

Indexes

Connection Pooling

Query Optimization

Troubleshooting

”No database URL configured for production”

“Table does not exist”

“Permission denied” on SQLite file

Turso connection timeout

Database Schema Changes

Next Steps