Skip to main content

​
Overview

Dub uses MySQL 8.0+ as its primary database, managed through Prisma ORM. This guide covers database setup for both local development and production environments.

​
Database Requirements

  • MySQL 8.0 or higher
  • Minimum 1GB RAM allocated to database
  • Support for at least 1000 concurrent connections
  • InnoDB storage engine (default)

​
Supported Platforms

  • PlanetScale - Serverless MySQL platform (recommended for production)
  • Amazon RDS for MySQL - Managed MySQL on AWS
  • Google Cloud SQL for MySQL - Managed MySQL on GCP
  • Azure Database for MySQL - Managed MySQL on Azure
  • Self-managed MySQL 8.0+ - On your own infrastructure
PlanetScale provides a serverless MySQL platform with branching, non-blocking schema changes, and automatic backups.
1

Create Database

  1. Sign up at planetscale.com
  2. Create a new database
  3. Note your database name
2

Generate Connection String

  1. Go to your database settings
  2. Click “Connect”
  3. Select “Prisma” as the connection method
  4. Copy the connection string
3

Configure Environment

Add the connection string to your .env file:
DATABASE_URL="mysql://username:[email protected]/database?sslaccept=strict"

​
Amazon RDS for MySQL

1

Create RDS Instance

  1. Open AWS RDS Console
  2. Click “Create database”
  3. Choose MySQL 8.0 or higher
  4. Select instance size (minimum db.t3.micro for testing)
  5. Configure VPC and security groups
2

Configure Security Group

Allow inbound traffic on port 3306 from your application servers:
  • Type: MySQL/Aurora
  • Port: 3306
  • Source: Your application’s security group or IP range
3

Set Connection String

DATABASE_URL="mysql://username:[email protected]:3306/dub"

​
Self-Managed MySQL

1

Install MySQL 8.0

Ubuntu/Debian:
sudo apt update
sudo apt install mysql-server-8.0
macOS (Homebrew):
brew install [email protected]
Docker:
docker run -d \
  --name dub-mysql \
  -e MYSQL_ROOT_PASSWORD=yourpassword \
  -e MYSQL_DATABASE=dub \
  -p 3306:3306 \
  mysql:8.0
2

Configure MySQL

Edit MySQL configuration (/etc/mysql/mysql.conf.d/mysqld.cnf):
[mysqld]
max_connections = 1000
default-authentication-plugin = mysql_native_password
Restart MySQL:
sudo systemctl restart mysql
3

Create Database and User

CREATE DATABASE dub CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'dubuser'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON dub.* TO 'dubuser'@'%';
FLUSH PRIVILEGES;
4

Set Connection String

DATABASE_URL="mysql://dubuser:secure_password@localhost:3306/dub"

​
Local Development Setup

For local development, use Docker Compose to run MySQL and PlanetScale proxy.

​
Using Docker Compose

Dub includes a docker-compose.yml file for local development:
docker-compose.yml
services:
  ps-mysql:
    image: mysql:8.0
    restart: always
    environment:
      MYSQL_DATABASE: planetscale
      MYSQL_ROOT_HOST: "%"
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    command:
      [
        "--max_connections=1000",
        "--default-authentication-plugin=mysql_native_password",
      ]
    ports:
      - 3306:3306
    volumes:
      - ps-mysql:/var/lib/mysql

  planetscale-proxy:
    image: ghcr.io/mattrobenolt/ps-http-sim:latest
    command:
      [
        "-mysql-no-pass",
        "-listen-port=3900",
        "-mysql-dbname=planetscale",
        "-mysql-addr=ps-mysql",
      ]
    depends_on:
      - ps-mysql
    ports:
      - 3900:3900

volumes:
  ps-mysql:
1

Start Services

From the apps/web directory:
docker compose up -d
This starts:
  • MySQL 8.0 on port 3306
  • PlanetScale HTTP proxy on port 3900
2

Configure Environment

Update your .env file:
DATABASE_URL="mysql://root:@localhost:3306/planetscale"
PLANETSCALE_DATABASE_URL="http://root:unused@localhost:3900/planetscale"
3

Verify Connection

Test the connection:
docker compose ps
Both services should show as “Up”.

​
Prisma Configuration

Dub uses Prisma for database management. The schema is located at packages/prisma/schema/schema.prisma.

​
Prisma Schema Overview

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

generator client {
  provider = "prisma-client-js"
}
The relationMode = "prisma" setting enables Prisma to handle foreign key constraints at the application level, which is required for PlanetScale compatibility.

​
Prisma Commands

Dub includes several Prisma commands in package.json: 
pnpm prisma:generate

​
Initial Database Setup

1

Generate Prisma Client

Generate the Prisma client from the schema:
cd apps/web
pnpm prisma:generate
This creates the Prisma client in node_modules/@prisma/client.
2

Push Schema to Database

Create all tables in the database:
pnpm prisma:push
This command:
  • Reads the Prisma schema
  • Creates all tables and columns
  • Sets up indexes
  • Configures relationships
