Guide

DuckDB on Layerbase

Hosted DuckDB is served over the PostgreSQL wire protocol, so you connect with psql or any Postgres driver, not the duckdb CLI. This page explains why the connection string is a Postgres URL, what SQL and introspection work, how to get data in, and how to export a regular .duckdb file.

Choosing a version

New Cloud databases default to DuckDB 2.0.0. Choose version 1.5 to create a database running exactly 1.5.6. Existing databases keep their exact versions through restarts, branches and restores. Older versions are unavailable for new empty Cloud databases.

To check the actual DuckDB version over your SQL connection, run SELECT library_version FROM pragma_version();. The version() function reports a PostgreSQL compatibility string instead.

Why the connection string is Postgres

To make a hosted DuckDB reachable over TCP and TLS, Layerbase Cloud runs it behind a PostgreSQL-compatible proxy (duckgres) on port 5432. You connect with the same tools you would use for Postgres: psql, a Postgres driver in your language, Prisma or Drizzle, or a BI tool. Pointing the duckdb CLI or a DuckDB client library at that Postgres URL will not work. On DuckDB 2 you can also enable a native DuckDB connection for DuckDB clients.

Copy the Postgres-style URL from Quick Connect in the dashboard. TLS is required: append ?sslmode=require. Do not add sslnegotiation=direct here: DuckDB uses the standard Postgres SSL negotiation, and forcing direct negotiation drops the connection.

psql "postgresql://layerbase:PASSWORD@your-host.cloud.layerbase.dev:5432/duck1?sslmode=require"

Authentication is the username and password embedded in that URL, the same as any Postgres connection. See connecting to DuckDB for per-client snippets.

Native DuckDB connection

The owner of a DuckDB 2 database in a personal account can open Connect and choose the Native DuckDB tab. Enable native connection gives you an endpoint and token as a snippet for a DuckDB client, which installs the Quack extension and attaches the database. It reaches the same database as your PostgreSQL-compatible connection. The snippet contains a secret, so keep it out of shared history and logs. Enabling, rotating the token, and disabling each briefly restart the database and interrupt active connections; after a rotation, clients must use the new token. Native requests do not wake a sleeping database. Requests and responses are limited to 16 MiB, so paginate large result sets. DuckDB 1 databases and shared team databases do not offer native connections.

The tab shows the current state. If the container needs a newer runtime first, choose Review runtime update to open Maintenance. If native access is paused because the database needed recovery, choose Enable native access again to resume it. If it is unavailable because its extension could not be installed, the database starts without native access and ordinary SQL keeps working; native access resumes after the next successful start, or you can disable it. Every start reinstalls the pinned extension. Enable and rotate need the database running. Disable also works while the database is stopped, errored, or hibernated: it saves the next-start settings without restarting anything. While native access is configured, branch, restore, move, transfer, and delete are refused until you disable it. Backups still run.

A plain ATTACH '<snippet url>' AS <alias> (TYPE quack) lets your client query the database's default layerbase catalog. DuckLake tables (ducklake.*) and attached source catalogs (for example pg_source.*) appear in the client's duckdb_tables(), but querying them directly raises a CatalogException. Run those queries on the server with quack_query_by_name instead.

-- First run the full Connect snippet: it creates the Quack secret and the
-- ATTACH with your token. Keep the token out of shared history.
-- Replace lb below with the alias your snippet attaches.

-- DuckLake and attached sources: run the query on the server
SELECT * FROM quack_query_by_name('lb', 'SELECT * FROM ducklake.events LIMIT 10');
SELECT * FROM quack_query_by_name('lb', 'SELECT count(*) FROM pg_source.public.users');

What works

Once connected, you write DuckDB SQL as normal. Standard SQL, information_schema, and most pg_catalog introspection work, so a client or BI tool can browse your tables and columns. The database is a real DuckDB file underneath, so DuckDB functions and types behave the way they do locally.

Internal tables you may see

The proxy materializes its Postgres-compatibility shims as real tables in your schema, including some information_schema copies and __duckgres-prefixed bookkeeping tables (for example __duckgres_column_metadata). The in-dashboard query console filters these out, so its Tables sidebar shows only your own tables. A raw client listing (psql \dt or a SELECT against information_schema.tables) will still show them alongside yours. They are safe to ignore.

