Schema Conventions

When PormG generates a database schema from your Julia models, it follows a small set of naming and structural conventions. The moment you run migrate() these choices are physically in your database — a later change would mean the ORM no longer matches your existing schema, with no version-pin escape. They are therefore a stability contract, documented here as final.

The guiding principle is no magic: what you declare is what you get. PormG does not pluralize, does not invent column names, and does not auto-add fields to a native model. This makes the generated schema predictable and — importantly for PormG's ETL/secondary-app role — easy to point at a database whose table and column names already exist.

Table names

A table name is the model's name verbatim — no pluralization, no Inflector, no snake-casing — and that name is always lowercase:

Result = Models.Model("result", …)   # → table  result
Driver = Models.Model("driver", …)   # → table  driver

The Julia binding stays capitalized (Result), while the stored name ("result") is what becomes the table. To point a model at a different physical table — an existing database, a Django app's f1_ prefix, a legacy spelling — pin it with db_table rather than folding the difference into name:

Result = Models.Model("result", db_table = "f1_results", …)   # → table  f1_results

Naming the model "f1_results" outright also reaches that table, but it spends the logical name on a physical detail: every accessor, reverse relation and related_name is then keyed on f1_results, and the model can only ever belong to one prefix. db_table keeps the two separable, which is what lets one models file carry tables from several Django apps at once.

Lowercase is enforced, not merely conventional. A name given positionally must already be lowercase — Models.Model("Driver_Profile", …) raises ModelDefinitionError — while a name derived from the Julia binding is lowercased as it is filled in. Both routes therefore produce the same guarantee, which is what lets the DDL and the query builder agree on one spelling:

Models.Model("Driver_Profile", driverid = Models.IDField())
# ModelDefinitionError: The model name 'Driver_Profile' must be lowercase; …

A positional name given with a leading underscore is rejected the same way, and for the same reason: a model name is a lowercase logical identifier, and an underscore-prefixed name is not a shape PormG generates.

Models.Model("_order", id = Models.IDField())
# ModelDefinitionError: The model name '_order' starts with '_'; …

If the physical table really is called _order, pin it — the same escape valve as for case:

Order = Models.Model("order", db_table = "_order", id = Models.IDField())

