fbsql (CLI Client)
fbsql is a terminal-based front-end to FeatureBase. It allows you to query your FeatureBase data and see the results via the command-line. Alternatively, input can be from a file or from command line arguments. fbsql allows you to query your data:
- directly from the fbsql interface
- by loading files containing the SQL queries
You can also write scripts and automate tasks using built-in meta commands and shell-like features.
Before you begin
Install fbsql
fbsql flags
The following flags can be provided when running fbsql. None of the flags are required to start fbsql.
Flag | Description | Default |
---|---|---|
-c --command | Specifies that fbsql is to execute the given command string (enclosed in either single or double quotes). This option can be repeated and combined with the -f option. All -c options will be processed before all -f options are processed. When either -c or -f is specified, fbsql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. | |
--config | Configuration file to read from. | |
-d --dbname | Specifies the name of the database to connect to. | |
--email | Email address for FeatureBase Cloud access. | |
-f --file | Read commands from the file filename, rather than standard input. This option can be repeated with the -c option. All -c options will be processed before all -f options are processed. When either -c or -f is specified, fbsql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence. Except for that, this option is largely equivalent to the meta-command \i . | |
--history-path | File in which to store command history. This defaults to .featurebase/fbsql_history in the current user’s home directory. | |
--host | Specifies the host name of the machine on which the server is running. This can be a URL to a cloud instance of FeatureBase. In that case, the value of hostname might be something like https://query.featurebase.com . | localhost |
--kafka-config | Run fbsql as a Kafka consumer in non-interactive mode. Based on the configuration file provided as an argument to this flag, fbsql will read messages from a Kafka topic and submit them to FeatureBase via BULK INSERT statements. In this mode, fbsql processes messages until terminated by the user. For more information, see How To Use Kafka With fbsql | |
--org-id | Specified the Organization ID to use. Organizations are a concept used in FeatureBase Cloud, and in that case they are determined automatically based on user authorization. They are exposed here in case on-prem installations want to mimic that functionality. | |
--password | Password for FeatureBase Cloud access. | |
-p --port | Specifies the TCP port or the local Unix-domain socket file extension on which FeatureBase is listening for connections. | Attempts to detect 10101 or 8080 (for serverless) |
Usage
Entering SQL Commands
In normal operation, fbsql provides a prompt with the name of the database to which fbsql is currently connected, followed by the string =#. For example:
$ fbsql -d testdb
FeatureBase CLI ()
Type "\q" to quit.
testdb=#
At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.
Meta-Commands
Anything you enter in fbsql that begins with an unquoted backslash is a fbsql meta-command that is processed by fbsql itself. These commands make fbsql more useful for administration or scripting. Meta-commands are often called slash or backslash commands.
The format of an fbsql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters.
To include whitespace in an argument you can quote it with single quotes.
If an unquoted colon (:) followed by a fbsql variable name appears within an argument, it is replaced by the variable’s value, as described in SQL Interpolation below. The forms :’variable_name’ and :”variable_name” described there work as well.
Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command.
Some of the meta-commands act on the current query buffer. This is simply a buffer holding whatever SQL command text has been typed but not yet sent to the server for execution. This will include previous input lines as well as any text appearing before the meta-command on the same line. The query buffer will only ever hold one complete or partial query
The following meta-commands are defined:
connect
\c or \connect [ dbname ]
Establishes a new connection to a FeatureBase database.
change directory
\cd [ directory ]
Changes the current working directory to directory. Without argument, changes to the current user’s home directory.
list databases
\d
Lists all of the objects in the database your are connected to (tables and views).
list tables
\dt
Lists the tables in the database your are connected to.
list views
\dv
Lists the views in the database your are connected to.
print output
\echo text
Prints the argument to standard output, followed by a newline.
use a file for commands
\i or \include filename
Reads input from the file filename and executes it as though it had been typed on the keyboard.
list databases
\l or \list
Lists the databases on the server
save results to file
\o or \out [ filename ]
Arranges to save future query results to the file filename. If sent without the filename, the output will return to standard output
print query buffer
\p or \print
Prints the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.
set options
\pset [ option [ value ] ]
This command sets options affecting the output of query result tables. option indicates which option is to be set. The semantics of value vary depending on the selected option. For some options, omitting value causes the option to be toggled or unset, as described under the particular option. If no such behavior is mentioned, then omitting value just results in the current setting being displayed.
\pset
without any arguments displays the current status of all printing options.
Adjustable printing options are:
border
The value must be a number. In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. In most formats only values 0 (no border), 1 (internal dividing lines), and 2 (table frame) make sense, and values above 2 will be treated the same as border = 2. The latex and latex-longtable formats additionally allow a value of 3 to add dividing lines between data rows.
expanded (or x)
If value is specified it must be either on or off, which will enable or disable expanded mode, or auto. If value is omitted the command toggles between the on and off settings. When expanded mode is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This mode is useful if the data wouldn’t fit on the screen in the normal “horizontal” mode.
format
Sets the output format to one of aligned or csv.
aligned
format is the standard, human-readable, nicely formatted text output; this is the default.
csv
format writes column values separated by commas, applying the quoting rules described in RFC 4180. A header line with column names is generated unless the tuples_only parameter is on. Titles and footers are not printed. Each row is terminated by the system-dependent end-of-line character, which is typically a single newline (\n) for Unix-like systems or a carriage return and newline sequence (\r\n) for Microsoft Windows.
tuples_only (or t)
If value is specified it must be either on or off which will enable or disable tuples-only mode. If value is omitted the command toggles between regular and tuples-only output. Regular output includes extra information such as column headers, titles, and various footers. In tuples-only mode, only actual table data is shown.
print output to query output channel
\qecho text
This command is identical to \echo except that the output will be written to the query output channel, as set by \o.
quit
\q or \quit
Quits the fbsql program.
clear query buffer
\r or \reset
Resets (clears) the query buffer.
set variables
\set [ name [ value [ ... ] ] ]
Sets the fbsql variable name to value, or if more than one value is given, to the concatenation of all of them. If only one argument is given, the variable is set to an empty-string value. To unset a variable, use the \unset
command.
\set
without any arguments displays the names and values of all currently-set fbsql variables.
Variable names are case-sensitive.
timing
\timing [ on | off ]
With a parameter, turns displaying of how long each SQL statement takes on or off. Without a parameter, toggles the display between on and off.
unset variables
\unset name
Unsets (deletes) the fbsql variable name.
write buffer to file
\w or \write filename
Writes the current query buffer to the file filename. If the current query buffer is empty, the most recently executed query is written instead.
warn to standard error channel
\warn text
This command is identical to \echo except that the output will be written to fbsql’s standard error channel, rather than standard output.
re-execute query buffer
\watch [ seconds ]
Repeatedly execute the current query buffer until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions.
If the current query buffer is empty, the most recently sent query is re-executed instead.
set table formatting mode
\x [ on | off ]
Sets or toggles expanded table formatting mode. As such it is equivalent to \pset expanded.
send command to the shell
\! [ command ]
The command is simply passed literally to the shell.
Examples
Starting fbsql with FeatureBase Cloud
With command-line flags
fbsql --host="https://query.featurebase.com" \
--email="user@example.com" \
--password="a1b2c3d4e5f6"
With a configuration file
fbsql --config=cloud.toml
where the contents of cloud.toml
look like:
host = "https://query.featurebase.com"
email = 'user@example.com'
password = 'a1b2c3d4e5f6'
Starting fbsql with FeatureBase Community
With command-line flags
fbsql --host="localhost" \
--port=10101
With a configuration file
fbsql --config=community.toml
where the contents of community.toml
look like:
host = "localhost"
port = "10101"
Run SQL in fbsql from a local file
The following examples assume the presence of a file called example.sql
containing:
create table people (_id id, name string, age int);
insert into people values (1, 'Amy', 42), (2, 'Bob', 27), (3, 'Carl', 33);
select * from people;
Using the –file flag
- example of running sql from a file (–file)
fbsql --config=community.toml \
--file example.sql
Will result in the following output:
Host: http://localhost:10101
_id | name | age
-----+------+-----
1 | Amy | 42
2 | Bob | 27
3 | Carl | 33
Using the \include meta-command
fbsql=# \include example.sql
_id | name | age
-----+------+-----
1 | Amy | 42
2 | Bob | 27
3 | Carl | 33
fbsql=#