Managed Databases

DigitalOcean Managed Databases

Fully managed database clusters with:

• Automatic backups - Daily with point-in-time recovery
• High availability - Standby nodes and failover
• Scaling - Resize without downtime
• Security - Private networking, encryption
• Maintenance - Automatic updates

Available Engines

Creating a Database

Via CLI

bash
Copy
# PostgreSQL
doctl databases create mydb \
  --engine pg \
  --version 16 \
  --size db-s-1vcpu-1gb \
  --region nyc1 \
  --num-nodes 1

# MySQL
doctl databases create mydb \
  --engine mysql \
  --version 8 \
  --size db-s-1vcpu-1gb \
  --region nyc1

# Redis
doctl databases create myredis \
  --engine redis \
  --version 7 \
  --size db-s-1vcpu-1gb \
  --region nyc1

# MongoDB
doctl databases create mymongo \
  --engine mongodb \
  --version 7 \
  --size db-s-1vcpu-1gb \
  --region nyc1

Via Control Panel

1. Go to Databases → Create Database Cluster
2. Choose engine (PostgreSQL, MySQL, etc.)
3. Select version
4. Choose size and region
5. Configure nodes (1 for dev, 2+ for HA)
6. Click Create

Database Sizes

Connecting to Your Database

Get Connection Details

bash
Copy
# Get connection info
doctl databases connection DB_ID

# Output includes:
# Host, Port, User, Password, Database, URI

Connection String Formats

PostgreSQL:

postgresql://user:password@host:port/database?sslmode=require

MySQL:

mysql://user:password@host:port/database?ssl-mode=REQUIRED

Redis:

rediss://user:password@host:port

MongoDB:

mongodb+srv://user:password@host/database?tls=true&authSource=admin

Connect from Application

Node.js (PostgreSQL):

javascript
Copy
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

Node.js (MySQL):

javascript
Copy
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false }
});

Node.js (Redis):

javascript
Copy
const Redis = require('ioredis');

const redis = new Redis(process.env.REDIS_URL);

Database Management

Create Database

bash
Copy
# Create a database in the cluster
doctl databases db create DB_CLUSTER_ID myappdb

Create User

bash
Copy
# Create user
doctl databases user create DB_CLUSTER_ID myuser

# Get user info (includes password)
doctl databases user get DB_CLUSTER_ID myuser

List Databases and Users

bash
Copy
# List databases
doctl databases db list DB_CLUSTER_ID

# List users
doctl databases user list DB_CLUSTER_ID

Connection Pooling (PostgreSQL)

Manage connections efficiently:

Enable Pooling

bash
Copy
# Create connection pool
doctl databases pool create DB_ID mypool \
  --db defaultdb \
  --user doadmin \
  --size 10 \
  --mode transaction

Pool Modes

Get Pool Connection

bash
Copy
doctl databases pool get DB_ID mypool

Use the pool URI instead of direct connection for better performance.

Security

Trusted Sources

Restrict access to specific IPs:

bash
Copy
# Add trusted source
doctl databases firewalls append DB_ID \
  --rule droplet:DROPLET_ID

# Or by IP
doctl databases firewalls append DB_ID \
  --rule ip_addr:1.2.3.4

# Or App Platform
doctl databases firewalls append DB_ID \
  --rule app:APP_ID

Private Networking

Use VPC for secure connections:

1. Create database in same VPC as Droplets
2. Use private hostname instead of public
3. No encryption overhead (already private)

bash
Copy
# Get private connection
doctl databases connection DB_ID --format PrivateURI --no-header

High Availability

Standby Nodes

Add standby nodes for failover:

bash
Copy
# Create with HA
doctl databases create mydb \
  --engine pg \
  --size db-s-2vcpu-4gb \
  --region nyc1 \
  --num-nodes 2  # Primary + 1 standby

Read Replicas

Scale read operations:

bash
Copy
# Add read replica
doctl databases replica create DB_ID mydb-replica \
  --size db-s-1vcpu-2gb \
  --region nyc1

Use replica connection for read queries to reduce primary load.

Backups

Automatic Backups

• Daily automatic backups
• 7-day retention (default)
• Point-in-time recovery (PostgreSQL)

Restore from Backup

bash
Copy
# Fork database from backup
doctl databases create mydb-restored \
  --engine pg \
  --size db-s-1vcpu-1gb \
  --region nyc1 \
  --restore-from DB_ID \
  --restore-from-time "2024-01-15 10:30:00"

Maintenance

Maintenance Windows

bash
Copy
# Set maintenance window
doctl databases maintenance-window update DB_ID \
  --day sunday \
  --hour 04:00

Version Upgrades

bash
Copy
# Upgrade PostgreSQL version
doctl databases update DB_ID --version 16

App Platform Integration

Dev Database (Free)

yaml
Copy
# .do/app.yaml
databases:
  - name: db
    engine: PG
    production: false  # Free dev database

Production Database

yaml
Copy
databases:
  - name: db
    engine: PG
    production: true
    cluster_name: my-db-cluster
    db_name: myapp
    db_user: myuser

Reference in Service

yaml
Copy
services:
  - name: web
    envs:
      - key: DATABASE_URL
        scope: RUN_TIME
        value: ${db.DATABASE_URL}

Performance

Query Performance

Monitor slow queries in Control Panel:

• Database → Insights → Query Statistics

Index Recommendations

Check for missing indexes:

• Database → Insights → Index Advisor

Connection Management

Best practices:

1. Use connection pooling
2. Close connections properly
3. Set appropriate pool sizes
4. Monitor active connections

Migrations

Run Migrations

bash
Copy
# Connect and run migrations
DATABASE_URL="postgresql://..." npx prisma migrate deploy

# Or with knex
DATABASE_URL="postgresql://..." npx knex migrate:latest

Schema Changes

For zero-downtime migrations:

1. Add new columns (nullable)
2. Deploy code that writes to both old/new
3. Migrate data
4. Deploy code that uses new column
5. Remove old column

Complete Example

PostgreSQL Setup

bash
Copy
# Create database cluster
DB_ID=$(doctl databases create myapp-db \
  --engine pg \
  --version 16 \
  --size db-s-1vcpu-1gb \
  --region nyc1 \
  --output json | jq -r '.[0].id')

echo "Database ID: $DB_ID"

# Wait for database to be ready
while true; do
  STATUS=$(doctl databases get $DB_ID --format Status --no-header)
  if [ "$STATUS" = "online" ]; then
    break
  fi
  echo "Waiting for database... ($STATUS)"
  sleep 10
done

# Create application database
doctl databases db create $DB_ID myapp

# Create application user
doctl databases user create $DB_ID appuser

# Get connection string
DATABASE_URL=$(doctl databases connection $DB_ID --format URI --no-header)
echo "Connection: $DATABASE_URL"

# Add trusted source (your app)
doctl databases firewalls append $DB_ID \
  --rule app:YOUR_APP_ID

Connect from App Platform

yaml
Copy
# .do/app.yaml
name: myapp
region: nyc

services:
  - name: web
    github:
      repo: username/myapp
      branch: main
    http_port: 3000
    envs:
      - key: DATABASE_URL
        scope: RUN_TIME
        type: SECRET
        value: "postgresql://..."  # From doctl output

databases:
  - name: db
    engine: PG
    production: true

Troubleshooting

Connection Refused

1. Check trusted sources include your app/IP
2. Verify connection string is correct
3. Check SSL mode is set

Too Many Connections

1. Enable connection pooling
2. Reduce pool size in app
3. Close unused connections
4. Upgrade database size

Slow Queries

1. Check query statistics
2. Add missing indexes
3. Optimize query structure
4. Consider read replicas

Connection Timeout

1. Check network/firewall settings
2. Verify private networking is configured
3. Check database is online