search

launchbadge / sqlx

🧰 The Rust SQL Toolkit. An async, pure Rust SQL crate featuring compile-time checked queries without a DSL. Supports PostgreSQL, MySQL, and SQLite.
Apache License 2.0
13.22k stars 1.25k forks source link

CLI: command to execute arbitrary SQL #1375

Open abonander opened 3 years ago

abonander commented 3 years ago

I'm surprised this hasn't been requested on the issue tracker yet.

If you want to just run a SQL snippet on your database, currently you have to reach for something database-specific like psql. I personally use CLion's Database tool, but that can't be used in scripts and is a bit daunting to configure.

Of course, it would also be super convenient to have a CLI SQL client that uses the DATABASE_URL that's already set in your .env, which you don't get with psql.

I'm thinking, for an MVP of this:

sqlx exec: connect to DATABASE_URL and read from stdin a single statement terminated with a semicolon or EOF. This will probably be the most convenient way to directly invoke it in CLI.

sqlx exec -m: connect to DATABASE_URL and read stdin until EOF and issue the whole input as a multi-statement command. For sanity, sqlx exec should assume this flag in piped input mode instead of terminating on the first semicolon.

sqlx exec -c <quoted-statement> [bind-params...]: execute quoted-statement with CLI-delimited bind parameters (optional)

sqlx exec -f <file-path> [bind-params...]: load file-path as a SQL statement and execute with CLI-delimited bind parameters (optional)

All these options should support overriding the database URL, of course. We already have precedent for that with other subcommands.

Not sure what I want the output format to be by default. Similar to psql's, I guess? As a bonus feature we should be capable of outputting CSV or JSON as well, maybe with -o csv or -o json.

Also not really thinking about turning this into a REPL just yet, I'd like to keep this simple with an aim towards scripting and composability.

abonander commented 3 years ago

Looks like there's not really a portable way to check if stdin is attached to a TTY or piped in. So maybe sqlx exec should just exit on the first semicolon?

jwangnz commented 3 years ago

It would be convenient if the CLI could launch a psql command line and connection to database using the parameters defined in the DATABASE_URL.

abonander commented 3 years ago

My ideal is to not even need psql installed for most commands you'd want to issue via CLI. For more advanced dbadmin stuff I personally prefer to reach for a tool with a nice GUI like DataGrip.

We could support subcommands to wrap the native database CLI clients, which I could see being pretty useful, but that's an orthogonal issue, IMO. Go ahead and open a new one if you like.