How to host your own libSQL instance on Docker guide

[aliozinan]

As discussed on the Discord server #support channel here :

https://discord.com/channels/1309279407743172608/1345758358141141113

The steps below explain how to use a self-hosted libSQL instance running on a local working environment using Docker for StudioCMS projects. This setup can be used for Astro.js projects that utilize AstroDB and/or libSQL as well.

Note that this guide targets the development environment, and using this setup in production is not recommended since doing so has its own risks.

In addition, this issue is created to support the StudioCMS documentation.

Note : You'll need Docker and openssl installed on your system. pnpm is used in this guide.

• In your project root folder, create a new folder (here we're naming it as keys) :

mkdir keys

• Run the following command to create a new PKCS#8-encoded Ed25519 key-pair :

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

• Install jwtgen :

pnpm add -g jwtgen

jwtgen is a node CLI utility used to generate JWT tokens. run pnpm jwtgen -h for more options.

• Run the following command to get the contents of your private key file in single line format :

sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/\\n/g' ./keys/libsql.pem

• Copy the output and use it in the jwtgen command below

pnpm jwtgen -a HS256 -s "paste your private key output here" -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!

• Run the following command to encode the JWT auth token in base64URL format :

echo -n "paste the JWT auth token output here" | base64 | tr '/+' '_-' | tr -d '='

• Now update your Studio CMS .env file :

ASTRO_DB_REMOTE_URL=http://localhost:8080
ASTRO_DB_APP_TOKEN=paste your base64URL encoded JWT auth token here

• Create a new docker-compose.yml file for the libSQL instance. At this point you have two options to configure libSQL authentication :

  • Using the public key file itself
  • Using the encoded JWT auth token as an environment variable (the same ASTRO_DB_APP_TOKEN value you used in your .env 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

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 base64URL encoded JWT auth token here
    volumes:
      - ./data/libsql:/var/lib/sqld

If you'd like to use a different libSQL instance per project, you'll need to use different ports for each libSQL instance. You can find more details on how to configure it here :

https://github.com/tursodatabase/libsql/blob/main/docs/DOCKER.md

• Start the libSQL instance :

docker compose up -d

• Prepare your project's database :

pnpm astro db push --remote

• Run your StudioCMS application :

pnpm dev

[Adammatthiesen]

So, after further research, while the basis of this is great! I am going to be implementing some new commands into StudioCMS's CLI to be able to handle some of the work being done by commands here (jwtgen since it's quite old and has not been updated, awk since that is a linux only command, and the base64 encoder since that will be a simple util) in your tutorial so that it can be implemented on multiple different platforms and is not limited to Linux.

Integrating our own version these command will be trivial and only make things easier for a tutorial (that is cross platform) and our users.

Also from a security standpoint... it would be recommended that we have the user store their key pair OUTSIDE of the Astro project and Git... this would help ensure that their credentials are safe from being compromised by the users of the internet.

Once i have the update for the CLI into StudioCMS I will get started on a full tutorial for our docs!

[Adammatthiesen]

okay, @aliozinan with the PR withstudiocms/studiocms#563 it would merge the jwtgen steps into one simple command where you input the pem file from OpenSSL and set the exp and claims and it will produce a base64Url safe token 😄 simplifying this to only require OpenSSL (we already require this for generating the CMS_ENCRYPTION_KEY so no issue here) and StudioCMS.

[aliozinan]

so is it like now the users will be able to choose to use Turso DB or their own libSQL instance via CLI while creating a new project?

[Adammatthiesen]

so is it like now the usera will be able to choose to use Turso DB or their own libSQL instance via CLI while creating a new project?

The CLI already offers this when the user is asked to specify their credentials or configure turso, our CLI would not be used for configuring the actual DB outside of the turso helper we have due to our sponsor and their nice CLI which we interface with.

[Adammatthiesen]

The change just made in the PR 563 on StudioCMS, creates a helper command wrapping these 4 steps into one nice command allowing the .pem file, -c <claim> and -e <exp> as inputs and providing a base64Url encoded token without the need to install extra deps/packages. (and cross platform)

(To be released in beta.16)

[jdtjenkins]

We should have some kind of disclaimer on this that this is probably not production-ready and extra steps should always be taken to secure your Docker environment which will be out of scope for this guide. This guide should be just to get people up and running locally. I definitely get the idea for the guide, I'm just a bit worried if people go into production with this there may be other issues just because of how complex docker and hosting are.

[Adammatthiesen]

We should have some kind of disclaimer on this that this is probably not production-ready and extra steps should always be taken to secure your Docker environment which will be out of scope for this guide. This guide should be just to get people up and running locally. I definitely get the idea for the guide, I'm just a bit worried if people go into production with this there may be other issues just because of how complex docker and hosting are.

@jdtjenkins the helper command i added is the security stuff for the docker container to be safe to expose to public 😅 I just personally would not want to be responsible for telling people how/where to actually put the container. that is on them... for security and liability reasons

[aliozinan]

@jdtjenkins I also updated the issue content to highlight the "local working environment" phrase, since my original idea was all about having a libSQL instance running on my local while working with Astro.js and StudioCMS projects.

[Adammatthiesen]

@jdtjenkins I also updated the issue content to highlight the "local working environment" phrase, since my original idea was all about having a libSQL instance running on my local while working with Astro.js and StudioCMS projects.

@aliozinan I will note when i create the guide, i will note be mentioning "local only" or "hosted" for the reason that if we mention one or the other, it could reinforce that only one is possible... this is part of the reason we also don't provide any deployment docs, because those docs are already provided by https://docs.astro.build/en/guides/deploy/ for those users who wish to deploy a StudioCMS project (since we are just an Astro integration).

Now that being said i would be likely to link to docker's guides and resources for "How-To-Docker" for those who want more information about doing so. I will also be providing the full range of options, of (unsecured, dynamic security with the .pem file, and static security with JWT) with the template compose files for each and a small explanation of each as well.