Skip to content

sql

sql - execute SQL queries and scripts in dbvr.

Synopsis

dbvr sql [-hV]
         [-format=<outputFormat> | --output-format=<outputFormat>]
         [-in=<inputFile> | -input-file=<inputFile>]
         [--print-queries]
         [-l=<limit> | --limit=<limit>]
         [-op=<outputFormatParameters> | --output-format-parameters=<outputFormatParameters>]
         [-out=<outputFile> | -output-file=<outputFile>]
         [-p=<dbPassword> | --password=<dbPassword>]
         [--project=<projectIdOrName>]
         [-u=<dbUser> | --user=<dbUser>]
         [-auth=<authParams> | --auth-property=<authParams>]...
         [-ext=<providerParams> | --extended-property=<providerParams>]...
         [-prop=<connectionParams> | --property=<connectionParams>]...
         [[-net=<handlerParams> | --network-handler-param=<handlerParams>]...
          [-net-save-pwd=<savePassword> | --network-handler-save-password=<savePassword>]]
         (-ds=<existDataSourceIdOrName> | --datasource=<existDataSourceIdOrName> |
          -con=<connectionSpec> | -connect=<connectionSpec> | -ds-spec=<connectionSpec> | --datasource-specification=<connectionSpec> |
          [--driver=<driver>
            [[--host=<host>]
             [--database=<dbName>]
             [--server=<server>]
             [--url=<url>]
             [--auth-model=<authModel>]
             [--port=<port>]
             [--folder=<folder>]
             [--name=<dataSourceName>]
             [--save-password=<savePassword>]]])
         [<query>]

Tip

You can also use global options with this command.

Tip

SQL execution in dbvr follows the same engine and configuration model as in DBeaver. For UI-based query execution, result export, and execution settings, see SQL execution.

Description

Execute a SQL query or script.

Tip

To inspect database objects before running SQL queries, use meta.

Required argument

<query>

SQL statement to execute.

If specified, it must be the last argument in the command. If omitted, SQL is read from standard input or from -in/-input-file.

Connection options

Use connection settings to specify which database this command runs against.

Connect in one of these ways:

  • use an existing datasource with -ds or --datasource
  • provide a full connection specification with -con, -ds-spec, -connect, or --datasource-specification
  • define a connection inline with --driver and connection parameters

You can also provide credentials, network handlers, and driver-specific properties.

Info

For details on all connection settings, see Connection options.

Input and output options

-input-file

-in, -input-file=<inputFile>

Read SQL from a file instead of passing a query argument.

Tip

You can read scripts not only from local files, but also from cloud storage. For configuration, see Configure cloud providers. For usage examples, see Use cloud storage in SQL commands.

--print-queries

--print-queries

Print each SQL query above its result in the output.

Example

dbvr sql \
  --driver=sqlite_ee \
  --url=jdbc:sqlite:/path/to/Chinook.db \
  --print-queries \
  "select * from Album limit 3;"

Output:

select * from Album limit 3
|AlbumId|Title                                |ArtistId|
|-------|-------------------------------------|--------|
|1      |For Those About To Rock We Salute You|1       |
|2      |Balls to the Wall                    |2       |
|3      |Restless and Wild                    |2       |
Rows read: 3, (54ms)

--output-format

-format, --output-format=<outputFormat>

Write the execution result in a specific format.

Supported formats:

  • csv
  • txt
  • json
  • xml
  • html
  • md (markdown)
  • sql
  • php (source)
  • parquet
  • xlsx
  • dbunit

Default: txt.

--output-format-parameters

-op, --output-format-parameters=prop1=value1,prop2=value2

Exporter options as a list of properties. Comma and space are delimiters.

Example

dbvr sql \
  --driver=sqlite_ee \
  --url=jdbc:sqlite:/path/to/Chinook.db \
  -format=csv \
  -op="delimiter=;" \
  -out=orders.csv \
  "select * from Album limit 3;"

-output-file

-out, -output-file=<outputFile>

Write the execution result to a file.

Example

dbvr sql \
  --driver=postgres-jdbc \
  --host=localhost \
  --port=5432 \
  --database=testdb \
  -u=admin \
  -p=password \
  -format=json \
  -out=path/to/orders.json \
  "select * from public.orders limit 10;"

Tip

Export formats and options use the same exporters as in DBeaver. For details on supported formats and configuration, see Data export.

Tip

You can write results not only to local files, but also to cloud storage. For configuration, see Configure cloud providers. For usage examples, see Use cloud storage in SQL commands.

--limit

-l, --limit="[offset,]limit"

Limit the number of fetched rows and optionally set an offset. Default: 1000.

Example

dbvr sql \
  --driver=postgres-jdbc \
  --host=localhost \
  --port=5432 \
  --database=testdb \
  -u=admin \
  -p=password \
  -format=json \
  -l=20,10 \
  "select * from public.orders;"

Examples

Run a query using an existing datasource:

dbvr sql -ds=pg-local "select current_date;"

Run a script from a file:

dbvr sql -ds=pg-local -in=path/to/script.sql

Run with inline connection parameters:

dbvr sql \
  --driver=postgres-jdbc \
  --host=localhost \
  --database=testdb \
  -u=admin \
  -p=password \
  "select * from users;"

Export results to CSV:

dbvr sql \
  -ds=pg-local \
  -format=csv \
  -out=path/to/result.csv \
  "select * from users;"

Use cloud storage in SQL commands

Write query results to Azure Blob Storage:

dbvr sql \
  -ds=postgres \
  -out=azb:///output.txt?endpoint=https://myaccount.blob.core.windows.net \
  -format=txt \
  "select * from orders;"

Read a script from Google Cloud Storage and write results back:

dbvr sql \
  -ds=postgres \
  -in=gs://my-bucket/script.sql \
  -out=gs://my-bucket/output.txt \
  -format=txt

Read a script from Amazon S3 and write results back:

dbvr sql \
  -ds=postgres \
  -in=s3:///my-bucket/script.sql \
  -out=s3:///my-bucket/output.txt \
  -format=txt