# Save Scrapy Items to PostgreSQL

Yes. An agent writes an item pipeline that upserts into PostgreSQL, crawls against a real database in its sandbox, and matches the row count to the crawl stats.

[Run this on Rush](https://cloud.getrush.ai/run/save-items-to-postgres/scrapy/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent first reads what the spiders produce and what the project already has for storing it. In a project like scrapy/quotesbot, the two spiders `toscrape-css` and `toscrape-xpath` each yield a plain dict with `text`, `author` and `tags`. `quotesbot/items.py` declares a `QuotesbotItem` with no fields, `quotesbot/pipelines.py` holds a `QuotesbotPipeline` whose `process_item` returns the item unchanged, and in `quotesbot/settings.py` the `ITEM_PIPELINES` block is still the commented-out template. The README's way to keep data is `scrapy crawl toscrape-css -o quotes.json`. The repository has no requirements file, so nothing says which Scrapy version it expects.

It designs the table before the pipeline, because the hard question is what makes two scraped items the same row. The site gives quotes no id, so the agent derives a key from the author and a hash of the text and puts a unique constraint on it; `tags` becomes a `text[]` column, with `first_seen_at` and `last_seen_at` beside it. The table lives in a SQL file in the repository, and the pipeline never creates it, so the role the crawler connects with needs no rights to change the schema. The write is one `INSERT ... ON CONFLICT ... DO UPDATE` that refreshes the tags and `last_seen_at`. A second crawl therefore updates rows and adds none.

The pipeline replaces the placeholder class. `from_crawler` reads a `DATABASE_URL` setting that `settings.py` takes from the environment, and raises `NotConfigured` when it is empty, which makes Scrapy leave the pipeline out and keeps `-o quotes.json` working for anyone without a database. `open_spider` connects, `process_item` validates that `text` and `author` are present and raises `DropItem` when they are not, and `close_spider` commits what is pending and closes the connection. Writes are committed in batches, and a row that fails is logged with its key and rolled back without ending the crawl. The agent enables the class in `ITEM_PIPELINES` and adds a requirements file that pins Scrapy and the PostgreSQL driver at the versions it ran.

Then it runs the crawl against a real database. It installs PostgreSQL in the sandbox with `sudo apt-get install -y postgresql`, creates a database, applies the SQL file and runs `scrapy crawl toscrape-css`. From the stats at the end of the log it takes `item_scraped_count` and compares it with `select count(*)` from `psql`. This project offers a second check for free: both spiders extract the same data, so running `toscrape-xpath` afterwards must leave the count where it was and move only `last_seen_at`. It adds pytest tests that exercise the pipeline against that database: the same item twice gives one row, changed tags overwrite, an item with no text is dropped, and a missing `DATABASE_URL` disables the pipeline.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "A Linear issue labelled data asks for this Scrapy project to store its items in PostgreSQL in addition to feed exports. Read scrapy.cfg, quotesbot/settings.py, quotesbot/items.py, quotesbot/pipelines.py, every spider and README.md first, and say in the pull request what each spider yields and what the pipeline and ITEM_PIPELINES setting look like today. Design a table for the items with a unique key derived from the data, since the site provides no id, and put the DDL in a SQL file; the pipeline must not create tables. Replace the placeholder pipeline: read DATABASE_URL from settings, which take it from the environment, and raise NotConfigured when it is empty so feed exports still work; connect in open_spider; in process_item drop items missing required fields and upsert the rest with INSERT ... ON CONFLICT DO UPDATE; commit in batches; log and roll back a failing row without stopping the crawl; commit and close in close_spider. Enable it in ITEM_PIPELINES. Add a requirements file pinning Scrapy and the PostgreSQL driver at the versions you ran. Install postgresql with apt, create a database, apply the SQL file and run scrapy crawl toscrape-css. Paste item_scraped_count from the stats and select count(*) from psql, and explain any difference. Run toscrape-xpath next and show the count did not change. Add pytest tests against the sandbox database for a duplicate item, changed tags, a dropped item and a missing DATABASE_URL. Do not change the spiders' selectors. Open a pull request that links the issue.",
  "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

The pull request changes `quotesbot/pipelines.py` and `quotesbot/settings.py`, and adds a SQL file, a requirements file and tests. The description shows the two numbers that matter next to each other, `item_scraped_count` from the crawl log and the row count from `psql`, and then the same count after the second spider ran, with one sample row printed before and after so the changed `last_seen_at` is visible. The pytest output follows.

It explains the key it chose and what that choice costs. A key built from the author and a hash of the text treats a quote whose wording was corrected on the site as a new row, and the old one stays with an ageing `last_seen_at`; the description gives the query that finds such rows. It also states that the driver's calls block Scrapy's event loop for the length of each write, which is fine at this volume, and names the point at which larger batches or Twisted's `adbapi` would be worth the extra code.

A reviewer looks at the unique key first, because it decides what counts as a duplicate for as long as the table exists. They also confirm that the SQL file matches how the team manages schema elsewhere, and that `DATABASE_URL` is set only where crawls should write, since an empty value silently turns the pipeline off by design.

## Run it on every Linear issue labelled data

Each new spider tends to bring the same request a week later: put this somewhere we can query. In the console, add an automation to the Project with a `linear` trigger on `linear.issue.labeled` and the label `data`, once Linear is connected under Settings → Connections. The saved prompt covers the general job: read what the spider yields, design the table and its key, write an upsert pipeline, crawl against a database in the sandbox and report scraped items against stored rows. The labelled issue is placed after the prompt, and it supplies the spider's name and the fields that matter.

For a batch, such as every scraper repository in the org, call `POST /o/{org}/sessions` once per repository from a script, each with a `request_id` that is a UUID v5 of the repository's full name. A key runs at most its `max_concurrent` runs at once and the rest answer 202 as `queued`, so the script needs no retry loop.

## Why run it on Rush?

A pipeline is proven by a crawl that ends with the right number of rows. That takes a database server, the schema applied, network access to the site and a second crawl to show the upsert holds. In its sandbox the agent installs PostgreSQL, runs both spiders and keeps the crawl stats and the `psql` output in the run's record. The pull request carries the counts, and a reviewer who doubts them can read the exact commands in the transcript.

It also keeps experiments away from real data. Trying a unique key against the production table, or a shared staging one, risks a half-applied constraint and rows that are hard to remove. The sandbox database is created for the run and discarded with it, so the agent can apply the schema, load it, find that a key was wrong and start again, and none of that reaches a system someone else depends on.

## Frequently asked questions

### Will feed exports with -o still work?

Yes. Feed exports and item pipelines are separate parts of Scrapy, and the pipeline returns each item after writing it. With `DATABASE_URL` unset the pipeline is not loaded at all, so a contributor without a database crawls exactly as before.

### Does it create the table automatically on first run?

No. The DDL is a file in the repository that you apply with your usual migration process. That keeps schema changes reviewable and lets the crawler connect with a role that can only insert and update.

### Can it write to MySQL or SQLite instead?

Yes, name the database in the issue. The pipeline's shape is the same, and the upsert statement and driver change. The agent installs that database in the sandbox and runs the same count check against it.

### What happens to the crawl if the database goes down halfway?

Each failing write is logged with the item's key and rolled back, and the crawl continues, so the feed export, if one is set, still has every item. The description says how to reload the missed rows from that export.

### Should the spiders yield Item classes instead of dicts?

The pipeline works with either. This job leaves the spiders as they are; converting them to declared items or dataclasses is a reasonable follow-up, and the tests added here would cover it.

## More Scrapy jobs

- [Upgrade a Scrapy Project to the Latest Scrapy](https://getrush.ai/run/upgrade-scrapy/scrapy/)
- [Write Tests for Scrapy Spiders](https://getrush.ai/run/write-spider-tests/scrapy/)
- [Add AutoThrottle to a Scrapy Project](https://getrush.ai/run/add-autothrottle/scrapy/)

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

- [Upgrade a FastAPI App from SQLAlchemy 1.4 to 2.0](https://getrush.ai/run/upgrade-sqlalchemy/sqlalchemy/)
- [Add Account Deletion to a Next.js App on Drizzle](https://getrush.ai/run/add-account-deletion/drizzle/)
- [Add Account Deletion to a Supabase App from a Linear Issue](https://getrush.ai/run/add-account-deletion/supabase/)

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