Bulk writes, upserts, and RETURNING

save() and delete() on an object, and update()/delete() on a query, cover most writes — see Active Record and ModelCriteria & Query. This page covers the write paths that exist because the per-row approach is either too slow or too racy: insert-or-update in one round trip, loading tens of thousands of rows at once, letting the database compute a new value from the old one, and getting the affected rows back without a second query.

Upsert

doUpsert() inserts a row, or updates it instead if it conflicts with an existing one — one round trip instead of a separate existence check followed by an insert or an update.

$affected = BookQuery::create()->doUpsert(
    // Values to insert.
    ['Id' => 42, 'Title' => 'War And Peace', 'ViewCount' => 1],
    // Values to set instead, if a row with this conflict target already exists.
    ['Title' => 'War And Peace']
);

Column names are phpNames, the same as everywhere else in the query API.

The conflict target defaults to the table’s primary key. Name other columns explicitly with the third argument:

BookQuery::create()->doUpsert(
    ['Isbn' => '978-0140447934', 'Title' => 'War And Peace'],
    ['Title' => 'War And Peace'],
    ['Isbn']                            // conflict on the isbn unique index
);

An empty $updateValues means “do nothing on conflict”.

Computed update values

The values in $updateValues accept a ColumnExpression, so you can compute the new value from the row’s current one:

use Propulsion\Query\ColumnExpression;

// Insert the row, or bump its view count if it's already there.
BookQuery::create()->doUpsert(
    ['Id' => 42, 'Title' => 'War And Peace', 'ViewCount' => 1],
    ['ViewCount' => ColumnExpression::raw(BookPeer::VIEW_COUNT . ' + ?', 1)]
);

Platform differences

Platform	Mechanism	Notes
PostgreSQL, SQLite	ON CONFLICT (...) DO UPDATE SET ... / DO NOTHING	Honors $conflictColumns
MySQL, MariaDB	ON DUPLICATE KEY UPDATE ...	Ignores $conflictColumns; see below
MSSQL, Oracle	MERGE INTO ... WHEN MATCHED ... WHEN NOT MATCHED ...	Honors $conflictColumns

Two MySQL/MariaDB specifics, both inherited from MySQL itself rather than choices Propulsion made:

• $conflictColumns is ignored. MySQL infers the conflict target from any unique or primary key violation and has no syntax for naming one.
• There is no “do nothing” form, so an empty $updateValues throws on MySQL/MariaDB rather than silently inserting.
• The affected-row count is 2, not 1, for a row that was updated (1 for a row inserted fresh). This is documented MySQL C-API behaviour. Propulsion doesn’t normalize it away, because doing so would discard the insert-vs-update distinction some callers want from the return value.

Note

Naming a unique constraint as the conflict target isn’t supported yet — only naming its columns, which is what $conflictColumns does. Unique-index metadata exists at build time but isn’t emitted into the generated runtime TableMap, so there’s nothing to resolve a constraint name against at runtime.

The low-level primitive is BasePeer::doUpsert(Criteria $criteria, Criteria $updateValues, PropulsionPDO $con, array $conflictColumns = []), taking fully-qualified column names.

Database-computed UPDATE values

ModelCriteria::update() normally takes literal values. Pass a ColumnExpression instead to have the database compute the new value from the row’s current one:

use Propulsion\Query\ColumnExpression;

BookQuery::create()
    ->filterById(123)
    ->update(['ViewCount' => ColumnExpression::raw(BookPeer::VIEW_COUNT . ' + ?', 1)]);

// UPDATE book SET VIEW_COUNT = book.VIEW_COUNT + 1 WHERE book.ID = ?

This matters for correctness, not just for saving a round trip. Reading a counter into PHP, incrementing it, and writing it back is a lost-update race: two concurrent requests both read 10, both write 11, and one increment vanishes. A single SET counter = counter + 1 statement has no such window.

ColumnExpression::raw(string $expression, mixed $value = null) takes a raw SQL fragment referencing columns by their fully-qualified names, with at most one ? placeholder bound from $value.

Caution

$expression is raw SQL, not a bound parameter — only the single ? placeholder is bound. Build it from Peer column constants and never from user input.

Computed values only work on update()’s default single-statement path. Passing $forceIndividualSaves = true alongside one throws: that path hydrates rows into PHP objects and re-saves them individually, so there’s no SQL statement for the expression to be spliced into.

Bulk loading rows

BasePeer::doBulkInsert() loads rows through the platform’s native bulk mechanism, bypassing the per-row doInsert() path entirely. It’s roughly an order of magnitude faster than multi-row INSERT for seeding and imports.

use Propulsion\Util\BasePeer;
use Propulsion\Propulsion;

$con = Propulsion::getConnection(BookPeer::DATABASE_NAME, Propulsion::CONNECTION_WRITE);

