ZQL ODBC Database Query Tool
Versions
| Version | Released | Change |
|---|---|---|
| 1.0.0 | 15-Mar-2026 | Initial version. |
Contents
Introduction
ZQL is a command line tool for querying databases via ODBC.
Installation
To install ZQL from the command line using Cargo:
$ cargo install zql-cli
To install MySQL database server, see the MySQL quick guide.
To install SQLite3 database server on Ubuntu:
$ sudo apt install sqlite3
To install ODBC database drivers on Ubuntu:
$ sudo apt install unixodbc-dev
$ sudo apt install libsqliteodbc
$ sudo apt install mysql-connector-odbc
To verify ODBC database drivers on Ubuntu:
$ odbcinst -q -d
[SQLite]
[SQLite3]
[MySQL ODBC 9.4 Unicode Driver]
[MySQL ODBC 9.4 ANSI Driver]
Command Line Usage
Configure Connection Strings
ODBC connection strings are stored in configuration file ~/.config/zql/drivers.yaml on Linux, or C:\Users\username\.config\zql\drivers.yaml on Windows.
Connection strings can be added and removed with subcommand zql config, and a single connection can be marked as default with the --default flag:
$ zql config clear
$ zql config add mysqla 'Driver={MySQL ODBC 9.4 ANSI Driver};Server=localhost;Database=hugos;User=hwalters;Password='
$ zql config add mysql 'Driver={MySQL ODBC 9.4 Unicode Driver};Server=localhost;Database=hugos;User=hwalters;Password='
$ zql config add sqlite 'Driver=SQLite3;Database=/etc/data/hugos.db' --default
$ zql config remove mysqla
Connection strings can be listed in zql config add format, which can be copy/pasted into a terminal window to populate the configuration file on another machine:
$ zql config list
zql config add mysql 'Driver={MySQL ODBC 9.4 Unicode Driver};Server=localhost;Database=hugos;User=hwalters;Password='
zql config add sqlite 'Driver=SQLite3;Database=/etc/data/hugos.db' --default
Sample connection strings for a variety of database systems can be found at www.connectionstrings.com.
Execute SQL from Command Line
SQL commands can be run directly from the command line with subcommand zql exec:
$ zql exec "SELECT * FROM Book JOIN Author ON Author.Id = Book.AuthorId LIMIT 5"
Year,AuthorId,Article,Title,Genre,Id,Forename,Surname
1953,1,The,Demolished Man,SF,1,Alfred,Bester
1956,2,,Double Star,SF,2,Robert A,Heinlein
1958,3,The,Big Time,SF,3,Fritz,Leiber
1959,4,A,Case of Conscience,SF,4,James,Blish
1960,2,,Starship Troopers,SF,2,Robert A,Heinlein
Column names can be suppressed with option -r or --raw:
$ zql exec --raw "SELECT * FROM Book JOIN Author ON Author.Id = Book.AuthorId LIMIT 5"
1953,1,The,Demolished Man,SF,1,Alfred,Bester
1956,2,,Double Star,SF,2,Robert A,Heinlein
1958,3,The,Big Time,SF,3,Fritz,Leiber
1959,4,A,Case of Conscience,SF,4,James,Blish
1960,2,,Starship Troopers,SF,2,Robert A,Heinlein
This happens automatically if there is only one column in the output:
$ zql exec "SELECT Title FROM Book LIMIT 5"
Demolished Man
Double Star
Big Time
Case of Conscience
Starship Troopers
Output can be formatted as a table with option -t or --table; null and empty values are shown as a single hyphen:
$ zql exec --table "SELECT * FROM Book JOIN Author ON Author.Id = Book.AuthorId LIMIT 5"
Year AuthorId Article Title Genre Id Forename Surname
----- --------- -------- ------------------- ------ --- --------- --------
1953 1 The Demolished Man SF 1 Alfred Bester
1956 2 - Double Star SF 2 Robert A Heinlein
1958 3 The Big Time SF 3 Fritz Leiber
1959 4 A Case of Conscience SF 4 James Blish
1960 2 - Starship Troopers SF 2 Robert A Heinlein
Output can be separated by values in a named column with option -g or --group:
$ zql exec --table --group "genre" "SELECT * FROM Book WHERE Year BETWEEN 1960 AND 1969 ORDER BY Genre"
Year AuthorId Article Title Genre
----- --------- -------- --------------------------- ------
1963 6 The Man in the High Castle F
----- --------- -------- --------------------------- ------
1960 2 - Starship Troopers SF
1961 5 A Canticle for Leibowitz SF
1962 2 - Stranger in a Strange Land SF
1964 7 - Way Station SF
1965 3 The Wanderer SF
1967 2 The Moon Is a Harsh Mistress SF
1969 10 - Stand on Zanzibar SF
----- --------- -------- --------------------------- ------
1966 8 - Dune SF/F
1966 9 - And Call Me Conrad SF/F
1968 9 - Lord of Light SF/F
Output can be formatted as a flattened list with option -f or --flatten:
$ zql exec --flatten "SELECT * FROM Book LIMIT 2"
column value
--------- ------
Year 1953
AuthorId 1
Article The
Title Demolished Man
Genre SF
--------- ------
Year 1956
AuthorId 2
Article -
Title Double Star
Genre SF
Flattened list output can include the data type and nullability with option -v or --verbose:
$ zql exec -fv "SELECT * FROM Book LIMIT 2"
column type nullable value
--------- --------- --------- ------
Year INTEGER NOT NULL 1953
AuthorId INTEGER NOT NULL 1
Article CHAR(5) NULL The
Title CHAR(50) NOT NULL Demolished Man
Genre CHAR(5) NOT NULL SF
--------- --------- --------- ------
Year INTEGER NOT NULL 1956
AuthorId INTEGER NOT NULL 2
Article CHAR(5) NULL -
Title CHAR(50) NOT NULL Double Star
Genre CHAR(5) NOT NULL SF
Execute SQL from Text File
SQL commands can be run from text files with subcommand zql run:
$ cat query.sql
SELECT * FROM Author WHERE Surname LIKE 'B%'
$ zql run --table query.sql
Id Forename Surname
1 Alfred Bester
4 James Blish
10 John Brunner
SQL commands may span multiple lines; to allow this, individual commands must be separated by semicolons in SQLite and MySQL, or GO statements in SQL Server:
$ cat query.sql
SELECT COUNT(*)
FROM Book;
SELECT COUNT(*)
FROM Author;
$ zql run query.sql
20
15
If command line arguments are given with no recognised subcommand, ZQL checks if the first argument references a file in the file system. If it does, all arguments are treated as SQL files to be read; otherwise, the arguments are concatenated and treated as a SQL statement to be executed.
Format CSV from Text File
CSV files can be formatted as a table with subcommand zql format:
$ cat fruit.csv
Name,Colour,Shape,Weight
Apple,Green,Round,185
Banana,Yellow,Long,120
Cherry,Red,Round,5.5
Date,Brown,Oblong,25
$ zql format --table fruit.csv
Name Colour Shape Weight
------- ------- ------- -------
Apple Green Round 185
Banana Yellow Long 120
Cherry Red Round 5.5
Date Brown Oblong 25
Numeric columns can be detected and aligned in the output with option -a or --auto:
$ cat fruit.csv
Name,Colour,Shape,Weight
Apple,Green,Round,185
Banana,Yellow,Long,120
Cherry,Red,Round,5.5
Date,Brown,Oblong,25
$ zql format -ta fruit.csv
Name Colour Shape Weight
------- ------- ------- -------
Apple Green Round 185
Banana Yellow Long 120
Cherry Red Round 5.5
Date Brown Oblong 25
CSV files can be formatted as a flattened list:
$ cat fruit.csv
Name,Colour,Shape,Weight
Apple,Green,Round,185
Banana,Yellow,Long,120
$ zql format --flatten fruit.csv
column value
------- ------
Name Apple
Colour Green
Shape Round
Weight 185
------- ------
Name Banana
Colour Yellow
Shape Long
Weight 120
Show Database Schema and Keywords
The current database schema can be queried with subcommands show databases, show tables, show columns and show procedures:
$ zql show tables
table
------
Author
Book
The output can be filtered by database (where applicable) or table name:
$ zql show columns book
table column type nullable index
------ --------- --------- --------- ------
Book Year INTEGER NOT NULL 1
Book AuthorId INTEGER NOT NULL 2
Book Article TEXT(5) NULL 3
Book Title TEXT(50) NOT NULL 4
Book Genre TEXT(5) NOT NULL 5
Builtin keywords and functions can be queried with subcommands show keywords and show functions:
$ zql show keywords
keyword
--------
ABORT
ACTION
ADD
AFTER
ALL
ALTER
ALWAYS
ANALYZE
...
$ zql show functions
function
---------
abs
acos
acosh
asin
asinh
atan
atan2
atanh
...
This functionality is currently supported only for SQLite, MySQL and SQL Server, but it is still possible to run standard SQL queries on other database systems, as long as they have an ODBC driver.
Interactive Usage
Read Execute Print Loop
If run with no subcommands, ZQL maintains a read/execute/print loop:
$ zql
zql hugos.db> SELECT * FROM Book LIMIT 5;
Year AuthorId Article Title Genre
----- --------- -------- ------------------- ------
1953 1 The Demolished Man SF
1956 2 - Double Star SF
1958 3 The Big Time SF
1959 4 A Case of Conscience SF
1960 2 - Starship Troopers SF
zql hugos.db>
$ zql
zql hugos.db> SELECT * FROM Author LIMIT 5;
Id Forename Surname
1 Alfred Bester
2 Robert A Heinlein
3 Fritz Leiber
4 James Blish
5 Walter M Miller
zql hugos.db>
By default, results are printed as a table if all columns would fit into the current width of the terminal window, or as a flattened list otherwise. This can be overridden with directives :format table, :format flatten and :format verbose:
$ zql
zql hugos.db> :format flatten
zql hugos.db> SELECT * FROM Book LIMIT 2;
column value
--------- ------
Year 1953
AuthorId 1
Article The
Title Demolished Man
Genre SF
--------- ------
Year 1956
AuthorId 2
Article -
Title Double Star
Genre SF
zql hugos.db>
$ zql
zql hugos.db> :format flatten
zql hugos.db> :format verbose
zql hugos.db> SELECT * FROM Book LIMIT 2;
column type nullable value
--------- --------- --------- ------
Year INTEGER NOT NULL 1953
AuthorId INTEGER NOT NULL 1
Article CHAR(5) NULL The
Title CHAR(50) NOT NULL Demolished Man
Genre CHAR(5) NOT NULL SF
--------- --------- --------- ------
Year INTEGER NOT NULL 1956
AuthorId INTEGER NOT NULL 2
Article CHAR(5) NULL -
Title CHAR(50) NOT NULL Double Star
Genre CHAR(5) NOT NULL SF
zql hugos.db>
The current database schema can be queried with directives :show databases, :show tables, :show columns and :show procedures:
$ zql
zql hugos.db> :show tables
table
------
Author
Book
zql hugos.db>
$ zql
zql hugos.db> :show columns book
table column type nullable index
------ --------- --------- --------- ------
Book Year INTEGER NOT NULL 1
Book AuthorId INTEGER NOT NULL 2
Book Article TEXT(5) NULL 3
Book Title TEXT(50) NOT NULL 4
Book Genre TEXT(5) NOT NULL 5
zql hugos.db>
Builtin keywords and functions can be queried with directives :show keywords and :show functions:
$ zql
zql hugos.db> :show keywords
keyword
--------
ABORT
ACTION
ADD
AFTER
ALL
ALTER
ALWAYS
ANALYZE
...
zql hugos.db>
$ zql
zql hugos.db> :show functions
function
---------
abs
acos
acosh
asin
asinh
atan
atan2
atanh
...
zql hugos.db>
Line Editing and History
GNU Readline functionality is supported, with a separate history file for each database connection. It is therefore possible to use standard Emacs shortcuts like Ctrl+A and Ctrl+E for start and end of line, Alt+B and Alt+F for previous and next word, Up and Down for history navigation, and Ctrl+R for backwards history search.
Keyword and Schema Completion
When Tab is pressed, if there is a single completion candidate, the active word is replaced with the candidate, with a trailing space for builtin keywords, or an opening parenthesis for stored procedures and builtin functions:
$ zql
zql hugos.db> sel<Tab>|
zql hugos.db> SELECT |
$ zql
zql hugos.db> SELECT cou<Tab>|
zql hugos.db> SELECT count(|
$ zql
zql hugos.db> SELECT count(bo<Tab>|
zql hugos.db> SELECT count(Book|
$ zql
zql hugos.db> SELECT count(Book.au<Tab>|
zql hugos.db> SELECT count(Book.AuthorId|
When Tab is pressed, if there are multiple completion candidates, the active word is replaced with the longest common prefix, converted to upper case to match the most upper case candidate:
$ zql
zql hugos.db> au<Tab>|
zql hugos.db> AUT|
When Tab is pressed a second time, the candidates are listed:
$ zql
zql hugos.db> AUT<Tab>|
zql hugos.db> AUT|
AUThor AUThorId AUTOINCREMENT
Features
Coloured Output
If ANSI escape sequences are supported by the shell, ZQL outputs coloured text, with numeric values in green, all other values in blue, column names in yellow, and data types and nullability in red. This behaviour is only supported when the terminal is in teletype mode, and is disabled when writing to a pipe or file; but this can be overridden with option --color always or --color never.
Driver Selection
The ODBC connection string marked as --default is used by default. However, an alternate connection string can be used with option --config:
$ zql --config mysql
zql hugos@localhost>
Command Line Passwords
It is possible to specify ODBC connection strings with passwords, which is convenient but insecure. However, if the connection string has a Password= or Pwd= component with an empty value, ZQL queries for a password in the terminal, and uses that instead:
$ zql
Password?
zql hugos@localhost>
Command Line Completion
Bash, PowerShell and other completion scripts can be generated with option --completion, and can be called from Bash .bashrc and PowerShell $PROFILE files:
source <(/home/username/bin/zql --completion=bash)
C:\Users\username\bin\zql.exe --completion=powershell | Out-String | Invoke-Expression