Skip to content

SQLiter

PyPI version   Test Suite  Linting  Type Checking  PyPI - Python Version

SQLiter is a lightweight Object-Relational Mapping (ORM) library for SQLite databases in Python. It provides a simplified interface for interacting with SQLite databases using Pydantic models. The only external run-time dependency for core synchronous usage is Pydantic itself. Async support is available via the optional async extra, which installs the additional async SQLite driver.

It does not aim to be a full-fledged ORM like SQLAlchemy, but rather a simple and easy-to-use library for basic database operations, especially for small projects. It supports synchronous APIs and optional asynchronous APIs.

The ideal use case is more for Python CLI tools that need to store data in a database-like format without needing to learn SQL or use a full ORM.

Caution

This project is still in development and is lacking some planned functionality. Please use with caution - Classes and methods may change until a stable release is made. I'll try to keep this to an absolute minimum and the releases and documentation will be very clear about any breaking changes.

The async feature is new. It is covered by tests and expected to work, but treat it with some caution until it has seen a few releases of real-world use.

See the TODO for planned features and improvements.

Features

  • Table creation based on Pydantic models
  • Supports date and datetime fields
  • Support for complex data types (list, dict, set, tuple) stored as BLOBs
  • Automatic primary key generation
  • User defined indexes on any field
  • Set any field as UNIQUE
  • CRUD operations (Create, Read, Update, Delete)
  • Bulk Create and Update methods to reduce the number of queries required for multiple operations.
  • Foreign key relationships with referential integrity and CASCADE actions
  • ORM mode with lazy loading, reverse relationships, many-to-many support, and eager loading
  • Optional async database, query, and ORM support via the async extra
  • Chained Query building with filtering, ordering, and pagination
  • Projection and aggregation queries with grouping, aggregate helpers, and dictionary-based results
  • Transaction support
  • Optional query result caching with LRU eviction, TTL, and memory limits
  • Optional raw SQL debug logging
  • Custom exceptions for better error handling
  • Full type hinting and type checking
  • Detailed documentation and examples
  • Interactive TUI demo for exploring features
  • No required external dependencies other than Pydantic for core sync usage
  • Full test coverage

Caution

Supported Python Versions

Release 0.20.0 was the last release to support Python 3.9. Release 0.21.0 and onwards require Python 3.10

API Reference

For detailed documentation of every class, method, property, and constant, see the API Reference.

Interactive Demo

SQLiter includes an interactive terminal-based demo (TUI) that lets you explore all features hands-on without writing any code. Run the demo to see live code examples and their output:

# Install with the demo extra
uv add sqliter-py[demo]
# or
pip install sqliter-py[demo]

# Run the interactive demo
python -m sqliter.tui

The demo includes:

  • 14 demo categories covering all SQLiter features
  • Live code display with syntax highlighting
  • One-click execution to see results in real-time
  • Organized tutorials from basic to advanced topics

For more information, see the Interactive Demo documentation.

License

This project is licensed under the terms of the MIT license.