Connecting to MotherDuck

You can connect to one or more databases in the same MotherDuck account by creating a single DuckDB connection.

Create a connection

The below code snippets show how to create a connection to a MotherDuck database from the CLI, Python, JDBC and NodeJS language APIs.

Python

To connect to your MotherDuck database, use duckdb.connect("md:my_database_name") which will return a DuckDBPyConnection object that you can use to interact with your database.

import duckdb

# Create connection to your default database
conn = duckdb.connect("md:my_db")

# Run query
conn.sql("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)")
conn.sql("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)")
res = conn.sql("SELECT * FROM items")

# Close the connection
conn.close()

JDBC

To connect to your MotherDuck database, you can create a Connection by using the "jdbc:duckdb:md:databaseName" connection string format:

// Create connection to your default database
try (Connection conn = DriverManager.getConnection("jdbc:duckdb:md:my_db");
     Statement stmt = conn.createStatement()) {
    // Run query
    stmt.executeUpdate("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)");
    stmt.executeUpdate("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)");

    try (ResultSet rs = stmt.executeQuery("SELECT * FROM items")) {
        while (rs.next()) {
            System.out.println("Item: " + rs.getString(1) + " costs " + rs.getInt(3));
        }
    }
}

NodeJS

To connect to your MotherDuck database, you can create a duckdb.Database with the 'md:databaseName' connection string format:

const duckdb = require('duckdb');

// Create connection to your default database
const db = new duckdb.Database('md:my_db');

// Run queries
db.all('CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)', function(err, res) {
  if (err) {
    console.warn(err);
    return;
  }
  console.log(res[0].fortytwo)
});

CLI

To connect to your MotherDuck database, run duckdb md:<database_name>.

duckdb "md:my_db"

Now, you will enter the DuckDB interactive terminal to interact with your database.

D CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER);
D INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2);
D SELECT * FROM items;

Connect to multiple databases

If you need to connect to MotherDuck and run one or more queries in succession on the same account, you can use a single database connection. If you want to connect to another database in the same account, you can either reuse the same connection, or create copies of the connection.

Python

If you need to connect to multiple databases, you can either directly reuse the same DuckDBPyConnection instance, or create copies of the connection using the .cursor() method.

note

FROM <table name> is a shorthand version of SELECT * FROM <table name>.

Example 1: Reuse the same DuckDB Connection

To connect to different databases in the same MotherDuck account, you can use the same connection object and simply fully qualify the names of the tables in your query.

conn = duckdb.connect("md:my_db")

res1 = conn.sql("FROM my_db1.main.tbl")
res2 = conn.sql("FROM my_db2.main.tbl")
res3 = conn.sql("FROM my_db3.main.tbl")

conn.close()

Example 2: Create copies of the initial DuckDB Connection

conn.cursor() returns a copy of the DuckDB connection, with a reference to the existing DuckDB database instance. Closing the original connection also closes all associated cursors.

conn = duckdb.connect("md:my_db")

cur1 = conn.cursor()
cur2 = conn.cursor()
cur3 = conn.cursor()

cur1.sql("USE my_db1")
cur2.sql("USE my_db2")
cur3.sql("USE my_db3")

res = []
for cur in [cur1, cur2, cur3]:
  res.append(cur.sql("SELECT * FROM tbl"))

# This closes the original DuckDB connection and all cursors
conn.close()

note

duckdb.connect(path) creates and caches a DuckDB instance. Subsequent calls with the same path reuse this instance. New connections to the same instance are independent, similar to conn.cursor(), but closing one doesn't affect others. To create a new instance instead of using the cached one, make the path unique (e.g., md:my_db?user=<unique ID>).

Example 3: Create multiple connections

You can also create multiple connections to the same MotherDuck account using different DuckDB instances. However, keep in mind that each connection takes time to establish, and if connection times are an important factor for your application, it might be beneficial to consider Example 1 or Example 2.

note

If you need to run queries on separate connections in quick succession, instead of opening and closing a connection for every query, we recommend using a Connection Pool (Python, JDBC or NodeJS).

conn1 = duckdb.connect("md:my_db1")
conn2 = duckdb.connect("md:my_db2")
conn3 = duckdb.connect("md:my_db3")

res1 = conn1.sql("SELECT * FROM tbl")
res2 = conn2.sql("SELECT * FROM tbl")
res3 = conn3.sql("SELECT * FROM tbl")

conn1.close()
conn2.close()
conn3.close()

JDBC

If you need to connect to multiple databases, you typically won't need to create multiple DuckDB instances. You can either directly reuse the same DuckDBConnection instance, or create copies of the connection using the .duplicate() method.

try (DuckDBConnection duckdbConn = DriverManager.getConnection("jdbc:duckdb:md:my_db")) {
    Connection conn1 = ((DuckDBConnection) duckdbConn).duplicate();
    Connection conn2 = ((DuckDBConnection) duckdbConn).duplicate();
    Connection conn3 = ((DuckDBConnection) duckdbConn).duplicate();
    // ...
}

NodeJS

If you need to connect to multiple databases, you can use the same duckdb.Database instance and create copies of the connection using the .connect() method.

const db = new duckdb.Database('md:my_db');
const con = db.connect();

con.all('FROM my_db1.main.tbl', function(err, res) {
  if (err) {
    console.warn(err);
    return;
  }
  console.log(res[0].fortytwo)
});