Session Stores#

SQLSpec provides session store backends for Litestar's session middleware. Store user sessions in your database instead of cookies or external caches.

Why SQL-backed Sessions?#

  • No external dependencies - use your existing database instead of Redis or Memcached.

  • Persistence - sessions survive server restarts.

  • Querying - inspect or clean up sessions with standard SQL.

Basic Setup#

Pass a SQLSpec store to Litestar's SessionMiddleware:

session store#
from sqlspec.adapters.aiosqlite import AiosqliteConfig
from sqlspec.adapters.aiosqlite.litestar import AiosqliteStore

config = AiosqliteConfig(connection_config={"database": ":memory:"})
store = AiosqliteStore(config)

Available Stores#

SQLSpec provides store backends for both async and sync database adapters, all inheriting from BaseSQLSpecStore:

  • SQLite: sqlspec.adapters.aiosqlite.litestar.AiosqliteStore (async) and sqlspec.adapters.sqlite.litestar.SQLiteStore (sync)

  • PostgreSQL: sqlspec.adapters.asyncpg.litestar.AsyncpgStore (asyncpg), sqlspec.adapters.psycopg.litestar.PsycopgAsyncStore (psycopg async), and sqlspec.adapters.psycopg.litestar.PsycopgSyncStore (psycopg sync)

  • MySQL: sqlspec.adapters.pymysql.litestar.PyMysqlStore

  • DuckDB: sqlspec.adapters.duckdb.litestar.DuckdbStore

  • SQL Server / ODBC: sqlspec.adapters.arrow_odbc.litestar.ArrowOdbcStore, sqlspec.adapters.pymssql.litestar.PymssqlStore, and sqlspec.adapters.mssql_python.litestar.MssqlPythonStore

  • CockroachDB: sqlspec.adapters.cockroach_asyncpg.litestar.CockroachAsyncpgStore

  • BigQuery: sqlspec.adapters.bigquery.litestar.BigQueryStore

  • Spanner: sqlspec.adapters.spanner.litestar.SpannerAsyncStore and sqlspec.adapters.spanner.litestar.SpannerSyncStore

  • Oracle: sqlspec.adapters.oracledb.litestar.OracleAsyncStore and sqlspec.adapters.oracledb.litestar.OracleSyncStore

  • IBM Db2: sqlspec.adapters.db2.litestar.Db2AsyncStore and sqlspec.adapters.db2.litestar.Db2SyncStore

  • Arrow / ADBC: sqlspec.adapters.adbc.litestar.ADBCStore (supports PostgreSQL, SQLite, DuckDB, MySQL/MariaDB, BigQuery, Snowflake, Oracle, and IBM Db2)

Each store can create its session table. It can also add new columns from its own DDL. Set this behavior under extension_config["litestar"]:

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://localhost/app"},
    extension_config={
        "litestar": {
            "manage_schema": True,
            "create_schema": True,
        }
    },
)

Set manage_schema=False when another tool owns the table. Set create_schema=False to add columns without creating a missing table. Use a versioned migration for renames, drops, or type changes.

Database-specific storage options#

Storage tuning belongs in the same extension_config["litestar"] mapping as the schema settings. SQLSpec validates that mapping for the selected adapter. Unknown keys and options from another database family raise ImproperConfigurationError instead of being silently ignored.

For typed settings, use a type from the adapter's litestar package, such as AsyncpgLitestarConfig or SqliteLitestarConfig. Each type adds the table options that the adapter can use to the shared LitestarConfig fields. You can still pass a plain dict. Sync and async stores share the same type. For Oracle, use OracleLitestarCompressionConfig and OracleLitestarPartitionConfig to type each map. For BigQuery, set partitioning to True or False.

The available options are:

  • PostgreSQL (asyncpg, psycopg, and psqlpy): fillfactor (default 80), autovacuum_vacuum_scale_factor, and autovacuum_analyze_scale_factor.

  • CockroachDB: enable_hash_sharded_indexes, hash_shard_bucket_count, and ttl_expiration_expression="expires_at".

  • BigQuery: partitioning, partition_expiration_days, and require_partition_filter. The partition column is expires_at.

  • SQLite and AioSQLite: pragma_profile and pragma_overrides. PRAGMAs are applied during schema preparation, not for each session-store operation.

  • MySQL and MariaDB: table_options and index_options for backend-specific DDL clauses.

  • Spanner: shard_count, table_options, and index_options.

  • Oracle Database: compression, partitioning, in_memory, and table_options. See Extension Table Storage Options for the capability and licensing behavior.

For example, enable the SQLite profile and override only the values needed by the application:

from sqlspec.adapters.sqlite import SqliteConfig
from sqlspec.adapters.sqlite.litestar import SqliteLitestarConfig

session_settings: SqliteLitestarConfig = {
    "pragma_profile": True,
    "pragma_overrides": {"busy_timeout": 10_000},
}

config = SqliteConfig(
    connection_config={"database": "sessions.db"},
    extension_config={"litestar": session_settings},
)

Session Expiry and Cleanup#

Configure session lifetime through Litestar's SessionMiddleware settings using the max_age parameter. Database session stores do not purge expired rows automatically during request handling. To prevent unbounded table growth:

  1. CLI Cleanup: Run the built-in Litestar CLI command:

    litestar sessions delete-expired

  2. Programmatic / Scheduled Cleanup: Call delete_expired() directly on the store instance within a background task or cron worker:

    # Purge expired sessions older than their max_age
await store.delete_expired()