Usage¶

This section provides comprehensive guides on using SQLSpec for database operations. Whether you’re new to SQLSpec or looking to leverage advanced features, these guides will help you get the most out of the library.

Overview¶

SQLSpec provides a unified interface for database operations across multiple backends while maintaining a direct connection to SQL. The key concepts are:

Data Flow: Understand how SQLSpec processes queries from input to result using its sophisticated pipeline architecture.
Configuration: Learn how to configure database connections, connection pools, and statement processing options.
Drivers and Querying: Discover the available database drivers and how to execute queries effectively.
Query Builder: Explore the experimental fluent API for programmatically constructing SQL queries.
Query Stack: Learn how to batch heterogeneous SQL statements with immutable stacks, choose between fail-fast and continue-on-error execution, and monitor native vs. sequential paths in Query Stack.
SQL Files: Manage SQL statements from files using the aiosql-style loader.
Command Line Interface: Use the SQLSpec CLI for migrations, with shell completion support for bash, zsh, and fish.
Database Migrations: Manage database schema changes with support for hybrid versioning, automatic schema migration, and extension migrations.
Framework Integrations: Integrate SQLSpec with Litestar, FastAPI, and other Python web frameworks.

Quick Reference¶

Basic Query Execution

basic query execution¶

from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

db_manager = SQLSpec()
db = db_manager.add_config(SqliteConfig())

with db_manager.provide_session(db) as session:
    session.execute(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL
        )
        """
    )
    session.execute("INSERT INTO users VALUES (?, ?)", 1, "alice")

with db_manager.provide_session(db) as session:
    user = session.select_one("SELECT * FROM users WHERE id = ?", 1)

Using the Query Builder

using the query builder¶

from sqlspec import SQLSpec, sql
from sqlspec.adapters.sqlite import SqliteConfig

db_manager = SQLSpec()
db = db_manager.add_config(SqliteConfig())
query = sql.select("id", "name", "email").from_("users").where("active = ?")

with db_manager.provide_session(db) as session:
    session.execute(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT,
            active BOOLEAN NOT NULL DEFAULT 1
        )
        """
    )
    session.execute(
        """
        INSERT INTO users VALUES
            (1, 'alice', '[email protected]', 1),
            (2, 'bob', '[email protected]', 0),
            (3, 'carol', '[email protected]', 1)
        """
    )
    users = session.select(query, True)  # noqa: FBT003

Loading from SQL Files

loading from sql files¶

from pathlib import Path

from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.loader import SQLFileLoader

queries_path = Path(__file__).parent.parent / "queries" / "users.sql"

db_manager = SQLSpec()
db = db_manager.add_config(SqliteConfig())
loader = SQLFileLoader()
loader.load_sql(queries_path)
get_user_by_id = loader.get_sql("get_user_by_id")

with db_manager.provide_session(db) as session:
    session.execute(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            email TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    session.execute(
        """
        INSERT INTO users(id, username, email)
        VALUES (1, 'alice', '[email protected]'),
               (2, 'bob', '[email protected]')
        """
    )
    user = session.select_one(get_user_by_id, user_id=2)

Next Steps¶

Start with Execution Flow to understand SQLSpec’s execution pipeline, then move on to Configuration to set up your database connections.