# Migrate an Echo and GORM API From SQLite to Postgres

Yes. An agent switches an Echo API's GORM layer from a SQLite file to Postgres by URL, runs the handler tests against a real database in its sandbox and opens a PR.

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

## What the agent does

The agent reads how the database is opened, migrated and torn down before it changes a driver. In an API like xesina/golang-echo-realworld-example-app, `go.mod` is at `go 1.18` with `labstack/echo/v4 v4.1.16`, `jinzhu/gorm v1.9.8`, the first-generation GORM, `dgrijalva/jwt-go v3.2.0`, `swaggo/echo-swagger` and `go-playground/validator.v9`, and `mattn/go-sqlite3 v1.10.0` arrives indirectly through the dialect. `db/db.go` imports `_ "github.com/jinzhu/gorm/dialects/sqlite"`, and `New()` calls `gorm.Open("sqlite3", "./realworld.db")`, prints the error rather than returning it, sets `SetMaxIdleConns(3)` and turns on `LogMode(true)`; `TestDB()` opens `./../realworld_test.db`, `DropTestDB()` removes that file, and `AutoMigrate` registers `User`, `Follow`, `Article`, `Comment` and `Tag`. `main.go` calls `db.New()`, `db.AutoMigrate`, builds the stores and starts Echo on `127.0.0.1:8585`. `handler/handler_test.go` has a `TestMain` whose `setup()` opens `db.TestDB()`, migrates, creates the stores and loads fixtures, and whose `tearDown()` closes the handle and drops the file, so every test in the package shares one fresh SQLite file. The `Dockerfile` builds on `golang:1.12.1` and installs the `sqlite` package in the Alpine runtime image, and `.github/workflows/go.yml` runs `go build -v ./...` and `go test -v ./...` on Go 1.18 with no service container.

The model files decide whether the schema moves cleanly. `model/user.go` uses `gorm.Model`, `unique_index;not null` tags on `Username` and `Email`, and a `Follow` join type whose two keys are tagged `gorm:"primary_key" sql:"type:int not null"`, a column type that is fine on both engines; `model/article.go` has two `many2many` tables, `favorites` and `article_tags`, and `association_autocreate:false` on tags. Nothing uses a SQLite-only function, so `AutoMigrate` produces the same tables on Postgres, with `serial` ids instead of SQLite's rowid aliases. The agent swaps the dialect import for `_ "github.com/jinzhu/gorm/dialects/postgres"`, which brings `lib/pq`, and makes `New()` read a `DATABASE_URL` and call `gorm.Open("postgres", url)`, returning the error to `main.go` instead of printing it, since a wrong URL should stop the process rather than start an API with a nil handle. `SetMaxIdleConns(3)` stays, and `LogMode(true)` becomes a check of an env var so production does not log every query.

The tests are the larger change, because `TestDB()` and `DropTestDB()` are built around a file. The agent gives `TestDB()` a `TEST_DATABASE_URL` and makes `DropTestDB()` drop the model tables and the two join tables through `db.DropTableIfExists` in dependency order, join tables first, so `tearDown()` keeps its meaning without a filesystem. The fixtures in `loadFixtures()` are untouched; the only test that needs a look is any that relied on SQLite's lenient typing, and the agent runs the package to find out rather than reading for it. `go.yml` gets a `postgres` service container with `POSTGRES_HOST_AUTH_METHOD: trust`, a health check, and `TEST_DATABASE_URL` in `env`, so `go test -v ./...` runs against the same engine as production. The `Dockerfile` loses the `sqlite` package from its runtime image, moves off `golang:1.12.1`, which predates the `go 1.18` directive in `go.mod` and cannot build it, to a current `golang` tag, and `README` gets the two URLs and a `docker run` line for a local Postgres. The `jwt-go v3` dependency is left alone and named in the pull request as a separate upgrade, because it has known advisories and nothing to do with the database.

