SQL command

The SQL command starts a REPL for running or piping SurrealQL queries to a local or remote SurrealDB database server.

BEFORE YOU START

Make sure you’ve installed SurrealDB — it should only take a second!

Command options

--auth-level option

Available since: v2.0.0

The --auth-level option sets the authentication level to use when connecting to the database. The option has three possible values: root, namespace, and database. The root value is the highest level of authentication, while the namespace and database values are used for authenticating as users defined on a specific namespace or database.

There are a few things to keep in mind when using the --auth-level option:

• The root value is used to access the database server as a root user, and if not specified is the default value.

surreal sql --endpoint http://localhost:8000 --namespace test --database test --auth-level root --username username --password password

• The namespace value is used for accessing a specific namespace and all databases within that namespace. When this level is specified, a namespace must be provided via --namespace.

surreal sql --endpoint http://localhost:8000 --namespace test --database test --auth-level namespace --username username --password password

• The database value is used for accessing a specific database within a namespace. When this level is specified, a namespace and a database must be provided via --namespace and --database.

surreal sql --endpoint http://localhost:8000 --namespace test --database test --auth-level database --username username --password password

--token option

Available since: v2.0.0

The --token option sets the authentication token to use when connecting to the server. This option allows you to connect to SurrealDB using a JWT instead of user credentials. The token is used to authenticate the user and provide access to the database server which means it cannot be provided at the same time as --username, --password or --auth-level.

surreal sql --endpoint http://localhost:8000 --namespace test --database test --token <token>

Experimental capabilities

Available since: v2.2.1

To use experimental capabilities, set the SURREAL_CAPS_ALLOW_EXPERIMENTAL environment variable to the experimental capability you want to allow.

For example, to use record references, set the SURREAL_CAPS_ALLOW_EXPERIMENTAL environment variable to record_references.

For graphql, use graphql and for bearer access, use bearer_access.

SURREAL_CAPS_ALLOW_EXPERIMENTAL="record_references" surreal sql ...

or, using the --allow-experimental flag:

surreal sql -e [CONNECTION_STRING] --allow-experimental record_references

Multiple experimental capabilities can be enabled by separating them with a comma.

SURREAL_CAPS_ALLOW_EXPERIMENTAL="record_references, graphql" surreal sql ...

-- OR 

surreal sql -e [CONNECTION_STRING] --allow-experimental record_references,graphql

Note

The experimental capability is completely hidden in the CLI help command, and --allow-all will not enable the experimental capabilities by default.

Example usage

To start a REPL and run or pipe queries to a local or remote SurrealDB database, run the surreal sql command in a terminal with the required arguments.

Once you see the > character you can type your SurrealQL query, followed by the enter key. The command has support for ↑ and ↓ arrows for selecting previous SQL statements, and stores the statement history in a history.txt file. To exit the REPL, use the ctrl + c or ctrl + d key combinations.

surreal sql --endpoint http://localhost:8000 --namespace test --database test --auth-level root --username username --password password

It is also possible to pipe a set of statements to a remote database. This functionality is only designed for submitting a small number of queries to the database server. For a large number of queries, use the import command.

cat myfile.surql | surreal sql --endpoint http://localhost:8000 --username root --password root --namespace test --database test

Using environment variables

When using the surreal sql command, you can also use environment variables to set the values for the command-line flags.

Important

Most of the flags mentioned in the command output above also mention a corresponding environment variables.

For example, the --database flag can be configured with the SURREAL_DATABASE environment variable instead.

For more on the environment variables available for CLI commands or SurrealDB instances in general, see the environment variables page.

Command help

To see the help information and usage instructions, in a terminal run the surreal sql --help command without any further arguments. This command gives general information on the arguments, inputs, and additional options for the sql command.

surreal sql --help

The output of the above command:

Start an SQL REPL in your terminal with pipe support

Usage: surreal sql [OPTIONS]

