DEV Community

Arnav Gosain
Arnav Gosain

Posted on

8 2

How to use Litestream with SQLite & Prisma

Originally posted on: https://arnavgosain.com/sqlite-prisma-litestream

One of my side projects is Rune, an alternative email client built for reading and annotating newsletter content.

Rune is built with Remix1 and uses Prisma2 as the ORM layer. Prisma supports quite a few databases, of which I prefer Postgres. But I wanted to keep things simple, and didn't want to set up a full-blown Postgres instance, so I decided to use SQLite.

Since since my preferred way to deploy is via Docker on Railway.app, I needed a way to persist SQLite since Docker containers are refreshed on every deployment. Litestream3 was the perfect tool for the job.

But since Litestream only works with SQLite's WAL journaling mode4, it needs to be enabled on the SQLite database before running the Remix app.

enable-wal.js

const { PrismaClient } = require("@prisma/client");

let client = new PrismaClient();

client.$queryRaw`PRAGMA journal_mode = WAL;`
  .then(() => {
    console.log("ENABLED WAL MODE FOR DATABASE");
  })
  .catch((err) => {
    console.log("DB SETUP FAILED", err);
    process.exit(1);
  });
Enter fullscreen mode Exit fullscreen mode

run.sh

#!/bin/bash

set -ex

## Restores the database from S3
litestream restore -v -config /etc/litestream.yml -if-replica-exists -o /data/db /data/db

## Runs migrations on the restored database
npx prisma migrate deploy

## Enables WAL Mode
node ./scripts/enable-wal.js

npx concurrently "litestream replicate -config /etc/litestream.yml" "npm run start"
Enter fullscreen mode Exit fullscreen mode

Dockerfile

CMD ["bash", "./scripts/run.sh"]
Enter fullscreen mode Exit fullscreen mode

Explanation

  1. Dockerfile runs the run.sh script.
  2. The script first restores the latest snapshot of the database from S3 using Litestream.
  3. It runs npx prisma migrate deploy to deploy any pending migrations.
  4. Then, enable-wal.js is run to enable WAL journaling mode using PrismaClient's $queryRaw
  5. Finally, the Remix app & Litestream are started concurrently using the concurrently5 CLI tool.


  1. https://remix.run 

  2. https://prisma.io 

  3. https://litestream.io 

  4. https://sqlite.org/wal.html 

  5. https://www.npmjs.com/package/concurrently 

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs