prisma-do-backup

📦 Automated database backup system for Prisma projects with DigitalOcean Spaces storage.

Features

• 🚀 Easy to use - Simple API and CLI
• 🔄 Auto-detect tables - Automatically discovers Prisma models
• ☁️ DigitalOcean Spaces - S3-compatible cloud storage
• 🧹 Smart cleanup - Intelligent retention policy
• 📊 Progress tracking - Callbacks for monitoring
• 🔐 Secure - Private storage with proper ACL

Installation

npm install prisma-do-backup

Quick Start

1. Set Environment Variables

# .env
DO_SPACES_ENDPOINT=https://fra1.digitaloceanspaces.com
DO_SPACES_REGION=fra1
DO_SPACES_KEY=your_access_key
DO_SPACES_SECRET=your_secret_key
DO_SPACES_BUCKET=my-backups

2. Use in Code

import { PrismaDoBackup } from 'prisma-do-backup';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();
const backup = new PrismaDoBackup(prisma);

// Create backup
const result = await backup.backup();
console.log(`Backup created: ${result.filename}`);

// List backups
const list = await backup.listBackups();
console.log(`Total backups: ${list.totalCount}`);

// Download latest backup
const data = await backup.downloadBackup('latest');
console.log(`Downloaded: ${data.filename}`);

// Cleanup old backups
const cleanup = await backup.cleanup({ dryRun: true });
console.log(`Would delete: ${cleanup.toDelete.length} backups`);

3. Use CLI

# Create backup
npx prisma-do-backup backup

# List backups
npx prisma-do-backup list

# Download latest
npx prisma-do-backup download latest

# Cleanup (dry run)
npx prisma-do-backup cleanup --dry-run

API Reference

Constructor

const backup = new PrismaDoBackup(prisma, options);

Options:

| Option | Type | Default | Description | |--------|------|---------|-------------| | endpoint | string | DO_SPACES_ENDPOINT | DigitalOcean Spaces endpoint | | region | string | fra1 | DigitalOcean region | | accessKeyId | string | DO_SPACES_KEY | Access key | | secretAccessKey | string | DO_SPACES_SECRET | Secret key | | bucket | string | database-backups | Bucket name | | prefix | string | backups/ | Key prefix for backups | | environment | string | NODE_ENV | Environment name | | tables | string[] | auto-detect | Specific tables to backup | | retention | object | see below | Retention policy |

Retention Policy (default):

{
  keepAllDays: 30,        // 0-30 days: keep all
  keepWeeklyDays: 90,     // 30-90 days: keep 1/week
  keepMonthlyDays: 365,   // 90-365 days: keep 1/month
  keepQuarterlyDays: 730, // 365-730 days: keep 1/quarter
  deleteAfterDays: 730,   // Beyond: delete
}

Methods

backup(options?)

Create a new backup.

const result = await backup.backup({
  tables: ['User', 'Order'],  // Optional: specific tables
  onProgress: ({ step, message }) => console.log(message),
});

// Result:
{
  success: true,
  timestamp: '2025-01-20T12:00:00.000Z',
  environment: 'production',
  filename: 'backup_production_2025-01-20T12-00-00.json',
  uploadSize: 1234567,
  uploadUrl: 'https://...',
  duration: 5432,
  summary: {
    totalTables: 25,
    totalRecords: 10000,
    exportedTables: [...],
    errors: []
  }
}

listBackups()

List all available backups.

const list = await backup.listBackups();

// Result:
{
  backups: [
    {
      filename: 'backup_production_2025-01-20.json',
      key: 'backups/backup_production_2025-01-20.json',
      size: 1234567,
      sizeMB: '1.18',
      lastModified: '2025-01-20T12:00:00.000Z'
    },
    // ...
  ],
  totalCount: 30,
  totalSize: 37037010,
  totalSizeMB: '35.32',
  latest: { ... },
  oldest: { ... }
}

downloadBackup(filename)

Download a backup. Use 'latest' for the most recent.

const data = await backup.downloadBackup('latest');

// Result:
{
  filename: 'backup_production_2025-01-20.json',
  size: 1234567,
  sizeMB: '1.18',
  data: {
    timestamp: '...',
    environment: 'production',
    tables: { User: [...], Order: [...] },
    summary: { ... }
  }
}

deleteBackup(filename)

Delete a specific backup.

await backup.deleteBackup('backup_production_2025-01-01.json');

cleanup(options?)

Clean up old backups based on retention policy.

const result = await backup.cleanup({
  dryRun: true,  // Simulate without deleting
});

// Result:
{
  success: true,
  toKeep: ['backup_1.json', 'backup_2.json'],
  toDelete: ['old_backup_1.json', 'old_backup_2.json'],
  deleted: [],  // Empty if dryRun
  freedSpace: 5000000,
  freedSpaceMB: '4.77',
  dryRun: true,
  retention: { ... }
}

testConnection()

Test connection to DigitalOcean Spaces.

const result = await backup.testConnection();

// Result:
{
  success: true,
  message: 'Connection successful',
  bucket: 'my-backups',
  endpoint: 'https://fra1.digitaloceanspaces.com',
  region: 'fra1'
}

