All jobs

Add Bulk Inserts to a sqlc and pgx Project

Yes. The agent picks :copyfrom or a batch query for each insert, regenerates the code with the pinned sqlc version, updates the mock and tests against PostgreSQL.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

Which bulk insert sqlc can generate depends on the driver package, so the agent reads sqlc.yaml first. In a project like techschool/simplebank it is a version 2 config for the postgresql engine with sql_package: "pgx/v5", output in db/sqlc and emit_interface: true. That driver supports both of sqlc's bulk forms: a :copyfrom query, which uses PostgreSQL's COPY protocol, and the :batchexec, :batchone and :batchmany queries, which send many statements in one round trip. Every generated file's header says sqlc v1.22.0, and the agent installs exactly that release. Running sqlc generate with it on the untouched repository produces no diff, which is the baseline that makes the later diff readable.

Then it chooses a form per insert, because the two are not interchangeable. :copyfrom is the fast one and returns only a row count. On sqlc 1.22.0 it also refuses anything beyond plain parameters: a query with RETURNING stops with :copyfrom is not compatible with RETURNING, and one with a literal such as now() among the values stops with :copyfrom doesn't support non-parameter values. So loading many rows whose ids are not needed afterwards, entries from an import for example, gets a -- name: CreateEntries :copyfrom beside the existing CreateEntry in db/query/entry.sql. An insert whose caller needs the rows back gets :batchone. TransferTx in db/sqlc/tx_transfer.go is the instructive case. It creates two entries with two CreateEntry calls and returns both as FromEntry and ToEntry, so COPY cannot serve it, and for two rows the agent leaves it alone and says why.

Generation changes more than the new method, and the agent follows each change to where it lands. A :copyfrom query adds db/sqlc/copyfrom.go, with an iterator type that implements pgx.CopyFromSource, and adds a CopyFrom method to the DBTX interface in db/sqlc/db.go; a batch query adds batch.go and SendBatch. Both the pool and a pgx transaction satisfy the wider interface, so execTx in db/sqlc/exec_tx.go keeps working and a bulk insert can run inside it. The break is elsewhere. Store embeds the generated Querier, and db/mock/store.go is a mockgen file that no longer implements it: go vet ./api/ fails with *mockdb.MockStore does not implement db.Store (missing method CreateEntries). The agent regenerates the mock with the make mock target and the mockgen version the README installs, and keeps that in its own commit so the reviewer can skip over generated noise.

The tests run against a real PostgreSQL, as the existing ones in db/sqlc do through TestMain in main_test.go and the DB_SOURCE in app.env. The Makefile's postgres target assumes Docker, which the sandbox lacks, so the agent installs PostgreSQL from system packages, creates the root role and the simple_bank database, and applies db/migration with golang-migrate as make migrateup does. New tests in entry_test.go copy a slice of entries for a random account and read them back with ListEntries, pass an empty slice, and include one row whose account_id does not exist to show that the foreign key rejects the whole copy and leaves nothing behind. A last one runs the copy inside execTx with a function that returns an error and asserts no rows remain. make test runs the full suite.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "The linked issue asks for a way to insert many rows at once in this Go project that uses sqlc. Read sqlc.yaml, the Makefile, every file in db/query and db/migration, the hand-written files in db/sqlc (store, transaction helpers, tests), db/mock, the README's tool versions and the test workflow first. Install the sqlc version named in the generated files' headers and confirm that sqlc generate on the untouched repository produces no diff. For each insert the issue names, choose between a :copyfrom query and a batch query and say why: whether the caller needs the inserted rows back, whether the values are all plain parameters, and how many rows are expected. Add the queries next to the existing single-row ones without removing those. Regenerate, then build and vet the whole module and fix every place the wider generated interfaces break, including mocks, using the repository's own make targets and tool versions; keep regenerated mocks in a separate commit. Do not hand-edit generated files. Install PostgreSQL from system packages, create the role and database the test config expects, apply the migrations with the migration tool the Makefile uses, and add tests against that database: many rows inserted and read back, an empty input, a row that violates a foreign key, and a bulk insert inside the project's transaction helper that is rolled back. Run make test. Open a pull request that closes the issue with the choice made for each insert, the generated files that changed, any sqlc errors you hit and how the query was changed, and the test output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

A pull request in three commits: the new queries with their generated code, the regenerated mock, and the tests with whatever call site the issue asked for. The description has a short table of inserts, each with the form chosen and the reason, including the ones deliberately left as single-row calls. Any error sqlc printed on the way, such as the RETURNING refusal, is quoted with the query as first written and as fixed.

The generated-code section lists files rather than pasting them: copyfrom.go or batch.go added, db.go with its wider DBTX, querier.go with the new methods, and the mock. It names the sqlc and mockgen versions used and repeats that the baseline regeneration was clean, so every changed line traces back to a query in this pull request. The make test output from the sandbox's PostgreSQL closes it, with the server version.

A reviewer reads the foreign-key test and the rollback test, since those state the failure behaviour the callers will rely on: a copy is all or nothing. They also decide on input size. COPY streams, but the generated method takes a slice that is held in memory, so an import of unbounded size should be chunked by the caller, and the description says where.

Run it on every Linear issue labelled bulk-import

Bulk paths get requested one table at a time, usually when an import job starts timing out. Add an automation to the Project in the console with a linear trigger on linear.issue.labeled and bulk-import in its labels. The saved prompt is the method: pin the generator, prove a clean baseline, choose the form per insert, follow the interface changes into the mocks, test against a real database. The issue is appended after the prompt and names the table and the caller.

The automation runs as the member who last changed it and needs that member's Linear connection, made under Settings → Connections. Its max_runs_per_hour limits how many of a batch of labelled issues start in the same hour, which matters here because every run regenerates the same querier.go and the pull requests would conflict.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

Generated code is only trustworthy when it comes from the versions the repository already uses, and a developer's machine has whichever sqlc and mockgen were installed last. The sandbox starts empty, so the agent installs the versions it read from the files, shows that regenerating changes nothing, and only then adds queries. The commands and their output are kept in the transcript at GET /o/{org}/sessions/{id}/messages, including the errors the generator printed.

The behaviour that matters, a copy that fails as a whole and a batch that rolls back with its transaction, has to be seen on PostgreSQL to be believed. The agent has a server of its own with the project's migrations applied. The pull request therefore carries tests that ran, not a query file that compiled, and the issue's author did not have to stop their own work to get it.

Frequently asked questions

When should I use a batch instead of :copyfrom?

When you need rows back, when the statement is not a plain insert, or when you want ON CONFLICT handling. A batch sends ordinary statements together, so anything a single query can do still works. :copyfrom is for many plain rows where a count is enough.

Does this work with database/sql or lib/pq?

The batch annotations are for pgx only, and :copyfrom generation for PostgreSQL also relies on pgx's copy support. A project on database/sql needs its sql_package changed first, which alters every generated signature and is a larger migration. The agent checks the config and says so before writing queries that cannot be generated.

Can a bulk insert run inside an existing transaction?

Yes. The generated methods are on Queries, and the project's helper builds a Queries from a pgx transaction, which has both CopyFrom and SendBatch. One of the new tests does exactly that and rolls back.

Why is the mock in a separate commit?

Regenerating it can change lines unrelated to the new method, for instance comments that differ between mockgen releases. Keeping it apart lets a reviewer read the commit with the queries and tests closely and confirm that the other one is purely tool output.

More sqlc jobs

More jobs like this: Turn a Linear label into a PR

Keep reading