FineSQL

A minimalist Python ORM built directly on top of sqlite3 — no SQLAlchemy, no Django dependency, no metaclass magic. The entire library fits in a single afternoon of reading.

Why FineSQL exists

Most Python ORMs hide what they do. FineSQL exposes it.

If you've ever wondered:

• How does an ORM turn a class definition into a CREATE TABLE statement?
• Where does a lazy-loaded foreign-key attribute actually come from?
• How much of "ORM magic" can be replaced with explicit, readable Python?

FineSQL is that — in code you can fork, read end-to-end, and break in interesting ways.

Design Philosophy

• Explicit over magic — no hidden descriptors doing invisible work
• Small surface area — one Database, one Table, one Column, one ForeignKey
• Zero runtime dependencies — sqlite3 is the entire engine
• Readable internals — designed to be understood, not just used

Installation

pip install finesql

Quick Start

Define schema as Python classes

from finesql import Database, Table, Column, ForeignKey

class User(Table):
    username = Column(str)
    age = Column(int)

class Post(Table):
    title = Column(str)
    body = Column(str)
    author = ForeignKey(User)

CRUD operations

db = Database("app.db")
db.create(User)
db.create(Post)

# Create
user = User(username="Alice", age=25)
db.save(user)

post = Post(title="Hello World", body="First post", author=user)
db.save(post)

# Read
all_users = db.all(User)
alice = db.get(User, id=1)
match = db.get_by_field(User, field_name="username", value="Alice")

# Update
alice.age = 26
db.update(alice)

# Delete
db.delete(User, id=1)

Lazy foreign-key resolution

post = db.get(Post, id=1)
print(post.author.username)  # triggers a SELECT on User on attribute access

How It Works

A brief tour of the internals — the whole library is around 500 lines and structured so you can read it top to bottom:

• Schema introspection — Table collects class-level Column and ForeignKey definitions at class creation time and stores them in a schema dictionary.
• DDL generation — Database.create() walks that schema and emits CREATE TABLE with Python-to-SQLite type mapping (str → TEXT, int → INTEGER, bool → INTEGER, bytes → BLOB).
• Parameterised inserts — Database.save() builds prepared statements from instance attributes and assigns id back from cursor.lastrowid.
• Lazy relationships — ForeignKey resolves on attribute access rather than via JOINs, keeping the query layer dead simple.

If you want to see how a small ORM is actually structured, the finesql/ directory is the documentation.

API Reference

Method	Description
Database(path)	Open or create a SQLite database
db.create(table)	Issue CREATE TABLE IF NOT EXISTS for the model
db.save(instance)	Insert row and assign generated id
db.all(table)	Return all rows as model instances
db.get(table, id)	Fetch a single instance by primary key
db.get_by_field(table, field_name, value)	LIKE search on a single column
db.update(instance)	Persist changes back to the row
db.delete(table, id)	Delete by primary key

Roadmap

Each item targets a specific internal mechanism worth implementing from scratch:

• Migrations — schema diffing and incremental ALTER statements
• Chainable query builder — .filter() / .order_by() / .limit() instead of bespoke methods
• Async support — non-blocking I/O via aiosqlite
• mypy plugin — inferred column types on model instances

Scope

FineSQL is not a SQLAlchemy replacement. It targets a different niche: small scripts, internal tools, and developers who want ORM ergonomics without the dependency footprint. For multi-database production systems with migrations, connection pooling, and complex query optimisation, use SQLAlchemy or the Django ORM.

License

MIT © Abdulmajid Yunusov