Options:
  -e, --endpoint <ENDPOINT>
          Remote database server url to connect to
          
          [default: ws://localhost:8000]
          [aliases: conn]

  -u, --username <USERNAME>
          Database authentication username to use when connecting
          
          [env: SURREAL_USER=]
          [aliases: user]

  -p, --password <PASSWORD>
          Database authentication password to use when connecting
          
          [env: SURREAL_PASS=]
          [aliases: pass]

  -t, --token <TOKEN>
          Authentication token in JWT format to use when connecting
          
          [env: SURREAL_TOKEN=]

      --auth-level <AUTH_LEVEL>
          Level on which the authenticating user is defined
          
          [env: SURREAL_AUTH_LEVEL=]
          [default: root]
          [possible values: root, namespace, ns, database, db]

      --namespace <NAMESPACE>
          The selected namespace
          
          [env: SURREAL_NAMESPACE=]
          [aliases: ns]

      --database <DATABASE>
          The selected database
          
          [env: SURREAL_DATABASE=]
          [aliases: db]

      --pretty
          Whether database responses should be pretty printed

      --json
          Whether to emit results in JSON

      --multi
          Whether omitting semicolon causes a newline

      --hide-welcome
          Whether to show welcome message
          
          [env: SURREAL_HIDE_WELCOME=]

  -l, --log <LOG>
          The logging level for the command-line tool
          
          [env: SURREAL_LOG=]
          [default: info]
          [possible values: none, full, error, warn, info, debug, trace]

  -h, --help
          Print help (see a summary with '-h')

Capabilities:
  -A, --allow-all
          Allow all capabilities except for those more specifically denied
          
          [env: SURREAL_CAPS_ALLOW_ALL=]

      --allow-scripting
          Allow execution of embedded scripting functions
          
          [env: SURREAL_CAPS_ALLOW_SCRIPT=]

      --allow-guests
          Allow guest users to execute queries
          
          [env: SURREAL_CAPS_ALLOW_GUESTS=]

      --allow-funcs [<ALLOW_FUNCS>...]
          Allow execution of all functions except for functions that are specifically denied. Alternatively, you can provide a comma-separated list of function names to allow
          Specifically denied functions and function families prevail over any other allowed function execution.
          Function names must be in the form <family>[::<name>]. For example:
           - 'http' or 'http::*' -> Include all functions in the 'http' family
           - 'http::get' -> Include only the 'get' function in the 'http' family
          
          
          [env: SURREAL_CAPS_ALLOW_FUNC=]

      --allow-arbitrary-query [<ALLOW_ARBITRARY_QUERY>...]
          Allow execution of arbitrary queries by certain user groups except when specifically denied. Alternatively, you can provide a comma-separated list of user groups to allow
          Specifically denied user groups prevail over any other allowed user group.
          User groups must be one of "guest", "record" or "system".
          
          
          [env: SURREAL_CAPS_ALLOW_ARBITRARY_QUERY=]

      --allow-net [<ALLOW_NET>...]
          Allow all outbound network connections except for network targets that are specifically denied. Alternatively, you can provide a comma-separated list of network targets to allow
          Specifically denied network targets prevail over any other allowed outbound network connections.
          Targets must be in the form of <host>[:<port>], <ipv4|ipv6>[/<mask>]. For example:
           - 'surrealdb.com', '127.0.0.1' or 'fd00::1' -> Match outbound connections to these hosts on any port
           - 'surrealdb.com:80', '127.0.0.1:80' or 'fd00::1:80' -> Match outbound connections to these hosts on port 80
           - '10.0.0.0/8' or 'fd00::/8' -> Match outbound connections to any host in these networks
          
          
          [env: SURREAL_CAPS_ALLOW_NET=]

      --allow-rpc [<ALLOW_RPC>...]
          Allow all RPC methods to be called except for routes that are specifically denied. Alternatively, you can provide a comma-separated list of RPC methods to allow.
          
          [env: SURREAL_CAPS_ALLOW_RPC=]
          [default: ]

      --allow-http [<ALLOW_HTTP>...]
          Allow all HTTP routes to be requested except for routes that are specifically denied. Alternatively, you can provide a comma-separated list of HTTP routes to allow.
          
          [env: SURREAL_CAPS_ALLOW_HTTP=]
          [default: ]

  -D, --deny-all
          Deny all capabilities except for those more specifically allowed
          
          [env: SURREAL_CAPS_DENY_ALL=]

      --deny-scripting
          Deny execution of embedded scripting functions
          
          [env: SURREAL_CAPS_DENY_SCRIPT=]

      --deny-guests
          Deny guest users to execute queries
          
          [env: SURREAL_CAPS_DENY_GUESTS=]

      --deny-funcs [<DENY_FUNCS>...]
          Deny execution of all functions except for functions that are specifically allowed. Alternatively, you can provide a comma-separated list of function names to deny.
          Specifically allowed functions and function families prevail over a general denial of function execution.
          Function names must be in the form <family>[::<name>]. For example:
           - 'http' or 'http::*' -> Include all functions in the 'http' family
           - 'http::get' -> Include only the 'get' function in the 'http' family
          
          
          [env: SURREAL_CAPS_DENY_FUNC=]

      --deny-arbitrary-query [<DENY_ARBITRARY_QUERY>...]
          Deny execution of arbitrary queries by certain user groups except when specifically allowed. Alternatively, you can provide a comma-separated list of user groups to deny
          Specifically allowed user groups prevail over a general denial of user group.
          User groups must be one of "guest", "record" or "system".
          
          
          [env: SURREAL_CAPS_DENY_ARBITRARY_QUERY=]

      --deny-net [<DENY_NET>...]
          Deny all outbound network connections except for network targets that are specifically allowed. Alternatively, you can provide a comma-separated list of network targets to deny.
          Specifically allowed network targets prevail over a general denial of outbound network connections.
          Targets must be in the form of <host>[:<port>], <ipv4|ipv6>[/<mask>]. For example:
           - 'surrealdb.com', '127.0.0.1' or 'fd00::1' -> Match outbound connections to these hosts on any port
           - 'surrealdb.com:80', '127.0.0.1:80' or 'fd00::1:80' -> Match outbound connections to these hosts on port 80
           - '10.0.0.0/8' or 'fd00::/8' -> Match outbound connections to any host in these networks
          
          
          [env: SURREAL_CAPS_DENY_NET=]

      --deny-rpc [<DENY_RPC>...]
          Deny all RPC methods from being called except for methods that are specifically allowed. Alternatively, you can provide a comma-separated list of RPC methods to deny.
          
          [env: SURREAL_CAPS_DENY_RPC=]

      --deny-http [<DENY_HTTP>...]
          Deny all HTTP routes from being requested except for routes that are specifically allowed. Alternatively, you can provide a comma-separated list of HTTP routes to deny.
          
          [env: SURREAL_CAPS_DENY_HTTP=]