Introducing Flights: agent-native data pipelines in MotherDuckJoin the livestream

Skip to main content

ATTACH

The ATTACH command in MotherDuck can be used to:

  • Attach a local database to access local data
  • Re-attach a previously detached MotherDuck database or share
  • Attach a public share created by any MotherDuck user in the same cloud region as your Organization, including users outside your Organization

Attaching databases​

Syntax for databases​

ATTACH 'md:<database_name>'

Parameters:

  • database_name: The name of the MotherDuck database to attach. Pass an empty string (md:) to attach all databases in your workspace -- see the attach-mode behavior below.

Attach mode set by ATTACH​

If your session has not connected to MotherDuck yet, the form of the ATTACH string determines which attach mode the session enters:

  • ATTACH 'md:' -- enters workspace mode. Attaches every database in your saved workspace. Subsequent attach/detach changes are persisted and stay in sync with parallel connections.
  • ATTACH 'md:<database_name>' -- enters single mode. Attaches only the named database without using your saved workspace. Attachment changes are session-scoped and not persisted. You can still ATTACH additional databases mid-session, including shares (for example, ATTACH 'md:_share/<owner>/<share_id>').

The mode is set by the first command that connects to MotherDuck and can't be switched mid-session. That first command isn't always an ATTACH: client connection strings like duckdb.connect('md:') in Python or duckdb 'md:<database_name>' from the CLI also establish the mode. If the session is already connected in workspace mode, ATTACH 'md:<database_name>' just adds that database to the workspace rather than switching to single mode.

Important notes for database attachment​

Local database ATTACH operations:​

  • Are temporary and last only for the current session
  • Data stays local and isn't uploaded to MotherDuck
  • Use file paths instead of share URLs

MotherDuck database ATTACH operations:​

  • Are persistent, as they attach the database/share to your MotherDuck account.
  • Requires read/write permissions for the database.
  • The database must have been created by the active user and must have already been detached.
  • If the remote database was not detached prior to running the ATTACH command, using the md: prefix will produce an error rather than creating a local database and attaching it.
  • For a remote MotherDuck database, the database name is used to indicate what to attach and no alias is permitted.
Aliasing Databases

Aliasing behavior in MotherDuck considers your relationship to the database itself. For databases owned by your user, aliases are not allowed. You will see an error that says Database aliases are not yet supported by MotherDuck in workspace mode when attempting to do this.

Attaching shares​

Sharing in MotherDuck is done through shares. Recipients of a share must ATTACH the share, which creates a read-only database. This is a zero-copy, zero-cost, metadata-only operation. Learn more about sharing in MotherDuck.

note

Shares are region-scoped based on your Organization's cloud region. MotherDuck Organizations are scoped to a single cloud region that must be chosen when creating the organization on sign up.

Important

If a shared database contains views or other objects that reference tables using the fully qualified name, for example, my_db.main.my_table, you must alias the share to match the original database name. Otherwise, those references can't be resolved and queries against those views fail. For example ATTACH md:_share/ducks/e9ads7-dfr32-41b4-a230-bsadgfdg32tfa as birds; means a view that does SELECT * FROM ducks will no longer be able to resolve the correct table. See Views and fully-qualified table references for details.

Syntax for shares​

ATTACH <share URL> [AS <database name>];

Shorthand convention for shares​

You may choose to name the new database by using AS <database name>. If you omit this clause, the new database will be given the same name as the source database that's being shared. For shares, database aliases are optional, the default name is the string following _share, i.e. ATTACH md:_share/ducks/e9ads7-dfr32-41b4-a230-bsadgfdg32tfa; will have the alias ducks. When attaching a share, the alias name remains in effect for as long as the database is attached. If the database is detached for any reason, the associated alias name is automatically cleared as well.

Examples​

Attaching databases​

-- Attach a specific MotherDuck database. If this is the first MotherDuck
-- connection in the session, the session enters single mode; if the session
-- is already in workspace mode, the database is added to the workspace.
ATTACH 'md:<database_name>';

-- Attach all MotherDuck databases in the workspace (enters workspace mode
-- if no MotherDuck connection has been made yet).
ATTACH 'md:';

-- Attach a local database
ATTACH '/path/to/my_database.duckdb';
ATTACH 'a_new_local_duckdb';

Listing shares​

-- your shares
FROM MD_INFORMATION_SCHEMA.OWNED_SHARES;

-- list all shares from service accounts
FROM MD_INFORMATION_SCHEMA.SHARED_WITH_ME WHERE owner LIKE 'sa-%';

Attaching shares​

ATTACH 'md:_share/ducks/0a9a026ec5a55946a9de39851087ed81' AS birds;   # attaches the share as database `birds`
ATTACH 'md:_share/ducks/0a9a026ec5a55946a9de39851087ed81';            # attaches the share as database `ducks`

Troubleshooting​

Finding share URL​

The Share URL is provided when you create a share.

You can also see shares available for attachment by querying the MD_INFORMATION_SCHEMA.SHARED_WITH_ME view.

Share alias conflicts​

When you attach a share, its alias defaults to the source database name (the string after _share/ in the URL). If you already have a database with that name, the attach fails with:

Can't open share: Share alias cannot be the same as an existing database name.
<share_alias_name> is already taken and used as a database name.

To resolve this, either:

  1. Use a different alias when attaching the share:

    ATTACH 'md:_share/analytics/abc123' AS analytics_shared;

  2. Detach or rename the conflicting database so the share can use the default alias.

This commonly happens when a service account used for embedding Dives owns a database with the same name as one of the Dive's shared databases. In that case, detach the database from the service account or use a dedicated service account that does not own conflicting databases.

Handling name conflicts between local and remote databases​

In case of name conflict between a local database and a remote database, there are two possible paths:

  1. Attach the local database with a different name using an alias with AS. For instance : ATTACH 'my_db.db' AS my_new_name
  2. Create a share out of your remote database and attach it with an alias. Shares are read-only.

Using SHARES with read scaling replicas​

A database cannot be attached with a read_scaling token. Databases should first be attached by an account + token with read_write permission, then accessed through read_scaling tokens.

For more information, see the Attach & Detach guide.