embar 0.1.0

A SQL-y and well-typed ORM for Python

Embar

A Python ORM with types

---

Embar is a new ORM for Python with the following goals:

• Type safety: your type checker should know what arguments are valid, and what is being returned from any call.
• Type hints: your LSP should be able to guide you towards the query you want to write.
• SQL-esque: you should be able to write queries simply by knowing SQL and your data model.
• You should be able to actually just write SQL when you need to.

These are mostly inspired by Drizzle. The Python ecosystem deserves something with similar DX.

Embar uses Template strings and so only supports Python 3.14.

Quickstart

Install

uv add embar

Set up database models

# schema.py
from embar.column.common import Integer, Text
from embar.config import TableConfig
from embar.table import Table

class User(Table):
    embar_config = TableConfig(table_name="users")

    id: Integer = Integer(primary=True)
    email: Text = Text("user_email", default="text", not_null=True)

class Message(Table):
    id: Integer = Integer()
    user_id: Integer = Integer().fk(lambda: User.id)
    content: Text = Text()

Create client and apply migrations

# main.py
import sqlite3
from embar.db.sqlite import Db as SqliteDb

conn = sqlite3.connect(":memory:")
db = SqliteDb(conn)
db.migrate([User, Message])

Insert some data

user = User(id=1, email="[e-mail]")
message = Message(id=1, user_id=user.id, content="Hello!")

db.insert(User).values(user).run()
db.insert(Message).values(message).run()

Query some data

With join, where and group by.

from typing import Annotated
from embar.query.selection import Selection
from embar.query.where import Eq, Like, Or

class UserSel(Selection):
    id: Annotated[int, User.id]
    messages: Annotated[list[str], Message.content.many()]


users = (
    db.select(UserSel)
    .fromm(User)
    .left_join(Message, Eq(User.id, Message.user_id))
    .where(Or(
        Eq(User.id, 1),
        Like(User.email, "foo%")
    ))
    .group_by(User.id)
    .run()
)
# [ UserSel(id=1, messages=['Hello!']) ]

Query some more data

This time with fully nested child tables, and some raw SQL.

from datetime import datetime
from embar.sql import Sql

class UserHydrated(Selection):
    email: Annotated[str, User.email]
    messages: Annotated[list[Message], Message.many()]
    date: Annotated[datetime, Sql(t"CURRENT_TIMESTAMP")]


users = (
    db.select(UserHydrated)
    .fromm(User)
    .left_join(Message, Eq(User.id, Message.user_id))
    .group_by(User.id)
    .limit(2)
    .run()
)
# [UserHydrated(
#      email='[e-mail]',
#      messages=[Message(content='Hello!', id=1, user_id=1)],
#      date: datetime(2025, 10, 26, ...)
# )]

Update a row

Unfortunately this requires another model to be defined, as Python doesn't have a Partial[] type.

from typing import TypedDict

class MessageUpdate(TypedDict, total=False):
    id: int
    user_id: int
    content: str

(
    db.update(Message)
    .set(MessageUpdate(content="Goodbye"))
    .where(Eq(Message.id, 1))
    .run()
)

Contributing

Install uv.

Then:

uv sync

This project uses poethepoet for tasks/scripts.

You'll need Docker installed to run tests.

Format, lint, type-check, test:

uv run poe fmt
           lint
           check
           test

# or
uv run poe all

Or do this:

# Run this or put it in .zshrc/.bashrc/etc
alias poe="uv run poe"

# Then you can just:
poe test

Other ORMs to co consider

There seems to be a gap in the Python ORM market.

• SQLAlchemy (and, by extension, SQLModel) is too complicated
• PonyORM has no types
• Same for PugSQL
• TortoiseORM is probably appealing if you like Django/ActiveRecord
• Piccolo is cool but not type-safe
• ormar is not very type-aware and still basedon SQLAlchemy