usql 统一的sql命令行工具

> usql -c  '\drivers'                          
Available Drivers:
  avatica [av, phoenix]
  awsathena [s3, aws, athena]
  bigquery [bq]
  chai [ci, genji, chaisql]
  clickhouse [ch]
  cockroachdb (postgres) [cr, cdb, crdb, cockroach]
  cosmos [cm, gocosmos]
  cql [ca, scy, scylla, datastax, cassandra]
  csvq [cs, csv, tsv, json]
  databend [dd, bend]
  databricks [br, brick, bricks, databrick]
  duckdb [dk, ddb, duck]
  exasol [ex, exa]
  firebirdsql [fb, firebird]
  flightsql [fl, flight]
  gocosmos (cosmos) [cm, gocosmos]
  godynamo [dy, dyn, dynamo, dynamodb]
  h2
  hdb [sa, sap, hana, saphana]
  hive [hi, hive2]
  ignite [ig, gridgain]
  impala [im]
  maxcompute [mc]
  memsql (mysql) [me]
  moderncsqlite [mq, modernsqlite]
  mymysql [zm, mymy]
  mysql [my, maria, aurora, mariadb, percona]
  n1ql [n1, couchbase]
  nzgo [nz, netezza]
  oracle [or, ora, oci, oci8, odpi, odpi-c]
  ots [ot, tablestore]
  pgx [px]
  postgres [pg, pgsql, postgresql]
  presto [pr, prs, prestos, prestodb, prestodbs]
  ql [cznic, cznicql]
  ramsql [rm, ram]
  redshift (postgres) [rs]
  snowflake [sf]
  spanner [sp]
  sqlite3 [sq, sqlite]
  sqlserver [ms, mssql, azuresql]
  tds [ax, ase, sapase]
  tidb (mysql) [ti]
  trino [tr, trs, trinos]
  vertica [ve]
  vitess (mysql) [vt]
  voltdb [vo, vdb, volt]
  ydb [yd, yds, ydbs]

> usql --help        
usql, the universal command-line interface for SQL databases

Usage:
  usql [flags]... [DSN]

Arguments:
  DSN   database url or connection name

Flags:
  -c, --command COMMAND                     run only single command (SQL or internal) and exit
  -f, --file FILE                           execute commands from file and exit
  -w, --no-password                         never prompt for password
  -X, --no-init                             do not execute initialization scripts (aliases: --no-rc --no-psqlrc --no-usqlrc)
  -o, --out FILE                            output file
  -W, --password                            force password prompt (should happen automatically)
  -1, --single-transaction                  execute as a single transaction (if non-interactive)
  -v, --set NAME=VALUE                      set variable NAME to VALUE (see \set command, aliases: --var --variable)
  -N, --cset NAME=DSN                       set named connection NAME to DSN (see \cset command)
  -P, --pset VAR=ARG                        set printing option VAR to ARG (see \pset command)
  -F, --field-separator FIELD-SEPARATOR     field separator for unaligned and CSV output (default "|" and ",")
  -R, --record-separator RECORD-SEPARATOR   record separator for unaligned and CSV output (default \n)
  -T, --table-attr TABLE-ATTR               set HTML table tag attributes (e.g., width, border)
  -A, --no-align                            unaligned table output mode (default true)
  -H, --html                                HTML table output mode (default true)
  -t, --tuples-only                         print rows only (default true)
  -x, --expanded                            turn on expanded table output (default true)
  -z, --field-separator-zero                set field separator for unaligned and CSV output to zero byte (default true)
  -0, --record-separator-zero               set record separator for unaligned and CSV output to zero byte (default true)
  -J, --json                                JSON output mode (default true)
  -C, --csv                                 CSV output mode (default true)
  -G, --vertical                            vertical output mode (default true)
  -q, --quiet                               run quietly (no messages, only query output) (default true)
      --config string                       config file
  -V, --version                             output version information, then exit
  -?, --help                                show this help, then exit

mysql：
usql mysql://dts:dts@192.168.31.181:3306/test
usql my://user:pass@host/dbname
usql mysql://user:pass@host:port/dbname
usql my://
usql /var/run/mysqld/mysqld.sock

pg：
usql pg://user:pass@host/dbname
usql pgsql://user:pass@host/dbname
usql postgresql://dts:123456@192.168.31.181:5432/postgres
usql postgres://dts:123456@192.168.31.181:5432/postgres
usql /var/run/postgresql
usql pg://user:pass@host/dbname?sslmode=disable # Connect without SSL

usql pg://localhost/ -f script.sql   执行sql脚本


sqlserver：
usql ms://SA:Abcd1234@192.168.31.181/test
usql sqlserver://SA:Abcd1234@192.168.31.181/test


oracle：
usql or://user:pass@host/sid
usql oracle://user:pass@host:port/sid


sqlite3：
usql dbname.sqlite3
usql file:/path/to/dbname.sqlite3
usql sqlite3://path/to/dbname.sqlite3

以连接MySQL数据源为例

my:dts@192/test=> show tables;
 Tables_in_test 
----------------
 user 
(1 row)

my:dts@192/test=> select * from user;
 id |   name   | status | age 
----+----------+--------+-----
  1 | zhangsan |      0 |  22 
  2 | 李四光   |      0 |  34 
  4 | 张全蛋   |      0 |  28 
  5 | 张全蛋   |      0 |  28 
  6 | 张全蛋   |      0 |  28 
  8 | 赵钱孙李 |      1 |  18 
  9 | 张全蛋   |      0 |  28 
(7 rows)

my:dts@192/test=> delete from user where id =9;
DELETE 1

my:dts@192/test=> alter table user add index idx_name(name);
ALTER TABLE
my:dts@192/test=> show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE "user" (
  "id" int unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id',
  "name" varchar(45) DEFAULT NULL COMMENT 'user name',
  "status" tinyint DEFAULT NULL COMMENT 'user status',
  "age" tinyint unsigned DEFAULT NULL COMMENT 'user age',
  PRIMARY KEY ("id"),
  KEY "idx_name" ("name")
)

可以看到DDL的兼容性还是稍微差了些，但是用于日常的简单使用还是可以的。

mkdir -pv /root/.config/usql

vim  config.yaml 内容如下：

connections:
  mysql_conn: mysql://dts:dts@192.168.31.181:3306/test
  pg16_conn: postgresql://dts:123456@192.168.31.181:5432/postgres
init: |
  \set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]%R%# '
  \set PROMPT2 '... '
  \pset columns 900
  \set AUTOCOMMIT on
  \set PAGER ''
  \set timing on
  \x auto
  \pset null 'NULL'
  \timing on
  \pset border 2
  \pset linestyle ascii
  \set VERBOSITY verbose
  \set ON_ERROR_ROLLBACK interactive
  \set SHOW_ALL_RESULTS on
  \set HISTFILE ~/.usql_history
  \set HISTSIZE 10000
  \set timefmt 'YYYY-MM-DD HH24:MI:SS'
  \set timezone 'Asia/Shanghai'

说明： 
init:脚本通常用于设置环境变量 或其他配置，可通过命令行使用 --no-init`/`标志禁用。

更多配置含义见： https://github.com/xo/usql/blob/master/contrib/config.yaml

后续连接数据库就下面的命令：
usql mysql_conn
usql pg16_conn