sqlx-cli: provide generic Linux binaries for CI

[dsferruzza]

Hi!

I'm using sqlx-cli in my CI jobs, mainly to run/test migrations or run cargo sqlx prepare --check. Installing it (with cargo install sqlx-cli --no-default-features --features postgres) takes by itself around 5 minutes.

Would that make sense to provide generic Linux binaries in GitHub Releases? This way any user that needs sqlx-cli for its CI jobs could just download it. (Binaries generation seem to be already setup with GitHub Actions.)

[bsodmike]

Couldn't we do this with just another docker image with a pre-compiled binary of sqlx-cli? This would be nice to have.

[azzamsa]

takes by itself around 5 minutes.

Yes, with the binaries available. We can maximize the use of taiki-e/install-action. Installing sqlx-cli was the longest task I have in the CI.

[azzamsa]

I have migrated my CI to use auto-migration instead of sqlx-cli. Maybe it helps.

 ci: sqlx had auto migration

Currently, `sqlx-cli` installation took the longest duration.
Now it goes down from 5m 29s to 4m 19s.

diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index 066566c..2798037 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -62,11 +62,6 @@ jobs:
         with:
           tool: just
 
-      - name: Install latest sqlx-cli release
-        uses: taiki-e/install-action@v1
-        with:
-          tool: sqlx-cli
-
       - name: Cache
         uses: Swatinem/rust-cache@v2
 
@@ -75,16 +70,14 @@ jobs:
           cp .example.env .env
 
       - name: Prepare the database
-        run: |
-          docker-compose -f ./tests/docker-compose.yml run -d -p 5432:5432 --name postgres_db postgres_db
-          docker exec postgres_db bash -c "until pg_isready; do sleep 1; done"
-
-      - name: Migrate the database
         env:
-          DATABASE_URL: postgres://superuser:secret@localhost:5432/tin
+          PGPASSWORD: secret
         run: |
-          sqlx database create
-          sqlx migrate --source db/migrations/ run
+          docker-compose --file ./tests/docker-compose.yml run --detach -p 5432:5432 --name postgres_db postgres_db
+          # Wait until the DB is up
+          docker exec postgres_db bash -c "until pg_isready; do sleep 1; done"
+          # Check DB version
+          docker exec postgres_db psql -h localhost -p 5432 -U superuser --version
 
       - name: Run tests
         env:
diff --git a/tests/docker-compose.yml b/tests/docker-compose.yml
index b93db41..add3452 100644
--- a/tests/docker-compose.yml
+++ b/tests/docker-compose.yml
@@ -8,3 +8,4 @@ services:
     environment:
       POSTGRES_USER: superuser
       POSTGRES_PASSWORD: secret
+      POSTGRES_DB: tin

• https://github.com/azzamsa/tin/commit/bf456e35b63765aa923791689d2635cdd210cd27?diff=unified

[dsferruzza]

@azzamsa You mean that your app now run migrations automatically when it starts?

If so, that does not feel like solving the whole issue:

• you still need offline SQLx feature (or a migrated database) to compile the app (if you use checked macro queries in your code; but as it is a really cool feature of SQLx, I will assume that you do)
• you cannot test down migrations

[azzamsa]

you still need offline SQLx feature (or a migrated database) to compile the app

Correct.

you cannot test down migrations

I don't use this feature in my current app.

My requirements are basic. So yes. I agree that this ticket is still relevant.

[divyenduz]

Would love to see this move forward, happy to also contribute it, if we can decide on an approach.

[haruska]

Agreed that this ticket is still relevant.

I found you can use Swatinem/rust-cache to cache the bin in GH actions. Something like:

jobs:
  test:
    name: Test
    runs-on: ubuntu-latest
    services:
      db:
        image: postgres
        env:
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v3
      - uses: dtolnay/rust-toolchain@stable
      - uses: Swatinem/rust-cache@v2
        with:
          cache-all-crates: true
      - name: Migrate database
        run: |
          cargo install sqlx-cli --no-default-features --features native-tls,postgres
          sqlx migrate run
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/postgres
      - name: Run tests
        run: cargo test
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/postgres

[wawrzek]

