TopolearnChoosing in-memory versus file-backed databases decides whether your work is kept and how others can open it.
The DuckDB CLI is a single binary: run duckdb for an in-memory session or duckdb analytics.duckdb to open (or create) a database file. The same file can be opened from Python with duckdb.connect("analytics.duckdb"), from R, Java, Node and other clients, because the format is the same everywhere.
Inside the shell you type SQL ending in ; plus dot commands:
.tableslists tables,.schemashows their DDL.modeswitches output style (duckboxdefault,csv,markdown,line, ...).timer onprints how long each query took.read script.sqlruns a file;.quitexits
Tables are created with ordinary SQL. CREATE TABLE t AS SELECT ... (CTAS) is the common way to materialise a query or a file into a table. Everything you create in a file-backed database is persisted automatically; there is no separate save step, and committed transactions are durable.
You can also ATTACH additional database files to one session and query across them with db_name.table names, which makes copying data between files a single INSERT ... SELECT.
Since version 1.0 the storage format is backwards compatible: newer DuckDB versions read files written by older ones.
Example
-- in the shell: duckdb shop.duckdb
CREATE TABLE products (id INTEGER PRIMARY KEY, name VARCHAR, price DECIMAL(8, 2));
INSERT INTO products VALUES (1, 'pen', 1.50), (2, 'book', 12.00);
CREATE TABLE expensive AS -- CTAS: materialise a query
SELECT * FROM products WHERE price > 5;
ATTACH 'archive.duckdb' AS archive; -- a second database file
CREATE TABLE archive.products AS SELECT * FROM products;
SELECT count(*) FROM archive.products; -- 2
DETACH archive;Remember
duckdb file.duckdbopens or creates a persistent single-file database- No file argument means in-memory: data is lost on exit
CREATE TABLE ... AS SELECTmaterialises queries and filesATTACHlets one session work with several database files
Watch out for
- Forgetting the file name and losing an afternoon of work in an in-memory session.