# 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 Rush](https://cloud.getrush.ai/run/add-bulk-inserts/sqlc/). Opens 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`

```json
{
  "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.

## 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

- [Check sqlc Generated Code in CI With sqlc diff and sqlc vet](https://getrush.ai/run/add-codegen-check-to-ci/sqlc/)
- [Add Cursor Pagination to sqlc Queries](https://getrush.ai/run/add-cursor-pagination/sqlc/)
- [Convert a String Column to a PostgreSQL Enum With sqlc](https://getrush.ai/run/add-enum-types/sqlc/)

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

- [Add a Bundle Size Check to a TypeScript Library from a Linear Issue](https://getrush.ai/run/add-bundle-size-check/typescript-library/)
- [Add Caching to a Flask App](https://getrush.ai/run/add-caching/flask/)
- [Add Caching to a Streamlit App](https://getrush.ai/run/add-caching/streamlit/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
