sqlx

Create the database specified in the DATABASE_URL environment variable

$ sqlx database create

$ sqlx database drop [[-D|--database-url]] [database_url]

$ sqlx migrate add -r [migration_description]

$ sqlx migrate run [[-D|--database-url]] [database_url]

$ sqlx migrate revert [[-D|--database-url]] [database_url]

sqlx [OPTIONS] <SUBCOMMAND>

--database-url <URL>
    Specifies the database connection URL. This can also be set via the DATABASE_URL environment variable.

--config <FILE>
    Path to a TOML configuration file for sqlx.

--force
    Forces an operation to proceed, ignoring some warnings or checks (e.g., for dropping databases).

--offline
    Performs operations that don't require a database connection, primarily for migration generation.

--color <WHEN>
    Controls colored output (always, auto, never).

--verbose
    Enables verbose logging output.

--quiet
    Suppresses output messages.

--version
    Prints the version of sqlx-cli.

--help
    Prints help information for the command or a subcommand.

The sqlx command-line interface (CLI) is a powerful tool designed to complement the sqlx Rust asynchronous SQL crate. It provides essential utilities for database development, primarily focusing on managing database schemas through migrations and assisting with the pre-compilation of SQL queries. This CLI allows developers to create, drop, and manage databases, apply or revert migrations, and prepare SQL queries for optimal performance within sqlx applications. It supports various databases like PostgreSQL, MySQL, SQLite, and Microsoft SQL Server, aligning with the capabilities of the underlying sqlx library. By automating common database tasks, sqlx CLI streamlines development workflows, ensures schema consistency across environments, and helps leverage sqlx's compile-time query checking features.

The sqlx CLI is a Rust application and needs to be built and installed using cargo (cargo install sqlx-cli). It requires the Rust toolchain to be set up. For database-specific operations, underlying database client libraries might be necessary (e.g., libpq-dev for PostgreSQL, MySQL client libraries for MySQL). It does not provide a database server; it interacts with existing ones.

The sqlx CLI operates primarily through subcommands, each addressing a specific category of tasks:

database
Manages database instances. Common actions include sqlx database create (creates a new database), sqlx database drop (drops an existing database), and sqlx database reset (drops and recreates the database, running all migrations).

migrate
Handles database schema migrations. This is one of the most frequently used subcommands. Examples include sqlx migrate add <name> (creates a new migration file), sqlx migrate run (applies pending migrations), sqlx migrate revert (reverts the last applied migration), and sqlx migrate info (displays the status of all migrations).

prepare
Scans your Rust project for SQL queries used with sqlx and caches them. This step is crucial for enabling sqlx's compile-time query checking, which ensures your SQL queries are valid against your database schema before runtime. Run with sqlx prepare --check to just check or sqlx prepare to generate the cache.

info
Provides detailed information about the connected database, including its type, version, and the current status of the sqlx CLI and its configuration.

The sqlx Rust library was first released in 2019, quickly gaining traction for its asynchronous capabilities and compile-time query checking. The sqlx CLI was developed concurrently as a companion tool to simplify common database development tasks that are not directly handled by the library itself. Its primary focus has been on robust migration management, a critical aspect of database-backed applications, and later extended to include features like prepared query caching, making it an indispensable part of the sqlx ecosystem. It's a relatively modern tool, reflecting the advancements in async Rust and modern database development practices.

psql(1), mysql(1), sqlite3(1), cargo(1)