pookiedb 0.1.4

A Django-style ORM for PostgreSQL and SQLite with migrations, relationships, and a friendly API.

PookieDB ORM

A Django-style Python ORM for PostgreSQL and SQLite - with auto migrations, relationships, a chainable QuerySet API, and a friendly CLI.

Installation

pip install pookiedb
# PostgreSQL support is included via psycopg2-binary

---

Quick Start

1. Connect to your database

import pookiedb

# SQLite
pookiedb.connect("sqlite:///mydb.sqlite3")

# PostgreSQL
pookiedb.connect("postgresql://postgres:password@localhost:5432/mydb")

# Or using kwargs
pookiedb.connect(engine="postgresql", name="mydb", host="localhost", user="postgres", password="secret")

2. Define models

import pookiedb

class Author(pookiedb.Model):
    name = pookiedb.CharField(max_length=100)
    email = pookiedb.EmailField(unique=True)
    bio = pookiedb.TextField(null=True, blank=True)
    joined = pookiedb.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "authors"
        ordering = ["-joined"]


class Post(pookiedb.Model):
    title = pookiedb.CharField(max_length=200)
    slug = pookiedb.SlugField(unique=True)
    body = pookiedb.TextField()
    author = pookiedb.ForeignKey(Author, on_delete=pookiedb.CASCADE)
    published = pookiedb.BooleanField(default=False)
    metadata = pookiedb.JSONField(null=True)
    tags = pookiedb.ManyToManyField("Tag", related_name="posts")
    created_at = pookiedb.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "posts"


class Tag(pookiedb.Model):
    name = pookiedb.CharField(max_length=50, unique=True)

    class Meta:
        db_table = "tags"

3. Create tables

Author.create_table()
Tag.create_table()
Post.create_table()

---

QuerySet API

Pookie's QuerySet is lazy and chainable - queries only hit the database when you iterate or evaluate.

# Create
alice = Author.objects.create(name="Alice", email="[e-mail]")

# Get
author = Author.objects.get(email="[e-mail]")

# Filter (chained)
posts = (
    Post.objects
    .filter(published=True)
    .filter(author=alice)
    .order_by("-created_at")
    .limit(10)
)

# Exclude
drafts = Post.objects.exclude(published=True)

# Q objects for OR / AND / NOT logic
from pookiedb.queryset.queryset import Q

results = Post.objects.filter(
    Q(title__icontains="python") | Q(tags__name="python")
)

# get_or_create / update_or_create
tag, created = Tag.objects.get_or_create(name="django")

# Slicing
first_five = Post.objects.all()[:5]

# values / values_list
names = Author.objects.values("name", "email")
ids = Post.objects.values_list("id", flat=True)

# Aggregates
from pookiedb.queryset.queryset import Sum, Avg, Count
stats = Post.objects.aggregate(total=Count("id"), avg_len=Avg("id"))

# Bulk create
posts = Post.objects.bulk_create([
    Post(title="Post 1", slug="post-1", body="...", author=alice),
    Post(title="Post 2", slug="post-2", body="...", author=alice),
])

# Raw SQL
authors = Author.objects.raw("SELECT * FROM authors WHERE name ILIKE %s", ["%ali%"])

# Delete
Post.objects.filter(published=False).delete()

# Bulk update
Post.objects.filter(author=alice).bulk_update(published=True)

---

Relationships

ForeignKey

post = Post.objects.get(id=1)
print(post.author)       # lazy-loads the Author
print(post.author.name)

ManyToMany

post = Post.objects.get(id=1)
tag = Tag.objects.get(name="python")

post.tags.add(tag)
post.tags.all()     # QuerySet of related Tags
post.tags.remove(tag)
post.tags.set([tag1, tag2])
post.tags.clear()
post.tags.count()

OneToOne

class UserProfile(pookiedb.Model):
    user = pookiedb.OneToOneField("User", on_delete=pookiedb.CASCADE)
    avatar_url = pookiedb.URLField(null=True)

---

Field Reference