$rows = [
    ['War And Peace', 1, 2500],
    ['Anna Karenina', 1, 1800],
    // ...
];

$loaded = BasePeer::doBulkInsert('book', ['title', 'author_id', 'price'], $rows, $con);

$columns takes unqualified column names, and each row is a plain ordinally-indexed array of values in that same order. $rows is iterable, so a generator works — you don’t have to materialize the whole import in memory.

This is a low-level path

doBulkInsert() bypasses object hydration entirely, so none of the usual per-row machinery runs: no save(), no preInsert()/postInsert() hooks, no PSR-14 model events, no behavior-injected code, no validators, and no generated primary keys returned — the underlying COPY/LOAD DATA protocols have no mechanism for that. Use it for seeding and imports, not as a general-purpose write path.

Platform support

• PostgreSQL uses a real COPY FROM STDIN via PDO’s pgsqlCopyFromArray(). No temporary file, no extra configuration.
• MySQL/MariaDB use LOAD DATA LOCAL INFILE against a temporary file. This needs setup on both ends — see below.
• SQLite, MSSQL, and Oracle are not supported and throw a PropulsionException. MSSQL’s BULK INSERT requires the file to be readable by the SQL Server process itself, which a client library can’t assume shares a filesystem with it.

Enabling LOAD DATA on MySQL

Both halves are required, and neither can be toggled on an already-open connection:

1. Connection side — PDO::MYSQL_ATTR_LOCAL_INFILE must be set in the datasource’s PDO options (passed at connection construction, not as an attribute afterwards):

   runtime-conf.php

   return [
       'datasources' => [
           'bookstore' => [
               'adapter' => 'mysql',
               'connection' => [
                   'dsn'  => 'mysql:host=localhost;dbname=bookstore',
                   'user' => 'me',
                   'password' => 'secret',
                   'options' => [
                       'PDO::MYSQL_ATTR_LOCAL_INFILE' => ['value' => true],
                   ],
               ],
           ],
       ],
   ];

2. Server side — the local_infile global variable must be 1. It defaults to OFF on stock MySQL 8+.

bulkLoad() checks the connection-side half up front and throws a clear error if it’s missing, rather than letting MySQL fail with a less obvious message. It cannot check the server-side half without querying for it, so a local_infile=0 server surfaces MySQL’s own error.

INSERT ... RETURNING

This one needs nothing from you — it’s described here because it changed what generated SQL looks like.

Every platform now folds generated-ID retrieval into the INSERT statement itself rather than issuing a second round trip for it. PostgreSQL, SQLite, and MariaDB use a trailing RETURNING <pk>; MSSQL uses OUTPUT INSERTED.<pk>; Oracle uses RETURNING <pk> INTO :ret_id with an OUT bind. Plain MySQL has no RETURNING at any version and still uses lastInsertId().

Two consequences visible if you read generated SQL:

• On PostgreSQL, the primary-key column is no longer included in the INSERT column list at all for a plain auto-increment table — the column’s own SERIAL default supplies the value. Previously Propulsion pre-fetched the next sequence value with an explicit nextval() query and inserted it explicitly.
• DBPostgres::getId() (the old pre-INSERT nextval() query) still exists for any direct caller relying on isGetIdBeforeInsert(), but doInsert() never calls it.

UPDATE and DELETE ... RETURNING

BasePeer::doUpdate() and doDelete() take an optional trailing ?array $returningColumns. When given, they return the affected or deleted rows — each an associative array — instead of a plain count:

use Propulsion\Util\BasePeer;
use Propulsion\Query\Criteria;

$selectCriteria = new Criteria();
$selectCriteria->add(BookPeer::PUBLISHER_ID, 3);

$updateValues = new Criteria();
$updateValues->add(BookPeer::PRICE, 999);

$rows = BasePeer::doUpdate($selectCriteria, $updateValues, $con, ['ID', 'TITLE', 'PRICE']);
// [['ID' => 1, 'TITLE' => 'War And Peace', 'PRICE' => 999], ...]

Supported on PostgreSQL, SQLite, and MariaDB (trailing RETURNING col, ...) and on MSSQL (OUTPUT INSERTED.col / OUTPUT DELETED.col). Not supported on Oracle: RETURNING ... INTO only populates a scalar OUT bind per statement, so returning more than one affected row needs BULK COLLECT INTO array binds — a materially larger mechanism than the single-row INSERT case, and not implemented. Plain MySQL has no RETURNING at all.

Note

This is currently only available on the BasePeer primitives. There’s no ModelCriteria-level convenience wrapper exposing it, so a query-level update()/delete() still returns a count.

Related

• Unit of Work — batching a whole graph of inserts, updates, and deletes into one ordered, transactional flush.
• Locking — SELECT ... FOR UPDATE and optimistic concurrency, the other two answers to a lost-update race.
• Advanced queries — the read-side counterparts.
• Supported databases — which of the above works where.