Connecting to a Database

DuckDB offers two types of database storage locations: file-based (persistent) and in-memory (temporary). Understanding the difference between the two is essential before you start storing data.

File-Based Database (Persistent)

When you open a database with a file name, such as duckdb practice.duckdb, all tables and data are stored in that file. The data remains stored even after you exit the CLI and reopen the database later.

Example in the CLI:

duckdb practice.duckdb

All changes (CREATE TABLE, INSERT, etc.) are written to the practice.duckdb file.

In-Memory Database (Temporary)

If you don't provide a file name, DuckDB uses an in-memory database. The data is only available for the duration of the active session and is lost when you exit.

duckdb

Or explicitly using :memory::

ATTACH ':memory:' AS memdb;

An in-memory database is ideal for quick experiments or temporary analysis that doesn't need to be saved.

Connecting from Python

With the Python duckdb library, you can create connections easily:

import duckdb

# Connection to a file database (persistent)
con = duckdb.connect("practice.duckdb")

# In-memory connection (temporary)
con_mem = duckdb.connect()

Usage example:

import duckdb

con = duckdb.connect("practice.duckdb")

# Run a query
result = con.execute("SELECT 6 * 7 AS answer").fetchall()
print(result)  # [(42,)]

con.close()

Always close the connection with con.close() when you're done to release resources.

Opening Multiple Databases (ATTACH)

DuckDB lets you connect more than one database in a single session using the ATTACH command. This is useful if you want to move data between databases.

-- Connect an external database
ATTACH 'report.duckdb' AS report;

-- View a table from the report database
SELECT * FROM report.transactions;

-- Disconnect when you're done
DETACH report;

Checking Connected Databases

To see the list of databases currently connected, use:

SHOW DATABASES;

To see the tables in the active database:

SHOW TABLES;

Summary

DuckDB supports file-based (persistent) and in-memory (temporary) databases. From the CLI or Python, you can choose the storage location that fits your needs, and use ATTACH to work with multiple databases at the same time.

In the next chapter, we'll learn how to create tables with CREATE TABLE and define column data types.