Knexpy

A query builder for SQLite3 based on Knexjs. Knexpy compiles chainable, parameterized SQL for you, so you never interpolate a value into a query string.

Features

• Fluent query building — select, from_, aliases, distinct, every where_* variant, group_by/having, order_by (with NULLS FIRST/LAST), limit/offset, joins and subqueries.
• Aggregates — count, sum, avg, min, max.
• Writes — insert, insert_or_ignore, upsert, update, delete, and batch insert_json (accepts dicts, dataclasses and pydantic models, with chunked transactions and a mapping hook).
• Type checking — Knex(..., type_check=True) validates values against column types on insert/update.
• Transactions — context-manager support, atomic batch inserts, and manual begin/commit/rollback.
• Typed row models — db.select(...).as_(User).all() returns list[User] from dataclasses or TypedDicts.
• Pagination & inspection — paginate() and explain() / explain_query_plan().
• Schema & introspection — create/alter/drop tables and indexes, tables, columns, foreign_keys, and backup.
• Migrations — a runner (knexpy migrate CLI + Knex.run_migrations).
• Hooks — observe or intercept operations (before_*/after_* events).
• Async facade — AsyncKnex reuses the same builder over aiosqlite.
• Connection factories — from_connection, make_db, and query logging.

Installation

pip install knexpy

For the async facade, install the optional extra:

pip install "knexpy[async]"

Quick start

from Knexpy import Field, Knex

db = Knex(":memory:", type_check=True)  # pass a path to persist to a file

db.table("users", [Field.varchar("name"), Field.integer("age", {"null": True})])
db.insert_json(
    "users",
    [
        {"name": "Ada", "age": 36},
        {"name": "Lin", "age": 45},
        {"name": "Grace", "age": 40},
    ],
)

rows = db.select("id", "name").from_("users").order_by("name").query()
# [{"id": "...", "name": "Ada"}, ...]

When creating a table, id, created_at and modified_at are added automatically. id is a collision-resistant random hex string.

Examples

Runnable examples live in examples/ and cover the full feature set:

You can report bugs and discuss features on the GitHub issues page. For the full API reference check Readthedocs.