> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/medusajs/medusa/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

```bash theme={null}
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

```bash theme={null}
# 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:pass@db.example.com:5432/medusa-production

# With SSL (add ?ssl=true or ?sslmode=require)
DATABASE_URL=postgres://user:pass@db.example.com:5432/medusa?sslmode=require
```

## Creating a Database

### Using PostgreSQL CLI

```bash theme={null}
# 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

```bash theme={null}
createdb -U postgres medusa_store
```

<Note>
  Medusa can automatically create the database on first run if it doesn't exist and the user has create privileges.
</Note>

## Database Configuration

### Basic Configuration

```ts title="medusa-config.ts" theme={null}
import { defineConfig } from "@medusajs/utils"

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

### Advanced Configuration

```ts title="medusa-config.ts" theme={null}
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

<Warning>
  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.
</Warning>

### SSL Configuration

For production databases, enable SSL:

```bash theme={null}
# 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:

```bash theme={null}
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

```bash theme={null}
# 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
```

<Warning>
  Always run migrations before starting your application in production. Running without migrations will cause errors.
</Warning>

### 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:

```sql theme={null}
-- 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:

```ts title="medusa-config.ts" theme={null}
export default defineConfig({
  projectConfig: {
    databaseUrl: process.env.DATABASE_URL,
    databaseSchema: "medusa", // Custom schema
  },
})
```

Create the schema before running migrations:

```sql theme={null}
CREATE SCHEMA medusa;
```

## Database Backup and Restore

### Backup Database

```bash theme={null}
# 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

```bash theme={null}
# 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
```

<Note>
  Always test your backup and restore process before you need it in production.
</Note>

## Performance Optimization

### Indexes

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

```sql theme={null}
-- 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:

```ts title="medusa-config.ts" theme={null}
export default defineConfig({
  projectConfig: {
    databaseDriverOptions: {
      pool: {
        min: 2,
        max: 10,
        idleTimeoutMillis: 30000,
      },
    },
  },
})
```

### Query Performance

Monitor slow queries:

```sql theme={null}
-- 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

```sql theme={null}
-- Vacuum and analyze
VACUUM ANALYZE;

-- Reindex database
REINDEX DATABASE medusa_store;

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

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

## Multiple Database Support

Medusa supports module-specific databases:

```ts title="medusa-config.ts" theme={null}
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:

```bash theme={null}
# Shared database
DATABASE_URL=postgres://localhost/medusa_shared

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