For AI agents: the complete documentation index is at llms.txt. Every page is also available as markdown by appending .md to its URL, or by sending an Accept: text/markdown request header.

Copy a schema to another instance

Recreate the structure of one QuestDB instance on another: tables, views, materialized views, and, on QuestDB Enterprise, the access control layer.

Problem​

You want to copy the schema from one QuestDB instance to another.

Solution​

SHOW CREATE DATABASE returns a data-free dump of the whole database, with one round-trippable DDL statement per row:

Dump the database schemaDemo this query
SHOW CREATE DATABASE;

Statements come back in dependency order, so replaying them top to bottom on an empty instance recreates the database.

Get the dump as JSON​

Running the statement through the REST API gives you the same result set as JSON, with the statements under .dataset[][0]:

curl -s -G "https://demo.questdb.io/api/v1/sql/execute" \
  --data-urlencode "query=SHOW CREATE DATABASE"

From there you can either save the statements to a file or apply them straight to the target instance.

Write the statements to a .sql file​

curl -s -G "https://demo.questdb.io/api/v1/sql/execute" \
  --data-urlencode "query=SHOW CREATE DATABASE" \
| jq -r '.dataset[][0] | sub(";?$";";") + "\n"' > schema.sql

This keeps QuestDB's original multi-line formatting. sub(";?$";";") anchors to the end of the whole string, not to each line, so it guarantees exactly one trailing semicolon per statement without touching the intermediate lines. Appending "\n" separates consecutive statements with a blank line.

Apply directly to the target instance​

Send each statement to the target's /execute endpoint as you read it:

curl -s -G "https://demo.questdb.io/api/v1/sql/execute" \
  --data-urlencode "query=SHOW CREATE DATABASE" \
| jq --raw-output0 '.dataset[][0]' \
| xargs -0 -n1 sh -c \
    'curl -sG "http://localhost:9000/api/v1/sql/execute" \
       --data-urlencode "query=$1"; echo' _

Statements are sent one at a time on purpose. /execute is a GET endpoint, so a whole schema sent as a single request would very likely exceed the maximum request size the target accepts.

--raw-output0 emits NUL-separated values rather than newline-separated ones, which matters because each statement is itself multi-line. xargs -0 -n1 then hands one whole statement to each sh -c invocation, and the trailing _ fills in $0 so the statement lands in $1.

Each response is printed on its own line. A statement that succeeds returns {"ddl":"OK"}; one that fails returns HTTP 400 and an error field, for example {"query":"...","error":"table already exists","position":13}.

note

--raw-output0 requires jq 1.7 or later.

If either instance requires authentication, pass the credentials on the corresponding curl call. QuestDB open source supports HTTP basic authentication only, so use -u user:password. On QuestDB Enterprise, prefer a REST API token over basic authentication, since it can be scoped, given an expiry, and revoked on its own. Read it from an environment variable rather than pasting it into the command, so it stays out of your shell history:

export QDB_TOKEN="your-rest-api-token"

curl -s -G "https://source:9000/api/v1/sql/execute" \
  -H "Authorization: Bearer $QDB_TOKEN" \
  --data-urlencode "query=SHOW CREATE DATABASE"

Enterprise: schema without permissions​

In QuestDB Enterprise, the default dump also carries the access control layer: users, groups, service accounts, memberships, and grants. That is usually not what you want when seeding a development instance from production.

Select what to copy with INCLUDE:

# Structure only, no users or permissions
curl -s -G "https://source:9000/api/v1/sql/execute" \
  -H "Authorization: Bearer $QDB_TOKEN" \
  --data-urlencode "query=SHOW CREATE DATABASE INCLUDE (SCHEMA)"

# Permissions only, no tables or views
curl -s -G "https://source:9000/api/v1/sql/execute" \
  -H "Authorization: Bearer $QDB_TOKEN" \
  --data-urlencode "query=SHOW CREATE DATABASE INCLUDE (ACL)"

The ACL categories each require the matching LIST or USER DETAILS permission, while the schema categories need none, so a user with only SELECT can still dump the structure.

Passwords and tokens are never dumped, so CREATE USER and CREATE SERVICE ACCOUNT statements replay without credentials. Set those on the target after the replay.

Manual alternative: copy from the Web Console​

For a one-off copy, or when you only want a few objects, the schema explorer toolbar puts the DDL on your clipboard with no scripting:

  1. Click Select Mode, the checkbox circle icon in the toolbar.
  2. Click Select all, the circle icon, or tick individual objects.
  3. Click Copy schemas to clipboard.

Paste the result into an editor, or straight into the target's Web Console and run it. Selection mode exits once the copy succeeds.

Reorder chained materialized views first

The Web Console groups the copied statements by object type and sorts them alphabetically within each group, not in dependency order. Tables always precede materialized views, but a materialized view built on another materialized view can be emitted before its own base.

Related documentation