prisma:push is destructive and should only be used in development or for initial setup. For production, use proper migration workflows.
3

Verify Schema

Open Prisma Studio to verify the database structure:
pnpm prisma:studio
This opens a web interface at http://localhost:5555 where you can:
  • Browse all tables
  • View and edit data
  • Test queries
4

Seed Development Data (Optional)

For local development, you can seed the database with test data:
pnpm run script dev/seed
To reset the database before seeding:
pnpm run script dev/seed --truncate
The seed script will prompt for confirmation when using --truncate to prevent accidental data loss.

​
Database Schema

Dub’s database includes the following core tables:
  • User - User accounts and authentication
  • Account - OAuth provider accounts
  • Session - User sessions for NextAuth.js
  • Project - Workspaces/organizations
  • ProjectUsers - User memberships in projects
  • Link - Short links
  • Domain - Custom domains
  • Tag - Link tags and labels
  • Token - API tokens
  • RestrictedToken - Restricted API tokens with scoped permissions
  • Integration - Third-party integrations
  • Partner - Affiliate program partners
  • Program - Affiliate programs
  • Payout - Partner payouts
  • Commission - Affiliate commissions
And many more supporting tables for analytics, webhooks, notifications, etc.

​
Connection Pooling

For production deployments, configure connection pooling to handle high traffic:

​
PlanetScale Connection Pooling

PlanetScale automatically handles connection pooling. Use the connection string provided: 
DATABASE_URL="mysql://username:[email protected]/database?sslaccept=strict"

​
RDS Proxy (AWS)

For Amazon RDS, use RDS Proxy for connection pooling:
  1. Create an RDS Proxy in the AWS Console
  2. Configure target database
  3. Update connection string to use proxy endpoint:
DATABASE_URL="mysql://username:[email protected]:3306/dub"

​
External Pooling (PgBouncer Alternative)

For MySQL connection pooling outside of managed services, consider:
  • ProxySQL - MySQL-specific connection pooler
  • MaxScale - MariaDB connection pooler (MySQL compatible)

​
Performance Optimization

​
Indexes

Dub’s Prisma schema includes optimized indexes for common queries. Key indexes: 
model Link {
  // ...
  @@index([projectId])
  @@index([domain])
  @@index([url])
  @@index([createdAt])
  @@index([userId])
}

​
Query Performance

Monitor slow queries using: 
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- View slow queries
SHOW VARIABLES LIKE 'slow_query%';

​
Database Size Management

For analytics data, Dub uses Tinybird instead of storing click events in MySQL, keeping the database size manageable.

​
Backup & Recovery

​
PlanetScale Backups

PlanetScale provides automatic daily backups with point-in-time recovery:
  • Backups retained for 7-30 days depending on plan
  • Restore through PlanetScale dashboard
  • Test restores regularly

​
Manual Backups

For self-managed databases: 
# Create backup
mysqldump -u username -p dub > backup_$(date +%Y%m%d_%H%M%S).sql

# Restore from backup
mysql -u username -p dub < backup_20260303_120000.sql

​
Automated Backup Script

backup.sh
#!/bin/bash
BACKUP_DIR="/backups/mysql"
DATABASE="dub"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR
mysqldump -u dubuser -p$MYSQL_PASSWORD $DATABASE | gzip > $BACKUP_DIR/dub_$DATE.sql.gz

# Keep only last 30 days
find $BACKUP_DIR -name "dub_*.sql.gz" -mtime +30 -delete
Schedule with cron: 
0 2 * * * /path/to/backup.sh

​
Troubleshooting

​
Connection Issues

Error: “Can’t connect to MySQL server” 
# Test connectivity
mysql -h hostname -u username -p

# Check if MySQL is running
systemctl status mysql

# Verify port is open
telnet hostname 3306

​
Schema Sync Issues

Error: “The table does not exist in the current database” 
# Regenerate Prisma client and push schema
pnpm prisma:generate
pnpm prisma:push

​
Authentication Issues

Error: “Access denied for user” 
-- Verify user exists
SELECT User, Host FROM mysql.user WHERE User = 'dubuser';

-- Reset password
ALTER USER 'dubuser'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

​
Max Connections Exceeded

Error: “Too many connections” 
-- Check current connections
SHOW STATUS WHERE variable_name = 'Threads_connected';

-- Increase max connections
SET GLOBAL max_connections = 2000;
Make permanent by editing MySQL config: 
[mysqld]
max_connections = 2000

​
Migration from Other Databases

Dub is designed for MySQL. Migration from PostgreSQL or other databases requires schema conversion.
Dub’s Prisma schema is optimized for MySQL. Using other databases is not officially supported and may require significant code changes.

​
Production Checklist

  • Database is MySQL 8.0 or higher
  • Connection pooling is configured
  • Backups are automated and tested
  • Monitoring is set up for performance and errors
  • Connection string uses SSL/TLS
  • Database credentials are stored securely
  • max_connections is set appropriately (minimum 1000)
  • Slow query log is enabled
  • Regular maintenance windows are scheduled
  • Disaster recovery plan is documented

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us