Database Setup

PostgreSQL configuration, migrations, and database management for Medusa

Database Setup

Medusa uses PostgreSQL as its primary database. This guide covers database setup, configuration, and migrations.

PostgreSQL Requirements

• PostgreSQL version: 12 or higher (PostgreSQL 14+ recommended)
• Database user: Must have create privileges
• Extensions: No special extensions required by default

Database Connection

Connection URL

Medusa connects to PostgreSQL using a connection URL:

DATABASE_URL=postgres://[user][:password]@[host][:port]/[dbname]

Where:

• [user]: Your PostgreSQL username (required)
• [:password]: User password (optional, prefix with :)
• [host]: Database host (required, e.g., localhost)
• [:port]: PostgreSQL port (optional, default: 5432, prefix with :)
• [dbname]: Database name (required)

Example Connection URLs

# Local development
DATABASE_URL=postgres://postgres@localhost/medusa-store

# With password
DATABASE_URL=postgres://postgres:password123@localhost/medusa-store

# Custom port
DATABASE_URL=postgres://postgres:password123@localhost:5433/medusa-store

# Remote database
DATABASE_URL=postgres://user:[e-mail]:5432/medusa-production

# With SSL (add ?ssl=true or ?sslmode=require)
DATABASE_URL=postgres://user:[e-mail]:5432/medusa?sslmode=require

Creating a Database

Using PostgreSQL CLI

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE medusa_store;

# Create user (optional)
CREATE USER medusa_user WITH PASSWORD 'secure_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE medusa_store TO medusa_user;

Using createdb Command

createdb -U postgres medusa_store

Medusa can automatically create the database on first run if it doesn’t exist and the user has create privileges.

Database Configuration

Basic Configuration

medusa-config.ts

import { defineConfig } from "@medusajs/utils"

export default defineConfig({
  projectConfig: {
    databaseUrl: process.env.DATABASE_URL,
  },
})

Advanced Configuration

medusa-config.ts

import { defineConfig } from "@medusajs/utils"

export default defineConfig({
  projectConfig: {
    // Connection URL
    databaseUrl: process.env.DATABASE_URL,
    
    // Schema (default: "public")
    databaseSchema: "public",
    
    // Connection pool options
    databaseDriverOptions: {
      pool: {
        // Minimum connections in pool
        min: 2,
        
        // Maximum connections in pool
        max: 10,
        
        // Idle timeout in milliseconds
        idleTimeoutMillis: 30000,
        
        // Reap interval in milliseconds
        reapIntervalMillis: 1000,
        
        // Create retry interval in milliseconds
        createRetryIntervalMillis: 200,
      },
    },
  },
})

Connection Pool Sizing

Recommended pool sizes based on your deployment:

• Development: min: 2, max: 5
• Production (single instance): min: 2, max: 10
• Production (multiple instances): min: 2, max: 5 per instance

Be careful with connection pool sizes. PostgreSQL has a maximum connection limit (default: 100). If you have multiple application instances, ensure the total connections don’t exceed the limit.

SSL Configuration

For production databases, enable SSL:

# Basic SSL
DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=require

# Verify CA certificate
DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=verify-ca

# Verify full SSL
DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=verify-full

SSL modes:

• disable: No SSL
• require: SSL required, but don’t verify certificate
• verify-ca: Verify server certificate against CA
• verify-full: Verify certificate and hostname

Database Migrations

Migrations ensure your database schema is up-to-date with your Medusa version and modules.

Running Migrations

Before starting your application, run migrations:

npx medusa db:migrate

This command:

1. Creates the database if it doesn’t exist
2. Creates the migrations table
3. Runs all pending module migrations
4. Synchronizes link definitions between modules
5. Executes migration scripts

Migration Options

# Run migrations (default)
npx medusa db:migrate

# Skip link synchronization
npx medusa db:migrate --skip-links

# Skip migration scripts
npx medusa db:migrate --skip-scripts

# Execute all link migrations (not just safe ones)
npx medusa db:migrate --execute-all-links

# Set migration concurrency
npx medusa db:migrate --concurrency 5

Always run migrations before starting your application in production. Running without migrations will cause errors.

Migration Process

Medusa uses MikroORM for migrations. The migration process:

1. Module Migrations: Each module maintains its own migrations
2. Link Synchronization: Creates join tables for module relationships
3. Migration Scripts: Custom data transformations and updates

