Basics database operations

While embedded DuckDB uses files on your local filesystem to represent databases, MotherDuck implements SQL syntax for creating, listing and dropping databases.

Create database

SQL

-- [OR REPLACE] and [IF NOT EXISTS] are optional modifiers.
CREATE [OR REPLACE | IF NOT EXISTS] DATABASE <database name>;
USE <database name>;

Creating copies of databases in MotherDuck in this manner is a metadata-only operation that copies no data. Learn more in the CREATE DATABASE overview documentation.

Listing databases

SQL

-- returns all connected local and remote databases 
SHOW DATABASES;
-- returns current database    
SELECT current_database(); 

Learn more in the SHOW ALL DATABASES overview documentation.

Delete database

SQL

USE <database name>;
DROP DATABASE <database name>;

Example usage:

> SHOW DATABASES;
test01

-- Let's put two different t1  tables into into two different databases
> CREATE TABLE dbname.t1 AS (SELECT range AS r FROM range(12));
> SELECT * FROM t1;

-- now for the other database
> CREATE DATABASE test02;
> CREATE TABLE test02.t1 AS (SELECT 'test02' AS dbname)

-- show the databases we've created
> SHOW DATABASES;
test01
test02

Learn more in the DROP DATABASE overview documentation.