sql

Table of contents

• Synopsis
• Description
• Required argument
• Connection options
• Input and output options
  • -input-file
  • --print-queries
  • --output-format
  • --output-format-parameters
  • -output-file
  • --limit
• Examples
  • Use cloud storage in SQL commands

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.

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