Command-Line Interface¶

Introduction¶

The Command-Line Interface (CLI) is a tool that allows interactive execution of SQL on GlueSQL. It supports Dot commands for more convenient use, and the .edit command allows immediate modification of query files, which can then be executed with .execute. In addition, it supports HTML table format output for SQL results, making it possible to use the results directly on the web.

Installation¶

To install the GlueSQL Command-Line Interface (CLI), run the following command:

$ cargo install gluesql

Running the CLI¶

Once you have installed the GlueSQL CLI, you can use it to interact with your database. The CLI has several options that you can use to customize your database configuration:

$ gluesql [--execute ~/sql_path] [--storage=memory]
$ gluesql [--execute ~/sql_path] --path ~/data_path --storage={redb | json | csv | parquet | file}

--execute¶

This option allows you to execute a SQL query that is stored in a specific file path. You need to provide the path to the SQL file that contains the query you want to execute. For example, you can use the following command to execute a file located at ~/sql_path/query.sql

gluesql --execute ~/sql_path/query.sql

--path¶

This option allows you to specify the path to your database's data directory or file. The default memory storage is non-persistent and does not use --path. For path-backed storages, specify both --path and --storage. For example, you can use the following command to specify a custom data path ~/mydatabase:

gluesql --path ~/mydatabase --storage=redb

--storage¶

This option allows you to specify the storage engine you want to use for your database. By default, GlueSQL uses the memory storage engine. For persistent storage, use redb. The other path-backed storages are json, csv, parquet, and file. Storage engines other than memory must be used with --path. For example, you can use the following command to specify the json storage engine:

gluesql --path ~/mydatabase --storage=json

--dump¶

Export the selected storage's table definitions and rows to a SQL file, then exit:

gluesql --storage=redb --path ~/data.redb --dump ./dump.sql

--dump requires --storage. For path-backed storages, also specify --path. --dump cannot be combined with --execute or --upgrade.

Dot command¶

.show¶

This command shows current Print options.

gluesql> .show all
tabular ON
colsep "|"
colwrap ""
heading ON

or you can specify a option

gluesql> .show colsep
colsep "|"

.set¶

This command can set each Print options

Print options¶

gluesql> VALUES (1, 'Glue'), (2, 'SQL');
| column1 | column2 |
|---------|---------|
| 1       | Glue    |
| 2       | SQL     |

gluesql> .set tabular off
gluesql> VALUES (1, 'Glue'), (2, 'SQL');
column1|column2
1|Glue
2|SQL
gluesql> .set colsep ,
gluesql> VALUES (1, 'Glue'), (2, 'SQL');
column1,column2
1,Glue
2,SQL
gluesql> .set colwrap '
gluesql> VALUES (1, 'Glue'), (2, 'SQL');
'column1','column2'
'1','Glue'
'2','SQL'
gluesql> .set heading off
gluesql> VALUES (1, 'Glue'), (2, 'SQL');
'1','Glue'
'2','SQL'

.edit¶

This command open editor with last executed SQL or PATH

With last executed SQL¶

if you execute .edit, it opens specified (set on $EDITOR env) or OS default editor.

$ export $EDITOR=vi
$ gluesql
gluesql> VALUES (1, 'Glue'), (2, 'SQL');
| column1 | column2 |
|---------|---------|
| 1       | Glue    |
| 2       | SQL     |
gluesql> .edit

Last executed SQL is opened with vi

--! /tmp/Glue_xxxxx.sql
VALUES (1, 'Glue'), (2, 'SQL');

With PATH¶

gluesql> .edit insert.sql

It opens editor and shows the contents of create_insert.sql

--! create_insert.sql
CREATE TABLE Items (id INT, name TEXT);
INSERT INTO Items VALUES (1, 'Glue'), (2, 'SQL');

.execute¶

This command executes SQL from PATH

gluesql> .execute create_insert.sql
Table created

2 rows inserted

.run¶

This command executes last executed command again.

gluesql> VALUES (1, 'Glue'), (2, 'SQL');
| column1 | column2 |
|---------|---------|
| 1       | Glue    |
| 2       | SQL     |

gluesql> .run
| column1 | column2 |
|---------|---------|
| 1       | Glue    |
| 2       | SQL     |

Also possible to combinate with .edit

gluesql> VALUES (1, 'Glue'), (2, 'SQL');
| column1 | column2 |
|---------|---------|
| 1       | Glue    |
| 2       | SQL     |

gluesql> .edit

edit to add (3, 'Rust')

--! /tmp/Glue_xxxxxx.sql
VALUES (1, 'Glue'), (2, 'SQL'), (3, 'Rust')

gluesql> .run
| column1 | column2 |
|---------|---------|
| 1       | Glue    |
| 2       | SQL     |
| 3       | Rust    |

More commands¶

If you execute .help, you can see various helper command starting with dot(.)

Migration using CLI¶

Use --dump to export table definitions, index definitions, and rows as SQL:

gluesql --storage=redb --path ~/data.redb --dump ./dump.sql

To restore the dump into a new Redb database, execute the SQL file:

gluesql --storage=redb --path ~/restored.redb --execute ./dump.sql

When moving between storage types, choose a destination that supports the features used by the exported SQL, such as indexes.