# SQLite not getting setup properly **URL:** \ **Category:** General\ **Created:** [March 15, 2022, 1:40pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386 "2022-03-15T13:40:44Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![kentcdodds](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kentcdodds/32/2803_2.png) [@kentcdodds](https://community.fly.io/u/kentcdodds)\ **Post date:** [March 15, 2022, 1:40pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/1 "2022-03-15T13:40:44Z")
I’m trying to get this template to work: [GitHub - remix-run/indie-stack: The Remix Stack for deploying to Fly with a database, authentication, testing, linting, formatting, etc.](https://github.com/remix-run/indie-stack) Here’s my repo: [GitHub - kentcdodds/kents-indie-stack](https://github.com/kentcdodds/kents-indie-stack) It uses prisma and SQLite for data persistence. I followed all the instructions in the README for creating a persistent volume and [the fly.toml runs `npx prisma migrate deploy`](https://github.com/kentcdodds/kents-indie-stack/blob/2559b82cf05fb0f43fea82326ee7824499c667ac/fly.toml#L12) successfully as shown in [the github action during deploy](https://github.com/kentcdodds/kents-indie-stack/runs/5545830249?check_suite_focus=true#step:7:81): ```auto Preparing to run: `docker-entrypoint.sh npx prisma migrate deploy` as root 2022/03/14 23:04:03 listening on [fdaa:0:23df:a7b:2985:8a45:369b:2]:22 (DNS: [fdaa::3]:53) Prisma schema loaded from prisma/schema.prisma Datasource "db": SQLite database "sqlite.db" at "file:/data/sqlite.db" SQLite database sqlite.db created at file:/data/sqlite.db 1 migration found in prisma/migrations Applying migration `20220307190657_init` The following migration have been applied: migrations/ └─ 20220307190657_init/ └─ migration.sql All migrations have been successfully applied. ``` [The migration script](https://github.com/kentcdodds/kents-indie-stack/blob/2559b82cf05fb0f43fea82326ee7824499c667ac/prisma/migrations/20220307190657_init/migration.sql) that should have been run shows creating all the tables (and in the sqlite case, prisma creates the sqlite file in the first place which I’ve SSH-ed into to verify _does_ exist). However, when hitting [the healthcheck endpoint](https://github.com/kentcdodds/kents-indie-stack/blob/main/app/routes/healthcheck.tsx), we get [output](https://github.com/kentcdodds/kents-indie-stack/runs/5545830249?check_suite_focus=true#step:7:122) that suggests the database has not been initialized properly: ```auto 2022-03-14T23:08:55Z [info]GET /healthcheck 500 - - 3.511 ms 2022-03-14T23:08:55Z [info]HEAD / 200 - - 1.889 ms 2022-03-14T23:09:05Z [info]healthcheck ❌ { 2022-03-14T23:09:05Z [info] error: PrismaClientKnownRequestError: The table `main.User` does not exist in the current database. 2022-03-14T23:09:05Z [info] at Object.request (/myapp/node_modules/@prisma/client/runtime/index.js:39809:15) 2022-03-14T23:09:05Z [info] at PrismaClient._request (/myapp/node_modules/@prisma/client/runtime/index.js:40637:18) 2022-03-14T23:09:05Z [info] at async Promise.all (index 0) 2022-03-14T23:09:05Z [info] at loader2 (/myapp/build/index.js:540:5) 2022-03-14T23:09:05Z [info] at Object.callRouteLoader (/myapp/node_modules/@remix-run/server-runtime/data.js:73:14) 2022-03-14T23:09:05Z [info] at handleResourceRequest (/myapp/node_modules/@remix-run/server-runtime/server.js:457:14) 2022-03-14T23:09:05Z [info] at requestHandler (/myapp/node_modules/@remix-run/server-runtime/server.js:66:20) 2022-03-14T23:09:05Z [info] at /myapp/node_modules/@remix-run/express/server.js:43:22 { 2022-03-14T23:09:05Z [info] code: 'P2021', 2022-03-14T23:09:05Z [info] clientVersion: '3.10.0', 2022-03-14T23:09:05Z [info] meta: { table: 'main.User' } 2022-03-14T23:09:05Z [info] } 2022-03-14T23:09:05Z [info]} ``` So the app fails to deploy. I don’t know what step I’m getting wrong. We have the volume [mounted](https://github.com/kentcdodds/kents-indie-stack/blob/2559b82cf05fb0f43fea82326ee7824499c667ac/fly.toml#L18-L20) properly I think and the [`DATABASE_URL`](https://github.com/kentcdodds/kents-indie-stack/blob/2559b82cf05fb0f43fea82326ee7824499c667ac/fly.toml#L7) set properly I believe. Our [`schema.prisma` file is configured to use the `DATABASE_URL`](https://github.com/kentcdodds/kents-indie-stack/blob/2559b82cf05fb0f43fea82326ee7824499c667ac/prisma/schema.prisma#L3) and I even SSH-ed in to verify the `DATABASE_URL` is `file:/data/sqlite.db` (it is). The template repo only works because I SSH-ed into that app to manually run the `npx prisma db push` command to get the database to match the schema. You should not have to do that as the migration runs the same commands. Have I missed a step? ---
**Author:** ![kurt](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kurt/32/43757_2.png) [@kurt](https://community.fly.io/u/kurt)\ **Post date:** [March 15, 2022, 2:24pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/2 "2022-03-15T14:24:49Z")
sqlite is a little confusing in our infrastructure. The `release_command` runs in an isolated VM and is not accessing the same sqlite file as your deployed app. It’s not supposed to be allowed when you have `mounts` defined, we’ll double check that. You’ll need to run the sqlite migrations at app boot time. You can do this with a custom entrypoint, or make your node server process do it if you prefer. ---
**Author:** ![jsierles](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/jsierles/32/595_2.png) [@jsierles](https://community.fly.io/u/jsierles)\ **Post date:** [March 15, 2022, 3:33pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/3 "2022-03-15T15:33:14Z")
Here’s how we did it in the original jokes Remix app: [remix-jokes/start\_with\_migrations.sh at main · remix-run/remix-jokes · GitHub](https://github.com/remix-run/remix-jokes/blob/main/start_with_migrations.sh) ---
**Author:** ![kentcdodds](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kentcdodds/32/2803_2.png) [@kentcdodds](https://community.fly.io/u/kentcdodds)\ **Post date:** [March 15, 2022, 3:55pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/4 "2022-03-15T15:55:45Z")
That did it! Thank you! I tried this first: [try this · remix-run/indie-stack@8b5a4ef · GitHub](https://github.com/remix-run/indie-stack/commit/8b5a4efb7e40fa76a1fac27604ed258e98bd624f) That didn’t work, so then I went with: [fix start script · remix-run/indie-stack@946adc8 · GitHub](https://github.com/remix-run/indie-stack/commit/946adc86a0a08e4f776a82165363ddc4ccc68f90) And that worked well 🙂 Thanks again! ---
**Author:** ![kentcdodds](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kentcdodds/32/2803_2.png) [@kentcdodds](https://community.fly.io/u/kentcdodds)\ **Post date:** [March 15, 2022, 3:55pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/5 "2022-03-15T15:55:59Z")
Oh yeah! 🙃 Thanks for that 🙂 ---
**Author:** ![sastreen](https://avatars.discourse-cdn.com/v4/letter/s/c68b51/32.png) [@sastreen](https://community.fly.io/u/sastreen)\ **Post date:** [February 27, 2023, 3:49am UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/6 "2023-02-27T03:49:29Z")
EDIT: Realized this post is a year old - sorry for the notifications. Put up a [new topic](https://community.fly.io/t/prisma-sqlite-causes-an-out-of-memory-error-on-deploy/11039). ---
**Author:** ![MrKodama](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@MrKodama](https://community.fly.io/u/MrKodama)\ **Post date:** [March 24, 2024, 3:45pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/7 "2024-03-24T15:45:22Z")
Hi @jsierles Having successfully created a volume, using: ```auto flyctl volumes create sb_user_admin_app_v1_data ``` I then used your `start_with_migrations.sh` script, for the first time and it worked great. 🙂 However, when I made an update to the DB [added new column] and tried to do: ```auto run deploy ``` With the same **fly.toml** settings: ```auto # fly.toml app configuration file generated for sb-user-admin-app-v1 on 2024-03-22T11:44:39Z # # See https://fly.io/docs/reference/configuration/ for information about how to use this file. # app = 'sb-user-admin-app-v1' primary_region = 'ams' [build] [env] PORT = "8081" SHOPIFY_APP_URL = "https://sb-user-admin-app-v1.fly.dev" SHOPIFY_API_KEY = "[API KEY]" SCOPES = "read_checkouts,read_customers,read_orders,read_products,write_checkouts,write_customers,write_orders,write_products" DATABASE_URL="file:/data/dev.sqlite" [experimental] cmd = "start_with_migrations24032024.sh" entrypoint = "sh" [http_service] internal_port = 8081 force_https = true auto_stop_machines = true auto_start_machines = true min_machines_running = 0 processes = ['app'] [mounts] source = "sb_user_admin_app_v1_data" destination = "/data" [[vm]] memory = '1gb' cpu_kind = 'shared' cpus = 1 ``` I get an error saying: ```auto 2024-03-24T16:17:50.700 runner[6833260f191258] ams [info] Pulling container image registry.fly.io/sb-user-admin-app-v1:deployment-01HSRKYH2D0RQFMWVG4KWAZZ14 2024-03-24T16:18:08.604 runner[6833260f191258] ams [info] Successfully prepared image registry.fly.io/sb-user-admin-app-v1:deployment-01HSRKYH2D0RQFMWVG4KWAZZ14 (17.903990832s) 2024-03-24T16:18:08.631 runner[6833260f191258] ams [info] Setting up volume 'sb_user_admin_app_v1_data' 2024-03-24T16:18:08.631 runner[6833260f191258] ams [info] Opening encrypted volume 2024-03-24T16:18:09.075 runner[6833260f191258] ams [info] Configuring firecracker 2024-03-24T16:18:09.280 app[6833260f191258] ams [info] INFO Sending signal SIGINT to main child process w/ PID 314 2024-03-24T16:18:14.356 app[6833260f191258] ams [info] INFO Sending signal SIGTERM to main child process w/ PID 314 2024-03-24T16:18:19.579 app[6833260f191258] ams [warn] Virtual machine exited abruptly 2024-03-24T16:18:20.606 app[6833260f191258] ams [info] [0.037987] Spectre V2 : WARNING: Unprivileged eBPF is enabled with eIBRS on, data leaks possible via Spectre v2 BHB attacks! 2024-03-24T16:18:20.644 app[6833260f191258] ams [info] [0.050516] PCI: Fatal: No config space access function found 2024-03-24T16:18:20.844 app[6833260f191258] ams [info] INFO Starting init (commit: 02be9e0)... 2024-03-24T16:18:20.859 app[6833260f191258] ams [info] INFO Mounting /dev/vdb at /data w/ uid: 0, gid: 0 and chmod 0755 2024-03-24T16:18:20.862 app[6833260f191258] ams [info] INFO Resized /data to 3204448256 bytes 2024-03-24T16:18:20.863 app[6833260f191258] ams [info] INFO Preparing to run: `sh start_with_migrations24032024.sh` as root 2024-03-24T16:18:20.872 app[6833260f191258] ams [info] INFO [fly api proxy] listening at /.fly/api 2024-03-24T16:18:20.876 app[6833260f191258] ams [info] 2024/03/24 16:18:20 listening on [fdaa:9:74d:a7b:242:fcab:c6ff:2]:22 (DNS: [fdaa::3]:53) 2024-03-24T16:18:20.878 app[6833260f191258] ams [info] start_with_migrations24032024.sh: line 2: : not found 2024-03-24T16:18:20.878 app[6833260f191258] ams [info] start_with_migrations24032024.sh: set: line 3: illegal option - 2024-03-24T16:18:20.883 runner[6833260f191258] ams [info] Machine created and started in 30.207s 2024-03-24T16:18:21.872 app[6833260f191258] ams [info] INFO Main child exited normally with code: 2 2024-03-24T16:18:21.873 app[6833260f191258] ams [info] INFO Starting clean up. 2024-03-24T16:18:21.881 app[6833260f191258] ams [info] INFO Umounting /dev/vdb from /data 2024-03-24T16:18:21.882 app[6833260f191258] ams [info] ERROR error opening /dev/kmsg: No such file or directory (os error 2) 2024-03-24T16:18:21.883 app[6833260f191258] ams [info] WARN hallpass exited, pid: 315, status: signal: 15 (SIGTERM) 2024-03-24T16:18:21.884 app[6833260f191258] ams [info] WARN can't run hallpass: an unhandled IO error occurred: No such file or directory (os error 2) ``` Maybe, I only need to use your script, on the first deployment? So, perhaps I need to now comment out the **experimental** section of the **fly.toml** , before I deploy again? But, I would have thought you need to run the script each time, there are new structural changes to the DB? Please advise? ---
**Author:** ![kentcdodds](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kentcdodds/32/2803_2.png) [@kentcdodds](https://community.fly.io/u/kentcdodds)\ **Post date:** [March 24, 2024, 5:24pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/8 "2024-03-24T17:24:11Z")
For an up-to-date example of deploying SQLite (with LiteFS) on fly, check [GitHub - epicweb-dev/epic-stack: This is a Full Stack app starter with the foundational things setup and configured for you to hit the ground running on your next EPIC idea.](https://github.com/epicweb-dev/epic-stack) as a reference. Alternatively, for one without LiteFS (so no multi-region) check [GitHub - remix-run/indie-stack: The Remix Stack for deploying to Fly with SQLite, authentication, testing, linting, formatting, etc.](https://github.com/remix-run/indie-stack) ---
**Author:** ![MrKodama](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@MrKodama](https://community.fly.io/u/MrKodama)\ **Post date:** [March 24, 2024, 5:45pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/9 "2024-03-24T17:45:43Z")
Hi @kentcdodds Thank you so much for the information. I have looked at the 2nd link: > **[GitHub - remix-run/indie-stack: The Remix Stack for deploying to Fly with...](https://github.com/remix-run/indie-stack)** > > The Remix Stack for deploying to Fly with SQLite, authentication, testing, linting, formatting, etc. - remix-run/indie-stack I have noticed a couple of things: In my **start.sh** , I have: ```auto set -ex npx prisma migrate deploy npm run start ``` The example link, **start.sh** , uses: ```auto npx prisma migrate deploy npm run start ``` What exactly does: `-ex` Do? ---
**Author:** ![kentcdodds](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kentcdodds/32/2803_2.png) [@kentcdodds](https://community.fly.io/u/kentcdodds)\ **Post date:** [March 24, 2024, 5:55pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/10 "2024-03-24T17:55:10Z")
Here’s ChatGPT’s response: The `set -ex` command in a shell script (like your `start.sh`) has two parts: - `set -e`: Tells the shell to exit immediately if a command exits with a non-zero status. It ensures that if an error occurs during any step of your script, the script halts execution at that point to prevent further errors or unintended effects. - `set -x`: Makes the shell print each command before executing it. This is useful for debugging, as it shows you exactly what is being executed, helping you understand the flow of the script or catch mistakes in the commands. So, `set -ex` in your `start.sh` script is used to make the script exit on any error while also printing out each command before it is executed, providing a clear and detailed execution trace. ---
**Author:** ![MrKodama](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@MrKodama](https://community.fly.io/u/MrKodama)\ **Post date:** [March 24, 2024, 6:02pm UTC](https://community.fly.io/t/sqlite-not-getting-setup-properly/4386/11 "2024-03-24T18:02:45Z")
OK. Great. Thanks. Its really weird. I have essentially uesd exactly the same **schema.prisma** and **fly.toml** , as the first time I deployed the DB to the volume. And everything worked perfectly. The script worked & the migration worked. But after I made a change in the DB. Added a new column and then redeployed to **[fly.io](http://fly.io)**, I keep getting the following: ```auto 2024-03-24T18:00:08.089 app[6833260f191258] ams [info] INFO Preparing to run: `sh start_with_migrations.sh` as root 2024-03-24T18:00:08.097 app[6833260f191258] ams [info] INFO [fly api proxy] listening at /.fly/api 2024-03-24T18:00:08.101 app[6833260f191258] ams [info] 2024/03/24 18:00:08 listening on [fdaa:9:74d:a7b:242:fcab:c6ff:2]:22 (DNS: [fdaa::3]:53) 2024-03-24T18:00:08.103 app[6833260f191258] ams [info] start_with_migrations.sh: line 2: : not found 2024-03-24T18:00:08.103 app[6833260f191258] ams [info] start_with_migrations.sh: set: line 3: illegal option - ``` The _error_ does not like line 3: `set -ex` **start\_with\_migrations.sh** ```auto #!/bin/sh set -ex npx prisma migrate deploy npm run start ``` My **start\_with\_migrations.sh** is in the project root: ![image](https://global.discourse-cdn.com/fly1/original/3X/0/9/09847d07569d4145835f6d04410972c67126e5d5.png) ![sb-user-admin-app-v1-1](https://global.discourse-cdn.com/fly1/original/3X/9/4/94e1a30f1ccbff328f518cfb43b5991ac4cc4d39.png) **fly.toml:** ```auto app = 'sb-user-admin-app-v1' primary_region = 'ams' [build] [env] PORT = "8081" SHOPIFY_APP_URL = "https://sb-user-admin-app-v1.fly.dev" SHOPIFY_API_KEY = "[API KEY]" SCOPES = "read_checkouts,read_customers,read_orders,read_products,write_checkouts,write_customers,write_orders,write_products" DATABASE_URL="file:/data/dev.sqlite" [experimental] cmd = "start_with_migrations.sh" entrypoint = "sh" [http_service] internal_port = 8081 force_https = true auto_stop_machines = true auto_start_machines = true min_machines_running = 0 processes = ['app'] [mounts] source = "sb_user_admin_app_v1_data" destination = "/data" [[vm]] memory = '1gb' cpu_kind = 'shared' cpus = 1 ``` **schema.prisma:** ```auto ... datasource db { provider = "sqlite" url = "file:/data/dev.sqlite" } .. ``` I really don’t understand what’s going on? Here is my [fly.io](http://fly.io) remote root: ![remote-1](https://global.discourse-cdn.com/fly1/original/3X/8/f/8f3a3566ef2d723bee2b7a64097691fb52d4a7a6.png) Maybe this is the problem: ![image](https://global.discourse-cdn.com/fly1/original/3X/2/a/2a30f4faf9e8fa0096f7ab464d8772aefac0dcf9.png) **UPDATE:** 24.032024 20:17 After several hours of tearing all my hair out and howling at the moon, I found out what the problem was: > > Check whether your script or data has DOS style end-of-line characters Use: `cat -v start_with_migrations.sh` > DOS carriage returns will show up as ^M after each line. If you find them, delete them using dos2unix (a.k.a. fromdos) or tr -d ‘\r’ Make sure you run the script with bash, not sh The first line in the script must be #!/bin/bash or #!/usr/bin/env bash. It must not be #!/bin/sh even if your system’s /bin/sh is a symlink to /bin/bash Run the script with ./yourscript or bash yourscript. Do not run it with sh yourscript. This applies even when sh is a symlink to bash. On Windows, you can use Notepad++: > Your script should look like this, once you have removed the ^M: ![image](https://global.discourse-cdn.com/fly1/original/3X/f/a/fac539b96f5b5f088b89e61abc2ad7ad5ba1003b.png) After removing the offending ^M characters, everything works again, once more! 🙂