How do I run SQL queries with fbsql?

Valid SQL queries can be run directly in the fbsql interface and via files in accessible directories.

This reference explains fbsql flags relating to database connections and schema

Before you begin

Enter fbsql --help at the CLI to list available commands. \q quits the fbsql interface.

Syntax

[
  (<meta-flag-prefix>)
    [ c|connect [ <cloud-database-name> | - ] ] |
    [ d[< tablename>|t|v ] ] |
    [ l|list ] |
    [ set <variable-name> [variable-value,...] ] |
    [ unset <variable-name> ] |
    [ i|include <filename> ] |
    [ watch <seconds> ] |
    [ t|timing [on|off] ] |
    [ p|print ] |
    [ r|reset ] |
]
<sql-query>

Meta-command flag prefix

Meta-command flags can be run from the fbsql interface or CLI using an appropriate prefix:

Interface	Prefix	Description	Example
fbsql	`\`	Leading backslash followed by one or more meta-command flags and arguments separated by `\` backslash characters.	`\print \echo "this is a test"`
CLI	`fbsql [-c\|--command]`	Meta-command flags enclosed in single or double quotes are executed without opening the fbsql interface	`fbsql --command '\print \echo this is a test'`

Database flags

Argument	Description	Additional information
`[c\|connect] <cloud-database-name>`	Connect to an available database in the currently connected cloud account.	Connect cloud database additional
`[c\|connect] -`	Disconnect from the current cloud database
`d`	List all tables in the connected database. Equivalent to Cloud `SHOW DATABASE` statement	SHOW DATABASE
`d <tablename>`	Equivalent to `SHOW COLUMNS` statement	SHOW COLUMNS
`dt`	Equivalent to `SHOW TABLES` statement	SHOW TABLES
`dv`	Show definition of all views	CREATE VIEWS
`[l\|list]`	List databases in connected Cloud database

File flags

Flag	Description	Additional information
`file <filename> [<alias>]`	Create alias for filename to use in SQL queries	Filename alias
`[w\|write] <filename>`	Write most recent query or query buffer to file		File write
`[i\|include] <filename>`	Run content from file which can include valid meta-commands and SQL queries	Equivalent to `fbsql --file` command

Query flags

Flag	Description	Default
`timing`	Toggle time SQL statement takes to execute	off
`watch <seconds>`	Repeat query from buffer or last in history at interval of until failure or manual break

Query buffer flags

Flag	Description
`[p \| print]`	Display most recent query or query buffer to fbsql interface followed by a newline
`[r \| reset]`	Reset query buffer

Set variable flags

Flag	Description	Additional information
`set`	List all variable names
`[set\|unset] <variable-name>`	Set or unset named variable	Named variables in single quotation marks are treated as string literals in queries
`set <variable-name> <value>...`	Set a variable name and value. Multiple values are concatenated.	SET variable names

SQL query syntax

SQL guide

Additional information

Flag execution

Flags can be executed individually or as a sequence delimited by \
Use single quotation marks ' ' to insert whitespace

fbsql query buffer

The query buffer holds partial or not executed:

meta-commands
SQL queries

Cloud database connect additional

The following commands are equivalent:
fbsql -[d|-dbname] <cloud-database-name>
and
\[c|connect]="<cloud-database-name>"

Filename alias

Aliases are case sensitive and can be inserted into statements in two ways:

Quotation marks	Example
Single	`:'<variable-name>'`
Double	`:"<variable-name>"`

Query output formatting

Query output can be formatted using PSET (Print Set) meta flags.

PSET query output formatting reference

Examples

Filename alias examples

Alias for CSV file

Create a filename alias for file containing CSV data:

\file insert_test.csv icsv

Use the alias in SQL statement:

fbsql-# bulk replace
     -#     into insert_test (_id, int1, string1, timestamp1)
     -#     map (0 id, 1 int, 2 string)
     -#     transform (@0, @1, @2, current_timestamp)
     -# from
     -#     'icsv'
     -# with
     -#     format 'CSV'
     -#     input 'STREAM';

Alias for SQL file

Create docviewtest.sql and add the following SQL statement:

SELECT * from doctest where _id = 0;

Add filename alias:

fbsql -c '\file docviewtest.sql mynewview'

Use the alias in a SQL statement:

CREATE VIEW docview AS :mynewview;

SET variable

SET variable	Result
`set myvarname 1,2,3,4`	`myvarname = '1,2,3,4'`
`set myvarname 1 2 3 4`	`myvarname = '1234'`

SET variable in a query

\SET prod products

Query with variable

select * from :prod;