Skip to main content

Creating Databases and Schemas

Learning Focus

Use this lesson to create and organize databases and schemas in DuckDB for structured data management.

DuckDB Database Files​

DuckDB stores an entire database in a single file:

# Create a new database (file created on first connection)
duckdb production.db

# In-memory — nothing persisted after exit
duckdb

Creating Schemas​

Schemas are namespaces that group tables within a database:

-- Create a schema
CREATE SCHEMA analytics;
CREATE SCHEMA staging;
CREATE SCHEMA reporting;

-- List all schemas
SHOW ALL TABLES;  -- includes schema names
SELECT * FROM information_schema.schemata;

-- Set default schema for session
SET search_path = analytics;

Creating Tables​

-- Basic table
CREATE TABLE analytics.employees (
    id         INTEGER PRIMARY KEY,
    name       VARCHAR NOT NULL,
    department VARCHAR,
    salary     DECIMAL(10,2),
    hire_date  DATE,
    active     BOOLEAN DEFAULT true
);

-- With CHECK constraint
CREATE TABLE analytics.products (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0)
);

-- CREATE TABLE IF NOT EXISTS
CREATE TABLE IF NOT EXISTS analytics.logs (
    id         INTEGER,
    event_type VARCHAR,
    logged_at  TIMESTAMP DEFAULT current_timestamp
);

-- CREATE TABLE AS (from query)
CREATE TABLE analytics.high_earners AS
SELECT * FROM analytics.employees WHERE salary > 80000;

Creating Views​

-- Standard view (virtual table)
CREATE VIEW analytics.active_employees AS
SELECT id, name, department, salary
FROM analytics.employees
WHERE active = true;

-- Query the view
SELECT * FROM analytics.active_employees;

-- Replace a view
CREATE OR REPLACE VIEW analytics.active_employees AS
SELECT id, name, department, salary, hire_date
FROM analytics.employees
WHERE active = true;

-- Drop a view
DROP VIEW analytics.active_employees;

Altering Tables​

-- Add a column
ALTER TABLE analytics.employees ADD COLUMN email VARCHAR;

-- Rename a column
ALTER TABLE analytics.employees RENAME COLUMN email TO work_email;

-- Drop a column
ALTER TABLE analytics.employees DROP COLUMN work_email;

-- Rename a table
ALTER TABLE analytics.employees RENAME TO analytics.staff;

Dropping Objects​

DROP TABLE analytics.employees;
DROP TABLE IF EXISTS analytics.employees;
DROP SCHEMA analytics;
DROP SCHEMA analytics CASCADE;  -- drops all tables in the schema

Attaching Multiple Databases​

-- Attach another DuckDB database file
ATTACH 'archive.db' AS archive;

-- Query across databases
SELECT * FROM archive.analytics.employees;

-- Detach
DETACH archive;

Common Pitfalls​

PitfallConsequencePrevention
No schema prefix on large projectsTable name collisionsAlways use schemas to namespace tables
DROP TABLE without IF EXISTSError if table doesn't existAlways add IF EXISTS for idempotent scripts
ATTACH without AS nameCannot reference tablesAlways provide an alias in ATTACH ... AS name

Quick Reference​

CREATE SCHEMA myschema;
CREATE TABLE myschema.t (id INTEGER PRIMARY KEY, name VARCHAR);
CREATE VIEW myschema.v AS SELECT * FROM myschema.t WHERE active = true;
ALTER TABLE myschema.t ADD COLUMN col VARCHAR;
DROP TABLE IF EXISTS myschema.t;
ATTACH 'other.db' AS other;
SELECT * FROM other.schema.table;
DETACH other;

What's Next​