Drizzle Team•16mo ago

How do I connect to sqlite on fly.io?

I followed the instructions on fly.io on how to setup a project using sqlite. I believe I did it all correctly. I've created a volume and an app and I've linked the volume to the app. Now I don't know how to connect to the sqlite db on there. On local I simply do new Database('sqlite.db') but I don't know what to use on fly.io. There are no docs on fly.io about this either, and I already asked on their forums. Has anyone done this before? 🙂

9 Replies

bloberenober•16mo ago

you do it exactly the same read about LiteFS in their docs

Meexa•16mo ago

I've gone into their litefs section but it keeps mentioning rails

bloberenober•16mo ago

there's a node example somewhere in the docs also, the example we provided should have everything you need to use it

Meexa•16mo ago

Hmm yeah I've found this section on LiteFS but it's just so confusing to me. It feels like they expect me to have more knowledge about LiteFS that I just don't have. https://fly.io/docs/litefs/getting-started/#installing-litefs For example this litefs.yml file

# This directory is where your application will access the database.
fuse:
  dir: "/litefs"

# This directory is where LiteFS will store internal data.
# You must place this directory on a persistent volume.
data:
  dir: "/var/lib/litefs"

# This directory is where your application will access the database.
fuse:
  dir: "/litefs"

# This directory is where LiteFS will store internal data.
# You must place this directory on a persistent volume.
data:
  dir: "/var/lib/litefs"

How does this correlate to the volume that I created? I named my volume primenv_db. Should I point to that instead of /litefs? And then the lease config. I only need 1 node that has write access. Do I still need this config? Where do I put it? I already have a lease config with type: 'static'.

lease:
  type: "static"
  candidate: false
  hostname: "${PRIMARY_HOSTNAME}"
  advertise-url: "http://${PRIMARY_HOSTNAME}:20202"

lease:
  type: "static"
  candidate: false
  hostname: "${PRIMARY_HOSTNAME}"
  advertise-url: "http://${PRIMARY_HOSTNAME}:20202"

And in the drizzle-orm docs I only see this

// better-sqlite3 or fly.io LiteFS
import { drizzle, BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('sqlite.db');

// better-sqlite3 or fly.io LiteFS
import { drizzle, BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('sqlite.db');

Where does sqlite.db come from? How do I know what the name of the .db file is on fly.io? Sorry for the bombardment of questions. It's just very confusing 😓

bloberenober•16mo ago

Yes, it is a bit confusing. The fuse.dir is the directory where you can put your DB files. So you can create the DB with any file name in that directory and it will be automatically synced using LiteFS. The example I was referring to is a separate repo where we implemented the fly.io/LiteFS example specifically.

bloberenober•16mo ago

https://github.com/drizzle-team/drizzle-flyio-litefs

GitHub

GitHub - drizzle-team/drizzle-flyio-litefs: Drizzle ORM Fly.io Lite...

Drizzle ORM Fly.io LiteFS example. Contribute to drizzle-team/drizzle-flyio-litefs development by creating an account on GitHub.

bloberenober•16mo ago

It's not linked in the README though, so I can't blame you for not finding it yourself This example is a bit outdated in terms of the ORM API, but it should give you an idea about how to use it

Meexa•16mo ago

Thanks I will check it out I've updated my config but now the server is crashing.

2023-04-12T13:28:55.751 app[9080544f610278] ams [info] [ 2.142691] reboot: Restarting system
2023-04-12T13:28:56.788 runner[9080544f610278] ams [info] machine did not have a restart policy, defaulting to restart
2023-04-12T13:29:13.880 app[9080544f610278] ams [info] Starting init (commit: e878f33)...
2023-04-12T13:29:13.901 app[9080544f610278] ams [info] Mounting /dev/vdb at /mnt/sqlite w/ uid: 1001, gid: 65533 and chmod 0755
2023-04-12T13:29:13.908 app[9080544f610278] ams [info] Preparing to run: `/bin/sh -c "litefs"` as nextjs
2023-04-12T13:29:13.926 app[9080544f610278] ams [info] 2023/04/12 13:29:13 listening on [fdaa:1:e59d:a7b:c6ef:a4e9:e319:2]:22 (DNS: [fdaa::3]:53)
2023-04-12T13:29:14.921 app[9080544f610278] ams [info] Starting clean up.
2023-04-12T13:29:14.921 app[9080544f610278] ams [info] Umounting /dev/vdb from /mnt/sqlite

2023-04-12T13:28:55.751 app[9080544f610278] ams [info] [ 2.142691] reboot: Restarting system
2023-04-12T13:28:56.788 runner[9080544f610278] ams [info] machine did not have a restart policy, defaulting to restart
2023-04-12T13:29:13.880 app[9080544f610278] ams [info] Starting init (commit: e878f33)...
2023-04-12T13:29:13.901 app[9080544f610278] ams [info] Mounting /dev/vdb at /mnt/sqlite w/ uid: 1001, gid: 65533 and chmod 0755
2023-04-12T13:29:13.908 app[9080544f610278] ams [info] Preparing to run: `/bin/sh -c "litefs"` as nextjs
2023-04-12T13:29:13.926 app[9080544f610278] ams [info] 2023/04/12 13:29:13 listening on [fdaa:1:e59d:a7b:c6ef:a4e9:e319:2]:22 (DNS: [fdaa::3]:53)
2023-04-12T13:29:14.921 app[9080544f610278] ams [info] Starting clean up.
2023-04-12T13:29:14.921 app[9080544f610278] ams [info] Umounting /dev/vdb from /mnt/sqlite

I took these steps: 1. Removed all apps from my fly.io dashboard 2. Updated my fly.toml with this

[mounts]
  source = "primenv_db"
  destination = "/mnt/sqlite"

[mounts]
  source = "primenv_db"
  destination = "/mnt/sqlite"

3. Updated my dockerfile

# Fetch the LiteFS binary using a multi-stage build.
FROM flyio/litefs:0.2 AS litefs

# Install dependencies only when needed
FROM node:16-alpine AS builder
WORKDIR /app
COPY . .

# Check https://github.com/nodejs/docker-node/tree/b4117f9333da4138b03a546ec926ef50a31506c3#nodealpine to understand why libc6-compat might be needed.
RUN apk add --no-cache libc6-compat

RUN npm ci

ENV NEXT_TELEMETRY_DISABLED 1

RUN npm run build

# If using npm comment out above and use below instead
# RUN npm run build


# Production image, copy all the files and run next
FROM node:16-alpine AS runner
WORKDIR /app

ENV NODE_ENV production
ENV NEXT_TELEMETRY_DISABLED 1

RUN addgroup --system --gid 1001 nodejs
RUN adduser --system --uid 1001 nextjs

COPY --from=builder /app ./

USER nextjs

ADD etc/litefs.yml /etc/litefs.yml

# Ensure our mount & data directories exists before mounting with LiteFS.
# RUN mkdir -p /data /mnt/sqlite

# Run LiteFS as the entrypoint. Anything after the double-dash is run as a
# subprocess by LiteFS. This allows the file system to mount and initialize
# before the application starts.
ENTRYPOINT "litefs"

# Fetch the LiteFS binary using a multi-stage build.
FROM flyio/litefs:0.2 AS litefs

# Install dependencies only when needed
FROM node:16-alpine AS builder
WORKDIR /app
COPY . .

# Check https://github.com/nodejs/docker-node/tree/b4117f9333da4138b03a546ec926ef50a31506c3#nodealpine to understand why libc6-compat might be needed.
RUN apk add --no-cache libc6-compat

RUN npm ci

ENV NEXT_TELEMETRY_DISABLED 1

RUN npm run build

# If using npm comment out above and use below instead
# RUN npm run build


# Production image, copy all the files and run next
FROM node:16-alpine AS runner
WORKDIR /app

ENV NODE_ENV production
ENV NEXT_TELEMETRY_DISABLED 1

RUN addgroup --system --gid 1001 nodejs
RUN adduser --system --uid 1001 nextjs

COPY --from=builder /app ./

USER nextjs

ADD etc/litefs.yml /etc/litefs.yml

# Ensure our mount & data directories exists before mounting with LiteFS.
# RUN mkdir -p /data /mnt/sqlite

# Run LiteFS as the entrypoint. Anything after the double-dash is run as a
# subprocess by LiteFS. This allows the file system to mount and initialize
# before the application starts.
ENTRYPOINT "litefs"

bloberenober•16mo ago

Honestly, I'm not sure. Maybe you should ask around the fly.io community, since this is not really related to Drizzle I think.

Gaming

Programming

How do I connect to sqlite on fly.io?