SQLite not getting setup properly

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.

Here’s my repo: GitHub - 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 successfully as shown in the github action during deploy:

	 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:
	   └─ 20220307190657_init/
	     └─ migration.sql
	 All migrations have been successfully applied.

The migration script 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, we get output that suggests the database has not been initialized properly:

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 properly I think and the DATABASE_URL set properly I believe. Our schema.prisma file is configured to use the DATABASE_URL 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?

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.

1 Like

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

That did it! Thank you!

I tried this first: try this · remix-run/indie-stack@8b5a4ef · GitHub

That didn’t work, so then I went with: fix start script · remix-run/indie-stack@946adc8 · GitHub

And that worked well :slight_smile: Thanks again!

Oh yeah! :upside_down_face: Thanks for that :slight_smile: