Writing robust data pipelines with AILivestream July 22

Skip to main content

Attach Modes

MotherDuck attach modes: workspace and single modes​

This guide explains MotherDuck's two connection modes: workspace and single. Workspace mode is designed for working with multiple databases persistently across sessions, while single mode uses a non-persistent, isolated session that does not reuse your saved workspace.

tip

TL;DR Use single mode for service accounts and automated workflows, use workspace mode for personal usage across sessions, for example the MotherDuck UI, your AI agent and the local DuckDB CLI.

Connection modes​

MotherDuck offers two connection modes: workspace and single. The mode you use determines how your attachments and detachments are handled and whether these changes are saved for future sessions. Both modes allow you to ATTACH databases, the difference is whether those attachments are remembered for your next session.

  • Workspace Mode is the default mode when you want to work with all attached MotherDuck databases. When you attach or detach a database in this mode, that change is remembered for your next session. This is useful when you consistently work with the same set of databases. Parallel connections to MotherDuck in workspace mode will keep their attachments in sync. E.g. detaching a database in one client in workspace mode will detach it in all other clients that are connected in workspace mode.

  • Single Mode is for when you want a one-time, non-persistent session that does not reuse or change your saved workspace. This is useful in automated workflows and minimizes the catalog size. Any databases you attach or detach during this session will not affect the saved workspace for the next time you connect or interfere with attachment state of other parallel connections to MotherDuck. You can still attach multiple databases in a single-mode session, including databases shared with you. For example, you can start with your own database and then ATTACH 'md:_share/...' to attach a share. Single mode is useful with BI tools that only support a single attached database at a time.

tip

You can't switch between modes in the middle of a session. The mode is set by the first command you use to connect to MotherDuck.

Connecting to MotherDuck with a connection string​

When you first connect to MotherDuck in a session, the connection string you use determines the attach mode. This applies to most of clients, like the DuckDB CLI (duckdb 'md:...') and Python (duckdb.connect('md:...')).

  • To connect in Workspace Mode (default):

    • Use md: or md:<database_name>.
    • This connects to your MotherDuck workspace, attaching all databases from your last saved session.
    • If you specify a database name, it becomes the active database.
    • Any changes to attachments (attaching or detaching databases) are saved and will be restored in your next workspace session.

  • To connect in Single Mode:

    • Use md:<database_name>?attach_mode=single.
    • This connects to the specified database without using your saved workspace.
    • Attachment changes are temporary and will not be saved.
    • Note: You must specify a database name to use single mode. Connecting with md:?attach_mode=single is not allowed, as this mode requires a specific database target.

Connecting to MotherDuck using the ATTACH command​

If you are already in a DuckDB session, but not connected to MotherDuck yet, your first ATTACH command that targets MotherDuck establishes the attach mode for that session.

  • To connect in Workspace Mode:

    • Use ATTACH 'md:'.
    • This attaches your entire saved workspace.
    • The session is now in workspace mode, and any subsequent attachment changes will be persisted for future sessions.

  • To connect in Single Mode:

    • Use ATTACH 'md:<database_name>'.
    • This attaches the specified database without using your saved workspace.
    • The session is implicitly set to single mode. Attachment changes are not saved.
    • Once in single mode, you cannot attach the entire workspace using ATTACH 'md:'.

Tips & tricks​

Further Notes:

  • You can also explicitly set the attach mode before connecting to MotherDuck.
   LOAD motherduck;
   SET motherduck_attach_mode = 'workspace'; -- or 'single'
   ATTACH 'md:foo'; -- database created by your account
  • The MotherDuck UI always connects in workspace mode.