Importing data

The hosted DuckDB has no access to your local filesystem, so you cannot ATTACH or read a file straight off your machine over the wire. Use one of these instead.

Upload a file in the query console. The Import button (and drag-and-drop onto the editor) turns a CSV, TSV, JSON, NDJSON, or Parquet file into a table. The upload ceiling is 100 MB per file. In the console's Tables sidebar, a table imported this way shows a Parquet, CSV, or JSON symbol instead of the plain table icon; hover it to see the file it came from. Layerbase records this as the table's comment (layerbase:import:<type>:<file name>), which stays in the database file. Appending to an existing table keeps its comment.

Query a remote file in place. If your data is at a URL, DuckDB can read it directly without importing.

-- Turn a hosted file into a view, no upload needed
CREATE OR REPLACE VIEW sales AS
SELECT * FROM read_parquet('https://example.com/sales.parquet');

-- Or CSV / JSON
SELECT * FROM read_csv_auto('https://example.com/data.csv');

Outbound connections. DuckDB ATTACH and httpfs reads can reach any public internet address. Plaintext connections to your own servers are allowed but are not encrypted, so prefer TLS (https://, or a source that requires TLS). Private and internal addresses (RFC 1918 ranges, carrier-grade NAT, link-local and cloud metadata addresses, loopback, and their IPv6 equivalents), this server's own addresses, and other customers' containers on it are refused immediately: the client sees connection refused rather than a timeout. Layerbase-managed sources and DuckLake catalogs attach inside your container and are unaffected.

Standard INSERT statements from any Postgres client work too. To restore a whole existing DuckDB database, upload its .duckdb file through the console import.

Exporting

For standalone DuckDB, export your database from the dashboard and you get back a regular .duckdb file. It is a standard DuckDB database: open it with local DuckDB, the duckdb CLI, or the desktop app. Nothing about the hosted format is proprietary.

Exporting a query result or a table

In the query console, the result Export menu has two DuckDB-only formats at the top: Parquet and Excel (.xlsx). These run the statement behind the result again on the server and write the file with DuckDB itself, so every row of the result is included (not just the rows on screen) and column types survive: numbers stay numbers, dates stay dates. The other formats in the menu (CSV, TSV, JSON, Markdown, SQL INSERT) are built in your browser from the rows shown.

To download a whole table, hover it in the console’s Tables list and pick Download table as Parquet or CSV. To export a DuckLake or attached-source table, run SELECT * FROM ducklake.main.events and export the result as Parquet; this works on a read-only lake too.

• Only a single read query can be exported: one statement starting with SELECT, WITH, FROM or VALUES.
• Each export file is limited to 256 MiB and the query to 2 minutes. Past either limit the export stops with a message; filter rows, select fewer columns, or export the whole database instead.
• Excel files stop at Excel’s own limit of 1,048,576 rows. Use Parquet or CSV for more.
• One export runs per database at a time. A sleeping database wakes for an export the same way it does for a query. A stopped export (over a limit, or canceled) finishes only once DuckDB has stopped writing, so the next export can start right after it.
• Parquet and Excel exports need the current DuckDB 2.0 runtime on the database. Where it is not installed yet, the export answers with a message and the browser formats in the menu still work.

Account sources and DuckLake

See the DuckLake overview for workflows, SQL examples and lifecycle restrictions. Two options are in Beta on Pro and Dedicated, with no invitation or enrollment: keeping the lake in your own R2 or S3 bucket and external PostgreSQL sources.

Query PostgreSQL and MariaDB separately or join both in one DuckDB query. When DuckDB starts or wakes, Layerbase first wakes any hibernated source or catalog database, and keeps it awake while the DuckDB database runs. A stopped dependency is not started for you.

On a DuckDB 1.5 or DuckDB 2 database, open Sources to attach a PostgreSQL or MariaDB database from the same personal account and the same server container. Layerbase creates a dedicated SELECT-only login for that source. The attachment is read-only. Query it by the alias you choose, for example pg_source.public.orders or maria_source.customers. Removing a source revokes that login and does not delete the source tables. Shared teams, app backing stores, and databases on another container are not eligible.

The query console's Tables sidebar lists each attached source under its alias, below your local tables. Expand a source to load its tables from the source database (the list is not loaded until you open it), and use its refresh button to pick up tables created at the source since. Clicking a table runs a SELECT with its full name: alias.schema.table for PostgreSQL and alias.table for MariaDB. While a source's database is down, DuckDB cannot list tables at all, so the sidebar stays empty (local tables included) until it is back. Queries on your local tables keep working.

Managed DuckLake is available on Pro and Dedicated accounts without an invitation. Read-only account sources are available on every plan, including Free, within your database limits.

DuckDB 2 can also enable DuckLake 1.1. Pick a PostgreSQL database in the same account as the metadata catalog. Parquet objects go to a private bucket for that account (or your own bucket). The lake is attached as ducklake and does not replace your local tables. Copy rows yourself, for example CREATE TABLE ducklake.main.orders_snapshot AS SELECT * FROM pg_source.public.orders. That copy is a snapshot, not ongoing replication. DuckDB 1 databases are left unchanged. Detach keeps the catalog and the objects. Catalog-and-object recovery is operator-assisted; self-service recovery is not available. Source attachments block branch, restore, move, transfer, and delete until removed.

If a source or the catalog cannot be woken or fails to attach, the DuckDB database starts without it so local SQL keeps working. In the Sources tab, each source and the lake carry a status badge with an explanation underneath. A source left out this way shows Not attached, and Attaches on restart once its database is available again. It attaches on the next start of the DuckDB database (a wake after sleep, a restart, or an attach or detach). Layerbase never restarts a running DuckDB database to re-attach a dependency, so open sessions are not ended for it. A source or lake that fails to attach while the DuckDB database is running shows Restart required: restart the database to continue without it, or fix it and restart. For both restart states the Sources tab asks you to stop and then start the database with the Stop and Start controls at the top of the database page; nothing restarts it automatically. In the API these states are the statusDetail values dependency_unavailable, dependency_reattach_pending, and dependency_restart_required, with a readable statusMessage beside them.

Managed lake storage and Reclaim storage

Managed lake storage has its own allowance, equal to your plan storage and separate from local database storage: 25 GB on Pro, plus 25 GB for each pool block. Layerbase measures the bucket about once an hour, and the lake shows a warning at 80% of the allowance. When it goes over, the lake turns read-only: queries and exports keep working, nothing is deleted, and new writes fail. Open sessions switch to read-only without a restart or a dropped connection, within seconds on databases running the current DuckDB 2 runtime and within about five minutes on databases still on an earlier one. A transaction that was already open becomes read-only once it ends. To free space yourself, drop the tables you no longer need (dropping a table changes only the catalog, so it works while the lake is read-only), then press Reclaim storage on the lake in the Sources tab. Reclaim expires snapshots that stopped being current more than an hour ago, so time travel to them ends, and deletes only the files the lake no longer references. A file is deleted only after it has been unused for an hour, which gives queries still reading it time to finish (a query that keeps reading an expired snapshot for longer can fail). Files freed by the run are deleted automatically about an hour later while the database and its catalog are running. Each run measures usage again and restores writes once usage is back under the allowance. Nothing restarts and no session ends. Reclaim is offered once the lake is at the warning level or over, one run at a time, at least 10 minutes apart. It applies only to Layerbase-managed storage, never to your own bucket. Adding a pool block raises the allowance instead.

Delete lake data

Detaching keeps a lake's catalog and files, and the lake still counts as the account's one DuckLake and against its storage allowance. To free it for good, the account owner can press Delete lake data on a detached lake in the Sources tab and type the DuckDB database's name to confirm. Nothing is deleted for 7 days: the lake shows the deadline in your local time with an Undo button, the owner gets an email when it is scheduled and a reminder about a day before, and the lake cannot be attached again until it is undone. After the deadline every file of the lake in Layerbase managed storage is deleted permanently. There is no backup of these files. Then the lake's own catalog (its logical database and login inside your PostgreSQL catalog database) is dropped; the PostgreSQL database itself and your other tables are kept, and the catalog is dropped the next time that database is running if it is asleep. Once the files are gone the lake no longer counts, a new lake can be created, and the DuckDB and catalog databases can be deleted normally. On a lake in your own bucket, Layerbase forgets the storage credentials as soon as you confirm and drops the catalog after the 7 days, but never lists or deletes anything in your bucket: the files stay there, no longer referenced by any lake. After an Undo on such a lake, rotate its storage credentials before attaching it again.

If you need the databases gone sooner, for example to get under your plan's database limit, choose Delete now instead of Schedule deletion, or press Delete now on a deletion that is already scheduled. It skips the undo window and starts right away, usually finishing within a few minutes, and it cannot be undone. When it finishes, the DuckDB and catalog databases can be deleted. While a lake still holds them, the delete dialog of either database names the DuckDB database with the lake and says when it can be deleted.

Deleting your Layerbase account deletes its managed lake data too. With the erase option (delete all backups) the lake files and the account's lake bucket are deleted immediately, as part of the account deletion. Without it, the lake files are deleted 7 days later, the same window as Delete lake data, and the owner gets the same two emails. Files in your own bucket are never deleted.

Customer-owned lake storage Beta

On Pro and Dedicated, when you enable DuckLake you can choose your own Cloudflare R2 or AWS S3 bucket instead of Layerbase-managed R2. Enter the bucket, the AWS region or Cloudflare account ID, an optional prefix, and an access key. Session tokens are accepted with an expiry at least five minutes away. Only the standard AWS and Cloudflare endpoints are supported, not other S3-compatible services. Layerbase creates a new, empty prefix under <your prefix>/layerbase/ and checks access by writing, reading and deleting one small temporary object. Use credentials scoped to that bucket or prefix that can list, read, write and delete objects. Your provider bills storage, requests and transfer, and Layerbase does not count these bytes as managed lake usage.

One lake per personal account, and a detached lake still uses that slot. The bucket, region, prefix and catalog cannot change later. To replace credentials, open Rotate storage credentials on the lake: new credentials are checked before they are saved, and a running DuckDB database restarts to use them. Rotation keeps working after a plan change so your data is not stranded. Detach keeps the catalog and the objects. Layerbase never lists or deletes files in your bucket, not even for Delete lake data or an account deletion, which only forget the credentials and drop the catalog. Lake data in your storage is not covered by Layerbase backups (see Backups and DuckLake).

Expired credentials never keep the database down. If your credentials have expired, expire within about ten minutes, or cannot be used when DuckDB starts, the database still starts with local SQL and sources, and the lake is left out and marked Not attached with a message about its credentials. A running database keeps running, but lake queries fail until you rotate. Rotating restarts the database with the lake attached. If the provider rejects the key or the bucket policy changes, lake queries fail and the lake shows the reason. Restoring access to the same key needs no restart; rotating the key restarts the database as above. Ordinary SQL keeps working throughout. Use Test storage on the lake to check again at once.

External PostgreSQL sources Beta

Pro and Dedicated accounts can attach a PostgreSQL database hosted outside Layerbase to a standalone DuckDB 2 database on a personal team. Enter a public hostname or IP address, port, database, username, password, and an optional PEM CA certificate. Connection-string options, Unix sockets and TLS downgrades are not accepted. TLS is always verified: the certificate must chain to a trusted public CA or to the CA you paste, and must name the hostname you entered, so use the hostname your provider gives you rather than an IP address or another name for the same server. The hostname must resolve only to public addresses. Layerbase checks it when you save and again on each start of the DuckDB database, and connects to the address it checked. The DuckDB database needs an updated source runtime; Sources tells you when an update is required.

The login must be read-only. Layerbase refuses a login that is, or is a member of, a role with SUPERUSER, CREATEDB, CREATEROLE, REPLICATION or BYPASSRLS; one that can INSERT, UPDATE, DELETE, TRUNCATE or add triggers on any table or view outside the system catalogs; one that can create objects in any schema; and one that can create schemas in the database. DuckDB also attaches the source read-only. These checks run when you save and on every test. Keep the grants SELECT-only afterwards: a write grant added later at your provider is not detected until the next test. A dedicated login like this one passes:

CREATE ROLE layerbase_reader LOGIN PASSWORD 'choose-a-password' NOINHERIT;
GRANT CONNECT ON DATABASE analytics TO layerbase_reader;
GRANT USAGE ON SCHEMA public TO layerbase_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO layerbase_reader;
-- PostgreSQL 14 and earlier: every login can create in public until you revoke it.
-- This affects every role that relies on that default, not only this login.
-- Grant CREATE ON SCHEMA public explicitly to roles that still need it first.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Server-side extensions keep running on your provider. Layerbase never installs, changes or stops extensions such as pg_net or pg_cron. Some of these extensions grant write access to PUBLIC, which includes your login. On Supabase, pg_net grants all privileges on net.http_request_queue and net._http_response, and pg_cron grants SELECT and DELETE on cron.job_run_details. Your login cannot revoke those grants, so a database with those extensions installed fails the read-only check. Point the source at a separate database on the same server where they are not installed and copy or expose the tables you need there.

If saving fails, the message names the cause: the hostname did not resolve, it resolves to a non-public address, the host is unreachable or timed out, the server does not offer TLS, TLS verification failed, the login was rejected, the database does not exist, access was denied, or the login is not read-only (with each check it failed). Nothing is saved when the check fails. A host that only has an IPv6 address may be unreachable; use an IPv4 endpoint such as your provider's connection pooler.

Test connection on a source checks it without changing the running database: nothing is attached, no configuration is written and the database does not restart. For an external source it first connects from Layerbase with the saved settings, then queries the source through your DuckDB database and confirms a write is refused. The result uses the same causes as saving. A passing test shows the source as Connected. A failed test marks the source Unavailable with the reason (in the API, statusDetail external_ followed by the cause, such as external_tls_failed, plus unavailableCategory). The next passing test, saved change or start of the DuckDB database clears it, so test again after a restart if the cause may remain. Queries that use the source fail while it is unreachable. Error messages never repeat your password or the provider's error text.

To change credentials, replace the username, password or CA. The hostname, port and database cannot change; remove the source and add it again for a different server. New credentials are checked before they are saved, and saving restarts the DuckDB database briefly. Removing an external source detaches it and deletes the saved credentials, but does not revoke the login at your provider or change any data there. External MariaDB and MySQL sources are not available.

Backups and DuckLake

DuckDB backups copy the local database file, and each copy is verified before the backup is marked complete. Databases with only account sources or a native connection back up normally; the backup does not include the live source data. Layerbase does not back up a DuckLake. The Parquet objects in R2 or S3 are never copied, and the catalog follows its own PostgreSQL database's scheduled backups. A DuckDB database with a retained lake, even after detach, is not covered by local backups: the Backups tab shows a notice saying so, Backup Now is grayed out (the API refuses it with ducklake_attached), and scheduled backups record a not-covered attempt instead of a copy. Branch, in-place restore, move, transfer, and delete stay blocked for it and for its catalog database. Restore to new still works from a backup taken before the lake: it restores only the local database file into a new database, without the lake, and the dialog says so.

To check a DuckDB backup without overwriting the original, the team owner can choose Restore to new on any backup in the Backups tab. Layerbase creates a new DuckDB database named <database>-restore-<yyyymmdd> with the same version and team, on the same server, waits for it to start, and restores the backup into it. The original is never changed. The new database counts toward your plan's database limit like any other (the dialog shows your current count first), and it is created without scheduled backups so it can receive the backup. If a step fails, the dialog shows why and links to the new database so you can check it before deleting it from its own page. Switch to the team that owns the original first if you have several teams. The same restore is available in the API with an existing target that is fresh (created after the backup or within the last day, and never backed up) and empty: POST /v1/databases/:id/backups/:backupId/restore-into/:targetId. A target that holds any tables, views, sequences, macros, types, or schemas of your own is refused, and a key scoped to one database cannot target another. See Backups.

Sources checks container runtime readiness before enabling a new attachment. If an update is required, choose Review update to open Maintenance, review the affected databases, and arrange the restart. If self-service execution is unavailable, request assistance there. Return to Sources and choose Check runtime again after maintenance. Container updates preserve data and selected engine versions; they do not convert DuckDB 1 databases to DuckDB 2. Missing source runtime artifacts require support, even when the container image is current.

Plan and local use

DuckDB is a Standard engine included on the Free tier. A Free database hibernates after 15 minutes of inactivity (six hours on paid plans) and wakes on the next connection, usually in one to two seconds. You can also run DuckDB locally with SpinDB or Layerbase Desktop; the database files are portable across local, desktop, and cloud.