Add upsert() for INSERT ... ON DUPLICATE KEY UPDATE - #1
Merged
Merged
Conversation
insert()/update()/delete() cover every other write path, but "insert this row, or update it if a unique key already exists" -- one of the most common write patterns (sync jobs, idempotent writes, counters) -- had no terminal helper, forcing callers to hand-write raw SQL just for this one case and step outside the bindings-only guarantee the rest of the library exists to provide. upsert() builds on the existing insert() (reusing its empty-data guard and column/binding handling) and appends ON DUPLICATE KEY UPDATE for the given columns, defaulting to refreshing every inserted column.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
1 participant
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
QueryBuilder::upsert(string $table, array $data, array $updateColumns = []): arraycompiling MySQL/MariaDB'sINSERT ... ON DUPLICATE KEY UPDATE.insert()(reuses its column/placeholder compilation, its bindings-only value handling, and its empty-$dataguard) rather than duplicating that logic.$updateColumnsdefaults to refreshing every column in$dataon conflict; pass a subset to only refresh those. Every entry must be a key of$data, orupsert()throwsInvalidArgumentException.Why
insert(),update(), anddelete()cover every other write path, but "insert this row, or update it if a unique/primary key already exists" — one of the most common write patterns (idempotent writes, sync jobs, counters) — had no terminal helper. Without it, a caller needs this pattern has to hand-write raw SQL just for that one case, stepping outside the bindings-only guarantee that's the whole point of this library.Changes
src/QueryBuilder.php— newupsert()method.tests/run.php— 8 new test cases: default update-columns behavior, explicit$updateColumnssubset, the SQL-injection regression check applied toupsert()(matching the existing pattern forwhere()/insert()/delete()), the inherited empty-$dataguard, and a new guard for an$updateColumnsentry that isn't a key of$data.README.md/CHANGELOG.md— documented the new method with usage examples.Test plan
php -lonsrc/QueryBuilder.phpandtests/run.php— clean.php tests/run.php— all 55 checks pass (47 existing + 8 new), including the full end-to-end SQLite execution suite for the pre-existing methods.ON DUPLICATE KEY UPDATEis MySQL/MariaDB-specific syntax (SQLite has no equivalent), soupsert()is verified via SQL-string/bindings assertions, the same way the majority of this suite already verifiesinsert()/update()/delete(), rather than added to the live-SQLite integration section.🤖 Generated with Claude Code