restore(source, options?)

Restore database from a backup. Dry-run by default for safety.

const result = await backup.restore('latest', {
  dryRun: true,               // Preview only (default: true)
  mode: 'upsert',             // 'upsert', 'insert', 'deleteAndCreate'
  tables: ['User', 'Order'],  // Specific tables (optional)
  createSafetyBackup: true,   // Backup before restore (default: true)
  continueOnError: false,     // Stop on first error (default)
  onProgress: ({ message }) => console.log(message),
});

// Result:
{
  success: true,
  dryRun: false,
  mode: 'upsert',
  source: 'backup_production_2025-01-20.json',
  safetyBackup: 'backup_production_2025-01-20T12-05-00.json',
  tables: { total: 5, restored: 5, skipped: 0, failed: 0 },
  records: { total: 1000, processed: 1000, created: 500, updated: 500, skipped: 0, failed: 0 },
  details: [...],
  errors: [],
  duration: 5432
}

previewRestore(source, options?)

Alias for restore() with dryRun: true. Always safe.

const preview = await backup.previewRestore('latest');

getAvailableModels()

Get list of available Prisma models.

const models = backup.getAvailableModels();
// ['User', 'Order', 'Product', ...]

CLI Reference

prisma-do-backup <command> [options]

Commands:
  backup              Create a new backup
  restore <file>      Restore from backup (⚠️ use --no-dry-run to execute)
  list                List all available backups
  download <file>     Download a backup (use 'latest' for most recent)
  delete <file>       Delete a specific backup
  cleanup             Clean up old backups based on retention policy
  test                Test connection to DigitalOcean Spaces

Options:
  --dry-run           Preview changes without modifying (default for restore)
  --no-dry-run        Execute restore (modifies database!)
  --tables=t1,t2      Specific tables to backup/restore
  --mode=<mode>       Restore mode: upsert (default), insert, deleteAndCreate
  --no-safety-backup  Skip safety backup before restore
  --continue-on-error Continue if some records fail
  --env=<path>        Path to .env file (default: .env.local or .env)
  --prisma=<path>     Path to Prisma client
  --debug             Show debug information

Examples

# Create backup
prisma-do-backup backup

# Backup specific tables
prisma-do-backup backup --tables=User,Order,Product

# List all backups
prisma-do-backup list

# Download latest backup
prisma-do-backup download latest

# Download specific backup
prisma-do-backup download backup_production_2025-01-20.json

# Delete a backup
prisma-do-backup delete backup_production_2025-01-01.json

# Cleanup (preview)
prisma-do-backup cleanup --dry-run

# Cleanup (execute)
prisma-do-backup cleanup

# Test connection
prisma-do-backup test

# Use custom .env file
prisma-do-backup backup --env=.env.production

Integration Examples

Next.js API Route (Cron Job)

// app/api/cron/backup/route.js
import { NextResponse } from 'next/server';
import { PrismaDoBackup } from 'prisma-do-backup';
import prisma from '@/lib/prisma';

export async function GET(request) {
  // Verify cron secret in production
  if (process.env.NODE_ENV === 'production') {
    const authHeader = request.headers.get('authorization');
    if (authHeader !== `Bearer ${process.env.CRON_SECRET}`) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
    }
  }

  try {
    const backup = new PrismaDoBackup(prisma);
    const result = await backup.backup();
    
    return NextResponse.json(result);
  } catch (error) {
    return NextResponse.json(
      { error: error.message },
      { status: 500 }
    );
  }
}

Vercel Cron Configuration

// vercel.json
{
  "crons": [
    {
      "path": "/api/cron/backup",
      "schedule": "0 4 * * *"
    }
  ]
}

Express.js Middleware

import express from 'express';
import { PrismaDoBackup } from 'prisma-do-backup';
import { PrismaClient } from '@prisma/client';

const app = express();
const prisma = new PrismaClient();

app.post('/admin/backup', async (req, res) => {
  const backup = new PrismaDoBackup(prisma);
  const result = await backup.backup();
  res.json(result);
});

app.get('/admin/backups', async (req, res) => {
  const backup = new PrismaDoBackup(prisma);
  const list = await backup.listBackups();
  res.json(list);
});

Scheduled Task with Node-cron

import cron from 'node-cron';
import { PrismaDoBackup } from 'prisma-do-backup';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Daily backup at 4 AM
cron.schedule('0 4 * * *', async () => {
  console.log('Starting scheduled backup...');
  
  const backup = new PrismaDoBackup(prisma);
  const result = await backup.backup();
  
  console.log(`Backup completed: ${result.filename}`);
});

// Weekly cleanup on Sunday at 5 AM
cron.schedule('0 5 * * 0', async () => {
  console.log('Starting scheduled cleanup...');
  
  const backup = new PrismaDoBackup(prisma);
  const result = await backup.cleanup();
  
  console.log(`Cleanup completed: ${result.deleted.length} files deleted`);
});

DigitalOcean Spaces Setup

📖 Guida Completa: Per istruzioni dettagliate passo-passo, consulta docs/DIGITALOCEAN_SETUP_GUIDE.md

Quick Setup

1. Crea un Account DigitalOcean:

   • Vai su digitalocean.com e registrati
   • Puoi ottenere $200 di crediti gratuiti per i primi 60 giorni

2. Crea uno Space:

   • Dashboard → Spaces Object Storage → Create a Space
   • Scegli la regione (es: fra1 per Frankfurt, Europa)
   • Scegli un nome unico (es: myapp-backups)
   • Seleziona "Restrict File Listing" per sicurezza

3. Genera API Keys:

   • Dashboard → API → Spaces Keys → Generate New Key
   • IMPORTANTE: Copia subito Access Key e Secret Key (mostrate una sola volta!)

4. Configura le Variabili d'Ambiente:

# .env
DO_SPACES_ENDPOINT=https://fra1.digitaloceanspaces.com
DO_SPACES_REGION=fra1
DO_SPACES_KEY=DO00XXXXXXXXXXXXXX
DO_SPACES_SECRET=la-tua-secret-key
DO_SPACES_BUCKET=myapp-backups

Endpoint per Regione

| Regione | Codice | Endpoint | |---------|--------|----------| | Frankfurt | fra1 | https://fra1.digitaloceanspaces.com | | Amsterdam | ams3 | https://ams3.digitaloceanspaces.com | | New York | nyc3 | https://nyc3.digitaloceanspaces.com | | San Francisco | sfo3 | https://sfo3.digitaloceanspaces.com | | Singapore | sgp1 | https://sgp1.digitaloceanspaces.com | | Sydney | syd1 | https://syd1.digitaloceanspaces.com |

Costi

• Storage: $0.02/GB al mese
• Transfer OUT: $0.01/GB (primi 1TB gratuiti)
• Transfer IN: Gratuito

Per un backup tipico (< 1GB): meno di $0.10/mese

Restore from Backup

⚠️ Important Safety Features

The restore function includes multiple safety measures:

1. Dry-run by default - Preview what will change before executing
2. Automatic safety backup - Creates a backup of current database before restore
3. Upsert mode - Updates existing records, creates new ones (no duplicates)
4. Transaction-safe - Errors are handled gracefully
5. 5-second warning - Time to abort before live restore

CLI Usage

# Preview restore (dry-run - safe, no changes made)
npx prisma-do-backup restore latest

# Execute restore (⚠️ modifies database!)
npx prisma-do-backup restore latest --no-dry-run

# Restore specific tables only
npx prisma-do-backup restore latest --no-dry-run --tables=User,Order

# Restore with insert mode (skip existing records)
npx prisma-do-backup restore latest --no-dry-run --mode=insert

# Restore with deleteAndCreate mode (⚠️ deletes all existing data first!)
npx prisma-do-backup restore latest --no-dry-run --mode=deleteAndCreate

# Skip safety backup (dangerous!)
npx prisma-do-backup restore latest --no-dry-run --no-safety-backup

Code Usage

import { PrismaDoBackup } from 'prisma-do-backup';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();
const backup = new PrismaDoBackup(prisma);

// Preview restore (dry-run)
const preview = await backup.restore('latest');
console.log(`Would restore ${preview.records.total} records`);

// Execute restore
const result = await backup.restore('latest', {
  dryRun: false,              // Execute for real
  mode: 'upsert',             // 'upsert', 'insert', or 'deleteAndCreate'
  tables: ['User', 'Order'],  // Specific tables (optional)
  createSafetyBackup: true,   // Create backup first (default: true)
  continueOnError: false,     // Stop on first error (default)
  onProgress: ({ message }) => console.log(message),
});

console.log(`Restored ${result.records.processed} records`);
console.log(`Safety backup: ${result.safetyBackup}`);

Restore Modes

| Mode | Description | Use Case | |------|-------------|----------| | upsert (default) | Update if exists, create if not | Safe migration, sync data | | insert | Insert only, skip existing | Add missing records only | | deleteAndCreate | Delete all, then insert | Full reset (⚠️ dangerous) |

Typical Migration Workflow

# 1. Create backup from old database
DATABASE_URL=old_db_url npx prisma-do-backup backup

# 2. Preview restore to new database
DATABASE_URL=new_db_url npx prisma-do-backup restore latest

# 3. Execute restore
DATABASE_URL=new_db_url npx prisma-do-backup restore latest --no-dry-run

# 4. If something goes wrong, restore from safety backup
DATABASE_URL=new_db_url npx prisma-do-backup restore backup_safety_xxx.json --no-dry-run

Security Best Practices

1. Never commit credentials - Use environment variables
2. Use private ACL - Backups are stored with private access
3. Rotate keys regularly - Change Spaces keys every 6 months
4. Limit permissions - Use keys with minimal required permissions
5. Encrypt sensitive data - Consider encrypting backup files

Documentation

• README - Guida rapida e API reference
• DigitalOcean Setup Guide - Configurazione completa passo-passo

License

MIT

Contributing

Contributions are welcome! Please open an issue or PR.

Support

• GitHub Issues
• Documentation