SQLAlchemy + Turso

Configure SQLAlchemy to work with your Turso database

Prerequisites

Before you start, make sure you:

• Install the Turso CLI
• Sign up or login to Turso

1. Install the libSQL dialect for SQLAlchemy

   pip install sqlalchemy-libsql
   
2. Configure 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="..."
   
3. 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})"
   
   
4. Construct SQLAlchemy URL for Turso

   app.py

   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")
   
   dbUrl = f"sqlite+{TURSO_DATABASE_URL}/?authToken={TURSO_AUTH_TOKEN}&secure=true"
   
   engine = create_engine(dbUrl, connect_args={'check_same_thread': False}, echo=True)
   
5. Query database

   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)