Metadata-Version: 2.4
Name: duckling-orm
Version: 0.2.0
Summary: A Beanie-inspired ORM for DuckDB — async-first, Pydantic-powered.
Author: Duckling Contributors
License: Apache-2.0
Project-URL: Homepage, https://github.com/carbonbits/duckling
Project-URL: Repository, https://github.com/carbonbits/duckling
Project-URL: Issues, https://github.com/carbonbits/duckling/issues
Keywords: duckdb,orm,pydantic,async,beanie
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Typing :: Typed
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: duckdb>=0.9.0
Requires-Dist: pydantic>=2.0.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: pytest-asyncio>=0.21; extra == "dev"
Requires-Dist: pre-commit>=3.5; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Dynamic: license-file

# 🦆 Duckling

**A Beanie-inspired ORM for DuckDB — async-first, Pydantic-powered.**

Duckling brings the elegant, developer-friendly API of [Beanie](https://github.com/BeanieODM/beanie) (MongoDB ODM) to [DuckDB](https://duckdb.org/) — the fast, in-process analytical database. Define your models with Pydantic, query with Pythonic expressions, and enjoy both async and sync APIs.

The original version of this project was generated by Claude AI.

The human intervention in the code is to customize it for use within the carbonbits ecosystem. No plans to publish to pypi yet

## Installation

```bash
pip install duckling
# or from source:
pip install -e .
```

**Requirements:** Python ≥ 3.10, `duckdb >= 0.9`, `pydantic >= 2.0`

---

## Quick Start

```python
import asyncio
from typing import Annotated, Optional
from duckling import Document, IndexSpec, init_duckling

class User(Document):
    name: str
    email: Annotated[str, IndexSpec(unique=True)]
    age: int = 0

    class Settings:
        table_name = "users"

async def main():
    await init_duckling(database=":memory:", document_models=[User])

    # Insert
    alice = User(name="Alice", email="alice@example.com", age=30)
    await alice.insert()

    # Query
    users = await User.find(User.age > 25).sort("+name").limit(10).to_list()

    # Update
    alice.age = 31
    await alice.save()

    # Delete
    await alice.delete()

asyncio.run(main())
```

---

## API Reference

### Initialization

```python
from duckling import init_duckling, init_duckling_sync

# Async
await init_duckling(
    database=":memory:",          # or "path/to/file.db"
    document_models=[User, Product],
    recreate_tables=False,        # drop & recreate tables
)

# Sync
init_duckling_sync(database="app.db", document_models=[User])
```

### Defining Models

Duckling models are Pydantic `BaseModel` subclasses. By default, `id` is an
auto-generated, auto-incrementing integer primary key:

```python
from duckling import Document, IndexSpec
from typing import Annotated, Optional, List
import datetime

class Product(Document):
    name: str
    price: float
    category: Optional[str] = None
    tags: Optional[List[str]] = None       # stored as JSON
    created_at: datetime.datetime = datetime.datetime.now()
    in_stock: bool = True

    class Settings:
        table_name = "products"   # optional, auto-generated from class name
```

**Supported types:** `str`, `int`, `float`, `bool`, `bytes`, `datetime.date`, `datetime.datetime`, `datetime.time`, `uuid.UUID`, `Optional[T]`, `List[T]` (→ JSON), `dict` (→ JSON), nested Pydantic models (→ JSON), `Enum`.

### Custom Primary Keys (UUID, ULID, string)

Override `id` with any type other than `int` to opt out of the auto-increment
sequence and use your own primary key — a UUID, a sortable ULID, or a plain
caller-supplied string:

```python
import uuid
from pydantic import Field
from duckling import Document, generate_ulid

class Session(Document):
    id: str = Field(default_factory=generate_ulid)   # sortable string PK
    user_email: str

class ApiKey(Document):
    id: uuid.UUID = Field(default_factory=uuid.uuid4)  # UUID PK
    label: str

class Tag(Document):
    id: str            # no default — caller must supply an id
    name: str
```

Inserting a document whose `id` already exists raises `DocumentAlreadyExists`.

A non-integer `id` with no default must be supplied by the caller; inserting one
that is still `None` raises `InvalidQueryError`.

### Renaming Columns (including the primary key)

Any field can be stored under a different column name with `Field(alias=...)`.
The Python attribute keeps its name, so `id` remains the primary-key handle even
when the column is called something else:

```python
from pydantic import Field
from duckling import Document, generate_ulid

class Ticket(Document):
    id: str = Field(default_factory=generate_ulid, alias="key")   # → "key" column
    subject: str
    priority: int = Field(default=0, alias="prio")                # → "prio" column
```

```python
ticket = Ticket(subject="Printer down", priority=2)
await ticket.insert()

await Ticket.get(ticket.id)                       # WHERE "key" = ?
await Ticket.find(Ticket.priority > 5).to_list()  # WHERE "prio" > ?
await Ticket.find_all().sort("-priority")         # ORDER BY "prio" DESC
```

Aliases apply everywhere a column is addressed: `CREATE TABLE`, indexes and
`UNIQUE` constraints, the auto-increment sequence name, filters, sorts,
projections and aggregations. Both spellings work as constructor arguments
(`Ticket(priority=2)` and `Ticket(prio=2)`), and `sort()`/`project()`/`Avg()`
accept either the field name or the column name.

Two fields that map to the same column raise `InvalidQueryError` when the model
class is defined.

### Schemas

Set `Settings.schema_name` to place a model's table in a named schema. Duckling
issues `CREATE SCHEMA IF NOT EXISTS` before creating the table, so the schema
does not have to exist beforehand:

```python
class Role(Document):
    name: Annotated[str, IndexSpec(unique=True)]
    display_name: Optional[str] = None

    class Settings:
        schema_name = "v1"
        table_name = "roles"     # optional, as always
```

Everything Duckling generates is qualified — reads, writes, the primary-key
sequence and the index:

```sql
CREATE SCHEMA IF NOT EXISTS "v1"
CREATE SEQUENCE IF NOT EXISTS "v1"."seq_roles_id" START 1
CREATE TABLE IF NOT EXISTS "v1"."roles" (
  "id" INTEGER PRIMARY KEY DEFAULT(nextval('"v1"."seq_roles_id"')),
  "name" VARCHAR UNIQUE,
  "display_name" VARCHAR
)
SELECT "id", "name", "display_name" FROM "v1"."roles" WHERE "name" = ?
```

Because the sequence and index live in the table's own schema, two schemas can
hold same-named tables independently:

```python
class ArchivedRole(Document):
    name: str

    class Settings:
        schema_name = "v2"
        table_name = "roles"     # "v2"."roles", unrelated to "v1"."roles"
```

Omit `schema_name` and the table goes in the connection's default schema
(`main`), exactly as before.

`schema_name` and `table_name` are each rendered as a *single* quoted
identifier, so neither may contain `.` or `"` — a pre-qualified value like
`table_name = "v1.roles"` raises `InvalidQueryError` when the class is defined,
rather than silently creating a table named `v1.roles`.

### Indexed Fields

```python
from duckling import IndexSpec
from typing import Annotated

class User(Document):
    email: Annotated[str, IndexSpec(unique=True)]   # unique index
    age: Annotated[int, IndexSpec()]                 # regular index
```

### CRUD Operations

Every method has an async version (default) and a `_sync` variant:

| Async | Sync | Description |
|---|---|---|
| `await doc.insert()` | `doc.insert_sync()` | Insert a new document |
| `await doc.save()` | `doc.save_sync()` | Upsert (insert or update) |
| `await doc.delete()` | `doc.delete_sync()` | Delete this document |
| `await doc.refresh()` | — | Reload from database |
| `await Model.insert_many([...])` | `Model.insert_many_sync([...])` | Bulk insert |
| `await Model.delete_all()` | `Model.delete_all_sync()` | Delete all rows |
| `await Model.get(id)` | `Model.get_sync(id)` | Fetch by primary key |
| `await Model.count()` | `Model.count_sync()` | Count all rows |

### Queries

Duckling's query interface mirrors Beanie's fluent API:

```python
# Find with conditions
users = await User.find(User.age > 25).to_list()
users = await User.find(User.age > 25, User.active == True).to_list()

# Find one
user = await User.find_one(User.email == "alice@example.com")

# Find all
all_users = await User.find_all().to_list()

# Chaining
results = (
    await User.find(User.active == True)
    .find(User.age >= 18)          # additional conditions (AND)
    .sort("+name")                  # ascending
    .sort("-age")                   # descending
    .skip(10)                       # offset
    .limit(20)                      # limit
    .to_list()
)

# Count & exists
count = await User.find(User.age > 30).count()
has_any = await User.find(User.name == "Alice").exists()

# Async iteration
async for user in User.find(User.active == True).sort("+name"):
    print(user.name)
```

### Query Expressions

Use Pythonic operators directly on model fields:

```python
# Comparison operators
User.age == 30          User.age != 30
User.age > 25           User.age >= 25
User.age < 40           User.age <= 40

# Boolean combinators
(User.age > 25) & (User.active == True)    # AND
(User.name == "A") | (User.name == "B")    # OR
~(User.active == True)                      # NOT

# FieldProxy helper methods
User.name.startswith("Ali")     # LIKE 'Ali%'
User.name.endswith("son")       # LIKE '%son'
User.name.contains("lic")       # LIKE '%lic%'
User.name.like("A%e")           # LIKE 'A%e'
User.name.ilike("alice")        # ILIKE (case-insensitive)
User.age.is_in([25, 30, 35])    # IN (25, 30, 35)
User.age.not_in([0, 99])        # NOT IN
User.age.between(18, 65)        # BETWEEN 18 AND 65

# Sort helpers
User.name.asc()     # → ("name", ASCENDING)
User.name.desc()    # → ("name", DESCENDING)
```

### Operator Functions

For more complex queries, use the operator functions:

```python
from duckling.operators import And, Or, Not, In, NotIn, Between, Like, ILike, Raw

await User.find(In(User.age, [25, 30, 35])).to_list()
await User.find(Between(User.age, 18, 65)).to_list()
await User.find(Like(User.name, "%smith%")).to_list()

# Combine
await User.find(
    And(
        User.active == True,
        Or(User.city == "NYC", User.city == "LA"),
        Not(User.age < 18),
    )
).to_list()

# Raw SQL escape hatch
await User.find(Raw('"age" % 2 = 0')).to_list()
```

### Aggregation

```python
from duckling.query import Count, Sum, Avg, Min, Max, CountDistinct

stats = await User.find(User.active == True).aggregate(
    total=Count(),
    avg_age=Avg("age"),
    max_age=Max("age"),
    min_age=Min("age"),
    sum_age=Sum("age"),
    unique_names=CountDistinct("name"),
)
print(stats)  # {'total': 42, 'avg_age': 31.5, ...}
```

### Sort Syntax

```python
# String syntax
.sort("+name")          # ascending
.sort("-age")           # descending
.sort("+name", "-age")  # multi-column

# Tuple syntax
.sort(("name", SortDirection.ASCENDING))

# FieldProxy syntax
.sort(User.name.asc(), User.age.desc())
```

### Transactions

```python
session = get_session()

# Async
async with session.async_transaction():
    await user.insert()
    await order.insert()

# Sync
with session.transaction():
    user.insert_sync()
    order.insert_sync()
```

### Raw SQL Escape Hatch

```python
from duckling import get_session

session = get_session()

# Async
rows = await session.async_fetchall("SELECT * FROM users WHERE age > ?", [25])

# Get pandas DataFrame
df = await session.async_fetchdf("SELECT name, age FROM users")

# Sync
rows = session.fetchall("SELECT count(*) FROM users")
```

---

## Beanie → Duckling Comparison

| Beanie (MongoDB) | Duckling (DuckDB) |
|---|---|
| `init_beanie(database, models)` | `await init_duckling(database, models)` |
| `class User(Document)` | `class User(Document)` |
| `await user.insert()` | `await user.insert()` |
| `await user.save()` | `await user.save()` |
| `await User.find(cond).to_list()` | `await User.find(cond).to_list()` |
| `await User.find_one(cond)` | `await User.find_one(cond)` |
| `User.name == "Alice"` | `User.name == "Alice"` |
| `In(User.age, [...])` | `In(User.age, [...])` |
| `await User.find().sort("+name")` | `await User.find().sort("+name")` |
| Settings class | Settings class |
| `Indexed(str, unique=True)` | `Annotated[str, IndexSpec(unique=True)]` |

---

## Project Structure

Classes live one per module, with each package's `__init__.py` re-exporting them
so import paths stay stable as the layout evolves.

```
src/duckling/
├── __init__.py           # Public exports
├── connection.py         # DuckDB session management
├── init.py               # init_duckling() / init_duckling_sync()
├── operators.py          # And, Or, In, Between, Like, … (functions)
├── identifiers.py        # SQL identifier quoting / schema qualification
├── ids.py                # generate_ulid()
├── exceptions.py         # Custom exceptions
├── document/
│   ├── base.py           # Document — the core
│   ├── meta.py           # DocumentMeta (class-level field access)
│   └── types.py          # Python ↔ DuckDB type and value conversion
├── fields/
│   ├── proxy.py          # FieldProxy
│   ├── index_spec.py     # IndexSpec, Indexed()
│   └── sort.py           # SortDirection
├── expressions/
│   ├── base.py           # Expression
│   ├── comparison.py     # ComparisonExpression
│   ├── inclusion.py      # InExpression
│   ├── between.py        # BetweenExpression
│   ├── like.py           # LikeExpression
│   ├── raw.py            # RawExpression
│   ├── conjunction.py    # AndExpression
│   ├── disjunction.py    # OrExpression
│   └── negation.py       # NotExpression
├── aggregations/
│   ├── base.py           # AggFunc
│   └── count.py, count_distinct.py, sum.py, avg.py, min.py, max.py
└── query/
    ├── find_query.py     # FindQuery builder
    └── iterator.py       # FindQueryIterator

tests/                    # Per-module tests (test_document.py, test_query.py, …)
```

`duckling.fields` re-exports the expression classes and `duckling.query`
re-exports the aggregation functions, so `from duckling.fields import Expression`
and `from duckling.query import Avg` still resolve.

---

## TODO

- **Require an explicit `id` declaration.** Today every model inherits
  `id: Optional[int] = None` from `Document`, so an auto-increment integer
  primary key is the silent default. Making the declaration mandatory would
  force models to state their key strategy, at the cost of a breaking change to
  every existing model plus the examples in this README. Deferred — revisit
  before 1.0, when it is still cheap.
- **Split up `Document`.** Every other class now has its own module, but
  `document/base.py` still carries table DDL, row serialization, CRUD and the
  query entry points on a single class. Those are four separable concerns.
- **`aggregate_sync()`.** Every other execution method has a sync counterpart;
  aggregation is async-only.
- **Schema validation against existing tables.** `CREATE TABLE IF NOT EXISTS`
  silently no-ops when a table already exists with a different shape, so a
  model/table mismatch surfaces as a mapping bug rather than a clear error.

## License

MIT
