GitHub

libSQL and Turso

A libSQL client for a local file, an in-memory database or a remote sqld or Turso server over HTTP.

pyfect.sql.libsql opens a database from a URL. A file: path or :memory: runs on the standard library's sqlite3, so it needs nothing extra. A libsql://, https:// or http:// URL talks to a libSQL server (sqld or Turso) over Hrana over HTTP, which needs the pyfect-dev[httpx] extra. libsql.layer provides both LibsqlClient and SqlClient, so the code from the SQL guide runs unchanged.

connect.py
import os

import pyfect as fx
from pyfect.sql import libsql
from pyfect.sql.error import ConstraintError

# A file or :memory: runs on sqlite3; libsql://, https:// or http:// talk to
# a libSQL server (sqld, Turso). LIBSQL_URL=libsql://db.turso.io python connect.py
config = libsql.LibsqlClientConfig(url=os.environ.get("LIBSQL_URL", ":memory:"))


@fx.fn
def program():
    sql = yield from libsql.LibsqlClient()
    yield from sql("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)")

    # .raw gives the ResultSet, with the rows changed and the new rowid.
    print((yield from sql("INSERT INTO users (email) VALUES ({})", "ada@example.com").raw))

    # Failures are read from SQLite's primary code, as in Effect.
    error = yield from sql("INSERT INTO users (email) VALUES ({})", "ada@example.com").flip()
    assert isinstance(error.reason, ConstraintError)

    # A transaction rolls back when its effect fails.
    insert = sql("INSERT INTO users (email) VALUES ({})", "grace@example.com")
    yield from sql.with_transaction(insert.and_then(fx.fail("changed my mind"))).ignore()
    print((yield from sql("SELECT email FROM users ORDER BY id")))


fx.run_main(program().pipe(fx.provide(libsql.layer(config))))

Connecting

LibsqlClientConfig takes the URL and, for a server, its auth_token (a Redacted, so it never shows up in logs). The URL is checked when the config is made, so a typo fails at startup rather than on the first statement:

  • libsql://host uses HTTPS; add ?tls=0 and an explicit port for plain HTTP.
  • ?authToken=... on the URL takes the place of auth_token.
  • ws:// and wss:// URLs are served over HTTP and HTTPS. sqld and Turso accept both on the same address.

To read the settings from the environment, use libsql.layer_config({"url": fx.config.string("LIBSQL_URL"), "auth_token": fx.config.redacted("LIBSQL_TOKEN")}). A client you made yourself goes in LibsqlLiveConfig(live_client=...). It is any object with async execute(sql, args) and transaction(mode) methods and a protocol ("http", "ws" or "file"; libsql.LibsqlSdk), and the caller closes it.

How it differs

A local database runs one thing at a time. A statement holds it until it finishes, and a transaction holds it from BEGIN IMMEDIATE until it commits or rolls back. A remote one (http or ws) runs statements side by side, and each transaction on its own stream. Nested transactions use savepoints. A few things work differently than on the other drivers:

  • .raw gives a ResultSet with columns, column_types, rows, rows_affected and last_insert_rowid. A local database leaves column_types empty, since sqlite3 doesn't expose declared types.
  • A failure's reason comes from SQLite's primary code, as in Effect. A unique violation is a ConstraintError, not a UniqueViolation.
  • Streams aren't supported, and BeginTransaction is ignored.
  • A local database that can't be opened is a defect, not a SqlError.
  • libsql.layer opens no connection, as in Effect. A layer for a server that is down builds fine, and the first statement fails with a SqlError (its reason is an UnknownError). The MySQL and ClickHouse layers check the server when they are built, so run a SELECT 1 at startup if you want the same here.
  • Hrana has no way to cancel a statement. Interrupting a remote statement stops waiting for it, as in Effect, and the server finishes it. A transaction it was part of rolls back after that.
  • sqld (checked with 0.24.33) can wedge when several clients write to one database at once: a writer that gets SQLITE_BUSY during a checkpoint is left in sqld's write queue, and every later writer then waits forever (libsql#2286). Effect's @effect/sql-libsql wedges it the same way. Writing from one fiber at a time, for example behind a one-permit Semaphore, avoids it. Once sqld is wedged, interrupting a statement waits too, since the interrupt waits for the request in flight.

Migrations

libsql.migrator works like the SQLite migrator: migrator.layer runs the migrations when the layer is built and records them in effect_sql_migrations.