Bulk Operations

Bulk operations are designed for high-performance data manipulation of large datasets. PormG provides three dedicated tools:

• bulk_insert(): Standard SQL-based insertion with automatic chunking.
• bulk_copy(): PostgreSQL native COPY protocol for ultra-fast insertion.
• bulk_update(): Efficient multi-row updates from a table of rows using match_on= keys.

Input: any Tables.jl table

Every bulk writer, and allocate_primary_keys(), takes any Tables.jl table (#1344): a DataFrame, a Vector of NamedTuples, a NamedTuple of vectors, a CSV.File, or anything else with Tables.istable(x) == true. The examples on this page build DataFrames (after using DataFrames: DataFrames is your app's dependency, not PormG's), and every one of them works the same with the same rows in another kind of table. Anything that is not a table raises QueryBuildError.

statuses = [(statusid = 1, status = "Finished"), (statusid = 2, status = "Disqualified")]
bulk_insert(M.Status.objects, statuses)                       # no DataFrame needed

more = (statusid = [3, 4], status = ["Accident", "Collision"])  # a NamedTuple of vectors
bulk_insert(M.Status.objects, more)

All three operations accept show_query=:sql, show_query=:dict, show_query=:inspection, show_query=:params, or show_query=:none to inspect the generated SQL without executing it. See Query Inspection for a full description of each mode.

Return Value

When a bulk operation executes, it returns a NamedTuple (count, rows). count is the number of rows the operation affected, summed across every chunk:

rows is nothing unless you ask bulk_insert() for the written values with returning= — then it is a table of the kind you passed, aligned with your input, see Returning Generated Values. An empty table returns (count = 0, rows = nothing) — with returning=, rows is an empty table of your kind carrying the requested columns. The dry-run show_query modes are unchanged: they return the statement (or nothing for an empty table, which has none).

Check the count when a short one matters. It is the only way to tell that a bulk_update() matched only part of the frame, or nothing at all — say, resultid keys from another season sent to a handler scoped to this one:

season_2008 = M.Race.objects.filter("year" => 2008).values("raceid")
df_rescored = DataFrame(resultid = [1, 2], points = [10.0, 8.0])

r = bulk_update(M.Result.objects.filter("raceid__@in" => season_2008), df_rescored,
    columns = ["points"], match_on = ["resultid"])
r.count == nrow(df_rescored) || @warn "some results are outside the 2008 season" matched = r.count

For an insert that skips duplicates, nrow(df) - count is how many rows already existed:

r = bulk_insert(M.Status.objects, statuses_df, on_conflict = :nothing)
@info "statuses seeded" inserted = r.count already_present = nrow(statuses_df) - r.count

The Mapping Adaptor Strategy ⭐

All bulk operations in PormG use a Mapping Adaptor approach. This means:

• Never Mutates, Never Copies: The pipeline works on its own frame over your table's columns. A column table (a DataFrame, a CSV.File, a NamedTuple of vectors) is read in place, with no copy; a row table (a Vector of NamedTuples) is turned into columns once. Injected defaults and timestamps are added to that internal frame only — see Defaults and Auto Values — so your table is untouched, unconditionally. There is no copy= knob because there is nothing to protect against.
• Flexible Mapping: Use columns = ["df_col" => "model_field"] to map any DataFrame column to any table field.
• No conflicting target mappings: A model field may not be mapped from two different DataFrame source columns. columns = ["c1" => "laps", "c2" => "laps"] raises a QueryBuildError instead of silently letting the later entry win — which column survived used to depend on list position. An unambiguous repeat of the same source (["c1" => "laps", "c1" => "laps"], or a bare "laps" alongside "laps" => "laps") is still accepted, and a bare "laps" the frame has no column for claims no source at all, so it may still be paired with an explicit "c2" => "laps" mapping.
• Auto-Detection: If you don't provide mappings, PormG automatically matches columns to fields by exact, case-sensitive name. A column differing only in case from a model field (e.g. RaceId vs raceid) raises an error instead of being silently folded — normalize first with rename!(df, lowercase.(names(df))) or map it explicitly.
• Centralized Validation: Every row is automatically checked against the model's constraints (max_length, nullability, etc.) before reaching the database.
• Relation Value Semantics: Foreign-key columns accept scalar key values (including 0 if present in the target table). Use nothing or missing when you want SQL NULL on nullable relation columns.

Defaults and Auto Values

For a field the write touches, PormG supplies a value only when your DataFrame has no column for it. A column that is present is your data, and PormG never rewrites its cells.

That is one rule covering every fill kind — a static default, auto_now, auto_now_add, and a UUID auto_add — across bulk_insert(), bulk_copy() and bulk_update() alike, whether or not the field is nullable.

So a blank cell (missing or nothing) in a column you supplied means NULL, exactly as an explicit "field" => nothing does in create(). On a null=false field that blank raises InvalidValueError — "null values are not allowed" — which is PormG's own validation reporting the field by name, not a database constraint error, and it is the same error create() raises for the same input. Nothing is persisted: the write is wrapped in a transaction, so a row that fails validation part-way through rolls back the chunks already sent.

To have PormG supply the value, leave the column out of the DataFrame:

# Stint::Models.ModelType = Models.Model("stint",
#     driver    = Models.CharField(),
#     laps      = Models.IntegerField(default = 0, null = true),
#     pit_stops = Models.IntegerField(default = 0))

df = DataFrames.DataFrame(driver = ["Senna", "Prost"], laps = [missing, 71])

bulk_insert(M.Stint.objects, df)
# laps      → NULL, 71   (your column, your cells — the blank is honored)
# pit_stops → 0, 0       (absent from the frame — PormG fills the default)

bulk_insert(M.Stint.objects, DataFrames.select(df, DataFrames.Not(:laps)))
# laps      → 0, 0       (now absent too, so the default applies)

Qualifications:

• A field you leave out of columns= is out of scope, not "your data". If your frame carries a column for it, those cells are not written — you said not to write that field. On bulk_insert()/bulk_copy() PormG still supplies the field's default or timestamp, which is what lets a partial columns= insert satisfy the model's null=false columns.
• bulk_update(..., columns = [...]) does not synthesize a static default for an absent column — that would overwrite live rows merely because your frame lacks the column. auto_now/auto_now_add still inject, so timestamps stay current — except on a field you use as a match key, which is read and never written (next bullet).
• match_on key columns are matched, never written. A match key stays out of the SET clause entirely, so an auto_now field used as a key is not refreshed by that call — it identifies rows, it is not one of the values being updated. For the same reason, the value PormG would auto-populate for it never serves as its source: a column you supplied wins, and with no column at all the call raises rather than matching every row against one per-call constant.
• match_on key columns are not null-checked. They identify rows rather than being written, so a blank cell in one does not raise — it simply matches nothing, since column = NULL is never true. Before this rule changed, such a blank was quietly back-filled with the field's default and matched the default-valued row instead. (filters= fields are exempt from the check too, but they carry constants rather than DataFrame columns, so no cell can be blank there.)
• An auto-increment primary key column whose values are all blank counts as absent, so the database allocates the ids. A UUIDField(auto_add = true) primary key follows the same rule too — see Auto-Generated Primary Keys for both.

How rows reach the database

bulk_insert() and bulk_update() send each chunk as one statement, but the backends carry the rows differently. SQLite and MySQL have no array parameters, so this divergence is intentional:

For a table whose columns have the types their fields declare — every table PormG created or migrated — what you observe is the same on every engine: the rows written, the returned count, and the errors raised. Only the inspection output differs, because show_query shows what each backend actually runs. On PostgreSQL, show_query = :params returns one vector per column, after any filter values:

bulk_insert(M.Status.objects, statuses_df, show_query = :params)
# PostgreSQL: Any[Any[1, 3, 4, 130], Any["Finished", "Accident", "Collision", "Withdrew"]]
# SQLite:     Any[1, "Finished", 3, "Accident", 4, "Collision", 130, "Withdrew"]

Each cell must hold a single value on every backend. A Vector or a tuple in a text-like column (CharField, TextField, …) raises InvalidValueError naming the field, whatever its elements, since no row source can store it. JSONField and BinaryField values are unaffected: each is serialized to one value first.

An ArrayField (PostgreSQL only) is the other column whose one value is a vector. Each cell holds the Vector (or nothing), and rows may have different lengths. A column of arrays cannot be one two-dimensional array parameter — rows of different lengths are not one array, and unnest flattens every dimension — so each cell travels as its PostgreSQL array text in a text[] parameter and is cast back to the column's type in the statement ("u"."c3"::integer[], source."pit_laps"::integer[]). An ArrayField cannot be a bulk_update match_on key, nor the key returning= matches rows by.

Performance Comparison

⭐ bulk_copy() can be 10-100x faster than bulk_insert() for large datasets.

Bulk Insert

Use bulk_insert() to insert a DataFrame into the database. By default, it chunks data into batches of 1000 rows.

using CSV, DataFrames

# Prepare data
df = CSV.File("drivers.csv") |> DataFrame
# The CSV ships camelCase headers (driverid, driverref, ...); bulk matching is
# case-sensitive, so normalize them to the model's lowercase field names first.
rename!(df, lowercase.(names(df)))

# Bulk insert from DataFrame
query = M.Driver.objects
bulk_insert(query, df)

# Adjust the rows sent per statement
bulk_insert(query, df, chunk_size=500)

Generated SQL (PostgreSQL), for a frame carrying driverref, forename, surname, dob and nationality — one array per column, expanded by unnest, so every chunk sends the same statement:

INSERT INTO "driver" ("driverref", "forename", "surname", "dob", "nationality")
SELECT * FROM unnest($1::varchar[], $2::varchar[], $3::varchar[], $4::date[], $5::varchar[])

SQLite and MySQL render the same call as VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), … — see How rows reach the database.

