How do I backup an Sqlite database using Prisma?

[vedantroy]

I'm running a NodeJS server with sqlite + Prisma. I want to have an API endpoint, that returns the underlying sqlite file (a very simple backup solution). What would be the best way to do this?

[vedantroy]

It would also be useful to have an API endpoint that can accept a sqlite database file and just hotswap the entire database. While obviously not type-safe, I imagine this is still possible using some raw SQL.

[ludralph]

Hi @vedantroy 👋

To create an API endpoint in Node.js that returns the underlying SQLite file, you can do:

const express = require('express');
const path = require('path');
const app = express();
app.get('/api/backup', (req, res) => {
  const filePath = path.join(__dirname, 'path/to/sqlite.db');

  res.download(filePath, 'backup.sqlite', (err) => {
    if (err) {
      console.error('Error downloading SQLite file:', err);
      res.status(500).send('Internal Server Error');
    }
  });
});
app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Replace path/to/sqlite.db with the actual path to your SQLite file. When a client sends a GET request to /api/backup, the server will respond by downloading the SQLite file. The file will be named backup.sqlite in the client's browser. The res.download function takes care of handling the file download.

It would also be useful to have an API endpoint that can accept a sqlite database file and just hotswap the entire database. While obviously not type-safe, I imagine this is still possible using some raw SQL.

For this question, are you swapping the entire database with another sqlite database file?

[vedantroy]

The reason I don't think this will work is because the sqlite database could still be used, so if you just read the file, you're at risk of returning a corrupted database. I think you want to execute a backup from within sqlite. Ideally, by using prisma's $executeRaw api.

As for the second question, yes, I'm swapping the entire database with a new sqlite file.

[nicojs]

I think this is a correct assumption @vedantroy . I'm going to use this solution:

import sqlite3 from 'better-sqlite3';

const dbPath = 'prisma/dev.db';
const db = sqlite3(dbPath);
await db.backup('prisma/dev.db.bak');

It uses better-sqlite3's .backup functionality.

After creating the backup file, you can serve it in a download response.

[mrfzd]

I have been looking for something like this for the last 2 days and it’s been so painful until I saw this post! Thank you so much for being a life saver

[anvimaa]

Hi @vedantroy 👋

To create an API endpoint in Node.js that returns the underlying SQLite file, you can do:

const express = require('express');
const path = require('path');
const app = express();
app.get('/api/backup', (req, res) => {
  const filePath = path.join(__dirname, 'path/to/sqlite.db');

  res.download(filePath, 'backup.sqlite', (err) => {
    if (err) {
      console.error('Error downloading SQLite file:', err);
      res.status(500).send('Internal Server Error');
    }
  });
});
app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Replace path/to/sqlite.db with the actual path to your SQLite file. When a client sends a GET request to /api/backup, the server will respond by downloading the SQLite file. The file will be named backup.sqlite in the client's browser. The res.download function takes care of handling the file download.

It would also be useful to have an API endpoint that can accept a sqlite database file and just hotswap the entire database. While obviously not type-safe, I imagine this is still possible using some raw SQL.

For this question, are you swapping the entire database with another sqlite database file?

When executing this code the following error appears: Can't set headers after they are sent.

How to resolve this?

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!