📚 GizmoSQL Documentation¶
Welcome to the official documentation for GizmoSQL, a high-performance, embeddable SQL server built on DuckDB and SQLite, exposed through Apache Arrow Flight SQL.
🧩 Overview¶
GizmoSQL enables the execution of SQL queries using DuckDB or SQLite as backends, accessed remotely over Arrow Flight SQL. The server supports TLS, password authentication, and JWT issuance via middleware.
It was forked from sqlflite and has been extended significantly under the Apache 2.0 license.
For a great introduction to Arrow Flight SQL, check out this Voltron Data article.
📦 Installation & Deployment¶
GizmoSQL - by GizmoData™¶
Copyright © 2025 GizmoData LLC¶
An Arrow Flight SQL Server with DuckDB or SQLite back-end execution engines¶
📱 GizmoSQL for iOS is now available on the App Store! (for development & prototyping — not production workloads)
Description¶
This is the GizmoSQL core repo - used to build an Apache Arrow Flight SQL server implementation using DuckDB or SQLite as a backend database.
It enables authentication via middleware and allows for encrypted connections to the database via TLS.
For more information about Apache Arrow Flight SQL - please see this article.
It is originally forked from sqlflite, an open-source project developed by Voltron Data. I contributed to sqlflite while employed at Voltron Data, and have since independently extended and evolved the project into GizmoSQL under the Apache 2.0 License.
Component Versions¶
| Component | Stable channel | LTS channel |
|---|---|---|
| DuckDB | v1.5.3 | v1.4.4 |
| SQLite | 3.53.1 | 3.53.1 |
| Apache Arrow (Flight SQL) | 23.0.1 | 23.0.1 |
| jwt-cpp | v0.7.2 | v0.7.2 |
| OpenTelemetry C++ | v1.25.0 | v1.25.0 |
| nlohmann/json | v3.12.0 | v3.12.0 |
Release channels¶
GizmoSQL ships two parallel release channels: stable (latest DuckDB minor) and LTS (most recent DuckDB long-term-support release). Both channels carry every GizmoSQL fix and feature; they only differ in which DuckDB release is bundled. LTS artifacts use an _lts/-lts suffix so they can coexist with stable installs.
See the LTS Channel guide for details on which DuckDB versions are LTS, artifact naming, install commands, and how to switch channels in production. The DuckDB release calendar is the authoritative source for current LTS designations and end-of-support dates.
Running from Docker Image¶
Default credentials: The server's default username is
gizmosql_user(override with--usernameorGIZMOSQL_USERNAME). A password is always required via--passwordorGIZMOSQL_PASSWORD.
Open a terminal, then pull and run the published Docker image which has everything setup (change: "--detach" to "--interactive" if you wish to see the stdout on your screen) - with command:
# Pull and run the Docker image
# Username defaults to "gizmosql_user" when GIZMOSQL_USERNAME is not set
docker run --name gizmosql \
--detach \
--rm \
--tty \
--init \
--publish 31337:31337 \
--env TLS_ENABLED="1" \
--env GIZMOSQL_PASSWORD="gizmosql_password" \
--env PRINT_QUERIES="1" \
--pull always \
gizmodata/gizmosql:latest
The above command will start GizmoSQL with an in-memory DuckDB database. To use a persistent database file, set the DATABASE_FILENAME environment variable (see example below).
Note: You can disable TLS in the container by setting environment variable: TLS_ENABLED to "0" (default is "1" - enabled). This is not recommended unless you are using an mTLS sidecar in Kubernetes or something similar, as it will be insecure.
Optional - open a different database file¶
When running the Docker image - you can have it run your own DuckDB database file (the database must be built with DuckDB version: 1.5.3).
Prerequisite: DuckDB CLI Install DuckDB CLI version 1.5.3 - and make sure the executable is on your PATH.
Platform Downloads: Linux x86-64 Linux arm64 (aarch64) MacOS Universal
In this example, we'll generate a new TPC-H Scale Factor 1 (1GB) database file, and then run the docker image to mount it:
# Generate a TPC-H database in the host's /tmp directory
pushd /tmp
duckdb ./tpch_sf1.duckdb << EOF
.bail on
.echo on
SELECT VERSION();
INSTALL tpch;
LOAD tpch;
CALL dbgen(sf=1);
EOF
# Run the gizmosql docker container image - and mount the host's DuckDB database file created above inside the container
docker run --name gizmosql \
--detach \
--rm \
--tty \
--init \
--publish 31337:31337 \
--env TLS_ENABLED="1" \
--env GIZMOSQL_PASSWORD="gizmosql_password" \
--pull always \
--mount type=bind,source=$(pwd),target=/opt/gizmosql/data \
--env DATABASE_FILENAME="data/tpch_sf1.duckdb" \
gizmodata/gizmosql:latest
Running initialization SQL commands¶
You can now run initialization commands upon container startup by setting environment variable: INIT_SQL_COMMANDS to a string of SQL commands separated by semicolons - example value:
SET threads = 1; SET memory_limit = '1GB';.
Here is a full example of running the Docker image with initialization SQL commands:
# Pull and run the Docker image
docker run --name gizmosql \
--detach \
--rm \
--tty \
--init \
--publish 31337:31337 \
--env TLS_ENABLED="1" \
--env GIZMOSQL_PASSWORD="gizmosql_password" \
--env PRINT_QUERIES="1" \
--env INIT_SQL_COMMANDS="SET threads = 1; SET memory_limit = '1GB';" \
--pull always \
gizmodata/gizmosql:latest
You can also specify a file containing initialization SQL commands by setting environment variable: INIT_SQL_COMMANDS_FILE to the path of the file containing the SQL commands - example value: /tmp/init.sql. The file must be mounted inside the container.
Note: for the DuckDB back-end - the following init commands are automatically run for you:
SET autoinstall_known_extensions = true; SET autoload_known_extensions = true;
Note: Initialization SQL commands which SELECT data will NOT show the results (this is not supported).
Note: Initialization SQL commands which fail will cause the Flight SQL server to abort and exit with a non-zero exit code.
Client Connections¶
Connecting to the server via JDBC¶
Download the new GizmoSQL JDBC driver
You can then use the JDBC driver to connect from your host computer to the locally running Docker Flight SQL server with this JDBC string (change the password value to match the value specified for the GIZMOSQL_PASSWORD environment variable if you changed it from the example above):
jdbc:gizmosql://localhost:31337?useEncryption=true&user=gizmosql_user&password=gizmosql_password&disableCertificateVerification=true
For instructions on setting up the JDBC driver in popular Database IDE tool: DBeaver Community Edition - see this repo.
Note - if you stop/restart the Flight SQL Docker container, and attempt to connect via JDBC with the same password - you could get error: "Invalid bearer token provided. Detail: Unauthenticated". This is because the client JDBC driver caches the bearer token signed with the previous instance's secret key. Just change the password in the new container by changing the "GIZMOSQL_PASSWORD" env var setting - and then use that to connect via JDBC.
Connecting to the server via the GizmoSQL ADBC Python driver¶
You can use the GizmoSQL ADBC Python driver to query the Flight SQL server. ADBC offers performance advantages over JDBC - because it minimizes serialization/deserialization, and data stays in columnar format at all phases.
You can learn more about ADBC and Flight SQL here.
Ensure you have Python 3.10+ installed, then open a terminal, then run:
# Create a Python virtual environment
python3 -m venv .venv
# Activate the virtual environment
. .venv/bin/activate
# Install the requirements including the GizmoSQL ADBC driver
pip install --upgrade pip
pip install pandas pyarrow adbc-driver-gizmosql
# Start the python interactive shell
python
SELECT queries¶
In the Python shell - you can query data using cursor.execute() and fetch_arrow_table():
from adbc_driver_gizmosql import dbapi as gizmosql
with gizmosql.connect(
"grpc+tls://localhost:31337",
username="gizmosql_user",
password="gizmosql_password",
tls_skip_verify=True, # Not needed if you use a trusted CA-signed TLS cert
) as conn:
with conn.cursor() as cur:
cur.execute(
"SELECT n_nationkey, n_name FROM nation WHERE n_nationkey = ?",
parameters=[24],
)
x = cur.fetch_arrow_table()
print(x)
You should see results:
pyarrow.Table
n_nationkey: int32
n_name: string
----
n_nationkey: [[24]]
n_name: [["UNITED STATES"]]
DDL and DML statements¶
Starting with adbc-driver-gizmosql v1.1.0, cursor.execute() automatically detects DDL/DML statements and executes them immediately on the server — no special API needed. Just use execute() for everything:
from adbc_driver_gizmosql import dbapi as gizmosql
with gizmosql.connect(
"grpc+tls://localhost:31337",
username="gizmosql_user",
password="gizmosql_password",
tls_skip_verify=True,
) as conn:
with conn.cursor() as cur:
# DDL — create a table
cur.execute("""
CREATE TABLE my_table (
id INTEGER,
name VARCHAR,
score DOUBLE
)
""")
# DML — insert data
cur.execute("""
INSERT INTO my_table VALUES
(1, 'Alice', 95.5),
(2, 'Bob', 87.3),
(3, 'Charlie', 92.1)
""")
# SELECT — works as usual
cur.execute("SELECT * FROM my_table ORDER BY score DESC")
print(cur.fetch_arrow_table().to_pandas())
# Cleanup
cur.execute("DROP TABLE my_table")
Note:
cursor.execute_update(query)is also available and returns the rows-affected count directly — useful when you need to know how many rows were inserted, updated, or deleted:rows = cur.execute_update("INSERT ...")
Connecting via gizmosql_client¶
GizmoSQL ships with an interactive SQL shell (gizmosql_client), inspired by psql and the DuckDB CLI. It is built into the Docker image and also available as a standalone executable for Linux and macOS.
Start an interactive session:
GIZMOSQL_PASSWORD="gizmosql_password" gizmosql_client \
--host "localhost" \
--port 31337 \
--username "gizmosql_user" \
--tls \
--tls-skip-verify
Or run a single query with --command:
GIZMOSQL_PASSWORD="gizmosql_password" gizmosql_client \
--host "localhost" \
--port 31337 \
--username "gizmosql_user" \
--tls \
--tls-skip-verify \
--command "SELECT version()"
For full details, see the Client Shell documentation.
Connecting via Ibis¶
See: https://github.com/gizmodata/ibis-gizmosql
Connecting via SQLAlchemy¶
See: https://github.com/gizmodata/sqlalchemy-gizmosql-adbc-dialect
Tear-down¶
Stop the docker image with:
Option 2 - Install via Homebrew (macOS & Linux)¶
GizmoSQL can be installed via Homebrew on macOS and Linux.
Supported platforms: - macOS (Apple Silicon / ARM64) - Linux (x86-64 / AMD64) - Linux (ARM64)
Then run the server:
GIZMOSQL_PASSWORD="gizmosql_password" gizmosql_server --database-filename your.duckdb --print-queries
Option 3 - Download and run the gizmosql CLI executable¶
Download (and unzip) the latest release of the gizmosql_server CLI executable from these currently supported platforms: Linux x86-64 Linux arm64 MacOS arm64 Windows x64 Windows arm64
Windows: prefer the signed MSI installers —
GizmoSQL-amd64.msi(x64) orGizmoSQL-arm64.msi(Windows on Arm, e.g. Snapdragon X-class) — from the GitHub Releases page. The installer adds the binaries toC:\Program Files\GizmoSQLand updates the system PATH.
Then from a terminal - you can run:
GIZMOSQL_PASSWORD="gizmosql_password" gizmosql_server --database-filename data/some_db.duckdb --print-queries
To see all program options - run:
Option 4 - Steps to build the solution manually¶
In order to run build the solution manually, and run SQLite and DuckDB Flight SQL server, you need to set up a new Python 3.10+ virtual environment on your machine. Follow these steps to do so (thanks to David Li!).
-
Clone the repo and build the static library and executable
-
Install Python requirements for ADBC client interaction - (ensure you have Python 3.10+ installed first)
-
Get some SQLite3 sample data.
-
Create a DuckDB database.
-
Optionally generate TLS certificates for encrypting traffic to/from the Flight SQL server
-
Start the Flight SQL server (and print client SQL commands as they run using the --print-queries option)
Option 5 - Install the iOS App (iPhone & iPad) 📱¶
GizmoSQL is available as a native iOS application on the Apple App Store, so you can run a full GizmoSQL server right on your iPhone or iPad.
The iOS edition bundles the DuckDB engine and the Arrow Flight SQL server, so any GizmoSQL client (JDBC, ADBC, CLI, UI, etc.) on your local network can connect to it just like a Linux, macOS, or Windows install. Configure the server and manage sessions directly from the app's UI — no terminal required.
⚠️ Intended for development, not production. The iOS edition is designed for developers, learners, demos, and local prototyping. iOS enforces aggressive background execution limits, memory caps, and network/thermal throttling that make a phone or tablet unsuitable for hosting production SQL traffic. For production workloads, use Docker, Kubernetes, Homebrew, or the native Linux/macOS/Windows builds documented above.
⚙️ Configuration & Environment Variables¶
GizmoSQL can be configured via environment variables or CLI flags. Below are the supported options, their env var fallbacks and defaults.
| Option / Env Var | Description | Default / behavior | CLI flag |
|---|---|---|---|
| backend / BACKEND | Choose database engine: duckdb or sqlite | duckdb | --backend, -B |
| hostname / GIZMOSQL_HOSTNAME | Hostname to listen on. If empty, falls back to env then 0.0.0.0 | 0.0.0.0 (if unset) | --hostname, -H |
| port / GIZMOSQL_PORT | Flight gRPC port | 31337 | --port, -R |
| database-filename / DATABASE_FILENAME | Path to DB file (absolute or relative). Empty => in-memory DB | "" (in-memory) | --database-filename, -D |
| username / GIZMOSQL_USERNAME | Default connection username | gizmosql_user | --username, -U |
| password / GIZMOSQL_PASSWORD | Server password (required). If unset server will exit | (required) | --password, -P |
| secret-key / SECRET_KEY | Secret used to sign JWTs. If unset, env SECRET_KEY used; else random key generated | "" (random if unset) | --secret-key, -S |
| tls cert/key / TLS_CERT, TLS_KEY | TLS cert and key paths (or provide via --tls cert key) | none (disabled if not set) | --tls, -T (cert key) |
| mtls CA / TLS_CA or mtls-ca-cert-filename | CA cert path for verifying client certs (PEM) | none | --mtls-ca-cert-filename, -M |
| print-queries / PRINT_QUERIES | Print incoming SQL to stdout | false | --print-queries, -Q |
| readonly | Open DB read-only | false | --readonly, -O |
| init-sql-commands / INIT_SQL_COMMANDS | Semicolon-separated SQL commands to run at startup | none | --init-sql-commands, -I |
| init-sql-commands-file / INIT_SQL_COMMANDS_FILE | File (mounted) containing init SQL commands | none | --init-sql-commands-file, -F |
| token-allowed-issuer / TOKEN_ALLOWED_ISSUER | Allowed JWT issuer for token auth | none | --token-allowed-issuer |
| token-allowed-audience / TOKEN_ALLOWED_AUDIENCE | Allowed JWT audience for token auth | none | --token-allowed-audience |
| token-signature-verify-cert-path / TOKEN_SIGNATURE_VERIFY_CERT_PATH | RSA PEM cert to verify token signatures | none | --token-signature-verify-cert-path |
| log-level / GIZMOSQL_LOG_LEVEL | Log level: debug | info | warn |
| log-format / GIZMOSQL_LOG_FORMAT | Log format: text | json | text (if unset) |
| access-log / GIZMOSQL_ACCESS_LOG | Per-RPC access logging: on | off | off (if unset) |
| log-file / GIZMOSQL_LOG_FILE | Log file path; '-' => stdout; empty => stderr | stderr (if unset) | --log-file |
| query-timeout | Query timeout in seconds (0 = unlimited) | DEFAULT_QUERY_TIMEOUT_SECONDS | --query-timeout |
| max-metadata-size / GIZMOSQL_MAX_METADATA_SIZE | Max inbound gRPC HTTP/2 header metadata bytes per call (GRPC_ARG_MAX_METADATA_SIZE). Raise above the gRPC default of ~8 KB if clients send large per-call metadata (e.g. extra Apache Flight SQL JDBC URL parameters that get forwarded as gRPC headers, large bearer tokens, accumulated cookies, proxy-injected trace headers) |
0 (= use gRPC default) | --max-metadata-size |
| query-log-level / GIZMOSQL_QUERY_LOG_LEVEL | Query log level | info (if unset) | --query-log-level |
| auth-log-level / GIZMOSQL_AUTH_LOG_LEVEL | Authentication log level | info (if unset) | --auth-log-level |
| session-log-level / GIZMOSQL_SESSION_LOG_LEVEL | Client session lifecycle (create/close) log level | info (if unset) | --session-log-level |
| health-port | Plaintext gRPC health check port (0 = disable) | DEFAULT_HEALTH_PORT | --health-port |
| health-check-query / GIZMOSQL_HEALTH_CHECK_QUERY | SQL query used for health checks | SELECT 1 | --health-check-query |
| enable-instrumentation / GIZMOSQL_ENABLE_INSTRUMENTATION | [Enterprise] Enable session instrumentation | false | --enable-instrumentation |
| instrumentation-db-path / GIZMOSQL_INSTRUMENTATION_DB_PATH | [Enterprise] Path for instrumentation database | (same dir as main DB) | --instrumentation-db-path |
| instrumentation-catalog / GIZMOSQL_INSTRUMENTATION_CATALOG | [Enterprise] Pre-attached DuckLake catalog name for instrumentation | none | --instrumentation-catalog |
| instrumentation-schema / GIZMOSQL_INSTRUMENTATION_SCHEMA | [Enterprise] Schema within the instrumentation catalog | main | --instrumentation-schema |
| license-key-file / GIZMOSQL_LICENSE_KEY_FILE | [Enterprise] Path to license key file (JWT format) | none | --license-key-file, -L |
| license-key / GIZMOSQL_LICENSE_KEY | [Enterprise] Inline license key (literal JWT value, not a path). Takes precedence over license-key-file when both are set | none | --license-key |
| allow-cross-instance-tokens / GIZMOSQL_ALLOW_CROSS_INSTANCE_TOKENS | Accept tokens issued by other GizmoSQL instances sharing the same secret key | false | --allow-cross-instance-tokens |
Notes and best practices: - Always set GIZMOSQL_PASSWORD in production. - Provide TLS cert/key via --tls cert key or TLS_CERT/TLS_KEY and prefer a trusted CA. Use an mTLS CA via --mtls-ca-cert-filename / TLS_CA and enable client verification as needed. - Use INIT_SQL_COMMANDS or INIT_SQL_COMMANDS_FILE for startup tuning (SELECTs in init commands do not display results). - If SECRET_KEY is not provided, the server will create a random signing key; persist SECRET_KEY if you need stable JWTs across restarts. - For logging and fine-grained runtime tuning prefer the CLI flags; corresponding GIZMOSQL_* env vars are supported for containerized deployments. - When rotating instances, rotate GIZMOSQL_PASSWORD to avoid stale cached tokens from clients.
Example (disable TLS, set password):
Example (mount DB and TLS certs):
docker run -e TLS_CERT="/certs/server.crt" -e TLS_KEY="/certs/server.key" \
-e DATABASE_FILENAME="data/custom.duckdb" -e GIZMOSQL_PASSWORD="secret" \
--mount type=bind,source=/host/certs,target=/certs \
--mount type=bind,source=/host/data,target=/opt/gizmosql/data \
gizmodata/gizmosql:latest
🛠 Backend Selection¶
This option allows choosing from two backends: SQLite and DuckDB. It defaults to DuckDB.
$ GIZMOSQL_PASSWORD="gizmosql_password" gizmosql_server --database-filename data/TPC-H-small.duckdb
GizmoSQL - Copyright © 2025 GizmoData LLC
Licensed under the Apache License, Version 2.0
https://www.apache.org/licenses/LICENSE-2.0
Apache Arrow version: 23.0.1
WARNING - TLS is disabled for the GizmoSQL server - this is insecure.
DuckDB version: v1.5.3
Running Init SQL command:
SET autoinstall_known_extensions = true;
Running Init SQL command:
SET autoload_known_extensions = true;
Using database file: "data/TPC-H-small.duckdb"
Print Queries option is set to: false
GizmoSQL server version: - with engine: DuckDB - will listen on grpc+tcp://0.0.0.0:31337
GizmoSQL server - started
The above call is equivalent to running gizmosql_server -B duckdb or gizmosql --backend duckdb. To select SQLite run
GIZMOSQL_PASSWORD="gizmosql_password" gizmosql_server --backend sqlite --database-filename data/TPC-H-small.sqlite
GizmoSQL - Copyright © 2025 GizmoData LLC
Licensed under the Apache License, Version 2.0
https://www.apache.org/licenses/LICENSE-2.0
Apache Arrow version: 23.0.1
WARNING - TLS is disabled for the GizmoSQL server - this is insecure.
SQLite version: 3.46.1
Using database file: "/Users/philip/Documents/git/gizmosql/data/TPC-H-small.sqlite"
Print Queries option is set to: false
GizmoSQL server version: - with engine: SQLite - will listen on grpc+tcp://0.0.0.0:31337
GizmoSQL server - started
🔐 Security¶
This server supports:
- TLS encryption
- mTLS verification (optional)
- Password authentication
- JWT token issuance
To enhance security, never disable TLS in production unless behind a secured proxy.
🧪 Slim Docker Image¶
There is now a slim docker image available, without Python, tls certificate generation, sample database files, etc.
You must supply the following environment variable to the slim image:
- GIZMOSQL_PASSWORD - the password to use for the GizmoSQL server
You can optionally supply the following environment variables:
- DATABASE_FILENAME - the path to the database file to use (default: in-memory; set to :memory: explicitly for the same effect)
- TLS_ENABLED - set to "1" to enable TLS (default is "0" - disabled)
- TLS_CERT - If TLS_ENABLED is 1 - provide the path to the TLS certificate file (it must be mounted in the container)
- TLS_KEY - If TLS_ENABLED is 1 - provide the path to the TLS key file (it must be mounted in the container)
To run that image - use the following command:
# Pull and run the Docker image
docker run --name gizmosql-slim \
--detach \
--rm \
--tty \
--init \
--publish 31337:31337 \
--env DATABASE_FILENAME="data/some_database.duckdb" \
--env TLS_ENABLED="0" \
--env GIZMOSQL_PASSWORD="gizmosql_password" \
--env PRINT_QUERIES="1" \
--pull always \
gizmodata/gizmosql:latest-slim
See start_gizmosql_slim.sh - the container's entrypoint script for more details.
🆘 Help & Support¶
To see all available command-line options:
For support, please email: 📧 info@gizmodata.com
🪪 License¶
Licensed under the Apache License 2.0.
Documentation generated and enhanced by GizmoData™ — 2025