Skip to content

plain-postgres.md

Latest commit

 

History

History
98 lines (68 loc) · 5.77 KB

plain-postgres.md

File metadata and controls

98 lines (68 loc) · 5.77 KB
Raw
paths
**/*.py

Database & Models

Field Imports

Import fields via from plain.postgres import types. Don't add primitive annotations — the field stubs return typed descriptors that resolve to the right value type on instance access:

from plain.postgres import types

name = types.TextField(max_length=100)
car = types.ForeignKeyField(Car, on_delete=postgres.CASCADE)

For string forward references ("self", "OtherModel"), the type checker can't infer the target type from the string — annotate explicitly so instance access keeps its type:

parent: TreeNode | None = types.ForeignKeyField("self", on_delete=postgres.CASCADE, allow_null=True)

For JSONField and EncryptedJSONField, the stub returns Any (the runtime class isn't generic over its value shape), so annotate explicitly to preserve typing:

parameters: dict[str, Any] | None = types.JSONField(required=False, allow_null=True)
config: dict | None = types.EncryptedJSONField(required=False, allow_null=True)

Do NOT import field classes directly from plain.postgres or plain.postgres.fields.

Schema Changes

Migrations are annoying to revise after the fact; convergence is cheap. So before making a batch of migration-generating changes — new models, new columns, or column-type changes — think the design through first. Nullability, defaults, indexes, constraints, on_delete, and choices aren't migrations; they're convergence (edit the model and re-sync), so they stay cheap to revise. For a large set of migration-dependent changes, surfacing the design first (plan mode fits) is worth it.

Migrations vs Convergence

uv run plain postgres sync runs three steps: create migrations → apply migrations → converge schema.

  • Migrations handle tables and columns (CreateModel, AddField, AlterField, etc.)
  • Convergence handles indexes, constraints, FK constraints, and storage parameters — declared on the model but NOT serialized into migration files. (FK columns like team_id bigint are created by migrations; the actual FOREIGN KEY constraint is added by convergence.)

This means: when you add an Index or UniqueConstraint to a model, no migration is generated. The converge step reads the live model class and syncs the database directly. Don't worry about serializing constraint expressions (like Lower()) for migrations — they never go there.

For custom data migrations, use uv run plain migrations create --empty --name <name> to scaffold the file.

Run uv run plain docs postgres for full workflow details.

Querying

Use Model.query to build querysets (e.g., User.query.filter(is_active=True)).

  • Use select_related() for FK access in loops, prefetch_related() for reverse/M2N
  • A foreign key returns a partial related object: obj.author and obj.author.id are query-free; other fields load on first access. There is no obj.author_id — use obj.author.id
  • Use .annotate(Count(...)) instead of calling .count() per row
  • Fetch all data in the view — templates should never trigger queries
  • Use .exists() not .count() > 0, .count() not len(qs)
  • Use bulk_create/bulk_update for batch ops, upsert/bulk_upsert for atomic insert-or-update (single row / many), .update()/.delete() for mass ops
  • Use .values_list() when you only need specific columns
  • Wrap multi-step writes in transaction.atomic()
  • Instance writes are obj.create() (always INSERT) and obj.update() (always UPDATE; update(fields=[...]) limits the columns) — there is no save(), force_insert, or force_update. Constructing an instance then create()-ing it inserts; a hand-set id that collides raises IntegrityError.
  • create()/update() raise ValidationError (not raw psycopg.IntegrityError) on a declared unique/check constraint violation, even a raced one — the DB enforces it, so inside an open transaction.atomic() the violation aborts the transaction (wrap the write in its own atomic() to catch and keep using the transaction). Set-based writes (QuerySet.update()/bulk_create()) raise raw psycopg.IntegrityError. Retrying on conflict? except (psycopg.IntegrityError, ValidationError), or upsert(**values, unique_fields=[...]) (single row) / bulk_upsert(objs, update_fields=[...], unique_fields=[...]) (many) for an atomic insert-or-update
  • Always paginate list queries — unbounded querysets get slower as data grows

Run uv run plain docs postgres for full patterns with code examples.

Schema Design

  • Always index FK columns — Postgres doesn't auto-create these. Use an Index, or a constraint with the FK as the first field.
  • Index fields used in .filter() and .order_by()
  • Indexes: {table}_{column(s)}_idx
  • Constraints: {table}_{column(s)}_{type} (e.g., _unique, _check)
  • Choose on_delete deliberately: CASCADE for owned children, RESTRICT for referenced data, SET_NULL for optional references
  • No allow_null on string fields — use default=""

Run uv run plain docs postgres for full patterns with code examples.

Database Doctor

Use the /plain-postgres-doctor skill to check overall database health — migration sync, schema correctness, and operational health.

Run uv run plain docs postgres for check details, thresholds, and production usage.

Differences from Django

  • Use Model.query not Model.objects
  • Import fields from plain.postgres.types not plain.postgres.fields — and don't import field classes directly from plain.postgres
  • Use model_options = postgres.Options(...) not class Meta
  • Never format raw SQL strings — always use parameterized queries
  • Migrations are forward-only — no reverse migrations. RunPython takes a single callable (no reverse_code or noop). The callable signature is fn(models, schema_editor), not fn(apps, schema_editor)