Janne Virtala

libSQL Self-hosting

You might have heard of Turso, a hosted SQLite solution. Their fork of SQLite, libSQL, is open source, so anyone can play with it for free.

Trouble is, their guide on self-hosting is somewhat lacking, so I figured I’d write down how I got everything up and running for some proper testing.

NOTE: This post is written for a Debian-based Linux system, but it’s probably not that far off for a Mac user.

Get the turso and sqld binaries

If you’re interested in self-hosting libSQL, then you probably already have the Turso CLI installed, in which case you can skip over to the next section.

To install the Turso CLI, follow the first step of their guide:

curl -sSfL https://get.tur.so/install.sh | bash

That’ll create a ~/.turso directory containing the turso and sqld binaries. It also modifies ~/.bashrc to add that directory to the PATH, so remember to

source ~/.bashrc

to get the PATH updated.

Create the sqld user and directory

Create sqld user

sudo useradd --system --home /opt/sqld --shell /bin/false sqld

Add yourself to the group

sudo  usermod --append --groups sqld $(whoami)

Create a directory for the data and scripts. I like using /opt

sudo mkdir /opt/sqld
sudo chown sqld:sqld /opt/sqld
sudo chmod 775 /opt/sqld

Go into that directory

cd /opt/sqld

Generate certificates

Get the script to generate certificates

wget https://raw.githubusercontent.com/tursodatabase/libsql/refs/heads/main/libsql-server/scripts/gen_certs.py

That script will, by default, generate certs that last 3 days, which is quite inconvenient, so you can edit that to something a bit longer, like 100 years:

-not_after = not_before + datetime.timedelta(days=3)
+not_after = not_before + datetime.timedelta(days=365*100)

Run the script

python3 gen_certs.py

You might have an error like this

Traceback (most recent call last):
  File "/opt/sqld/gen_certs.py", line 10, in <module>
    from cryptography import x509
ModuleNotFoundError: No module named 'cryptography'

In which case, install the missing package

sudo apt install python3-cryptography

This time, it should work

stored cert 'ca' into 'ca_cert.pem'
stored private key 'ca' into 'ca_key.pem'
stored cert 'server' into 'server_cert.pem'
stored private key 'server' into 'server_key.pem'
stored cert 'client' into 'client_cert.pem'
stored private key 'client' into 'client_key.pem'
these are development certs, they will expire at 2025-02-06 08:36:02.521579+00:00

Generate keypair for authentication tokens

To generate authentication tokens (JWTs), you need to generate another keypair, and as I never remember how to use OpenSSL, I just use the smallstep CLI, which you can install with

wget https://dl.smallstep.com/cli/docs-cli-install/latest/step-cli_amd64.deb
sudo dpkg -i step-cli_amd64.deb

Create the keypair

step crypto keypair jwt.pub jwt.key \
  --kty OKP \
  --curve Ed25519 \
  --no-password \
  --insecure

Run the primary server

To run the primary, create run-primary.sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

mkdir -p primary
cd primary

exec sqld \
  --enable-namespaces \
  --admin-listen-addr 127.0.0.1:5000 \
  --auth-jwt-key-file ../jwt.pub \
  --http-listen-addr 127.0.0.1:3000 \
  --grpc-listen-addr 127.0.0.1:4000 \
  --grpc-tls \
  --grpc-ca-cert-file ../ca_cert.pem \
  --grpc-cert-file ../server_cert.pem \
  --grpc-key-file ../server_key.pem

Make that executable

chmod +x run-primary.sh

And see if it runs!

           _     _
 ___  __ _| | __| |
