Writing robust data pipelines with AILivestream July 22

Skip to main content

REFRESH DATABASE

There are two types of databases that can be refreshed: database shares and databases attached to read scaling connections.

Read scaling connections sync automatically every minute. To ensure maximum freshness, run CREATE SNAPSHOT on the writer, followed by REFRESH DATABASES on the reader. This pulls the latest snapshot.

Database shares can also be refreshed—either automatically or manually. In this case, the writer uses UPDATE SHARE instead of CREATE SNAPSHOT, followed by REFRESH DATABASES on the reader.

Behavior by connection mode​

How REFRESH DATABASES behaves depends on which attach mode your session is in:

  • Workspace mode (the default; for example, ATTACH 'md:' or the md: connection string): Two things happen. First, refreshable databases in the session (shares and read-scaling) pull their latest snapshot. Second, MotherDuck reconciles the session's attachment list against the server-side workspace, so databases created by other connections (for example, R/W instances) since your last sync are auto-attached. Use this when you want your session to reflect catalog changes made elsewhere.
  • Single mode (for example, md:<database_name>?attach_mode=single connection string, ATTACH 'md:<database_name>' in an existing DuckDB session, or SET motherduck_attach_mode='single' before attaching): Refreshes only the refreshable databases (shares and read-scaling) already attached in the session. New databases on the server are not auto-discovered or attached. This is by design -- single mode keeps your session scoped, doesn't persist attachment changes, and stays out of sync with parallel connections. Attach more databases explicitly if you need them.

Syntax​

REFRESH DATABASES (plural, no name) and REFRESH DATABASE <database_name> (singular, with a name) are two distinct forms; you can't combine them. For example, REFRESH DATABASES my_db fails to parse.

-- Refresh all refreshable databases attached to the current session
REFRESH DATABASES;

-- Refresh a specific share or read-scaling database
REFRESH DATABASE <database_name>;

The single-database form only works for database shares and read-scaling databases. Calling REFRESH DATABASE <name> on a regular R/W database returns an error: "<name> is not a share or database on a read-scaling instance."

The behavior of REFRESH DATABASES depends on how you connected to MotherDuck:

  • Workspace mode (ATTACH 'md:'): Refreshes all databases in your workspace, including new databases created by other connections (e.g., R/W instances). This allows you to pick up databases that were created after your initial connection.

Syntax​

REFRESH { DATABASE | DATABASES } [<database_name>];

Examples​

REFRESH DATABASES;       -- Refreshes all connected databases and shares
┌─────────┬───────────────────┬──────────────────────────┬───────────┐
│  name   │       type        │  fully_qualified_name    │ refreshed │
│ varchar │     varchar       │        varchar           │  boolean  │
├─────────┼───────────────────┼──────────────────────────┼───────────┤
│ <name1> │ motherduck        │ md:<name1>               │ false     │
│ <name2> │ motherduck share  │ md:_share/<name2>/<uuid> │ true      │
└─────────┴───────────────────┴──────────────────────────┴───────────┘

REFRESH DATABASE my_db;  -- Alternatively, refresh a specific database
┌─────────┬──────────────────┬──────────────────────────┬───────────┐
│  name   │       type       │  fully_qualified_name    │ refreshed │
│ varchar │     varchar      │        varchar           │  boolean  │
├─────────┼──────────────────┼──────────────────────────┼───────────┤
│ <name1> │ motherduck share │ md:_share/<name1>/<uuid> │ false     │
└─────────┴──────────────────┴──────────────────────────┴───────────┘

Related Content​