How to run sqlx migrations with Rust application

Question

I am working on a Rust API application which connects to a Postgres database. I recently introduced a new table (order_events) in the database and now I need it to be created with sqlx migration. Initially I had 2 sql scripts in my /migrations folder. So I added a third file with my new table definition as below.

In my backend, I created a model for the same table as a struct and it has database queries (file name: event.rs).Now when I try cargo run I get this error complaining about the new table is missing.

error: error returned from database: relation "order_events" does not exist
  --> /src/models/event.rs:46:9
   |
46 | /         query!(
47 | |             r#"insert into order_events
48 | |         values ($1, $2, $3, $4, $5)"#,
...  |
54 | |             self.state,
55 | |         )
   | |_________^
   |

I already have this code snippet in my project. So I expect the migrations would run as an initial step, but it seems the compilation happens first.

sqlx::migrate!("./migrations")
        .run(&db)
        .await
        .expect("Error running DB migrations");

I used sqlx-cli tool and sqlx migrate run command to overcome this issue in my local develoment environment, but all the build pipelines are failing due to this error.

I have checked on this question as well Running rust sqlx migrations locally with docker-compose but it did not solve my case here.

How can I apply modifications to the database with sqls migration avoiding this error?

Update: In the test pipeline, I tried adding the sqlx-cli installation and sqlx migrate run command execution inside my Dockerfile as a step before the caro test command and it fails to do the installation. Full stacktrace is as below

RUN cargo install sqlx-cli
RUN cd /common/db
RUN sqlx migrate run

INFO[0021] Args: [-c cargo install sqlx-cli]            
INFO[0021] Running: [/bin/sh -c cargo install sqlx-cli] 
    Updating crates.io index
 Downloading crates ...
  Downloaded sqlx-cli v0.7.2
  Installing sqlx-cli v0.7.2
    Updating crates.io index