/ __|/ _` | |/ _` |
\__ \ (_| | | (_| |
|___/\__, |_|\__,_|
        |_|

Welcome to sqld!

version: 0.24.31
commit SHA: e88c6b513da5e87e2051c8dd58797ef367392440
build date: 2025-01-06

This software is in BETA version.
If you encounter any bug, please open an issue at https://github.com/tursodatabase/libsql/issues

config:
        - mode: primary (127.0.0.1:4000)
        - database path: data.sqld
        - extensions path: <disabled>
        - listening for HTTP requests on: 127.0.0.1:3000
        - grpc_tls: yes
2025-02-03T08:43:12.667913Z  INFO sqld: listening for incoming user HTTP connection on 127.0.0.1:3000
2025-02-03T08:43:12.668370Z  INFO sqld: Using JWT-based authentication
2025-02-03T08:43:12.668523Z  INFO sqld: listening for incoming adming HTTP connection on 127.0.0.1:5000
2025-02-03T08:43:12.669558Z  INFO sqld: listening for incoming gRPC connection on 127.0.0.1:4000
2025-02-03T08:43:12.674563Z  INFO restore: libsql_server::namespace::meta_store: restoring meta store
2025-02-03T08:43:12.674746Z  INFO restore: libsql_server::namespace::meta_store: meta store restore completed
2025-02-03T08:43:12.675639Z  INFO libsql_server: Server sending heartbeat to URL <not supplied> every 30s
2025-02-03T08:43:12.676206Z  INFO libsql_server::rpc: serving internal rpc server with tls
2025-02-03T08:43:12.677746Z  INFO libsql_server::http::admin: initializing prometheus metrics

Run the replica

A similar script can be created for a replica

run-replica-sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

mkdir -p replica
cd replica

exec sqld \
  --enable-namespaces \
  --auth-jwt-key-file ../jwt.pub \
  --http-listen-addr 127.0.0.1:3001 \
  --primary-grpc-url https://127.0.0.1:4000 \
  --primary-grpc-tls \
  --primary-grpc-ca-cert-file ../ca_cert.pem \
  --primary-grpc-cert-file ../client_cert.pem \
  --primary-grpc-key-file ../client_key.pem

You can run this along with the primary:

           _     _
 ___  __ _| | __| |
/ __|/ _` | |/ _` |
\__ \ (_| | | (_| |
|___/\__, |_|\__,_|
        |_|

Welcome to sqld!

version: 0.24.31
commit SHA: e88c6b513da5e87e2051c8dd58797ef367392440
build date: 2025-01-06

This software is in BETA version.
If you encounter any bug, please open an issue at https://github.com/tursodatabase/libsql/issues

config:
        - mode: replica (primary at https://127.0.0.1:4000)
        - database path: data.sqld
        - extensions path: <disabled>
        - listening for HTTP requests on: 127.0.0.1:3001
        - grpc_tls: no
2025-02-03T08:47:13.254061Z  INFO sqld: listening for incoming user HTTP connection on 127.0.0.1:3001
2025-02-03T08:47:13.254436Z  INFO sqld: Using JWT-based authentication
2025-02-03T08:47:13.262912Z  INFO restore: libsql_server::namespace::meta_store: restoring meta store
2025-02-03T08:47:13.263089Z  INFO restore: libsql_server::namespace::meta_store: meta store restore completed
2025-02-03T08:47:13.263977Z  INFO libsql_server: Server sending heartbeat to URL <not supplied> every 30s

Create a JWT token

To actually connect to the server(s), you’ll need to generate an authentication token. You can use jwt-cli for that:

wget https://github.com/mike-engel/jwt-cli/releases/download/6.2.0/jwt-linux-musl.tar.gz
tar xf jwt-linux-musl.tar.gz
rm jwt-linux-musl.tar.gz
sudo mv jwt /usr/bin

Create another script, gen-auth-token.sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

step crypto key format --pkcs8 jwt.key > jwt.key.der

args=(
  --alg EDDSA
  --out jwt.token
  --secret @jwt.key.der
  '{"a": "rw"}'
)

jwt encode "${args[@]}"
jwt decode - < jwt.token

Make it executable, and run it. It will create a jwt.token file, which you can use for authentication.

But before using that token, you should to create a database to connect to.

Database management

You’ll want to create a database using the Admin API, so create a script for that

db-create.sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

db_name=$1

if [[ -z "${db_name}" ]]; then
  echo "usage: $0 <db-name>"
  exit 1
fi

curl "http://127.0.0.1:5000/v1/namespaces/${db_name}/create" \
  -X POST \
  -H "content-type: application/json" \
  -d '{ "dump_url": null }'

You might also want to delete or fork a database, so here are scripts for those actions as well:

db-delete.sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

db_name=$1

if [[ -z "${db_name}" ]]; then
  echo "usage: $0 <db-name>"
  exit 1
fi

curl "http://127.0.0.1:5000/v1/namespaces/${db_name}" \
  -X DELETE \
  -H "content-type: application/json"

db-fork.sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

db_from=$1
db_to=$2

if [[ -z "${db_from}" ]] || [[ -z "${db_to}" ]]; then
  echo "usage: $0 <from> <to>"
  exit 1
fi

curl "http://127.0.0.1:5000/v1/namespaces/${db_from}/fork/${db_to}" \
  -X POST \
  -H "content-type: application/json" \
  -d '{ "dump_url": null }'

Create a database

Make sure the primary is running, and create the first database

./db-create.sh test

Use the interactive shell

For an interactive shell, create another script (which uses the JWT we created earlier)

shell-primary.sh

#!/bin/bash
set -eo pipefail

script_dir=$(dirname "$(realpath "$0")")
cd "${script_dir}"

db_name=$1

if [[ -z "${db_name}" ]]; then
  echo "usage: $0 <db-name>"
  exit 1
fi

exec turso db shell "http://${db_name}.localhost:3000?authToken=$(cat jwt.token)"

Try it out

./shell-primary.sh test

Connected to http://test.localhost:3000?authToken=eyJ0eXAiOiJKV1QiLCJhbGciOiJFZERTQSJ9.eyJhIjoicnciLCJpYXQiOjE3Mzg1NzI3MzJ9.lc1D2JR-3TPoGrCITABUVvqrRZJkhz_cDxVDIKxRg_6NagMqGWb9maas4Gea3zc0rILbnAS4BLY97hcHd0KaBw

Welcome to Turso SQL shell!

Type ".quit" to exit the shell and ".help" to list all available commands.

→  create table users (id integer primary key autoincrement, name text) strict;
→  insert into users (name) values ('foo'), ('bar');
→  select * from users;
ID     NAME
1      foo
2      bar

Notice that if you try connecting to a non-existent database, you’ll get an error

./shell-primary.sh test2

Error: failed to connect to database. err: failed to execute SQL:
error code 404: Namespace `test2` doesn't exist

Using this setup with the official SDK is also very simple

// typescript
import { createClient } from "@libsql/client";

const client = createClient({
  url: "libsql://test.localhost:3000",
  authToken: "< contents of jwt.token >",
});

Important note on DNS

Finally, please note that because selecting the database relies on the domain (eg. <db>.your.host.com), you need DNS to support it. Which is why I used <db>.localhost in this guide, as localhost has built-in wildcard DNS support.