Strapi Database Configuration — PostgreSQL, MySQL, and Migrations

In this tutorial, you will learn how to configure Strapi databases for different environments — comparing PostgreSQL and MySQL, tuning connection pools, migrating between databases, and managing production databases for reliability and performance.

What You'll Learn

• How PostgreSQL and MySQL compare for Strapi workloads
• How to configure database connections and connection pooling
• How to migrate data between SQLite and PostgreSQL
• How to manage database migrations and schema changes
• How to tune the database for production performance
• How to back up and restore Strapi databases

Why It Matters

The database is the foundation of every Strapi project. A poorly configured database causes slow API responses, connection exhaustion under traffic, and data loss when things go wrong. Choosing the right database and configuring it properly determines whether your Strapi project handles production traffic or buckles under load.

Real-World Use

A Strapi-powered news site started with SQLite during development. At launch, traffic grew to 10,000 daily API requests. SQLite could not handle concurrent writes — editors saw "database is locked" errors. The team migrated to PostgreSQL with a connection pool of 10, tuned the query cache, and the errors disappeared. The site now handles 500,000 requests daily without database issues.

Learning Path

flowchart LR
  A["Production Setup"] --> B["Database Configuration
-- You are here"]:::current
  B --> C["Environment Variables"]
  C --> D["CI/CD"]
  D --> E["Performance"]

  classDef current fill:#4945ff,color:#fff,stroke-width:2px

PostgreSQL vs MySQL

Strapi supports both PostgreSQL and MySQL. Here is how they compare:

Feature              PostgreSQL          MySQL
-----------          ----------          -----
ACID compliance      Full                Partial (depends on engine)
JSON support         Native JSONB        JSON (slower queries)
Concurrent writes    Excellent           Good (table locks in InnoDB)
Extension ecosystem  Rich (PostGIS, etc) Moderate
Connection pool      Built-in (pgbouncer) Via ProxySQL
Default in Strapi    Recommended         Supported
Recovery             Point-in-time       Point-in-time

PostgreSQL is the recommended production database for Strapi because of its superior concurrent write handling and JSONB support. Strapi stores dynamic zones and components as JSON, and PostgreSQL's JSONB enables efficient queries on this data.

// PostgreSQL configuration (recommended for production)
// config/database.js
module.exports = ({ env }) => ({
  connection: {
    client: "postgres",
    connection: {
      host: env("DATABASE_HOST", "localhost"),
      port: env.int("DATABASE_PORT", 5432),
      database: env("DATABASE_NAME", "strapi"),
      user: env("DATABASE_USERNAME", "strapi"),
      password: env("DATABASE_PASSWORD", "password"),
      ssl: env.bool("DATABASE_SSL", true),
      schema: "public",
    },
    pool: {
      min: 2,
      max: 10,
      acquireTimeoutMillis: 30000,
      createTimeoutMillis: 30000,
      idleTimeoutMillis: 30000,
      reapIntervalMillis: 1000,
    },
    debug: false,
  },
});

// MySQL configuration (alternative)
// config/database.js
module.exports = ({ env }) => ({
  connection: {
    client: "mysql",
    connection: {
      host: env("DATABASE_HOST", "localhost"),
      port: env.int("DATABASE_PORT", 3306),
      database: env("DATABASE_NAME", "strapi"),
      user: env("DATABASE_USERNAME", "strapi"),
      password: env("DATABASE_PASSWORD", "password"),
      ssl: env.bool("DATABASE_SSL", false),
    },
    pool: {
      min: 2,
      max: 10,
    },
    // Required for MySQL to handle timezone
    timezone: "UTC",
    debug: false,
  },
});

Connection Pooling Explained

The connection pool manages database connections so your Strapi server does not open a new connection for every request.

Without pooling:
  Request 1 → open connection → query → close connection
  Request 2 → open connection → query → close connection
  Request 3 → open connection → query → close connection

With pooling:
  Pool has [conn1, conn2, conn3] (reusable)
  Request 1 → get conn1 → query → return conn1 to pool
  Request 2 → get conn2 → query → return conn2 to pool
  Request 3 → get conn1 (reused) → query → return conn1

Pool settings control how many connections are maintained:

{
  pool: {
    min: 2,    // Keep 2 connections always ready
    max: 10,   // Never open more than 10 connections
    acquireTimeoutMillis: 30000,  // Wait 30s before giving up
    idleTimeoutMillis: 30000,     // Close idle connections after 30s
  }
}

Set min to the expected baseline traffic. Set max based on your database server's memory. Each connection uses about 10MB of RAM. On a 4GB database server, max of 200 connections is safe.

Database Migrations

Strapi handles schema migrations automatically when you change content types in the Content-Type Builder. Here is what happens behind the scenes:

When you add a "price" field to a "Product" content type:

1. Strapi reads the updated schema from content-type JSON files
2. Generates an ALTER TABLE statement:
   ALTER TABLE products ADD COLUMN price decimal(10,2);
3. Executes the migration
4. Updates the internal schema version tracker

You can also create manual migrations for data transformations:

// config/migrations/2026-06-28-add-slug-to-articles.js
module.exports = {
  async up(knex) {
    // Add slug column to articles table
    await knex.schema.table("articles", (table) => {
      table.string("slug").unique();
    });

    // Generate slugs for existing articles
    const articles = await knex("articles").select("id", "title");
    for (const article of articles) {
      const slug = article.title
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, "-")
        .replace(/(^-|-$)/g, "");
      await knex("articles").where({ id: article.id }).update({ slug });
    }
  },

  async down(knex) {
    // Rollback: remove the slug column
    await knex.schema.table("articles", (table) => {
      table.dropColumn("slug");
    });
  },
};

Migrating from SQLite to PostgreSQL

When moving from development to production, you migrate from SQLite to PostgreSQL. Here is the process:

# Step 1: Dump SQLite data to JSON
# Use a custom script to export all content types

# Step 2: Set up PostgreSQL database
sudo -u postgres psql -c "CREATE DATABASE strapi_production;"
sudo -u postgres psql -c "CREATE USER strapi_user WITH PASSWORD 'secure_password';"
sudo -u postgres psql -c "GRANT ALL ON DATABASE strapi_production TO strapi_user;"

# Step 3: Configure Strapi for PostgreSQL and run first start
# Strapi creates the schema tables automatically
NODE_ENV=production npm run build
NODE_ENV=production npm run start

// Migration script: export.js
// Run: node export.js > strapi-data.json
const sqlite3 = require("sqlite3").verbose();
const db = new sqlite3.Database(".tmp/data.db");

db.all("SELECT name FROM sqlite_master WHERE type='table'", (err, tables) => {
  const exportData = {};
  let completed = 0;

  tables.forEach((table) => {
    db.all(`SELECT * FROM "${table.name}"`, (err, rows) => {
      exportData[table.name] = rows;
      completed++;

      if (completed === tables.length) {
        console.log(JSON.stringify(exportData, null, 2));
        db.close();
      }
    });
  });
});

Database Backup and Restore

Regular backups protect against data loss. Use these commands for PostgreSQL:

# Backup the database
pg_dump strapi_production > strapi-backup-2026-06-28.sql

# Backup with compression
pg_dump strapi_production | gzip > strapi-backup-2026-06-28.sql.gz

# Automatic daily backup with cron
# crontab -e
# 0 2 * * * pg_dump strapi_production | gzip > /backups/strapi-$(date +\%Y-\%m-\%d).sql.gz

# Restore from backup
psql strapi_production < strapi-backup-2026-06-28.sql

# Or restore from compressed backup
gunzip -c strapi-backup-2026-06-28.sql.gz | psql strapi_production

For MySQL:

# Backup
mysqldump strapi_production > strapi-backup-2026-06-28.sql

# Restore
mysql strapi_production < strapi-backup-2026-06-28.sql

Database Performance Tuning

Tune your database for Strapi workloads:

PostgreSQL performance settings (postgresql.conf):

# Memory settings (adjust based on server RAM)
shared_buffers = 1GB           # 25% of RAM
work_mem = 32MB                # Per-operation memory
maintenance_work_mem = 256MB   # For VACUUM and index creation
effective_cache_size = 3GB     # 75% of RAM

# Write settings
wal_buffers = 16MB
synchronous_commit = off       # Faster but less durable
checkpoint_completion_target = 0.9

# Query planner
default_statistics_target = 100
random_page_cost = 1.1         # SSDs are fast

Enable query logging to find slow queries:

# postgresql.conf
log_min_duration_statement = 200  # Log queries taking 200ms+
log_connections = on
log_disconnections = on

Common Mistakes

1. Using SQLite in production. SQLite cannot handle concurrent writes. When two editors save content simultaneously, one gets a "database is locked" error. Always use PostgreSQL or MySQL for production deployments.

2. Setting pool max too high for the database server. Each database connection uses ~10MB of RAM. Setting pool max to 100 on a server with 512MB RAM causes out-of-memory errors. Calculate max connections = available RAM / 10MB.

3. Not enabling SSL for the database connection. Without SSL, data travels in plaintext between Strapi and the database. On cloud-hosted databases, this is a security risk. Enable SSL with ssl: true in the database config.

4. Skipping database backups. A corrupted database without backups means lost content. Set up automated daily backups before going to production. Test restoring from backups regularly.

5. Ignoring database migrations when deploying. If a team member added a content type field and you deploy without running the migration, the new code breaks because the column does not exist. Always run migrations as part of the deployment process.

Practice Questions

1. Why is PostgreSQL recommended over MySQL for Strapi production? Answer: PostgreSQL has better concurrent write handling, native JSONB for efficient component and dynamic zone queries, and full ACID Compliance. Strapi's internal schema is optimized for PostgreSQL.

2. What does the connection pool min setting control? Answer: The min setting controls how many database connections stay open and ready even when idle. Setting it to 2 means two connections are always available, reducing latency for sudden traffic spikes.

3. How do you migrate from SQLite to PostgreSQL for an existing Strapi project? Answer: Export data from SQLite as JSON, set up the PostgreSQL database and user, configure Strapi to use PostgreSQL, start Strapi (it creates the schema), and import the data. Test thoroughly before switching production.

4. Challenge: Set up a complete database management system for Strapi: (1) Configure PostgreSQL with connection pooling (min: 2, max: 10), (2) Enable SSL for the database connection, (3) Set up automated daily backups with 7-day retention, (4) Configure slow query logging for queries taking over 200ms, (5) Write a migration script that adds a "featured" boolean field to an existing content type and sets the first 5 entries to featured, (6) Test the backup and restore process.

FAQ

Mini Project

Your task: Configure and manage a production-like Strapi database.

1. Install PostgreSQL on your development machine or use a cloud service (ElephantSQL, Supabase, or RDS).
2. Create a database and user for Strapi with appropriate permissions.
3. Configure Strapi to use PostgreSQL with SSL enabled and connection pooling (min: 2, max: 10).
4. Start Strapi and verify the connection works by creating a few content entries.
5. Write a backup script that dumps the database to a timestamped file and keeps only the last 7 backups.
6. Simulate a database failure by stopping PostgreSQL, observe how Strapi behaves, then restore the service.
7. Test the restore process by dropping the database and restoring from your backup.
8. Write a migration script that adds a "views_count" integer field to an existing content type and seeds it with random values.

What's Next

Now that you understand database configuration, proceed to Environment Variables to learn how to manage configuration across development, staging, and production environments. After that, set up CI/CD for automated deployment.

Related lessons:

• PostgreSQL — Database administration
• Node.js — Node.js database best practices
• MySQL — Alternative database setup