Self-hosted libSQL server

The sqld (“SQL daemon”) project is a server mode for libSQL created by Turso. This guide will go over setting up and configuring sqld to work from a docker container for your StudioCMS project.

Learn more about sqld using their Documentation^

Prerequisites

• Docker and Docker Compose^ (either running on a server or locally)
• OpenSSL^ installed
• StudioCMS CLI (available from the root of your StudioCMS project)

The final file structure will look something like this if testing locally:

• docker-compose.yml
• Répertoiredata/

  • …
• Répertoirekeys/

  • …
• Répertoiremy-studiocms-project/

  • astro.config.mjs
  • studiocms.config.mjs
  • package.json
  • Répertoiresrc/

    • …

Generate Private and Public keys

From inside the keys directory run the following command to create a new PKCS#8-encoded Ed25519 key-pair:

openssl genpkey -algorithm Ed25519 -out ./libsql.pem

Then extract the public key with:

openssl pkey -in ./libsql.pem -pubout -out ./libsql.pub

This will generate a new PKCS#8 key-pair that will be used for the sqld server.

Generate your JWT token

Navigate to your studiocms project directory and run the following command:

npm

npm run studiocms crypto gen-jwt "../keys/libsql.pem" -c "iss=admin" -e 31556926

pnpm

pnpm run studiocms crypto gen-jwt "../keys/libsql.pem" -c "iss=admin" -e 31556926

yarn

yarn run studiocms crypto gen-jwt "../keys/libsql.pem" -c "iss=admin" -e 31556926

The output is the JWT auth token encrypted with your private key, which will be used for libSQL authentication. Keep in mind that the token will be valid for 1 year!

Update your StudioCMS .env with the following:

ASTRO_DB_REMOTE_URL=http://localhost:8080 # This should point to your docker server/local system
ASTRO_DB_APP_TOKEN= # paste your base64URL encoded JWT auth token here

Setting up the Docker container

Create a new docker-compose.yml file outside of the StudioCMS project directory, for the libSQL instance. At this point you have two options to configure libSQL authentication:

• Using the public key file itself
• Using the JWT token as an environment variable (the same ASTRO_DB_APP_TOKEN value you used in your .env file)

Public Key File

services:
  libsql:
    image: ghcr.io/tursodatabase/libsql-server:latest
    platform: linux/amd64
    ports:
      - "8080:8080"
      - "5001:5001"
    environment:
      - SQLD_NODE=primary
      - SQLD_AUTH_JWT_KEY_FILE=/home/.ssh/libsql.pub
    volumes:
      - ./data/libsql:/var/lib/sqld
      - ./keys/libsql.pub:/home/.ssh/libsql.pub

JWT Token

services:
  libsql:
    image: ghcr.io/tursodatabase/libsql-server:latest
    platform: linux/amd64
    ports:
      - "8080:8080"
      - "5001:5001"
    environment:
      - SQLD_NODE=primary
      - SQLD_AUTH_JWT_KEY=paste your JWT token here
    volumes:
      - ./data/libsql:/var/lib/sqld

Deploying to Coolify?

Coolify^ users: Use our GitHub Gist^ for a Coolify-compatible Docker Compose configuration that exposes libSQL directly through your domain without the :8080 port suffix.

Starting Everything up

1. Start the Docker container:

   From the base directory (or the server if you went that route) run the following command to start the libSQL Server container

   docker compose up -d

2. Sync your StudioCMS database schema:

   Move into your StudioCMS project directory and run the following command:

   npm

   npx astro db push --remote

   pnpm

   pnpm astro db push --remote

   yarn

   yarn astro db push --remote

3. Follow the first time setup for your new database.

   • See Getting Started

   Note

   If this is not your first time setting up a StudioCMS project you may need to enable the dbStartPage option in your studiocms.config.mjs

Final Thoughts

If you are looking for an option for Self-hosting your own libSQL database with StudioCMS sqld is a viable option and is not very complicated to get up and running if you do it the right way, following this guide provides you with a secure libSQL server to use alongside your StudioCMS project.