Field	Description
CharField(max_length=N)	VARCHAR with length limit
TextField()	Unlimited text
IntegerField()	INTEGER
BigIntegerField()	BIGINT
FloatField()	REAL / DOUBLE PRECISION
DecimalField(max_digits, decimal_places)	NUMERIC
BooleanField()	BOOLEAN / INTEGER
DateField(auto_now, auto_now_add)	DATE
DateTimeField(auto_now, auto_now_add)	TIMESTAMP
TimeField()	TIME
EmailField()	VARCHAR with email validation
URLField()	VARCHAR with URL validation
SlugField()	VARCHAR with slug validation
UUIDField(auto=True)	UUID / TEXT
AutoField()	Auto-increment primary key
BigAutoField()	Big auto-increment primary key
JSONField()	JSONB (Postgres) / TEXT (SQLite)
ArrayField(base_field)	ARRAY (Postgres) / JSON TEXT (SQLite)
ForeignKey(to, on_delete)	Many-to-one FK
OneToOneField(to, on_delete)	Unique FK
ManyToManyField(to)	Join table relationship

Common field kwargs

CharField(
    max_length=100,
    null=False,         # allow NULL in DB
    blank=False,        # allow empty value in forms/validation
    default=None,       # default value (or callable)
    unique=False,       # UNIQUE constraint
    db_index=False,     # CREATE INDEX
    db_column=None,     # override column name
    choices=[("draft", "Draft"), ("pub", "Published")],
    verbose_name="My Field",
)

---

Lookup Types

# Exact (default)
filter(name="Alice")
filter(name__exact="Alice")

# Case-insensitive
filter(name__iexact="alice")
filter(name__icontains="ali")

# Wildcards
filter(title__startswith="Hello")
filter(title__endswith="world")
filter(body__contains="pookie")

# Comparison
filter(age__gt=18)
filter(age__gte=18)
filter(age__lt=65)
filter(age__lte=65)

# IN / NULL / RANGE
filter(id__in=[1, 2, 3])
filter(email__isnull=True)
filter(age__range=(18, 65))
filter(name__ne="Bob")

---

Migrations

Generate a migration

pookiedb makemigrations --settings settings.py
pookiedb makemigrations --name add_slug_field --settings settings.py

Apply migrations

pookiedb migrate --settings settings.py

View migration status

pookiedb showmigrations --settings settings.py

Roll back

pookiedb rollback --steps 1 --settings settings.py

Your settings.py just needs to call pookiedb.connect() and import your models:

# settings.py
import pookiedb
from myapp.models import Author, Post, Tag

pookiedb.connect("sqlite:///mydb.sqlite3")

---

CLI Commands

pookiedb --help

Commands:
  makemigrations   Detect model changes and generate a migration file
  migrate          Apply pending migrations to the database
  rollback         Roll back the last N migrations
  showmigrations   List all migrations and their status
  shell            Interactive Python REPL with pookiedb pre-imported
  dbshell          Open raw psql / sqlite3 shell
  inspectdb        Introspect an existing DB and generate model code

Shell

pookiedb shell --settings settings.py
# All your models are available by name
>>> Author.objects.all()
>>> Post.objects.filter(published=True).count()

DBShell

pookiedb dbshell --settings settings.py   # opens psql or sqlite3

InspectDB

# Introspect all tables
pookiedb inspectdb --settings settings.py

# Single table
pookiedb inspectdb --table users --settings settings.py

# Write to file
pookiedb inspectdb --output models.py --settings settings.py

---

Transactions

from pookiedb.db.connection import transaction

with transaction() as conn:
    author = Author(name="Bob", email="[e-mail]")
    author.save()
    post = Post(title="Hello", slug="hello", body="...", author=author)
    post.save()
    # Auto commits on exit, rolls back on exception

---

Pagination

from pookiedb.utils import Paginator

paginator = Paginator(Post.objects.filter(published=True), per_page=20)
page = paginator.page(1)

print(page.object_list)     # list of Post instances
print(page.has_next())      # True / False
print(page.num_pages)       # total pages

---

Multiple Databases

pookiedb.connect("postgresql://...", alias="primary")
pookiedb.connect("sqlite:///analytics.sqlite3", alias="analytics")

class EventLog(pookiedb.Model):
    class Meta:
        db_alias = "analytics"

---

Model Meta options

class Meta:
    db_table = "custom_table_name"      # override table name
    ordering = ["-created_at", "name"]  # default ordering
    unique_together = [["first_name", "last_name"]]
    db_alias = "default"                # which DB connection to use
    abstract = True                     # don't create a table
    verbose_name = "blog post"
    verbose_name_plural = "blog posts"

---

License

MIT © Grace Peter Mutiibwa