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.

• 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

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).

➡️ 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;'

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

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.

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")
}

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.

➡️ 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.