Skip to content

Instantly share code, notes, and snippets.

@0gust1
Last active February 3, 2024 22:53
Show Gist options
  • Save 0gust1/19ff169231f84d70939943eaf781420e to your computer and use it in GitHub Desktop.
Save 0gust1/19ff169231f84d70939943eaf781420e to your computer and use it in GitHub Desktop.
How to use SQLite+prisma+nodeJS app on Azure app service

How to use SQLite+prisma+nodeJS app on Azure app service

Context

You want to run an app on Azure appService linux (here a nodeJS app) with a simple SQLite database.

Your have read https://www.sqlite.org/whentouse.html and your app requirements are simple enough (no big concurrency, no horizontal scaling) to consider SQLite, and you don't need yet more sophisticated solutions like PostgreSQL.

You use a NodeJS based app with the Prisma ORM.

disclaimer:

  • The tips explained here can apply also to non-nodeJS apps on azure AppService. Pick and choose the relevant tips.
  • There is no guarantees to those tips, use them wisely.

TLDR;

  • use PRAGMA journal_mode=WAL; on the database
  • be sure that only one client (app instance) will use the database
  • in some cases, you won't be able to call the prisma CLI directly (e.g. prisma migrate deploy), and you will have to do node node_modules/prisma/build/index.js migrate deploy instead
  • run the database migrations outside of /home and copy back the database file and the wal file

Problem 1 – SQLite and Azure AppService

Please note that SQLite use on AppService seems to be offically discouraged by Microsoft Support (ref1, ref2).

That's because of the architecture of Azure AppService:

  • AppService is horizontally scalable
  • when scaled out, the different instances of the app share the same app filesystem using SMB/CIFS network share...
    ...And SQLite doesn't like this (because its db locking mechanisms rely on the filesystem locking mechanism, and those are kind of broken on networked filesystems). See https://www.sqlite.org/whentouse.htm ("Situations Where A Client/Server RDBMS May Work Better" section).

Solution

➡️ Activate WAL journal mode on your database
put this at the top of your first Prisma-generated migration file (default location: prisma/migrations)

-- set up the DB in WAL journal mode
PRAGMA journal_mode=WAL;

You can also do this on a local database by running: sqlite3 <PATH_TO_SQLITE_DB_FILE> 'PRAGMA journal_mode=wal;'

Caveats:

⚠️ You must be sure that only one "client" (application) will access the DB at the same time.

Problem 2 – Prisma and zip-deploy on AzureAppService, running migrations

Prisma CLI is broken on the AppService

One of the simplest deployment on Azure AppService is "zip deploy" (which itself comes in different flavors):

  • you build locally
  • you zip everything important in an archive
  • you send this archive to Azure, for the target appService instance

The problem is: when doing this: the Azure deployment engine (Kudu/Oryx) breaks the symlinks which reside in node_modules/.bin, rendering the prisma CLI unusable.

You must also take care that the zip artefact you upload has the right prisma binaries for the AppService environment.

Solution

see the azure_database_migrate.sh file below for a contextual example.

➡️ Use direct paths when calling prisma on Azure
i.e., use node node_modules/prisma/build/index.js migrate deploy instead of prisma migrate deploy

It seems there is also an (undocumented) ENV var that could help: WEBSITE_ZIP_PRESERVE_SYMLINKS. I didn't test it, but could work.

➡️ Don't forget to configure prisma the right way
you should have something like that in your schema.prisma file:

generator client {
  provider = "prisma-client-js"

  // prisma engines:
  // "native is for local dev
  // "debian-openssl-1.1.x" is for Azure
  binaryTargets = ["native", "debian-openssl-1.1.x"]
}

datasource db {
  provider = "sqlite"
  url      = env("APPLICATION_DATABASE_URL")
}

"Database is locked" message when running migrations

For the same reason as "Problem 1" above, the /home directory (where your app lives) is a SMB/CIFS drive, and it seems that the prisma migrate deploy command provokes locking on the database file.

Solution

➡️ Before app start, move the db file outside of /home, run the migrations on it, and move it back to its original place.

You can use a simple shell script for this, that you can launch before app start using the prestart npm script.

{
  "scripts": {
    "prestart": "sh azure_database_migrate.sh",
    "start": "node node-dist/index.js"
    }    
}

see the azure_database_migrate.sh file example below.

#!/usr/bin/env bash
printf "######### Migrating application database - start\n"
rm -rf /migration
mkdir -p /migration
# move the sqlite files *if it exists*
[ -f local_data/application.sqlite ] && mv local_data/application.sqlite /migration/temp.sqlite
[ -f local_data/application.sqlite-wal ] && mv local_data/application.sqlite-wal /migration/temp.sqlite-wal
# here, we have to call the prisma command directly (Azure zip deployment destroys the symlinks in node_modules/.bin )
APPLICATION_DATABASE_URL="file:/migration/temp.sqlite" node node_modules/prisma/build/index.js migrate deploy && \
cp /migration/temp.sqlite local_data/application.sqlite && \
cp /migration/temp.sqlite-wal local_data/application.sqlite-wal && \
printf "######### Migrating application database - cleanup\n" && \
rm -rf /migration
#!/usr/bin/env bash
USAGE="Usage: ./deploy.sh <AZURE_ENV_NAME> <AZURE_SUFFIX>"
if [ $# == 0 ] ; then
echo "$USAGE"
exit 1;
fi
ENVNAME=$1
SUFFIX=$2
RG_NAME="rg-$ENVNAME-$SUFFIX"
APP_NAME="app-$ENVNAME-myappname-$SUFFIX"
rm -rf node-dist/ && rm -rf Archive_to_deploy.zip
# install deps, forcing AzureAppService-compatible binaries targets for prisma
PRISMA_CLI_BINARY_TARGETS=debian-openssl-1.1.x npm ci
npm run build
# consider Doing npm ci --production or npm ci --omit=dev before zipping
# => no build deps in prod (it would also make the Archive_to_deploy.zip smaller)
# zip all relevant files and directories
zip -r -X Archive_to_deploy.zip node_modules/ node-dist/ prisma/ package.json package-lock.json azure_database_migrate.sh
# deploy on Azure
az webapp deployment source config-zip --resource-group $RG_NAME --name $APP_NAME --src ./Archive_to_deploy.zip
@justinhenricks
Copy link

justinhenricks commented Oct 10, 2023

This is interesting - currently trying to get a prisma/sqlite setup working on an azure app. What about seeding production data? Whenever I run:

node node_modules/prisma/build/index.js db seed in my SSH console I am getting:

Running seed command `ts-node prisma/seed.ts --environment production` ...

An error occurred while running the seed command:
Error: Command failed with ENOENT: ts-node prisma/seed.ts --environment production
spawn ts-node ENOENT

Might give this a go!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment