iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article

☁️

Cloud Run × SQLite Architecture: Testing Replication to Cloud Storage with Litestream

2025/05/19に公開

Overview

I verified whether SQLite files within a container can be replicated to Cloud Storage in real-time by combining Cloud Run × Litestream. Locally, I used DevContainer to quickly build a development environment with Hono + Kysely + SQLite, set up MinIO to test Litestream's behavior, and implemented a user CRUD API for operational verification to check if updates are properly replicated. To eventually deploy it to GCP, I also set up Artifact Registry and Cloud Storage buckets, including permission management. In this article, I have summarized the process and the pitfalls I encountered.

Environment Setup

Setting up the local environment

I will build the environment based on the following article.

I changed the configuration to use Devcontainer, created a .devcontainer directory, and created the following files.

Dockerfile

FROM node:22.15.0-bullseye-slim

ARG username=vscode
ARG useruid=1000
ARG usergid=${useruid}

RUN set -ex \
    && apt-get update \
    && apt-get install -y \
        ca-certificates \
        sudo \
        sqlite3 \
        wget \
        --no-install-recommends \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/* \
    # Delete node user with uid=1000 and create vscode user with uid=1000
    && userdel -r node \
    && groupadd --gid ${usergid} ${username} \
    && useradd -s /bin/bash --uid ${useruid} --gid ${usergid} -m ${username} \
    && echo ${username} ALL=\(root\) NOPASSWD:ALL > /etc/sudoers.d/${username} \
    && chmod 0440 /etc/sudoers.d/${username}

RUN wget https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.tar.gz \
    -O litestream.tar.gz \
    && tar -xzf litestream.tar.gz -C ./ \
    && mv litestream /usr/local/bin/ \
    && rm litestream.tar.gz \
    && chmod +x /usr/local/bin/litestream

USER ${username}

compose.yml

volumes:
  minio_data:
  modules_data:

name: cloudrun-litestream-example
services:
  app:
    build: .
    volumes:
      - ..:/usr/src
      - modules_data:/usr/src/node_modules
    command: tail -f /dev/null
    working_dir: /usr/src
  minio:
    image: minio/minio:RELEASE.2025-02-18T16-25-55Z
    volumes:
      - minio_data:/minio/data
    command: server --console-address ':9001' /minio/data
    ports:
      - 9000:9000
      - 9001:9001

devcontainer.json (Customize extensions etc. as you like)

{
  "name": "CloudRun Litestream Example Remote Container Dev",
  "dockerComposeFile": ["compose.yaml"],
  "service": "app",
  "workspaceFolder": "/usr/src",
  "customizations": {
    "vscode": {
      "extensions": ["dbaeumer.vscode-eslint", "esbenp.prettier-vscode"],
      "settings": {
        "editor.tabSize": 2,
        "editor.formatOnSave": true,
        "editor.codeActionsOnSave": {
          "source.fixAll.eslint": "always"
        },
        "files.insertFinalNewline": true,
        "files.trimFinalNewlines": true
      }
    }
  },
  "features": {
    "ghcr.io/devcontainers/features/git:1": {}
  },
  "postAttachCommand": ".devcontainer/postAttach.sh",
  "remoteUser": "vscode"
}

postAttach.sh

#!/bin/sh

cd `dirname $0`
cd ..
sudo chown -R vscode node_modules

Implementing a simple API locally

I will implement a simple CRUD API with a Kysely × Hono × SQLite configuration based on this:

Project Creation

First, create a Hono project for Node.js.

$ yarn create hono .
yarn create v1.22.22
[1/4] Resolving packages...
[2/4] Fetching packages...
[3/4] Linking dependencies...
[4/4] Building fresh packages...

success Installed "create-hono@0.18.0" with binaries:
      - create-hono
create-hono version 0.18.0
✔ Using target directory … .
✔ Which template do you want to use? nodejs
✔ Directory not empty. Continue? Yes
✔ Do you want to install project dependencies? Yes
✔ Which package manager do you want to use? yarn
✔ Cloning the template
✔ Installing project dependencies
🎉 Copied project files

Just like in the article above, add the settings for .devcontainer/postAttach.sh and the port settings to .devcontainer/compose.yaml. Accessing http://localhost:3000 should display Hello Hono!.

Next, I'll create a test table using kysely-ctl. I'll use better-sqlite3 as the SQLite dialect, following the Kysely documentation.

Let's install the necessary packages immediately.

$ yarn add kysely better-sqlite3
$ yarn add -D kysely-ctl @types/better-sqlite3

Next, generate the Kysely config file.

yarn kysely init

The final .config/kysely.config.ts is configured as follows.

import Database from 'better-sqlite3';
import { SqliteDialect } from 'kysely';
import { defineConfig } from 'kysely-ctl';

export default defineConfig({
  dialect: new SqliteDialect({
    database: new Database('db.sqlite'),
  }),
  migrations: {
    migrationFolder: 'migrations',
  },
  //   plugins: [],
  //   seeds: {
  //     seedFolder: "seeds",
  //   }
});

User Table Creation

Create the User table just like in the previous article. I have modified the migration file for SQLite as follows.

import { sql, type Kysely } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('users')
    .addColumn('id', 'integer', (col) => col.primaryKey())
    .addColumn('first_name', 'text', (col) => col.notNull())
    .addColumn('last_name', 'text')
    .addColumn('created_at', 'text', (col) =>
      col.defaultTo(sql`CURRENT_TIMESTAMP`).notNull()
    )
    .addColumn('updated_at', 'text', (col) =>
      col.defaultTo(sql`CURRENT_TIMESTAMP`).notNull()
    )
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('users').execute();
}

Now, run the migration. It’s successful if the User table is created.

Seed Data Registration & API Implementation

Perform the data registration by referring to "Seed Data Registration" in the article mentioned above. The API implementation is also basically the same as the article, with some adjustments needed for SQLite.

The User model in src/types.ts is changed as follows because better-sqlite3 does not support Date objects:

export type User = {
  id?: number;
  first_name: string;
  last_name: string;
  created_at?: string; // Date -> string
  updated_at?: string; // Date -> string
};

Below is the src/index.ts modified for SQLite.

import { serve } from '@hono/node-server';
import SQLiteDatabase from 'better-sqlite3';
import { Hono } from 'hono';
import { Kysely, SqliteDialect } from 'kysely';
import type { Database } from './types.js';

const dialect = new SqliteDialect({
  database: new SQLiteDatabase('db.sqlite'),
});

export const db = new Kysely<Database>({
  dialect,
});

const app = new Hono();

app.get('/users', async (c) => {
  const users = await db.selectFrom('users').selectAll().execute();
  return c.json(users);
});

app.get('/users/:id', async (c) => {
  const id = c.req.param('id');
  const user = await db
    .selectFrom('users')
    .selectAll()
    .where('id', '=', Number(id))
    .executeTakeFirst();
  if (!user) {
    return c.notFound();
  }
  return c.json(user);
});

app.post('/users', async (c) => {
  const { first_name, last_name } = await c.req.json();
  const user = await db
    .insertInto('users')
    .values({ first_name, last_name })
    .returningAll()
    .executeTakeFirst();
  return c.json(user, 201);
});

app.put('/users/:id', async (c) => {
  const id = c.req.param('id');
  const { first_name, last_name } = await c.req.json();
  const user = await db
    .updateTable('users')
    // better-sqlite3 cannot take Date objects, so use toISOString
    .set({ first_name, last_name, updated_at: new Date().toISOString() })
    .where('id', '=', Number(id))
    .returningAll()
    .executeTakeFirst();
  if (!user) {
    return c.notFound();
  }
  return c.json(user);
});

app.delete('/users/:id', async (c) => {
  const id = c.req.param('id');
  const user = await db
    .deleteFrom('users')
    .where('id', '=', Number(id))
    .returningAll()
    .executeTakeFirst();
  if (!user) {
    return c.notFound();
  }
  return c.json(user);
});

serve(
  {
    fetch: app.fetch,
    port: 3000,
  },
  (info) => {
    console.log(`Server is running on http://localhost:${info.port}`);
  }
);

It's all good if you hit the API and CRUD is working properly.

Running Litestream × MinIO Configuration Locally

Next, I would like to try running Litestream on my local machine and performing replication to MinIO.

I basically followed the article above, but since the paths are different, I created litestream.yml with the following content.

dbs:
  - path: /usr/src/db.sqlite
    replicas:
      - type: s3
        bucket: litestream-bucket
        path: db.sqlite
        endpoint: http://minio:9000
        region: us-east-1
        access-key-id: xxxxxxxx
        secret-access-key: xxxxxxx
        force-path-style: true

Now that we've come this far, let's run Litestream and check the behavior. I will perform continuous replication and confirm that it is syncing properly.

litestream replicate -config ./litestream.yml

I'll keep the above command running to perform continuous replication. By the way, the current contents of the database are as follows.

Now, let's try registering one piece of data.

$ curl -H "Content-Type: application/json" \
-X POST \
-d "{\"first_name\": \"Ichiro\", \"last_name\": \"Tanaka\"}" \
http://localhost:3000/users

Next, stop litestream with Ctrl+C, restore the DB to a different path, and check its contents.

$ litestream restore -o restore_db.sqlite -config ./litestream.yml /usr/src/db.sqlite
$ sqlite3 restore_db.sqlite
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .table
_litestream_lock       kysely_migration       users
_litestream_seq        kysely_migration_lock
sqlite> SELECT * FROM users;
1|Taro|Yamada|2025-05-06 23:26:12|2025-05-06 23:26:12
2|Kenta|Fujimoto|2025-05-06 23:26:12|2025-05-06T23:38:30.072Z
3|Ichiro|Tanaka|2025-05-07 00:02:40|2025-05-07 00:02:40

It seems to be syncing properly ✨

Finally, I'll modify .devcontainer/postAttach.sh so that replication starts when the container is launched.

yarn dev &

# start litestream
litestream replicate -config ./litestream.yml

Adding configurations for deployment

First, we will proceed with creating the Cloud Storage for the replication destination and the Artifact Registry.

To work using the gcloud CLI, add the following service to .devcontainer/compose.yaml.

volumes:
  # ...
  # ↓ Add
  gcloud_config:
  docker_config:

name: cloudrun-litestream-example
services:
  # ...
  # ↓ Add
  infra:
    image: google/cloud-sdk:516.0.0-slim
    volumes:
      - ..:/usr/src
      - gcloud_config:/root/.config/gcloud
      - docker_config:/root/.docker
    working_dir: /usr/src
    command: tail -f /dev/null

This time, since a Docker in Docker configuration is required to create Docker images in Artifact Registry, create .devcontainer/devcontainer-infra.json which overrides .devcontainer/devcontainer.json with the following content.

{
  "name": "CloudRun Litestream Infra Example Remote Container Dev",
  "dockerComposeFile": ["compose.yaml"],
  "service": "infra",
  "workspaceFolder": "/usr/src",
  "features": {
    "ghcr.io/devcontainers/features/git:1": {},
    "ghcr.io/devcontainers/features/docker-in-docker:2": {},
    "ghcr.io/devcontainers/features/node:1": {
      "version": "22.15.0"
    }
  }
}

features/ghcr.io/devcontainers/features/docker-in-docker:2 in the above file enables the use of Docker in Docker.

Since it seems impossible to launch by overriding devcontainer.json from the GUI, use the CLI to launch it.

# Install devcontainer cli
$ npm install -g @devcontainers/cli
# Launch by overriding with devcontainer-infra.json
$ devcontainer up --workspace-folder . \
    --config .devcontainer/devcontainer.json \
    --override-config .devcontainer/devcontainer-infra.json

Creating the Dockerfile for Deployment

Since this is a trial, I will prepare a db.sqlite file with the users table and seed data already registered locally.

$ yarn kysely migrate latest
$ yarn kysely seed:run
$ db.sqlite
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .tables
kysely_migration       kysely_migration_lock  users
sqlite> select * from users;
1|Taro|Yamada|2025-05-13 00:47:34|2025-05-13 00:47:34
2|Hanako|Suzuki|2025-05-13 00:47:34|2025-05-13 00:47:34
# Change permissions beforehand
$ chmod 777 db.sqlite

Now that we're ready, I'll create a Dockerfile with the following content.

# ---------- 1) Build Stage ----------
FROM node:22.15.0-bullseye-slim AS build
WORKDIR /usr/src/app

RUN set -ex \
    && apt-get update \
    && apt-get install -y \
        ca-certificates \
        wget \
        --no-install-recommends \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

# Install dependencies (exclude devDependencies)
COPY package.json yarn.lock* ./
RUN --mount=type=cache,target=/usr/local/share/.cache/yarn \
    yarn install --frozen-lockfile

COPY tsconfig.json .
COPY src ./src
RUN yarn build

ARG LITESTREAM_VER=v0.3.13
RUN wget https://github.com/benbjohnson/litestream/releases/download/${LITESTREAM_VER}/litestream-${LITESTREAM_VER}-linux-amd64.tar.gz \
    -O litestream.tar.gz \
    && tar -xzf litestream.tar.gz -C ./ \
    && mv litestream /usr/local/bin/ \
    && rm litestream.tar.gz \
    && chmod +x /usr/local/bin/litestream

# ---------- 2) Runtime Stage ----------
FROM node:22.15.0-bullseye-slim

ENV PORT=8080
EXPOSE 8080

WORKDIR /usr/src/app

COPY --from=build /usr/src/app/node_modules ./node_modules
COPY --from=build /usr/src/app/dist ./dist
COPY --from=build /usr/local/bin/litestream /usr/local/bin/litestream
COPY --from=build /etc/ssl/certs/ca-certificates.crt /etc/ssl/certs/
COPY litestream.yml .
COPY db.sqlite .

# ---------- Entry point ----------
# - Restore from replica if DB does not exist
# - Then start the app as a child process using replicate -exec
ENTRYPOINT ["/bin/sh","-c", "\
  litestream replicate -config ./litestream.yml -exec 'node dist/index.js' \
"]

Creating the Cloud Storage Bucket

Enter the container launched with the devcontainer up command above, and first perform the authentication process.

gcloud auth login --no-launch-browser

Run the command above, access the displayed URL, and after granting permission, you will receive a verification code. Copy and paste it to complete the authentication.

# Verify that authentication was successful by running the command below
$ gcloud projects list
# It's convenient to set a default project for work
$ gcloud config set project <PROJECT_ID>
$ gcloud config list #=> Check project settings

Now that the preparation is complete, let's create the bucket for the replication destination.

gcloud storage buckets create gs://litestream-example-bucket \
  --uniform-bucket-level-access \
  --location=asia-northeast1

Creating the Artifact Registry Repository

First, check if the Artifact Registry API is enabled, and enable it if necessary.

$ gcloud services list --enabled | grep artifactregistry #=> Displayed if enabled
$ gcloud services enable artifactregistry.googleapis.com # Enable if necessary

Now that we're ready, let's create the repository.

gcloud artifacts repositories create litestream-example-repository \
    --repository-format=docker \
    --location=asia-northeast1

Finally, authenticate to allow docker push to asia-northeast1-docker.pkg.dev.

gcloud auth configure-docker asia-northeast1-docker.pkg.dev

Modifying the Port Specification and litestream.yml for Cloud Run

To run the application in the deployment environment, make the following slight modifications.

src/index.ts
- Enable passing the port value via the PORT environment variable.

serve(
  {
    fetch: app.fetch,
    port: Number(process.env.PORT) || 3000, // ← Modified
  },
  // ....

litestream.yml
- Configure it for Cloud Storage by referring to the following.

https://litestream.io/guides/gcs/

dbs:
  - path: /usr/src/app/db.sqlite
    replicas:
      - type: gcs
        bucket: litestream-example-bucket
        path: db.sqlite

Deployment Work

Pushing to the Artifact Registry Repository

In the Docker in Docker environment, execute the following to build and push the image to the created Artifact Registry repository.

$ docker build --tag asia-northeast1-docker.pkg.dev/<PROJECT_ID>/litestream-example-repository/app:latest .
$ docker push asia-northeast1-docker.pkg.dev/<PROJECT_ID>/litestream-example-repository/app:latest

Next, create a service account that allows Cloud Run to access Cloud Storage.

$ gcloud iam service-accounts create "litestream-example"
$ gcloud projects add-iam-policy-binding "<PROJECT_ID>" --member="serviceAccount:litestream-example@<PROJECT_ID>.iam.gserviceaccount.com" --role="roles/storage.admin"

Deploying to Cloud Run

Finally, deploy to Cloud Run.

$ gcloud run deploy "litestream-example" \
    --region="asia-northeast1" \
    --image="asia-northeast1-docker.pkg.dev/<PROJECT_ID>/litestream-example-repository/app:latest" \
    --port="8080" \
    --service-account="litestream-example@<PROJECT_ID>.iam.gserviceaccount.com" \
    --max-instances=1 \
    --allow-unauthenticated

Operation Verification

After deployment, try accessing it and check if you can retrieve the users list.

$ curl https://xxxxxxxxxxxxxx.run.app/users
[
  {
    "id": 1,
    "first_name": "Taro",
    "last_name": "Yamada",
    "created_at": "2025-05-13 00:47:34",
    "updated_at": "2025-05-13 00:47:34"
  },
  {
    "id": 2,
    "first_name": "Hanako",
    "last_name": "Suzuki",
    "created_at": "2025-05-13 00:47:34",
    "updated_at": "2025-05-13 00:47:34"
  }
]

A db.sqlite file has also been created in the Cloud Storage bucket.

Next, try adding a user.

$ curl -H "Content-Type: application/json" -X POST -d \
  "{\"first_name\": \"Ichiro\", \"last_name\": \"Tanaka\"}" \
  https://xxxxxxxxxxxxxxxxxxxxx.run.app/users
# => {"id":3,"first_name":"Ichiro","last_name":"Tanaka","created_at":"2025-05-13 20:07:06","updated_at":"2025-05-13 20:07:06"}

Retrieving the user list again:

$ curl https://xxxxxxxxxxxxxx.run.app/users
[
  {
    "id": 1,
    "first_name": "Taro",
    "last_name": "Yamada",
    "created_at": "2025-05-13 00:47:34",
    "updated_at": "2025-05-13 00:47:34"
  },
  {
    "id": 2,
    "first_name": "Hanako",
    "last_name": "Suzuki",
    "created_at": "2025-05-13 00:47:34",
    "updated_at": "2025-05-13 00:47:34"
  },
  {
    "id": 3,
    "first_name": "Ichiro",
    "last_name": "Tanaka",
    "created_at": "2025-05-13 20:07:06",
    "updated_at": "2025-05-13 20:07:06"}
]

It's reflected properly!

Next, to confirm that replication is working correctly, modify the Docker image slightly and re-deploy to Cloud Run.

👇 In the deployment, I am deleting the local db.sqlite file and restoring it from Cloud Storage.

ENTRYPOINT ["/bin/sh","-c", "\
  rm -f ./db.sqlite;\
  litestream restore -if-replica-exists -config ./litestream.yml ./db.sqlite;\
  litestream replicate -config ./litestream.yml -exec 'node dist/index.js' \
"]

Re-deploy it. It's successful if the new revision is deployed as shown in the "Revisions" tab below.

Execute curl https://xxxxxxxxxxxxxx.run.app/users again. It's successful if you can retrieve the registered data!

Pitfalls

"litestream shut down" logs appear during Cloud Run execution and nothing is displayed in Cloud Storage

In my case, it was simply because the path in litestream.yml was incorrect...

The following error occurs during Cloud Run execution (1)

SqliteError: attempt to write a readonly database

In my case, this error occurred because there were no write permissions for the db.sqlite file when I tried with a non-root user or when running litestream replicate using the exec command.

Although it's probably better to use a non-root user, I'm using the root user here as this is just a trial.

The following error occurs during Cloud Run execution (2)

tls: failed to verify certificate: x509: certificate signed by unknown authority

It seems to occur because ca-certificates is missing in the Docker image, preventing Litestream from verifying the GCS certificate. To fix this, you can either install ca-certificates or, if using a multi-stage build like the Dockerfile above, copy it from the build stage as shown below.

COPY --from=build /etc/ssl/certs/ca-certificates.crt /etc/ssl/certs/