Prisma Backup

#prisma #postgres

Github: https://github.com/DaxSoft/prisma-backup

Simple PostgreSQL Backups for Your Prisma Project

You're building an awesome project with Prisma and a free PostgreSQL database from a cloud provider. It's fast, it's modern, it's great... until you realize the free tier doesn't offer a way to export or back up your data, neither it allows to connect to pgAdmin. How do you migrate to another service? What if you need to recover from a mistake?

🚀 Ideal Use Cases

This tool is perfect for:

Migrating off a free-tier service: This is the primary use case. You can easily dump your entire database and move to a different provider without getting locked in.
Seeding development environments: Quickly populate your local or staging database with a snapshot of production data.
Manual recovery: Provides a safety net before performing major, destructive schema changes.

⚠️ A Quick Word of Caution

This tool is designed for convenience and is best suited for small-sized projects. It is not a substitute for enterprise-grade, point-in-time recovery solutions.

Step 1: Installation

First, add the package to your project.

# Using npm
npm install @vorlefan/prisma-backup

# Using yarn
yarn add @vorlefan/prisma-backup

Step 2: Creating a Backup

The backup process is straightforward. You create an instance of the PrismaBackup class, passing it your Prisma Client and some configuration.

Let's create a script called backup.ts:

// backup.ts
import { PrismaClient } from 'output/generated/prisma/client';
import { PrismaBackup } from '@vorlefan/prisma-backup';

const prisma = new PrismaClient();

async function main() {
  console.log('Starting backup...');

  const backup = new PrismaBackup(prisma, {
    // The root folder where all backup folders will be created
    folderName: '.db_backups',
    database: 'postgres',

    // Optional: Handle large tables by fetching data in batches
    offset: {
      Posts: { limit: 200 }, // Backup the 'Posts' table in batches of 200
    },
  });

  await backup.run();

  console.log('✅ Backup completed!');
}

main();

When you run this (ts-node backup.ts), it will:

Query all your PostgreSQL tables.
Create a new folder inside .db_backups.
Save the data for each table as a separate JSON file.

Step 3: Restoring From a Backup

Restoring is just as simple, but with one critical requirement: you must provide Prisma's schema metadata. This allows the tool to understand your table relationships and insert data in the correct order to avoid foreign key constraint errors.

Let's create restore.ts:

// restore.ts
import { PrismaClient, Prisma } from 'output/generated/prisma/client';
import { PrismaRestore } from '@vorlefan/prisma-backup';

// Important: Import the 'Prisma' namespace to access the DMMF
const prisma = new PrismaClient();

async function main() {
  console.log('Starting restore...');

  const restore = new PrismaRestore(prisma, {
    // The specific backup folder you want to restore from
    folderName: '.db_backups', 
    database: 'postgres',

    // THIS IS THE MOST IMPORTANT PART!
    // It allows the tool to understand your schema and relations.
    baseModels: Prisma.dmmf.datamodel.models as any,
  });

  await restore.run();

  console.log('✅ Restore completed!');
}

main();

When you run this script, it will automatically:

Analyze your baseModels to determine the correct insertion order (e.g., User before Posts).
Insert the backup data table-by-table.

Conclusion

And that's it! You now have a simple and effective strategy for backing up and migrating your Prisma project's database.

Check out the repository on GitHub for more details. Happy coding

DEV Community