Conflict Handling (ON CONFLICT)

By default a duplicate key aborts the batch. When overlap is expected — idempotent re-seeding, or concurrent loaders filling one shared dimension table — attach an ON CONFLICT clause with on_conflict= instead of catching the error. PostgreSQL and SQLite (≥ 3.24) share the syntax, so the same call works on both. MySQL renders it as ON DUPLICATE KEY UPDATE, under the limits below.

# The status dimension is re-seeded on every deploy; some rows already exist.
statuses_df = DataFrame(
    statusid = [1, 3, 4, 130],
    status   = ["Finished", "Accident", "Collision", "Withdrew"],
)

# Skip any row whose insert violates a unique constraint
bulk_insert(M.Status.objects, statuses_df, on_conflict = :nothing)

# Skip only when a specific target conflicts
bulk_insert(M.Status.objects, statuses_df,
    on_conflict = (action = :nothing, target = ["statusid"]))

# Upsert: keep the existing row, refresh its label
bulk_insert(M.Status.objects, statuses_df,
    on_conflict = (action = :update, target = ["statusid"], set = ["status"]))

Generated SQL (PostgreSQL):

INSERT INTO "status" ("statusid", "status")
SELECT * FROM unnest($1::bigint[], $2::varchar[])
ON CONFLICT ("statusid") DO UPDATE SET "status" = EXCLUDED."status"

Accepted forms:

• nothing (default) — no clause; a duplicate key raises, exactly as before.
• :nothing — ON CONFLICT DO NOTHING, untargeted: any unique violation skips the row.
• (action = :nothing, target = ["field"]) — skip only when the named columns conflict.
• (action = :update, target = ["field"], set = ["field", ...]) — upsert: on a target conflict, overwrite each set column with the value the batch tried to insert (EXCLUDED.column in SQL). Both target and set are required for :update.

Rules and behavior:

• target and set take logical model field names; PormG resolves db_column mappings and renders the quoted physical column names.
• set columns must participate in the INSERT (be present in the DataFrame / columns= selection). EXCLUDED.col for a non-inserted column would silently write the column default instead of a caller value, so PormG rejects it up front.
• target columns must exist on the model, but are not required to be declared unique/primary_key there (except on MySQL, below) — the database is the source of truth (partial indexes, constraints created outside PormG). A target with no matching constraint surfaces as the backend's own error (PostgreSQL: "there is no unique or exclusion constraint matching the ON CONFLICT specification").
• Dedupe the DataFrame on target first when using :update. A batch that conflicts with itself diverges across engines: PostgreSQL raises "cannot affect row a second time", while SQLite and MySQL apply rows serially (last one wins). unique(df, [:statusid]) before the call keeps behavior identical on every engine.
• With on_conflict set, the duplicate-key → sequence-resync retry is skipped: a conflict is expected, not a symptom of a stale sequence. A duplicate-key error that still surfaces (a different constraint than your target) propagates immediately. The normal post-insert sequence synchronization for explicit primary keys still runs.
• Under DO NOTHING with server-generated primary keys, PostgreSQL still consumes sequence values for skipped rows, and MySQL AUTO_INCREMENT values — the standard harmless gaps.
• The returned count leaves out rows DO NOTHING skipped, on every engine, so nrow(df) - count is the number of duplicates. A DO UPDATE upsert counts each row it inserted or updated. See Return Value.
• bulk_copy() cannot express ON CONFLICT (the COPY protocol has no such clause); use bulk_insert(...; on_conflict=...) when duplicates are possible.
• On MySQL the clause is ON DUPLICATE KEY UPDATE, which names no target and fires on a conflict with any unique key (#1267):
  • on_conflict = :nothing means the same as everywhere: skip a row that conflicts on any unique key. It renders as an update that writes nothing (status = status.status, on the first inserted column that is not AUTO_INCREMENT). Being an update, it locks each existing row it meets until the transaction ends and can fire the table's UPDATE triggers, which PostgreSQL's DO NOTHING does not: two loads that meet the same existing rows in different orders can deadlock on MySQL.
  • A targeted DO NOTHING and a DO UPDATE run only when the target is the one unique key these rows can conflict on, and the target must be a unique key declared on the model (the model is the only source on MySQL: a unique column whose database default the model does not declare as db_default is not seen). Another unique key counts when its columns are inserted and non-NULL in at least one row, or the database fills them; a primary key the DataFrame carries counts, a generated one or a UUID PormG mints does not. Otherwise the call raises BackendCapabilityError (upsert_conflict_target), naming the key, before any SQL.
  • The DO NOTHING count is MySQL's own report: a multi-row INSERT says how many rows it carried and how many met a duplicate, in the server's language, read by position. A one-row chunk reports nothing, so it is sent as a plain INSERT and a duplicate-key error is counted as the skip; any other error (a foreign key, say) still raises, and an open transaction stays usable. A DO UPDATE counts every row of the chunk.
  • A key repeated inside one chunk under DO UPDATE is applied in turn, as on SQLite.

Returning Generated Values

bulk_insert(...; returning = [...]) hands back the values the database wrote — generated primary keys, database defaults, what an upsert left in place — one row per input row, in input order:

rookies = DataFrame(
    driverref   = ["lindblad", "browning"],
    code        = ["LIN", "BRO"],
    forename    = ["Arvid", "Luke"],
    surname     = ["Lindblad", "Browning"],
    dob         = [Date(2007, 8, 8), Date(2002, 1, 30)],
    nationality = ["British", "British"],
    url         = ["", ""],
)

r = bulk_insert(M.Driver.objects, rookies; returning = ["driverid"])
rookies.driverid = r.rows.driverid     # r.rows[i, :] belongs to rookies[i, :]

r.rows is a table of the kind you passed, built by its own Tables.materializer: a DataFrame for a DataFrame, a Vector of NamedTuples for one, a NamedTuple of vectors for a NamedTuple of vectors or a CSV.File. It has one column per requested field (field names, not db_columns) and one row per input row. A row the call did not write is missing — so under on_conflict = :nothing it tells you which rows were skipped (the key already existed, or repeated an earlier row of the frame):

r = bulk_insert(M.Status.objects, statuses_df; returning = ["statusid"], on_conflict = :nothing)
already_present = statuses_df[ismissing.(r.rows.statusid), :]

# Rows in, rows out: no DataFrame needed
rookies = [(driverref = "lindblad", code = "LIN", forename = "Arvid", surname = "Lindblad",
            dob = Date(2007, 8, 8), nationality = "British", url = "")]
r = bulk_insert(M.Driver.objects, rookies; returning = ["driverid"])
r.rows[1].driverid      # r.rows is a Vector of NamedTuples, r.rows[i] belongs to rookies[i]

How rows are matched to your input. No engine promises that a multi-row INSERT reports its rows in input order, so PormG matches them by key value, never by position:

1. With an on_conflict target, by the target columns. Under DO UPDATE the row keeps the primary key it already had, so the target is the only key that finds it — and the returned row carries that existing id and the values just written.
2. Otherwise by the primary key. If your frame carries it (or it is a UUIDField(primary_key = true, auto_add = true), which PormG mints before the insert), that is the key. If it is a single auto-increment IDField the frame leaves out, PormG pre-allocates the ids from the column's own sequence first — exactly what allocate_primary_keys() does — inserts them explicitly, and matches on them. An IDField(generated_always = true) column is written with OVERRIDING SYSTEM VALUE for this, which is safe because the ids came from that identity's own sequence.
3. Anything else — a primary key the database generates by some other means (a server-side default), with no target to match on — raises QueryBuildError rather than guess. Supply the key, or pass a target naming a unique key your frame carries.

Rules and behavior:

• returning takes model field names — one name, or a vector. An unknown name raises UnknownFieldError. The key columns are always read, whether or not you ask for them.
• A target column the INSERT does not carry raises QueryBuildError: it is the key, so the frame must hold it.
• A key repeated inside the frame under DO NOTHING is written once, by its first occurrence; the later occurrences are missing. A row whose key contains a NULL is written but cannot be looked up, so it is missing too — missing means "not correlated", which is "not written" for every row with a complete key.
• Under DO UPDATE, a key repeated inside one chunk diverges across engines exactly as the Conflict Handling rules describe: PostgreSQL raises, SQLite and MySQL apply the rows in turn and every occurrence gets the final row. Dedupe on the target first.
• Keys are compared after the field's formatter, integers and decimals by value, so an integer key loaded as strings ("42", "042") or a decimal the column stores as 1.50 still matches. A returned row PormG cannot match to any input row, or — outside DO NOTHING — an input row that did not come back, raises QueryBuildError rather than reporting missing: that happens when the column normalizes a key in a way PormG does not model, such as a case-insensitive collation. Supply such keys as the database stores them.
• PostgreSQL reads the rows from RETURNING on each chunk's INSERT. SQLite does not: PormG never uses SQLite's RETURNING (it can hang inside libsqlite3 for some table shapes), so each chunk is read back with a SELECT on the same transaction connection. Under DO NOTHING it first reads which keys already exist, so a pre-existing row is never reported as written. The results are the same on both engines; SQLite pays one or two extra statements per chunk.
• With pre-allocated ids the duplicate-key → sequence-resync retry is skipped, as it is under on_conflict: the retry would send the same ids again. A collision there means the sequence had drifted below MAX(pk) before the call — run resync_sequences() and retry. Pre-allocation on PostgreSQL also shares allocate_primary_keys()'s default-schema scope (see its limitations).
• The dry-run show_query modes allocate nothing — drawing a sequence is a write — so the shown statement leaves an allocated pk column out. On PostgreSQL it carries the RETURNING clause.
• bulk_update() and bulk_copy() do not take returning=.
• MySQL reads the rows back as SQLite does, on the transaction's connection; outside DO NOTHING the read locks the rows the statement wrote, so it also finds a row another session committed that an upsert met unchanged. Under DO NOTHING the two reads are plain, so with another session writing the same keys during the call the rows can be wrong in either direction: a row it deletes in between is written by this call but reported missing (under any isolation level), and under READ COMMITTED a row it inserts in between is reported as written by this call, with that session's values. The count is exact either way. It cannot pre-allocate: AUTO_INCREMENT has no sequence to draw from ahead of the INSERT, and a multi-row INSERT's ids are not promised consecutive. So returning for an AUTO_INCREMENT key the table leaves out, with no on_conflict target, raises BackendCapabilityError (preallocated_keys): supply the key in the table, or name a unique key it carries as the target (#1266).

Auto-Generated Primary Keys

Do not prefill an auto-increment primary key with max(id) + 1 before calling bulk_insert() or bulk_copy().

• If the model uses IDField() and the DataFrame omits the primary key column, PormG leaves that field out of the SQL and lets the database allocate ids through its native sequence, identity, or autoincrement mechanism.
• If the DataFrame includes the primary key column but every value is blank (missing, nothing, or an empty string), PormG treats that column as omitted for bulk inserts and COPY as well.
• If you want to load explicit primary key values, provide a value for every row. Mixed blank and explicit values are rejected because the bulk path cannot safely express a row-by-row mix of generated and manual ids.
• A key the caller supplies, IDField(generated = false), is not generated by any engine (#1393). Its column is caller data like any other: a blank cell is a missing value, which the NOT NULL key refuses with InvalidValueError before any SQL is sent, and the column is never left out. A DataFrame that does not carry the column sends an INSERT without the key, which the database refuses.

This keeps id allocation concurrency-safe and avoids the collision risks of a client-side SELECT MAX(id) allocator.

A UUIDField(primary_key = true, auto_add = true) column follows the same absent/all-blank/mixed rules (#334): omit the column, or carry it with every cell blank, and PormG mints a fresh, distinct uuid4() per row; mix blank and explicit cells and it raises, same as an auto-increment pk. allocate_primary_keys() (below) does not support pre-reserving UUID pks, though — there is nothing to reserve from a database sequence. Generate the values locally instead, before the bulk call, if you need them ahead of the insert:

df[!, :token] = [UUIDs.uuid4() for _ in 1:DataFrames.nrow(df)]

Pre-allocating Primary Keys for Cross-Table FK Wiring

Sometimes you need the assigned primary key values before the insert—for example, when you are loading a parent table and a child table at the same time and need to populate a foreign key column.

If the ids are only needed after the parent insert — insert the drivers, then build the results from their ids — returning= is simpler: bulk_insert(M.Driver.objects, drivers_df; returning = ["driverid"]) does the allocation for you and hands the ids back in row order.

On MySQL allocate_primary_keys() raises BackendCapabilityError (preallocated_keys): AUTO_INCREMENT values cannot be drawn ahead of the INSERT.

On a model whose key is IDField(generated = false), a key the caller supplies, allocate_primary_keys() raises QueryBuildError on every engine, whatever the table holds: there is no sequence, identity or rowid to allocate from, so the ids have to come with the data (#1393).

Use allocate_primary_keys() when they are needed before:

drivers_df = CSV.File("f1/drivers.csv") |> DataFrame
rename!(drivers_df, lowercase.(names(drivers_df)))   # camelCase headers → lowercase fields

# Reserve ids from the database sequence without inserting yet.
# After this call, drivers_df has an `id` column with integer values.
drivers_df = allocate_primary_keys(M.Driver.objects, drivers_df)

# Build the results table using the pre-allocated driver ids.
results_df = DataFrame(
    driverid  = repeat(drivers_df.id, inner=num_races),
    raceid    = ...,
    ...
)

# Insert both tables in a single transaction.
PormG.run_in_transaction("db_2") do
    bulk_insert(M.Driver.objects, drivers_df)
    bulk_insert(M.Result.objects, results_df)
end

PostgreSQL: ids are reserved by calling nextval() on the column's sequence. The allocation is atomic and concurrent-safe. Ids consumed by a call that is never followed by an insert (e.g. the transaction rolled back) leave gaps in the sequence; this is expected PostgreSQL behaviour and has no functional impact.

SQLite: Since SQLite has no standalone sequences and only generates IDs at the exact moment of insertion, PormG fully emulates PostgreSQL's behavior to provide a consistent API. IDs are derived from max(MAX(pk), sqlite_sequence.seq) + 1 … + N, and allocate_primary_keys() immediately forces an update to the table's sqlite_sequence counter to the end of that reserved range. Critically, PormG tracks this reserved high-water mark in memory within the TransactionContext during an open transaction. This guarantees that any subsequent create(), bulk_insert(), or allocate_primary_keys() calls for that table within the same transaction will safely skip over the IDs you just reserved. allocate_primary_keys() is self-protecting: when it is not already running inside a transaction it auto-opens one (BEGIN IMMEDIATE plus the in-process write lock), so a bare call is collision-safe even under concurrent writers. Wrapping the full pre-allocation and insertion workflow inside PormG.run_in_transaction is still recommended — that way a rolled-back insert also releases the IDs you reserved, whereas a standalone allocation whose later insert never lands leaves a harmless gap, exactly like PostgreSQL.

The counter protects reserved ids from SQLite's own auto-id inserts only on an AUTOINCREMENT table, which is what PormG's migrations create. On a table declared INTEGER PRIMARY KEY without it (an imported or legacy database), SQLite ignores sqlite_sequence and assigns MAX(rowid) + 1, so a later create() without a primary key does not see the reservation. A database with no AUTOINCREMENT table at all has no sqlite_sequence: allocation then starts from MAX(pk) + 1 and the reservation lives only in the open transaction. On such a table, allocate and insert inside one run_in_transaction.

allocate_primary_keys() returns a new table of the kind you passed (#1344), built by that table's own Tables.materializer: a DataFrame for a DataFrame, a Vector of NamedTuples for one, and a NamedTuple of vectors for a table with no materializer of its own (a CSV.File). Its columns are copies, so your table is never modified: assign the result.

If the table already contains the primary key column with at least one non-blank value, allocate_primary_keys() returns a copy of it unchanged so it is safe to call unconditionally in a data-loading pipeline over models with a generated key. A model with no IDField key (a UUIDField or CharField key, or none) gets a copy back unchanged too.

Notes and Limitations

• Handler filters are ignored. allocate_primary_keys() operates on the model that backs the handler. Any filters or annotations attached to M.Model.objects.filter(...) have no effect — pk allocation is always table-wide.
• Column dtype narrows. If you pass a table with a Vector{Union{Missing, Int}} pk column, the returned column is a plain Vector{Int64}, on every engine. Downstream code expecting the missing-able element type must be adjusted.
• PostgreSQL schema scope. The PG path looks up the sequence via pg_get_serial_sequence('table', 'col') without a schema prefix and therefore assumes the model's table lives in the default search path (typically public).
• No in-place mode. A generic table cannot be written in place, so the key column exists only on the returned table; there is no clone keyword (#1344). Write df = allocate_primary_keys(M.Driver.objects, df).

Pre-processing and Error Handling

CSV data often contains strings like \N for null values. If these are passed to numeric columns, bulk_insert will throw an error. You must pre-process your DataFrame to use Julia's missing.

# Clean the DataFrame before insertion
cols_to_clean = [:position, :milliseconds, :rank]

for col in cols_to_clean
    df[!, col] = map(x -> ismissing(x) || x == "\\N" ? missing : x, df[!, col])
end

# Now the bulk insert will succeed
bulk_insert(query, df)

Atomicity and Transactions

One bulk_insert() call is all-or-nothing on its own: every chunk runs inside a single transaction, so if any chunk fails, the chunks already sent are rolled back with it. bulk_update() and bulk_copy() do the same. Inside an open transaction the call joins it instead of opening its own.

To make several calls succeed or fail together, wrap them in run_in_transaction():

using PormG, LibPQ   # "db_2" is a PostgreSQL connection

# The drivers and their standings land together, or neither does
PormG.run_in_transaction("db_2") do
    bulk_insert(M.Driver.objects, drivers_df)
    bulk_insert(M.Driver_standings.objects, standings_df)
end

Error Handling Examples

# Detect duplicate key errors
# (if duplicates are EXPECTED, prefer on_conflict = :nothing — see Conflict Handling above)
try
    bulk_insert(M.Driver.objects, df)
catch e
    e isa IntegrityError || rethrow()
    @warn "Some rows violate a constraint" msg=error_message(e) adapter=e.adapter
end

IntegrityError is what the database itself refused — UNIQUE, FOREIGN KEY, NOT NULL, CHECK. Match on the type, never on the message: the wording differs between engines and is not part of any contract. On PostgreSQL, e.sqlstate narrows it further ("23505" is a unique violation). The message never quotes the conflicting row, so it is safe to log or show. A database error is safe to show lists the fields each driver reports.

# Pre-validate data before insertion
using DataFrames

df_validated = df[
    (df.forename .!= "") .& 
    (!ismissing.(df.dob)),
    :
]
@info "Validated $(nrow(df_validated)) of $(nrow(df)) rows"
bulk_insert(M.Driver.objects, df_validated)

Memory Efficiency

For very large CSV files, avoid loading the entire file into memory:

using CSV, DataFrames

# Process CSV in chunks
reader = CSV.Reader("massive_drivers.csv"; ntasks=4)
for chunk in Iterators.partition(reader, 5000)
    df_chunk = DataFrame(chunk)
    # Pre-process if needed
    bulk_insert(M.Driver.objects, df_chunk)
end

Ultra-Fast Bulk Inserts (PostgreSQL COPY)

For truly massive datasets, PormG provides bulk_copy(), which uses PostgreSQL's native COPY FROM STDIN protocol. This is 10-100x faster than standard SQL inserts.

Why Use bulk_copy?

• Raw Speed: Bypasses the SQL statement parser.
• Memory Efficient: Streams data to the database.
• Safe by Design: Inherently immune to SQL injection.

Basic Usage

# Fast bulk insert via COPY protocol
query = M.Driver.objects
bulk_copy(query, df)

Generated SQL (PostgreSQL):

COPY "driver" ("forename", "surname", "nationality", "driverref", "dob") FROM STDIN WITH (FORMAT CSV, HEADER FALSE)

Advanced: Column Mapping

If your DataFrame column names differ from the database schema, use the columns parameter:

# Map DataFrame columns to model fields
bulk_copy(query, df_raw, columns = [
    "first_name" => "forename",
    "last_name" => "surname",
    "country" => "nationality"
])

Generated SQL (PostgreSQL):

COPY "driver" ("forename", "surname", "nationality") FROM STDIN WITH (FORMAT CSV, HEADER FALSE)

Sequence Management

After a bulk_copy, PormG automatically updates PostgreSQL SERIAL/IDENTITY sequences so that subsequent calls to create() do not result in primary key collisions.

bulk_insert does the same whenever the DataFrame supplies the primary key (MySQL has nothing to resync: an explicit id moves AUTO_INCREMENT past it). Both resync once per call, after the last chunk, not once per chunk. The whole call runs in one transaction, so nothing between chunks needs the sequence to be current.

# Bulk insert 10,000 drivers
bulk_copy(M.Driver.objects, df_large)

# The ID sequence is automatically synchronized
# Create a new driver—the ID is guaranteed to not collide
new_driver = M.Driver.objects.create(
    "forename" => "Oscar",
    "surname" => "Piastri",
    "nationality" => "Australian",
    "driverref" => "piastri",
    "dob" => Date(2001, 1, 25)
)
# new_driver[:driverid] will be the next available ID after the bulk copy

Row-level writers (create, update_or_create, get_or_create) do not resync automatically — see resync_sequences for repairing a sequence after one of those, or after any load that happened outside PormG entirely.

Real-World Example: Loading F1 Season Data

using CSV, DataFrames, Dates
import .models as M

# Load initial reference data
# The Ergast CSVs ship camelCase headers (circuitid, driverref, ...); bulk matching is
# case-sensitive, so normalize headers to the model's lowercase fields before loading.
circuits_df = CSV.File("f1/circuits.csv") |> DataFrame
rename!(circuits_df, lowercase.(names(circuits_df)))
M.Circuit.objects.exists() && M.Circuit.objects.delete(allow_delete_all=true)
bulk_copy(M.Circuit.objects, circuits_df)

# Load drivers
drivers_df = CSV.File("f1/drivers.csv") |> DataFrame
rename!(drivers_df, lowercase.(names(drivers_df)))
for col in [:number]
    drivers_df[!, col] = map(x -> ismissing(x) || x == "\\N" ? missing : x, drivers_df[!, col])
end
M.Driver.objects.exists() && M.Driver.objects.delete(allow_delete_all=true)
bulk_copy(M.Driver.objects, drivers_df)

# Load races with pre-processing
races_df = CSV.File("f1/races.csv") |> DataFrame
rename!(races_df, lowercase.(names(races_df)))
for col in [:fp1_date, :fp1_time, :fp2_date, :fp2_time, :fp3_date, :fp3_time, :quali_date, :quali_time, :sprint_date, :sprint_time, :time]
    races_df[!, col] = map(x -> ismissing(x) || x == "\\N" ? missing : x, races_df[!, col])
end
# `start_at` is a DateTimeField the CSV does not carry: derive it from `date` + `time` (UTC),
# leaving it NULL where no start time was published.
races_df[!, :start_at] = map((d, t) -> ismissing(t) ? missing : DateTime(Date(d), Time(t)),
                             races_df[!, :date], races_df[!, :time])
M.Race.objects.exists() && M.Race.objects.delete(allow_delete_all=true)
bulk_copy(M.Race.objects, races_df)

# Load results (the largest table)
results_df = CSV.File("f1/results.csv") |> DataFrame
rename!(results_df, lowercase.(names(results_df)))
for col in [:position, :time, :milliseconds, :fastestlap, :rank, :fastestlaptime, :fastestlapspeed, :number]
    results_df[!, col] = map(x -> ismissing(x) || x == "\\N" ? missing : x, results_df[!, col])
end
M.Result.objects.exists() && M.Result.objects.delete(allow_delete_all=true)
bulk_copy(M.Result.objects, results_df)

# Verify all data loaded
@info "Data loaded" \
    circuits=M.Circuit.objects.count() \
    drivers=M.Driver.objects.count() \
    races=M.Race.objects.count() \
    results=M.Result.objects.count()

Bulk Update

bulk_update() updates multiple rows from a DataFrame. It separates three concerns:

• columns= — the participating fields and their mappings. This is the single place a DataFrame column is mapped to a model field ("df_col" => "model_field", or a bare string when the names match). Fields selected by match_on= are used for matching only — they are not SET.
• match_on= — the per-row match keys that identify which row each DataFrame row updates (the merge condition Tb.field = source.field). Bare model field names only; the source column is the columns= mapping for that field when declared, otherwise a DataFrame column with the field's own name. If omitted, the model primary key(s) are used. A match key is only ever read — it is never SET, so a key on an auto_now field does not refresh that timestamp on the call (see Defaults and Auto Values).
• filters= — constant predicates AND'd onto every row's WHERE ("model_field" => value), e.g. "category_id" => 172100 or "points__@in" => [18, 25].

One border crossing. columns= is the only argument where DataFrame names appear; match_on= and filters= always speak the model's field language. That keeps every => in the bulk API meaning the same thing — "df column to model field" — and it appears exactly once.

PormG validates every row, then emits a multi-row UPDATE … FROM a row source — one array per column expanded by unnest(...) on PostgreSQL, a WITH source(...) AS (VALUES ...) CTE on SQLite — and on MySQL a multi-table UPDATE joined to a VALUES ROW(?, …), … derived table (see How rows reach the database).

Basic Usage

# Get existing data
query = M.Result.objects
df = query |> DataFrame

# Modify data in the DataFrame
for row in eachrow(df)
    row.points = row.points + 1
end

# Bulk update specifying the columns to SET and the keys that identify each row
bulk_update(query, df,
    columns=["points"],       # SET: auto-matches 'points' in the DataFrame
    match_on=["resultid"]     # match key: auto-matches 'resultid' in the DataFrame
)

Generated SQL (PostgreSQL): $1 carries every row's points and $2 every row's resultid, so the statement is the same whatever the chunk's row count:

UPDATE "result" AS "Tb"
SET "points" = source."points"
FROM unnest($1::float[], $2::bigint[]) AS source ("points","resultid")
WHERE "Tb"."resultid" = source."resultid"

The source columns are always the model field names (points, resultid), in SET-fields-then-match-keys order — never the DataFrame column names. The DataFrame mapping is applied when reading the values into the parameters, so the SQL you inspect is always expressed in model terms.

# Using explicit mapping (Adaptor style)
# This allows using a DataFrame with totally different column names
custom_df = DataFrame(
    "new_score" => [25, 18, 15],
    "record_id" => [1, 2, 3]
)

bulk_update(query, custom_df,
    columns=["new_score" => "points",       # SET: map 'new_score' to field 'points'
             "record_id" => "resultid"],    # mapping for the match key (not SET)
    match_on=["resultid"]                   # merge key, by model field name
)

Generated SQL (PostgreSQL): the mapped DataFrame names (new_score, record_id) do not appear — the source is named with the model fields they map to (points, resultid):

UPDATE "result" AS "Tb"
SET "points" = source."points"
FROM unnest($1::float[], $2::bigint[]) AS source ("points","resultid")
WHERE "Tb"."resultid" = source."resultid"

Matching and Execution Rules

• Case-sensitive DataFrame matching: PormG resolves columns and match_on against DataFrame column names exactly. A name that differs only in case from the model field (e.g. ID vs id) is rejected with an error that names the candidate column and suggests the fix — either rename!(df, lowercase.(names(df))) or an explicit "DF_COL" => "field" mapping in columns=. (An explicit columns= mapping is always honored, even when its source column differs in case from the field name.)
• Mapping-first match keys: A match_on field with a columns= mapping you declared always uses that mapping as its source. If the DataFrame also carries a column with the field's own name, the mapping still wins and the same-named column is ignored — with a warning, so the ambiguity is visible. "Declared" is the operative word: a value PormG auto-populates for a field columns= left out of scope is not a mapping you wrote, and it never outranks a same-named column you supplied. Your column wins, silently and by design.
• Primary key fallback: If you omit match_on=, bulk_update() infers the model primary key column(s) and expects those columns to be present in the DataFrame (or mapped in columns=). The same source precedence applies.
• Missing column errors: A match_on field with no source — no columns= mapping and no same-named DataFrame column — raises an UnknownFieldError rather than silently degrading to a constant filter. That includes a field PormG would auto-populate (auto_now, or a static default when columns= is omitted — an explicit columns= already suppresses static defaults on an update): an auto-populated value is minted once per call, so matching on it would match no rows, and the error says so instead of reporting a successful no-op. An explicit columns= mapping ("df_col" => "field") whose source column is absent likewise raises — it is never silently bound to a non-existent column.
• Handler filters are kept: filters already attached to the query handler are part of the update's scope — they are AND'd with the match_on= merge condition and the filters= predicates, exactly as update() and delete() honor them. A DataFrame row whose key falls outside that scope matches nothing. See Scoping an update with the handler.
• The handler is never modified: bulk_update() builds its statement from a private copy, so the handler you pass reads the same afterwards — filters= is applied to the statement only, never written back onto it.
• Unsupported handler state raises: a handler carrying limit(), offset(), order_by(), distinct(), an aggregate annotation, a CTE (.with), a cjoin/on/cjoin_on join, or a filter on a values() alias raises UnsafeMutationError — an UPDATE cannot express any of them, and dropping one would widen the statement past what you scoped. A plain values() projection is ignored. A handler filter that traverses a relation ("driverid__surname" => …) raises QueryBuildError, as a joined column does.
• Dry-run support: show_query=:dict and show_query=:inspection return metadata, :sql returns SQL text, :params returns the bound parameter list, and :none builds the statement and returns nothing without executing.
• Empty input is a no-op: An empty DataFrame returns (count = 0, rows = nothing) after logging a warning (nothing under a dry-run show_query mode). The handler checks above still run first, so an unsupported handler raises even with no rows.
• Nullable columns accept all-missing batches: If a nullable update column is missing for every targeted row, PormG writes SQL NULL for every row in that column — including a column that carries a static default, which is not written back over your blanks (see Defaults and Auto Values).

Scoping an update with the handler

The handler you pass is the update's scope, as it is for update(). Scope a re-scoring run to one season by filtering the handler — the DataFrame still carries the per-row values:

season_1988 = M.Race.objects.filter("year" => 1988).values("raceid")

bulk_update(M.Result.objects.filter("raceid__@in" => season_1988), df_rescored,
    columns  = ["points"],
    match_on = ["resultid"])

Generated SQL (PostgreSQL): the handler's filter is AND'd onto the per-row merge condition, so a resultid in df_rescored that belongs to another season updates nothing, and is left out of the returned count (see Return Value):

UPDATE "result" AS "Tb"
SET "points" = source."points"
FROM unnest($2::float[], $3::bigint[]) AS source ("points","resultid")
WHERE "Tb"."resultid" = source."resultid" AND
   "Tb"."raceid" IN (SELECT "R1"."raceid" as "raceid" FROM "race" as "R1" WHERE "R1"."year" = $1)

Handler filters and filters= are combined, and both scope the WHERE clause. They differ in one way: a field named by an equality filters= entry is kept out of the SET clause, while a handler filter never removes a column from SET. Before this contract (#665), bulk_update() discarded the handler's filters and left filters= behind on the handler — see the upgrade guide if you relied on either.

Migrating existing bulk_update() calls

If you have application code written against the older single-filters= API, update each call by splitting filters= into match_on= (row-matching keys) and filters= (constant predicates). The transformation is mechanical:

Rule of thumb:

• If an entry references a DataFrame column it is a per-row key: the bare field name goes to match_on=, and any "df_col" => "field" mapping goes to columns= (a field listed in both is used for matching only — it is never SET).
• If an entry is "field" => value with a literal value (number, string, bool, array, __@ lookup) → it is a constant predicate → leave it in filters=.

How to update your apps: search each project for bulk_update( and inspect its filters=. The fastest path is to run the app or its tests — every old per-row key still passed in filters= now raises a QueryBuildError that names the offending key and tells you to move it to match_on=. Fix them one at a time until the errors stop. Calls that only ever passed constant field => value predicates (or no filters= at all) need no change.

Use Cases

Bulk updates are ideal for:

• Award/penalty application: Adjust points across multiple race results
• Batch corrections: Fix systematic data issues (e.g., unit conversions)
• Bulk status changes: Update fields across many related records
• Data migrations: Transform existing data in place

Example: Adjust Points After Manual Review

query = M.Result.objects
df = query.filter("raceid__year" => 2024) |> DataFrame

# Apply manual adjustments
for row in eachrow(df)
    # Award bonus points for fastest lap
    if row.fastestlapspeed > 350.0
        row.points = row.points + 1
    end
    
    # Penalize for incidents
    if row.statusid == 137  # Collision
        row.points = max(0, row.points - 2)
    end
end

# Bulk update all adjustments in one transaction
PormG.run_in_transaction("db_2") do
    bulk_update(query, df, columns=["points"], match_on=["resultid"])
end

Match Keys and Constant Filters

bulk_update() combines per-row match keys (match_on=, driven by DataFrame values) with constant filters (filters=, the same value for every row).

• match_on=: bare model field names, e.g. ["id"]. Each key's per-row values come from the table — through the columns= mapping when one is declared, otherwise from the same-named column of the table; the keys identify which row each input row updates. A key is matched, never written: it stays out of the SET clause, so an auto_now field used as a match key is not refreshed by that call.
• filters=: ["status" => "active"]. Always a constant predicate AND'd onto the WHERE clause for every row.

# Per-row match key + constant scope guard
bulk_update(query, df,
    columns=["new_points" => "points",
             "record_id" => "resultid"],  # mapping for the match key (not SET)
    match_on=["resultid"],                # match DB 'resultid' against DF 'record_id'
    filters=["raceid" => 1073]            # constant: only results of race 1073
)

Generated SQL (PostgreSQL): again the source columns are the model fields (points, resultid), not the DataFrame names (new_points, record_id). The constant filter binds first, as $1, and the column arrays follow:

UPDATE "result" AS "Tb"
SET "points" = source."points"
FROM unnest($2::float[], $3::bigint[]) AS source ("points","resultid")
WHERE "Tb"."resultid" = source."resultid" AND
   "Tb"."raceid" = $1

The WHERE clause is the match_on= merge condition AND the filters= predicates AND any filters already on the handler (see Scoping an update with the handler). If you used query.filter(...) to read the rows you are about to write, passing that same handler keeps its predicate in the update — which is what you want when the predicate is on the model's own columns, and a QueryBuildError when it traverses a relation (see below).

Join Limits

• Base-table lookup operators are supported: Static filters such as "points__@in" => [18, 25] or "statusid__@isnull" => true are valid as long as they only reference columns on the model being updated.
• Relation traversals that require JOINs are rejected: Constant filters such as "statusid__status" => "Finished" or "raceid__circuitid__country" => "Italy" are not allowed on bulk_update() because the row-source mutation path (unnest(...) / VALUES) cannot safely merge in joined query state.
• Workaround: Read the target rows through a normal joined query, mutate the resulting DataFrame, then write back through a fresh handler matching on the primary key only. The handler must not carry the joined filter: its filters are part of the update's scope, and a joined one is rejected like a joined column.

df = M.Result.objects.filter("statusid__status" => "Finished") |> DataFrame

# Modify data
for row in eachrow(df)
    row.points = row.points + 1
end

# Bulk update by primary key on a fresh handler: the joined "statusid__status"
# predicate would be kept as the update's scope, and a joined filter is rejected.
bulk_update(M.Result.objects, df, columns=["points"], match_on=["resultid"])

Performance Characteristics

• Atomicity: All rows updated together or none.
• Speed: Much faster than individual update() calls (100-1000x for large datasets).
• Memory: PormG never copies the input frame — the pipeline works on a zero-copy wrapper (shared column vectors), and caller-owned data is unconditionally unchanged. Peak memory is bounded by the ORM-side columns it adds internally (e.g. updated_at), not by your data.
• Chunking: For very large datasets, process the DataFrame in chunks:

# Process in chunks for huge datasets
for chunk_df in Iterators.partition(eachrow(df), 10000)
    chunk = DataFrame(chunk_df)
    # Modify chunk
    bulk_update(query, chunk, columns=["points"], match_on=["resultid"])
end