Then it runs both suites. The sandbox has no Docker, so the agent installs Postgres with `apt-get`, starts it, creates `realworld` and `realworld_test` databases, exports both URLs, and runs `go build ./...` and `go test -v ./...`; it reads the `handler` package output for every `Test*` name, since that package is where the stores are exercised through Echo. It then starts the API with `go run .`, registers a user through `POST /api/users`, logs in, creates an article with two tags, and favourites it, reading `psql` afterwards to show the rows in `users`, `articles`, `tags`, `article_tags` and `favorites`, so the many-to-many tables are proven on Postgres and not only compiled. The Swagger route at `/swagger/` is checked once, because `docs/docs.go` is generated and the agent must not regenerate it.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Migrate this Echo and GORM API from SQLite to Postgres. Read go.mod, db/db.go, main.go, every file under model and store, handler/handler_test.go, the Dockerfile and the GitHub workflow first. Replace the sqlite dialect with the postgres dialect of the same GORM major version, open the database from a DATABASE_URL and return the error to main instead of printing it, and make query logging depend on an environment variable. Give the test database a TEST_DATABASE_URL and replace the file drop with dropping the tables in dependency order. Add a postgres service container and the test URL to the workflow. Remove sqlite from the Dockerfile's runtime image and move the build image to a Go version that can build the module. Leave jwt-go and the generated swagger docs alone and say why. Install Postgres in the sandbox, create both databases, run go build and go test -v ./... and quote the handler package's results, then start the API, register a user, create an article with tags, favourite it and show the rows with psql. Open a pull request with every command's 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 that changes `db/db.go`, `main.go`, `go.mod` and `go.sum`, `.github/workflows/go.yml`, the `Dockerfile` and `readme.md`, with the `model`, `store` and `handler` packages untouched except where a test depended on the file. The description shows the old and new `New()` side by side, quotes `go test -v ./...` with each `handler` test named, and shows the `psql` output for those tables after the scripted requests, so a reviewer sees the join tables filled on Postgres.

The `Go` workflow runs on the pull request with its new service container, so the build and test checks are the first run of the suite against Postgres on GitHub's runners. Before merging, a reviewer decides whether to keep a SQLite path for local development, which the pull request drops in favour of a `docker run` line, and schedules the `jwt-go` upgrade the description points at.

## Run it on every service on its last SQLite file

A team moving several small Go services off SQLite starts one run per repository from its own server: a `POST /o/{org}/sessions` each, with this prompt as `input`, the repository in `resources` and a `request_id` that is a UUID v5 of the repository's full name, so a retried call returns the run already started. Each run reads its own `db` package, so a service already on `gorm.io/gorm` gets the `gorm.io/driver/postgres` driver instead of the v1 dialect, and one with raw `database/sql` gets `pgx` and its placeholders rewritten from `?` to `$1`.

The key's `webhook_url` receives `run.completed` for each run, with the `session_url` to read the pull request link from, so the script that started them collects the links when the last run ends; a run that finds a SQLite-only query it cannot translate ends with that query named in its answer rather than a guessed rewrite.

## Why run it on Rush?

A driver swap compiles long before it works: the join tables, the test teardown and the Docker image all assumed a file on disk, and the only honest proof is the suite against a real Postgres plus a few requests that write the many-to-many rows. The agent installs Postgres in its own sandbox, runs the tests and the scripted requests, reads the tables back with `psql`, and fixes the build image that could not compile the module along the way, so the pull request arrives with output rather than a diff to trust.

Every command stays with the run, from `apt-get` to the last `psql` query, and the pull request quotes them. The same migration across a team's Go services costs no more attention than one, because each run starts its own database on its own machine and opens its own pull request.

## Frequently asked questions

### Does the schema change?

The tables and columns stay as `AutoMigrate` defines them from the five model types; ids become Postgres `serial` columns and `unique_index` tags become unique indexes, as before. Existing SQLite data is not migrated by this pull request, and the description names `pgloader` as the tool for a one-off copy if the file holds data worth keeping.

### Can I still run it locally without Postgres?

Not with this pull request, which removes the SQLite dialect so the module no longer needs cgo. The README gets a one-line `docker run` for a local Postgres and the two URLs to export; keeping both dialects behind an environment switch is possible and the description says what it would cost.

### How do the tests clean up without deleting a file?

`DropTestDB()` drops the model tables and the two join tables in dependency order through GORM, join tables first, so `tearDown()` leaves the test database empty for the next run. The workflow's service container starts fresh on every run anyway, and locally the same drop keeps the database reusable.

### Why does the pull request touch the Dockerfile's Go version?

Because the build stage used `golang:1.12.1` while `go.mod` declares `go 1.18`, so the image could not build the module before this change either. Moving to a current `golang` tag and dropping the `sqlite` package from the runtime stage are both needed for the Postgres build to ship.

## More Echo jobs

- [Migrate an Echo App From GORM v1 to GORM v2](https://getrush.ai/run/migrate-to-gorm-v2/echo/)
- [Upgrade Go Modules in an Echo App](https://getrush.ai/run/upgrade-go-modules/echo/)
- [Write Unit Tests for an Echo API](https://getrush.ai/run/write-unit-tests/echo/)

## The same job on other stacks

- [Migrate a Feathers App From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/feathers/)
- [Move a Gin API From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/gin/)
- [Migrate a Sequelize App From SQLite to Postgres](https://getrush.ai/run/migrate-sqlite-to-postgres/sequelize/)

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