warning: spurious network error (3 tries remaining): [28] Timeout was reached (Operation too slow. Less than 10 bytes/sec transferred the last 30 seconds)
warning: spurious network error (3 tries remaining): [28] Timeout was reached (Operation too slow. Less than 10 bytes/sec transferred the last 30 seconds)
.
.
.
warning: spurious network error (3 tries remaining): [28] Timeout was reached (Operation too slow. Less than 10 bytes/sec transferred the last 30 seconds)
warning: spurious network error (3 tries remaining): [28] Timeout was reached (Operation too slow. Less than 10 bytes/sec transferred the last 30 seconds)
 Downloading crates ...
  Downloaded colorchoice v1.0.0
  Downloaded anstyle v1.0.4
  Downloaded openssl-macros v0.1.1
  Downloaded equivalent v1.0.1
  Downloaded dirs-sys-next v0.1.2
  Downloaded tokio-macros v2.1.0
  Downloaded utf8parse v0.2.1
  Downloaded zeroize v1.6.0
  Downloaded version_check v0.9.4
  Downloaded url v2.4.1
  Downloaded tracing-core v0.1.31
  Downloaded rand_chacha v0.3.1
  Downloaded unicode_categories v0.1.1
  Downloaded unicode-normalization v0.1.22
  Downloaded unicode-width v0.1.11
  Downloaded unicode-ident v1.0.12
  Downloaded sqlx v0.7.2
  Downloaded openssl-probe v0.1.5
  Downloaded syn v2.0.38
  Downloaded syn v1.0.109
  Downloaded serde_json v1.0.107
  Downloaded promptly v0.3.1
  Downloaded vcpkg v0.2.15
  Downloaded thiserror-impl v1.0.49
  Downloaded unicode-bidi v0.3.13
  Downloaded minimal-lexical v0.2.1
  Downloaded indexmap v2.0.2
  Downloaded radix_trie v0.2.1
  Downloaded itertools v0.11.0
  Downloaded tracing-attributes v0.1.26
  Downloaded tokio-stream v0.1.14
  Downloaded smallvec v1.11.1
  Downloaded rustix v0.38.18
  Downloaded rsa v0.9.2
  Downloaded thiserror v1.0.49
  Downloaded finl_unicode v1.2.0
  Downloaded spki v0.7.2
  Downloaded libc v0.2.149
  Downloaded serde_derive v1.0.188
  Downloaded proc-macro2 v1.0.69
  Downloaded hex v0.4.3
  Downloaded slab v0.4.9
  Downloaded paste v1.0.14
  Downloaded native-tls v0.2.11
  Downloaded openssl v0.10.57
  Downloaded nix v0.23.2
  Downloaded linux-raw-sys v0.4.10
  Downloaded chrono v0.4.31
  Downloaded memchr v2.6.4
  Downloaded libsqlite3-sys v0.26.0
  Downloaded clap_builder v4.4.6
  Downloaded tokio v1.33.0
  Downloaded rand v0.8.5
  Downloaded idna v0.4.0
  Downloaded home v0.5.5
  Downloaded sqlx-postgres v0.7.2
  Downloaded spin v0.5.2
  Downloaded socket2 v0.5.4
  Downloaded hkdf v0.12.3
  Downloaded hashbrown v0.14.1
  Downloaded generic-array v0.14.7
  Downloaded futures-util v0.3.28
  Downloaded num-bigint-dig v0.8.4
  Downloaded nom v7.1.3
  Downloaded futures-task v0.3.28
  Downloaded futures-intrusive v0.5.0
  Downloaded unicode-segmentation v1.10.1
  Downloaded subtle v2.5.0
  Downloaded sqlx-core v0.7.2
  Downloaded sha2 v0.10.8
  Downloaded semver v1.0.20
  Downloaded rustyline v9.1.2
  Downloaded pin-project-lite v0.2.13
  Downloaded mio v0.8.8
  Downloaded libm v0.2.8
  Downloaded cc v1.0.83
  Downloaded ppv-lite86 v0.2.17
  Downloaded pin-utils v0.1.0
  Downloaded digest v0.10.7
  Downloaded clap v4.4.6
  Downloaded base64 v0.21.4
  Downloaded tracing v0.1.37
  Downloaded sqlx-sqlite v0.7.2
  Downloaded serde v1.0.188
  Downloaded num-iter v0.1.43
  Downloaded futures-channel v0.3.28
  Downloaded form_urlencoded v1.2.0
  Downloaded bytes v1.5.0
  Downloaded bitflags v2.4.0
  Downloaded anyhow v1.0.75
  Downloaded allocator-api2 v0.2.16
  Downloaded typenum v1.17.0
  Downloaded sqlx-mysql v0.7.2
  Downloaded spin v0.9.8
  Downloaded pkcs8 v0.10.2
  Downloaded pkcs1 v0.7.5
  Downloaded pem-rfc7468 v0.7.0
  Downloaded parking_lot v0.12.1
  Downloaded openssl-sys v0.9.93
  Downloaded num-traits v0.2.17
  Downloaded instant v0.1.12
  Downloaded futures-io v0.3.28
  Downloaded futures v0.3.28
  Downloaded flume v0.11.0
  Downloaded der v0.7.8
  Downloaded clap_complete v4.4.3
  Downloaded backoff v0.4.0
  Downloaded anstyle-parse v0.2.2
  Downloaded tinyvec v1.6.0
  Downloaded sqlx-macros-core v0.7.2
  Downloaded ryu v1.0.15
  Downloaded rand_core v0.6.4
  Downloaded quote v1.0.33
  Downloaded pkg-config v0.3.27
  Downloaded num-integer v0.1.45
  Downloaded log v0.4.20
  Downloaded iana-time-zone v0.1.57
  Downloaded hmac v0.12.1
  Downloaded hashlink v0.8.4
  Downloaded futures-executor v0.3.28
  Downloaded foreign-types v0.3.2
  Downloaded fastrand v2.0.1
  Downloaded either v1.9.0
  Downloaded crossbeam-queue v0.3.8
  Downloaded clap_derive v4.4.2
  Downloaded cfg-if v1.0.0
  Downloaded cargo_metadata v0.14.2
  Downloaded camino v1.1.6
  Downloaded byteorder v1.5.0
  Downloaded block-buffer v0.10.4
  Downloaded base64ct v1.6.0
  Downloaded autocfg v1.1.0
  Downloaded atoi v2.0.0
  Downloaded async-trait v0.1.73
  Downloaded anstream v0.6.4
  Downloaded whoami v1.4.1
  Downloaded tinyvec_macros v0.1.1
  Downloaded tempfile v3.8.0
  Downloaded strsim v0.10.0
  Downloaded stringprep v0.1.4
  Downloaded sqlformat v0.2.2
  Downloaded signature v2.1.0
  Downloaded sha1 v0.10.6
  Downloaded scopeguard v1.2.0
  Downloaded md-5 v0.10.6
  Downloaded lock_api v0.4.10
  Downloaded foreign-types-shared v0.1.1
  Downloaded fd-lock v3.0.13
  Downloaded event-listener v2.5.3
  Downloaded endian-type v0.1.2
  Downloaded crypto-common v0.1.6
  Downloaded crc-catalog v2.2.0
  Downloaded crc v3.0.1
  Downloaded cpufeatures v0.2.9
  Downloaded cargo-platform v0.1.4
  Downloaded anstyle-query v1.0.0
  Downloaded ahash v0.8.3
  Downloaded sqlx-macros v0.7.2
  Downloaded parking_lot_core v0.9.8
  Downloaded once_cell v1.18.0
  Downloaded num_cpus v1.16.0
  Downloaded nibble_vec v0.1.0
  Downloaded lazy_static v1.4.0
  Downloaded itoa v1.0.9
  Downloaded heck v0.4.1
  Downloaded glob v0.3.1
  Downloaded getrandom v0.2.10
  Downloaded futures-macro v0.3.28
  Downloaded futures-core v0.3.28
  Downloaded errno v0.3.5
  Downloaded dotenvy v0.15.7
  Downloaded crossbeam-utils v0.8.16
  Downloaded const-oid v0.9.5
  Downloaded console v0.15.7
  Downloaded clap_lex v0.5.1
  Downloaded percent-encoding v2.3.0
  Downloaded memoffset v0.6.5
  Downloaded futures-sink v0.3.28
  Downloaded filetime v0.2.22
  Downloaded bitflags v1.3.2
  Downloaded dirs-next v2.0.0
   Compiling proc-macro2 v1.0.69
   Compiling unicode-ident v1.0.12
   Compiling libc v0.2.149
   Compiling autocfg v1.1.0
   Compiling cfg-if v1.0.0
   Compiling version_check v0.9.4
   Compiling serde v1.0.188
   Compiling typenum v1.17.0
   Compiling generic-array v0.14.7
   Compiling const-oid v0.9.5
   Compiling vcpkg v0.2.15
   Compiling quote v1.0.33
   Compiling syn v2.0.38
   Compiling pkg-config v0.3.27
   Compiling cc v1.0.83
   Compiling getrandom v0.2.10
   Compiling futures-core v0.3.28
   Compiling libm v0.2.8
   Compiling smallvec v1.11.1
   Compiling crypto-common v0.1.6
   Compiling block-buffer v0.10.4
   Compiling num-traits v0.2.17
   Compiling subtle v2.5.0
   Compiling once_cell v1.18.0
   Compiling digest v0.10.7
   Compiling memchr v2.6.4
   Compiling pin-project-lite v0.2.13
   Compiling openssl-sys v0.9.93
   Compiling lock_api v0.4.10
   Compiling slab v0.4.9
   Compiling ahash v0.8.3
