Skip to main content

Managing a shared MotherDuck database

MotherDuck enables you to share snapshots of your databases with your colleagues and friends. To share a database requires creating a SHARE object, which gives you a URL that others can use to read the data. This is a form of Capability-Based Access Control.

Sharing semantics​

Sharing in MotherDuck is currently at the database-level and includes all schemata and tables for the given database. Consumers of a share get a readonly snapshot as of the time they connected. Changes to a share can be published with UPDATE SHARE <share name>

Sharing a database​

You can share databases with teammates by creating a SHARE object, which contains a URL that contains a hard-to-discover UUID. Shares can be read by anyone with the URL by attaching them as databases.

To share a database:

  • Create a SHARE
  • Retrieve a URL
  • Send the URL to your colleague

To Create a share:

CREATE SHARE <share name> [FROM <database name>];
# returns a share URL

For example:

CREATE SHARE duckshare; # creates a share from the current/active database 
CREATE SHARE duckdb FROM duck; # creates a share from database named duck
note
  • you can’t have a share name that has the same name as the database being shared. Typically, you can add _share to the end of the database name to create a share name. For instance : CREATE SHARE ducks_share FROM ducks will create a share named ducks_share from the database ducks.

Getting details about a share​

You can learn more about a specific share that you’ve created by using DESCRIBE SHARE command. For example:

DESCRIBE SHARE "duckshare";

Listing Shares​

You can list the shares you have created via the LIST SHARES statement. For example:

LIST SHARES;

Deleting a share​

Shares can be deleted with the DROP SHARE or DROP SHARE IF EXISTS method. For example:

Users who have ATTACH-ed it will lose access.

DROP SHARE "share1";

Updating a share​

Sharing a database creates a point-in-time snapshot of the database at the time it is shared. To publish changes, you need to explicitly run UPDATE SHARE <share name>. Subscribers will automatically see all raw data related changes (for example, new inserts or updates) within 1 minute. Subscribers will see catalog changes like new or dropped tables/schemas/views/columns (as well as raw data changes) on any new connection (native clients) or page reload (web client). When updating a SHARE with the same database, the URL does not change.

UPDATE SHARE <share name>;

Subscribers to the share see the changes on new connections.

In the following example database ‘mydb’ was previously shared by creating a share ‘myshare’, and the database ‘mydb’ has been updated since. Owner of the database would like her colleagues to receive the new version of this database:

# 'myshare' was previously created on the database 'mydb'
UPDATE SHARE "myshare";

If you lost your database share url, you can use the LIST SHARES command to list all your share or DESCRIBE SHARE <share name> to get specific details about a given share name.