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.
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://hostuses HTTPS; add?tls=0and an explicit port for plain HTTP.?authToken=...on the URL takes the place ofauth_token.ws://andwss://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:
.rawgives aResultSetwithcolumns,column_types,rows,rows_affectedandlast_insert_rowid. A local database leavescolumn_typesempty, sincesqlite3doesn't expose declared types.- A failure's
reasoncomes from SQLite's primary code, as in Effect. A unique violation is aConstraintError, not aUniqueViolation. - Streams aren't supported, and
BeginTransactionis ignored. - A local database that can't be opened is a defect, not a
SqlError. libsql.layeropens no connection, as in Effect. A layer for a server that is down builds fine, and the first statement fails with aSqlError(itsreasonis anUnknownError). The MySQL and ClickHouse layers check the server when they are built, so run aSELECT 1at 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_BUSYduring a checkpoint is left in sqld's write queue, and every later writer then waits forever (libsql#2286). Effect's@effect/sql-libsqlwedges it the same way. Writing from one fiber at a time, for example behind a one-permitSemaphore, 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.