error: failed to run custom build command for `openssl-sys v0.9.93`
Caused by:
  process didn't exit successfully: `/tmp/cargo-installCNaxQs/release/build/openssl-sys-b766a4d4525b46e4/build-script-main` (exit status: 101)
  --- stdout
  cargo:rerun-if-env-changed=X86_64_UNKNOWN_LINUX_MUSL_OPENSSL_LIB_DIR
  X86_64_UNKNOWN_LINUX_MUSL_OPENSSL_LIB_DIR unset
  cargo:rerun-if-env-changed=OPENSSL_LIB_DIR
  OPENSSL_LIB_DIR unset
  cargo:rerun-if-env-changed=X86_64_UNKNOWN_LINUX_MUSL_OPENSSL_INCLUDE_DIR
  X86_64_UNKNOWN_LINUX_MUSL_OPENSSL_INCLUDE_DIR unset
  cargo:rerun-if-env-changed=OPENSSL_INCLUDE_DIR
  OPENSSL_INCLUDE_DIR unset
  cargo:rerun-if-env-changed=X86_64_UNKNOWN_LINUX_MUSL_OPENSSL_DIR
  X86_64_UNKNOWN_LINUX_MUSL_OPENSSL_DIR unset
  cargo:rerun-if-env-changed=OPENSSL_DIR
  OPENSSL_DIR unset
  cargo:rerun-if-env-changed=OPENSSL_NO_PKG_CONFIG
  cargo:rerun-if-env-changed=PKG_CONFIG_x86_64-unknown-linux-musl
  cargo:rerun-if-env-changed=PKG_CONFIG_x86_64_unknown_linux_musl
  cargo:rerun-if-env-changed=HOST_PKG_CONFIG
  cargo:rerun-if-env-changed=PKG_CONFIG
  cargo:rerun-if-env-changed=OPENSSL_STATIC
  cargo:rerun-if-env-changed=OPENSSL_DYNAMIC
  cargo:rerun-if-env-changed=PKG_CONFIG_ALL_STATIC
  cargo:rerun-if-env-changed=PKG_CONFIG_ALL_DYNAMIC
  cargo:rerun-if-env-changed=PKG_CONFIG_PATH_x86_64-unknown-linux-musl
  cargo:rerun-if-env-changed=PKG_CONFIG_PATH_x86_64_unknown_linux_musl
  cargo:rerun-if-env-changed=HOST_PKG_CONFIG_PATH
  cargo:rerun-if-env-changed=PKG_CONFIG_PATH
  cargo:rerun-if-env-changed=PKG_CONFIG_LIBDIR_x86_64-unknown-linux-musl
  cargo:rerun-if-env-changed=PKG_CONFIG_LIBDIR_x86_64_unknown_linux_musl
  cargo:rerun-if-env-changed=HOST_PKG_CONFIG_LIBDIR
  cargo:rerun-if-env-changed=PKG_CONFIG_LIBDIR
  cargo:rerun-if-env-changed=PKG_CONFIG_SYSROOT_DIR_x86_64-unknown-linux-musl
  cargo:rerun-if-env-changed=PKG_CONFIG_SYSROOT_DIR_x86_64_unknown_linux_musl
  cargo:rerun-if-env-changed=HOST_PKG_CONFIG_SYSROOT_DIR
  cargo:rerun-if-env-changed=PKG_CONFIG_SYSROOT_DIR
  run pkg_config fail: `PKG_CONFIG_ALLOW_SYSTEM_CFLAGS="1" "pkg-config" "--libs" "--cflags" "openssl"` did not exit successfully: exit status: 1
  error: could not find system library 'openssl' required by the 'openssl-sys' crate
  --- stderr
  Package openssl was not found in the pkg-config search path.
  Perhaps you should add the directory containing `openssl.pc'
  to the PKG_CONFIG_PATH environment variable
  Package 'openssl', required by 'virtual:world', not found
  --- stderr
  thread 'main' panicked at '
  Could not find directory of OpenSSL installation, and this `-sys` crate cannot
  proceed without this knowledge. If OpenSSL is installed and this crate had
  trouble finding it,  you can set the `OPENSSL_DIR` environment variable for the
  compilation process.
  Make sure you also have the development packages of openssl installed.
  For example, `libssl-dev` on Ubuntu or `openssl-devel` on Fedora.
  If you're in a situation where you think the directory *should* be found
  automatically, please open a bug at https://github.com/sfackler/rust-openssl
  and include information about your system as well as this message.
  $HOST = x86_64-unknown-linux-musl
  $TARGET = x86_64-unknown-linux-musl
  openssl-sys = 0.9.93
  ', /usr/local/cargo/registry/src/index.crates.io-6f17d22bba15001f/openssl-sys-0.9.93/build/find_normal.rs:190:5
  note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
warning: build failed, waiting for other jobs to finish...
error: failed to compile `sqlx-cli v0.7.2`, intermediate artifacts can be found at `/tmp/cargo-installCNaxQs`
error building image: error building stage: failed to execute command: waiting for process to exit: exit status 101
Cleaning up project directory and file based variables
00:00
ERROR: Job failed: command terminated with exit code 101

Answer 1

Your error is specific to your database implementation. It states that you're missing openssl, so it's a missing dependency in your Dockerfile.

That said, I've been using sqlx database setup to create and migrate the database as necessary.

Answer 2

Take this suggestion with a grain of salt. I have only just started to use sqx with Rust and I had lots of bother with compile and panics because of the way the file/database was invoked/loaded(??).

In my very simple case, I took an example from a blog comment to load the db via the sqlx connection object.

With SQLite for example:

let mut config = sqlx::sqlite::SqliteConnectOptions::new();
config = config.filename( "../data/sqlite.db" );

let pool = SqlitePool::connect_with( config )
    .run ...

Once I had this 'structure' in place, resolving connection errors, etc was a little clearer.

Postgres you will need some kind of connection string ratehr than a simple DB filename of course.

While I realise your issue is with Postgres; I had the same kind of errors and used all kinds of traceback, ... Then I found the format above. Now it works.

In my case the way I specified the database location (URI, etc) was not correct. Using a config object hopefully moves your problem closer to the root cause. Good luck.