Why not to provide binaries outside of CI (where lack of them is really painful)?

You don't want to build anything in the production system. I might be wrong here, but there is no way to run sqlx migration other than with sqlx-cli.

[CommanderStorm]

@wawrzek Your comment seems a bit weird. Are you using https://docs.rs/sqlx/latest/sqlx/macro.migrate.html and why is a one time installation bad?

[wawrzek]

@CommanderStorm thanks for asking. Your question indicates that you assume that I'm a programmer. I'm not. For simplicity, let's assume I don't have even access to source code, so I cannot answer the question. ;-)

The scenario is:

• programmers writes code, run initial test etc.
• when ready, the binaries along the SQL migration produce by sqlx are copied over to a 'non-development environment', for further steps (or to run in production).
• in that environment, which does not have any compilers, the migrations have to be run. I was told that these migrations can only be run via sqlx-cli, otherwise 'bad' things going to happen.

The last step is achieved with a help of CD pipeline. At the moment it requires to building sqlx-cli, with copying extra packages every run.

In general, from what I understand, sqlx-cli is more an 'ops', than 'dev' tool, which suppose to be run after development. Such tools are provided in binary form. Ideally from a well established and trusted source. The original project page is probably the best place.

I guess for now I'm going to build it binaries for internal usage.

[abonander]

We're not against hosting binaries, but currently the release process is entirely manual. I hand-write the CHANGELOG entry with seed data from a Bash script, then type in the commands to publish new versions, by hand, in a terminal on my own computer. Adding steps to compile binaries for every supported platform for every release would just be too much.

Should all this be automated? Absolutely. I've been meaning to look into it. But I only have so much time in a week to spend on this project, and most of it is spent on triaging issues and reviewing PRs.

I know cargo-dist exists, but I'd likely spend as much time learning how it works and deciding whether or not I can trust it, as it would take to just write the Github Actions workflow myself.

[CommanderStorm]

I was told that these migrations can only be run via sqlx-cli, otherwise 'bad' things going to happen.

That sounds.. a little sus.. Ask your developers to be more specific than just 'bad'. The advantage of baking the migrations into the binary is that you can spend your time on things that make more productively than applying migrations.

For rolling back you might need the CLI depending on your tooling, but not for applying..

[abonander]

@CommanderStorm I'm going to ask that you please drop this. I don't want this to become a debate. Especially when the subject matter is, by literal definition, hearsay.

We can't assume what their workflow looks like or what their constraints are. Embedding the migrations may not be acceptable in their case. You just don't know.

@wawrzek if you're looking for more help, I would appreciate if your developers could share some more in-depth technical details here.

However, I can tell you that we do just build our own container images internally with sqlx-cli and other required tooling built-in. It's not that big of a deal.

[CommanderStorm]

Sorry

[wawrzek]

@abonander thanks for the response. Would a GitHub action which build binaries on a new tag be a good starting point? Something in line with: https://github.com/projectglove/glove-monorepo/blob/main/.github/workflows/release.yml

The above script is a bit more complicated than required for sqlx-cli. My understanding is that for this project, a script would need to:

• run cargo build
• upload artifacts (sqlx-cli binary) - actions/upload-artifact@v4
• prepare a release - softprops/action-gh-release@v2

The last action allows you to do more than just upload binaries, which might be useful for your project. I just didn't investigate them.

I see that cargo-dist (cargo-dist) do much more, having installers etc. would be nice, but simple binary is a good first step.

Anyway, if my thinking going in the right direction, I can look at spending some time on that.

@CommanderStorm - developers gave better explanation than just 'bad'. It was some time ago and I don't remember details. I didn't want to postpone writing the answer waiting to get explanation again. Therefore, I wrote just 'bad'. However, I'm not sure if they considered baking migrations into binaries. That's an interesting hint. I'll investigate it further.

[wawrzek]

@abonander I've created a test workflow in my fork and it works. The yaml file is here: https://github.com/ivy-net/sqlx/blob/main/.github/workflows/sqlx-cli-release.yml

The binaries can be found here: https://github.com/ivy-net/sqlx/releases/tag/v0.8.2

If you are interested, I can prepare a proper PR.