SQLAlchemy + Turso

Prerequisites

Before you start, make sure you:

Install the libSQL dialect for SQLAlchemy

pip install sqlalchemy-libsql

Retrieve database credentials

Get the database URL:

turso db show --url <database-name>

Get the database authentication token:

turso db tokens create <database-name>

Assign credentials to the environment variables inside .env.

TURSO_DATABASE_URL=
TURSO_AUTH_TOKEN=

Create database models

models.py

from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):
    pass

class Foo(Base):
    __tablename__ = "foo"
    id: Mapped[str] = mapped_column(primary_key=True)
    bar: Mapped[str] = mapped_column(String(100))
    def __repr__(self) -> str:
        return f"Item(id={self.id!r}, bar={self.bar!r})"

Create engine

Embedded Replicas

from dotenv import load_dotenv
from sqlalchemy import create_engine

TURSO_DATABASE_URL = os.environ.get("TURSO_DATABASE_URL")
TURSO_AUTH_TOKEN = os.environ.get("TURSO_AUTH_TOKEN")

engine = create_engine(
     "sqlite+libsql:///embedded.db",
     connect_args={
         "auth_token": TURSO_AUTH_TOKEN,
         "sync_url": TURSO_DATABASE_URL,
     },
)

Remote only

from dotenv import load_dotenv
from sqlalchemy import create_engine

TURSO_DATABASE_URL = os.environ.get("TURSO_DATABASE_URL")
TURSO_AUTH_TOKEN = os.environ.get("TURSO_AUTH_TOKEN")

engine = create_engine(
    f"sqlite+{TURSO_DATABASE_URL}?secure=true",
    connect_args={
        "auth_token": TURSO_AUTH_TOKEN,
    },
)

Memory Only

from sqlalchemy import create_engine

engine = create_engine("sqlite+libsql://")

Local only

from sqlalchemy import create_engine

engine = create_engine("sqlite+libsql:///local.db")

Query

app.py

from sqlalchemy.orm import Session
from sqlalchemy import select
from models import Foo

@app.route("/", methods=(["GET"]))
def home():
    session = Session(engine)

    # get & print foos
    stmt = select(Foo)

    for item in session.scalars(stmt):
        print(item)

Flask Quickstart

​Prerequisites

Prerequisites