Field names follow the same principle one level down, with db_column in place of db_table: a leading underscore is rejected there too (#317). See Field Naming Rules.

Pinning an explicit table name with db_table

The rules above constrain the logical model name. When the physical table is something those rules would reject — a mixed-case legacy table, a name PormG would never generate — pass db_table. It is the table-level sibling of db_column (#50): the model name stays a lowercase logical identifier, and db_table carries the physical one, verbatim.

DriverProfile = Models.Model("driver_profile",
  db_table  = "Driver_Profile_Legacy",   # ← the table that actually exists
  driverid  = Models.IDField(),
  surname   = Models.CharField(max_length = 100),
)

db_table is authoritative wherever a table identifier is rendered:

The value is stored exactly as written — no case fold, no leading-underscore strip, no identifier validation. That is deliberate: the option exists to name a table PormG's own conventions could not produce. It is also purely opt-in — a model that does not set db_table derives its table name exactly as it always has, so no existing schema changes and nothing needs re-migrating.

Every row of the table above holds for any spelling, including one PormG's own conventions would never produce — see Identifier quoting and case for the rule and why a physical name is escaped rather than validated.

Generated files: colliding bindings and names are disambiguated

inspectdb-style import (import_models_from_sqlite / import_models_from_postgres / import_models_from_django) derives both the Julia binding and — when the positional name would otherwise be empty, e.g. an all-underscore table — the positional name from the table name. Two tables can independently arrive at the same one: driver profile sanitizes to the same binding an already-legal driver_profile also produces; a table literally named models collides with the generated file's own import PormG.Models line.

Within a single generated file, each newly-derived binding and positional name is checked against everything already emitted into that file and, on a collision, suffixed with a digit — never another underscore, mirroring how a hostile field name is disambiguated (see Field Naming Rules). Without this, the second colliding Binding = Models.Model(...) line would silently overwrite the first Julia global when the file is loaded — no error, no warning, one model just gone.

Foreign keys follow the rename. A ForeignKey's target is written as the Julia binding of the model it points at, and PormG resolves every binding for a generated file before it writes the first model — so a key aimed at the table whose binding got suffixed names the suffixed one:

# live tables: "driver profile", "driver_profile", and a "pit_stop" holding one key into each
Driver_profile = Models.Model("driver profile",
  id = Models.IDField())

Driver_profile2 = Models.Model("driver_profile",
  id = Models.IDField())

Pit_stop = Models.Model("pit_stop",
  id = Models.IDField(),
  plain_id = Models.ForeignKey("Driver_profile2", null=true, pk_field="id"),
  spaced_id = Models.ForeignKey("Driver_profile", null=true, pk_field="id"))

plain_id references driver_profile, whose binding was suffixed, and it names the suffixed binding — so each key reaches the table it was declared against in the database. The same applies to a parent whose name needs sanitizing at all: a key into a table called 2fast targets Col_2fast, the binding that table actually gets, not the unusable "2fast".

Because the target is a binding, the target model has to be in the models module for the key to mean anything. A key whose target is not there — filtered out by include_table / ignore_table, in another schema, or hand-edited — cannot name a table, and PormG says so rather than guessing: set_models raises ModelDefinitionError when the module loads, and makemigrations raises the same error when it would otherwise have to render the parent into a REFERENCES clause.

django_prefix interop

A connection may set django_prefix in its connection.yml config: block (default: unset). It names the Django app label whose tables the connection reads, since Django names them <app_label>_<model>:

• When PormG generates a model file (via the Django importer), the prefix is emitted as the model's db_table. The positional slot keeps the logical handle:

  # class Dim_uf(models.Model) imported with django_prefix = "dash"
  Dim_uf = Models.Model("dim_uf", db_table = "dash_dim_uf", …)

  A Meta.db_table in the Django source still wins outright, exactly as in Django.

• Each auto-derived ManyToManyField also has its join table pinned to Django's spelling — <the owning model's table>_<field>, which is <prefix>_<model>_<field> normally but follows Meta.db_table when the class declares one. PormG's own derivation reproduces neither.

• When PormG derives relationship accessor names, it strips the prefix so Julia-side names stay clean. With the prefix out of name, that strip is a no-op — the name it would strip from is already clean.

• It remains the fallback that spells a physical table for a reverse-join target that declares no db_table. A regenerated file never reaches that fallback (every model carries a db_table), but a hand-written model on the same connection still does.

The physical-table rule is unchanged: the table is db_table when set, else name.

Sequence synchronisation

A PostgreSQL SERIAL/IDENTITY column only advances its sequence when the database generates the value. Insert a row with an explicit primary key and the sequence stays where it was; a later auto-generated key then collides with the row you inserted by hand.

PormG repairs this automatically only where drift is produced in bulk — bulk_insert and bulk_copy resynchronise the sequence afterwards whenever the write supplied explicit primary keys, to MAX(pk) + 1 on PostgreSQL and by rewriting sqlite_sequence on SQLite. bulk_insert also resyncs (once) before retrying an unexpected duplicate-key error.

Row-level writers — create/insert, update_or_create, get_or_create — do NOT auto-resync. Matching every comparable ORM checked when this was decided (Django, Rails, SQLAlchemy, Ecto, Prisma: none resyncs on a row-level write), the cost of two extra round-trips is not paid on every explicit-pk row write. Call resync_sequences yourself after one of these writes an explicit primary key, if a later auto-generated write to the same table needs to be safe.

No configuration governs any of this. The repair is not gated by change_db or django_prefix on either engine. (change_data: false is a different matter — it rejects the write itself, long before any resync would run, on both the automatic bulk paths and an explicit resync_sequences call.)

What differs between the engines

When the repair itself fails

On PostgreSQL the handling depends on whether the write is inside a transaction, because a failed statement there is not a local event — PostgreSQL marks the whole transaction aborted, rejects every later statement, and answers the COMMIT with a rollback.

• Inside a transaction the error propagates. Returning a row that the pending rollback is about to erase would be worse than raising. This is always the case for bulk_insert and bulk_copy, which run in a transaction by construction, and for any write inside your own atomic block.
• Outside one, PormG logs a warning and continues — naming the table, the column, and the sequence when it got as far as resolving one. This applies to a failure of the database round-trip or the connection pool; anything else (including a cancellation) still raises. The row (or, for a standalone repair, nothing) is already committed and only the next auto-generated key is at risk. A standalone resync_sequences call takes this path unless you wrap it in your own atomic block — row-level writers no longer call this machinery at all (see above), so it is reached almost exclusively through an explicit call now.

If the primary-key column has no owned sequence — a table PormG did not create, or a natural key — PormG looks for PostgreSQL's conventional <table>_<column>_seq in the table's own schema, and does nothing if it is absent. One consequence is worth knowing: PostgreSQL truncates generated object names at 63 bytes, so a table and column whose combined name exceeds that owns a sequence under a shortened name this lookup will not find. The skipped resync is logged at debug level together with the name it expected — run with JULIA_DEBUG=PormG to see it.

A recurring cause is a role holding USAGE but not UPDATE on the sequence: PostgreSQL requires UPDATE for setval while nextval accepts either, so such a role inserts rows perfectly well and can never resync. The logged exception tells you whether that is what happened.

Explicit repair: resync_sequences

resync_sequences(model) runs the same repair bulk_insert/bulk_copy run automatically, on demand, with no accompanying insert. Reach for it after:

• a row-level write (create, update_or_create, get_or_create) that supplied an explicit primary key, if a later auto-generated write to the same table needs to be safe;
• a pg_restore, a manual COPY, or any load that wrote rows outside PormG entirely.

# A migration replays historical drivers with their original ids.
for row in eachrow(legacy_drivers)
    M.Driver.objects.create("driverid" => row.id, "forename" => row.forename)
end
resync_sequences(M.Driver)   # once, after the batch — not per row

# A later ordinary create() is safe again:
M.Driver.objects.create("forename" => "Auto")

resync_sequences(models) (a collection) resyncs each in turn. Both forms return the pk field name(s) the repair was attempted for (empty if the model declares none); a per-field failure follows the same warn-or-propagate rule described above, not an exception from resync_sequences itself. Accepts a bare model (M.Driver) or its handler (M.Driver.objects) — same dual form as bulk_insert/bulk_copy.

Primary keys

PormG does not auto-create an id primary key for a native model. You declare one explicitly:

Driver = Models.Model("driver",
  driverid = Models.IDField(),                 # explicit PK
  surname  = Models.CharField(max_length=255),
)

A model with no declared primary key simply has none (get_model_pk_field returns nothing). The IDField renders as an auto-incrementing key per backend:

Foreign-key columns

A foreign-key column is the declared field name, verbatim (case preserved) — PormG never appends _id, lowercases, or otherwise transforms it:

Result = Models.Model("result",
  resultid = Models.IDField(),
  driverid = Models.ForeignKey("driver", pk_field="driverid"),  # → column  driverid
)

Whatever you name the field is the column. To interoperate with an existing schema that uses driver_id, name the field driver_id. This "verbatim" rule is deliberate and final: it keeps the ORM honest against externally-owned schemas, where an automatic _id suffix would fight the real column names.

Two consequences worth noting:

• The FK field name is also the join/lookup prefix: Result.objects.filter("driverid__surname" => "Senna"). There is no separate "related object vs. raw id" split like Django's obj.author / obj.author_id — the one field name serves both, by design.

• By default a column equals its field name. To map a field to a differently-named column, set db_column — it is authoritative (#50) across generated DDL, queries (SELECT/WHERE/ORDER BY/ INSERT/UPDATE, and the bulk APIs), and the migration diff. The field name stays the identity you declare and query by; only the physical column changes, and results stay keyed by the field name:

  sku = Models.CharField(db_column="product_sku")   # field `sku` → column "product_sku"

  This is non-breaking: the default (column == field name) is unchanged, so existing schemas are unaffected. On a ForeignKey/OneToOneField, db_column renames the local FK column; the referenced parent column follows pk_field, resolved through the parent field's own db_column. This works for both model-instance and string targets (ForeignKey(Driver, pk_field="code") or ForeignKey("Driver", pk_field="code")), and whether or not pk_field is spelled out — string targets are resolved to the model object once, up front (#62).

  `db_column` and the ManyToMany join columns

  db_column is honored across the whole stack — CRUD (create/get/update, bulk, sequence sync), FK constraints and joins (model-instance or string targets, a renamed parent PK), ManyToMany / CTE join keys when a participating model's primary key uses db_column (#64), and the join columns of an explicit through= model, which are that model's own foreign keys and resolve their db_column like any other field (#377).

  The auto-generated join columns are the one place db_column has nothing to say, because there is no field to hang it on — PormG both creates that table and names its columns <model>_<pk>. They are still overridable, by source_field / target_field on the ManyToManyField, which name those columns directly on this path; that is how the Django importer pins Django's <class>_id spelling (see above). With an explicit through= the same two options name fields on the through model instead, and the column is resolved from the field.

  A CTE exposes aliases, not columns

  A CTE (or any values()-projected subquery) is a derived table: its columns are the projection aliases, because the physical name is consumed inside the WITH body ("product_sku" AS "sku"). So CTE("ev", "sku") names the field (or its custom alias), never the db_column (#376) — the column-side half of the join-key rule above. Fields on real tables reached through the CTE are unaffected and still resolve their own db_column.

  That reference is a CTE(name, path) object rather than a "<cte>__col" string, because a CTE's columns are a namespace of their own — separate from the model's field paths, so a CTE may share a name with a field without shadowing it (#444). See Subqueries and CTEs.

Default on_delete

When a ForeignKey is declared without on_delete, the constraint is generated as ON DELETE NO ACTION. (Django, by contrast, requires you to choose.) Set it explicitly to change the behavior:

author   = Models.ForeignKey("driver")                       # → ON DELETE NO ACTION (default)
category = Models.ForeignKey("constructor", on_delete=CASCADE)

A foreign key may reference any unique column, not only the key

pk_field records the column the constraint actually names, which does not have to be the parent's primary key — referencing any column with a UNIQUE constraint is legal on both engines, and introspection reads it back as declared:

# live: parent_key VARCHAR(20) REFERENCES driver_registry(licence_no)
#       …where driver_registry's primary key is `id` and `licence_no` is UNIQUE
registration = Models.ForeignKey("driver_registry", pk_field="licence_no", on_delete=CASCADE)

Timestamp fields

PormG does not implicitly add created / modified columns. Auto-managed timestamps are opt-in, via auto_now_add / auto_now on a DateField or DateTimeField, and the column name is whatever you declare:

Driver = Models.Model("driver",
  driverid     = Models.IDField(),
  created_at   = Models.DateTimeField(auto_now_add=true),  # set once, on insert
  modified_at  = Models.DateTimeField(auto_now=true),      # refreshed on every save
)

auto_now_add writes the value on creation only; auto_now updates it on every save. Both attach settings.time_zone when generating the value.

Identifier quoting and case

Table and column identifiers are emitted with double quotes ("…") on both PostgreSQL and SQLite — no backticks, no backend-specific quoting — in DDL and in queries alike, and they preserve the declared case (#57), so a field declared driverId becomes "driverId". An embedded " is escaped by doubling it, the standard SQL escape on both backends.

That uniformity is what makes a db_table or db_column outside PormG's own naming conventions usable. PormG used to apply two different rules to one name: the DDL renderer escaped and accepted anything, while the query builder validated against a strict pattern and rejected spaces, leading digits and punctuation — so a db_table outside that pattern migrated cleanly and then raised InvalidValueError on the first SELECT. PormG could create a table it was unable to address ([#394]). The rule now depends on what kind of name it is:

The invariant that follows is the one worth remembering:

Any name PormG's DDL will create, PormG's queries will address.

That guarantee is about the physical names — db_table and db_column. A model's own field names still have to be plain identifiers wherever PormG uses one as a query-time label: bulk_update builds a derived table whose column list is made of field names, and those go through the fail-closed rule like any other alias.

Escaping is what makes that safe rather than merely permissive: a doubled " cannot terminate a quoted identifier, and a database interprets nothing else inside one. It is also, now, the only defence on a physical name — the query builder used to reject an odd spelling as a second layer and no longer does. That is the intended trade: db_table and db_column exist to carry names PormG did not choose, and a guard that refuses them is a guard against the feature.

Because column identifiers are quoted everywhere, a column may be named anything the database accepts — including a SQL reserved word or a Julia keyword. Declare the field under a legal Julia identifier and name the column with db_column; that is the sanctioned way to address a column whose name is not a legal PormG field name (#317):

end_ = Models.DateTimeField(db_column = "end")   # field `end_` → column "end"

Declaring lowercase snake_case (the house style) yields all-lowercase identifiers. The example below is the SQLite shape, which carries its foreign keys inline in CREATE TABLE. PostgreSQL renders its own column types (bigint, float, … GENERATED BY DEFAULT AS IDENTITY) and emits no inline FOREIGN KEY — there, foreign keys arrive as separate statements in the migration plan. The identifier quoting is identical on both:

CREATE TABLE IF NOT EXISTS result (
  "resultid" INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
  "driverid" INTEGER NOT NULL,
  "points"   REAL NOT NULL,
  FOREIGN KEY ("driverid") REFERENCES "driver"("driverid") ON DELETE NO ACTION
);

Summary

The migration format that records these schemas (file layout, checksum, tracking table) is a separate frozen contract — see Migration Format Stability.

[#394]: https://github.com/PingoLee/PormG.jl/issues/394