Migrating prisma data from sqlite to postgres

Readme.md

This was quiet an ordeal, but a combination of preexisting tools and a few lines of custom scripting made it possible.

I really benefitted from having a script to create whole database from scratch, heavily recommended. That part is inspired by: https://mgdm.net/weblog/postgresql-in-a-nix-shell/

What you need: postgres server, psql, pg_dump (all of those come standard with postgres installation) and pgloader. The postgresql starting script is written for nodejs but you could easily replace it...

The basic process is as follows

1. load the data from sqlite to postgres as is
2. fix any incompatibilities that'd break step 5
3. dump the data (not the schema)
4. on new database create the db from schema
5. import the data from step 3
6. done

To do that with scripts below (you'll need to modify at least the fix.psql) you need to run

bash restore1.sh && bash restore2.sh

fix.psql

begin transaction;
-- rename tables as pgloader lowercases everything
ALTER TABLE "image" RENAME TO "Image";
ALTER TABLE "person" RENAME TO "Person";
ALTER TABLE "payment" RENAME TO "Payment";
ALTER TABLE "personcontact" RENAME TO "PersonContact";
ALTER TABLE "attendee" RENAME TO "Attendee";
ALTER TABLE "eventexpensecategoryusedby" RENAME TO "EventExpenseCategoryUsedBy";
ALTER TABLE "keyvalue" RENAME TO "KeyValue";
ALTER TABLE "user" RENAME TO "User";
ALTER TABLE "paymentcategoryassignment" RENAME TO "PaymentCategoryAssignment";
ALTER TABLE "event" RENAME TO "Event";
ALTER TABLE "paymentcategory" RENAME TO "PaymentCategory";
ALTER TABLE "eventexpense" RENAME TO "EventExpense";
ALTER TABLE "role" RENAME TO "Role";
ALTER TABLE "eventexpensecategory" RENAME TO "EventExpenseCategory";

-- rename columns
ALTER TABLE "Attendee" RENAME COLUMN "eventid" TO "eventId";
ALTER TABLE "Attendee" RENAME COLUMN "personid" TO "personId";
ALTER TABLE "Event" RENAME COLUMN "endsat" TO "endsAt";
ALTER TABLE "Event" RENAME COLUMN "startsat" TO "startsAt";
ALTER TABLE "EventExpense" RENAME COLUMN "eventexpensecategoryid" TO "eventExpenseCategoryId";
ALTER TABLE "EventExpenseCategoryUsedBy" RENAME COLUMN "eventexpensecategoryid" TO "eventExpenseCategoryId";
ALTER TABLE "EventExpenseCategoryUsedBy" RENAME COLUMN "personid" TO "personId";
ALTER TABLE "Payment" RENAME COLUMN "operationtype" TO "operationType";
ALTER TABLE "Payment" RENAME COLUMN "transactionid" TO "transactionId";
ALTER TABLE "PaymentCategoryAssignment" RENAME COLUMN "paymentcategoryid" TO "paymentCategoryId";
ALTER TABLE "PaymentCategoryAssignment" RENAME COLUMN "transactionid" TO "transactionId";
ALTER TABLE "PersonContact" RENAME COLUMN "personid" TO "personId";
ALTER TABLE "EventExpense" RENAME COLUMN "personid" TO "personId";
ALTER TABLE "Event" RENAME COLUMN "paynote" TO "payNote";
ALTER TABLE "Event" RENAME COLUMN "payto" TO "payTo";
ALTER TABLE "KeyValue" RENAME COLUMN "allowindev" TO "allowInDev";
ALTER TABLE "Payment" RENAME COLUMN "commandid" TO "commandId";
ALTER TABLE "Payment" RENAME COLUMN "constantsymbol" TO "constantSymbol";
ALTER TABLE "Payment" RENAME COLUMN "messageforrecipient" TO "messageForRecipient";
ALTER TABLE "Payment" RENAME COLUMN "oaccountname" TO "oAccountName";
ALTER TABLE "Payment" RENAME COLUMN "oaccountnumber" TO "oAccountNumber";
ALTER TABLE "Payment" RENAME COLUMN "obankcode" TO "oBankCode";
ALTER TABLE "Payment" RENAME COLUMN "obankname" TO "oBankName";
ALTER TABLE "Payment" RENAME COLUMN "obankbic" TO "oBankBIC";
ALTER TABLE "Payment" RENAME COLUMN "payerreference" TO "payerReference";
ALTER TABLE "Payment" RENAME COLUMN "performedby" TO "performedBy";
ALTER TABLE "Payment" RENAME COLUMN "specificsymbol" TO "specificSymbol";
ALTER TABLE "Payment" RENAME COLUMN "useridentification" TO "userIdentification";
ALTER TABLE "Payment" RENAME COLUMN "variablesymbol" TO "variableSymbol";
ALTER TABLE "PaymentCategory" RENAME COLUMN "eventid" TO "eventId";
ALTER TABLE "Person" RENAME COLUMN "atomid" TO "atomId";
ALTER TABLE "Person" RENAME COLUMN "bankaccount" TO "bankAccount";
ALTER TABLE "Person" RENAME COLUMN "discordhandle" TO "discordHandle";
ALTER TABLE "Person" RENAME COLUMN "off_addressmunicipality" TO "off_addressMunicipality";
ALTER TABLE "Person" RENAME COLUMN "off_addressstreet" TO "off_addressStreet";
ALTER TABLE "Person" RENAME COLUMN "off_addresszipcode" TO "off_addressZipCode";
ALTER TABLE "Person" RENAME COLUMN "off_firstname" TO "off_firstName";
ALTER TABLE "Person" RENAME COLUMN "off_lastname" TO "off_lastName";
ALTER TABLE "Role" RENAME COLUMN "userid" TO "userId";
ALTER TABLE "EventExpenseCategory" RENAME COLUMN "eventid" TO "eventId";

COMMIT;

postgresql.mjs

// This script allows you to start, stop and create postgres more easily

import { spawnSync } from "child_process"
import { fileURLToPath } from "node:url"
import path from "node:path"
import fs from "fs"

const dirname = path.dirname(fileURLToPath(import.meta.url))
const cmds = {
  init,
  start,
  stop,
  run,
}

// eslint-disable-next-line no-undef
const cmd = process.argv[2]
if (!cmd) throw new Error("Missing command")
if (!cmds[cmd]) throw new Error(`Unknown command ${cmd}`)

cmds[cmd]()

function init() {
  // Create a database with the data stored in the current directory
  runSync("initdb", ["-D", ".tmp/mydb"])

  start()

  // Create a database
  runSync("createdb", ["-h", path.join(dirname, ".tmp"), "mydb"])

  stop()
}

function start() {
  // Start PostgreSQL running as the current user
  // and with the Unix socket in the current directory
  runSync("pg_ctl", [
    "-D",
    ".tmp/mydb",
    "-l",
    ".tmp/logfile",
    "-o",
    "--unix_socket_directories=" + path.join(dirname, ".tmp"),
    "start",
  ])
}

function run() {
  if (!fs.existsSync(path.join(dirname, ".tmp/mydb"))) {
    init()
  }
  // Start PostgreSQL running as the current user
  // and with the Unix socket in the current directory
  runSync("postgres", [
    "-D",
    ".tmp/mydb",
    "--unix_socket_directories=" + path.join(dirname, ".tmp"),
  ])
}

function stop() {
  runSync("pg_ctl", ["-D", ".tmp/mydb", "stop"])
}

function runSync(cmd, args) {
  console.log("Running", cmd, args.join(" "))
  const res = spawnSync(cmd, args, { stdio: "inherit", cwd: dirname })
  if (res.error) throw res.error
  if (res.status !== 0) throw new Error(`Exited with status ${res.status}`)
  if (res.signal) throw new Error(`Killed by signal ${res.signal}`)
  return res
}

restore1.sh

#!/bin/bash
# phase 1: load the data from sqlite to postgres
set -xe

# This creates a new database
if [ -f .tmp/mydb/postmaster.pid ] ; then node postgresql.mjs stop; fi
rm -rf .tmp
node postgresql.mjs init
node postgresql.mjs start

# Load data from sqlite to postgres
pgloader ./prisma/dev.db 'postgresql://localhost:5432/mydb'
# Rename some columns and other misc fixes. Only needed if you have capital letters in your
# column names (you probably do if you use prisma)
psql 'postgresql://localhost:5432/mydb' < fix.psql
# Dump the data from database
pg_dump 'postgresql://localhost:5432/mydb' -a -f datadump2.pgdump -F c

restore2.sh

#!/bin/bash
# phase 2: load the data to db created by prisma
set -xe

# Start new clear database
if [ -f .tmp/mydb/postmaster.pid ] ; then node postgresql.mjs stop; fi
rm -rf .tmp
node postgresql.mjs init
node postgresql.mjs start

# Make sure it reflects the schema
yarn prisma db push --skip-generate

# Restore columns in correct order (dependencies first)
# There might be smarter way to do this...
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t User datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t Role datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t Image datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t Person datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t Payment datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t PersonContact datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t Event datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t Attendee datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t KeyValue datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t PaymentCategory datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t PaymentCategoryAssignment datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t EventExpenseCategory datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t EventExpense datadump2.pgdump
pg_restore -a -d 'postgresql://localhost:5432/mydb' -e -1 -t EventExpenseCategoryUsedBy datadump2.pgdump