Checking Migration Status

Migrations are tracked in the mikro_orm_migrations table:

-- Check migration status
SELECT * FROM mikro_orm_migrations ORDER BY executed_at DESC;

Database Schemas

By default, Medusa uses the public schema. You can configure a custom schema:

medusa-config.ts

export default defineConfig({
  projectConfig: {
    databaseUrl: process.env.DATABASE_URL,
    databaseSchema: "medusa", // Custom schema
  },
})

Create the schema before running migrations:

CREATE SCHEMA medusa;

Database Backup and Restore

Backup Database

# Backup to file
pg_dump -U postgres -d medusa_store -F c -f medusa_backup.dump

# Backup with compression
pg_dump -U postgres -d medusa_store -F c -Z 9 -f medusa_backup.dump

# Backup specific schema
pg_dump -U postgres -d medusa_store -n public -F c -f medusa_backup.dump

Restore Database

# Restore from backup
pg_restore -U postgres -d medusa_store -c medusa_backup.dump

# Create database and restore
createdb -U postgres medusa_store
pg_restore -U postgres -d medusa_store medusa_backup.dump

Always test your backup and restore process before you need it in production.

Performance Optimization

Indexes

Medusa automatically creates necessary indexes through migrations. For custom optimizations:

-- Check missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY n_distinct DESC;

-- Create custom index
CREATE INDEX CONCURRENTLY idx_custom ON your_table(column_name);

Connection Pooling

Use connection pooling for better performance:

medusa-config.ts

export default defineConfig({
  projectConfig: {
    databaseDriverOptions: {
      pool: {
        min: 2,
        max: 10,
        idleTimeoutMillis: 30000,
      },
    },
  },
})

Query Performance

Monitor slow queries:

-- Enable query logging
ALTER DATABASE medusa_store SET log_min_duration_statement = 1000;

-- View slow queries
SELECT * FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Troubleshooting

Connection Refused

Error: connect ECONNREFUSED 127.0.0.1:5432

Solutions:

• Check if PostgreSQL is running: sudo systemctl status postgresql
• Verify the host and port in DATABASE_URL
• Check firewall rules

Authentication Failed

Error: password authentication failed for user "postgres"

Solutions:

• Verify username and password in DATABASE_URL
• Check pg_hba.conf for authentication settings
• Reset user password if needed

Database Does Not Exist

Error: database "medusa_store" does not exist

Solutions:

• Create the database manually
• Ensure the user has create privileges for auto-creation
• Check the database name in DATABASE_URL

Too Many Connections

Error: sorry, too many clients already

Solutions:

• Reduce connection pool size in databaseDriverOptions
• Increase PostgreSQL max_connections setting
• Use a connection pooler like PgBouncer

Migration Failures

Error: Migration failed

Solutions:

• Check PostgreSQL logs for details
• Ensure user has necessary privileges
• Verify no concurrent migration processes
• Check for conflicting data or constraints

Production Best Practices

Security

1. Use strong passwords: Generate random, complex passwords
2. Limit privileges: Grant only necessary permissions
3. Enable SSL: Use SSL for all connections
4. Network isolation: Use private networks or VPNs
5. Regular backups: Automate daily backups

Monitoring

1. Connection count: Monitor active connections
2. Query performance: Track slow queries
3. Disk usage: Monitor database size
4. Replication lag: If using replication

Maintenance

-- Vacuum and analyze
VACUUM ANALYZE;

-- Reindex database
REINDEX DATABASE medusa_store;

-- Check database size
SELECT pg_size_pretty(pg_database_size('medusa_store'));

Schedule regular VACUUM and ANALYZE operations during low-traffic periods to maintain optimal performance.

Multiple Database Support

Medusa supports module-specific databases:

medusa-config.ts

import { Modules } from "@medusajs/utils"

export default defineConfig({
  projectConfig: {
    databaseUrl: process.env.DATABASE_URL,
  },
  modules: [
    {
      key: Modules.PRODUCT,
      resolve: "@medusajs/product",
      options: {
        // Custom database for product module
        databaseUrl: process.env.PRODUCT_DATABASE_URL,
      },
    },
  ],
})

Environment variables:

# Shared database
DATABASE_URL=postgres://localhost/medusa_shared

# Module-specific database
PRODUCT_DATABASE_URL=postgres://localhost/medusa_products