Skip to main content

CREATE SHARE

The CREATE SHARE statement creates a new share from a database. This command is used to share databases with other users. Learn more about sharing in MotherDuck.

note

All shares are read-only. Only the creator of a database has write permissions.

Syntax​

CREATE [ OR REPLACE ] SHARE [ IF NOT EXISTS ] [<share name>] [FROM <database name>] (
    [ACCESS ORGANIZATION | UNRESTRICTED | RESTRICTED],
    [VISIBILITY DISCOVERABLE | HIDDEN],
    [UPDATE MANUAL | AUTOMATIC],
    [INCLUDE_PATTERN '<pattern list>']
);

If you attempt to create a share, yet a share with that name already exists, no new share will be created and the query will return an error. The error will be silenced when you specify IF NOT EXISTS.

This statement returns a share URL of the form md:_share/<source_database_name>/<share_token>.

  • If the share is Hidden, you must pass this URL to the data consumer, who will need to ATTACH the share.
  • If the share is Discoverable, passing the URL to the data consumer is optional.

OR REPLACE clause​

When you use the OR REPLACE clause to create or replace a share named foo, the share's URL changes. This means that any clients connected to the old share URL will be disconnected within a few minutes.

To continue using the share named foo, clients must re-attach to it using the new URL provided by the CREATE SHARE command. The old share URL will no longer be valid.

ACCESS clause​

You can configure scope of access of the share:

  • ACCESS ORGANIZATION (default) - equivalent to granting READ to the Explorer role, so every preset role can access the Share.
  • ACCESS UNRESTRICTED - all MotherDuck users in the same cloud region as the share creator can access the share.
  • ACCESS RESTRICTED - the share owner will be the only user with access to the share initially. Access for other users or roles can be granted using the GRANT and REVOKE commands.

If omitted, defaults to ACCESS ORGANIZATION.

warning

ACCESS ORGANIZATION is planned for deprecation in favor of role-based access control. It is equivalent to granting READ on the share to the Explorer role. To share with your whole organization, create a restricted share and run GRANT READ ON SHARE <share> TO ROLE explorer. See Roles and access control.

note

Shares are region-scoped based on your Organization's cloud region. Each MotherDuck Organization is scoped to a single cloud region that must be chosen at Org creation when signing up.

MotherDuck is available on AWS in six regions:

  • US East (N. Virginia): us-east-1
  • US West (Oregon): us-west-2
  • Europe (Frankfurt): eu-central-1
  • Europe (Dublin): eu-west-1
  • Asia Pacific (Tokyo): ap-northeast-1
  • Asia Pacific (Sydney): ap-southeast-2

VISIBILITY clause​

For Organization-scoped and Restricted Shares, you may choose to make them Discoverable:

  • VISIBILITY DISCOVERABLE (default) - organization members with access can list or find the Share in the UI or SQL.
  • VISIBILITY HIDDEN - the share can only be accessed directly by the share URL, and is not listed to other users. A Share can be hidden only if it has its ACCESS set to RESTRICTED.

If omitted, Organization-scoped and Restricted shares default to VISIBILITY DISCOVERABLE. Unrestricted shares can only be Hidden.

UPDATE clause​

Shares can be automatically or manually updated by the share creator.

  • UPDATE AUTOMATIC - the share is automatically updated when the underlying database changes. Typically, changes on the underlying database will automatically be published to the share within at most 5 minutes, after writes have completed. Ongoing overlapping writes may prolong share updating.
  • UPDATE MANUAL - shares are only updated using the UPDATE SHARE command.

If omitted, defaults to UPDATE AUTOMATIC.

note

On DuckDB clients 1.5.4 and lower, the default is UPDATE MANUAL. The MotherDuck UI and the PostgreSQL (pg) endpoint always default to UPDATE AUTOMATIC because MotherDuck manages the client version. A third-party integration such as Hex picks up the UPDATE AUTOMATIC default once it upgrades to DuckDB 1.5.5 or higher. To get the same behavior regardless of client version, set the update mode explicitly rather than relying on the default.

INCLUDE_PATTERN clause​

By default a Share exposes every table and view in the source database. INCLUDE_PATTERN limits the Share to the tables and views its patterns name, which is how you apply table-level security. It takes a comma-separated list of schema.table patterns, where * is the only wildcard and matches within one segment.

CREATE SHARE sales_share FROM sales (
    INCLUDE_PATTERN 'reporting.*, main.orders',
    ACCESS RESTRICTED
);
  • Omitting INCLUDE_PATTERN exposes the whole database.
  • INCLUDE_PATTERN '' exposes nothing. Only the default schema remains, so USE and unqualified lookups still resolve.
  • A value that's non-empty but trims to no pattern, such as ' , ', is rejected rather than treated as "expose nothing".

Every pattern is validated against the source database's catalog when the statement runs, and the validation is all-or-nothing: one pattern that matches nothing fails the whole statement, and no Share is created.

Change the pattern on an existing Share with ALTER SHARE, and read the stored value back from the INCLUDE_PATTERN column of LIST SHARES.

See the INCLUDE_PATTERN clause for the full pattern syntax, the validation rules, and how to quote names that contain ., *, or ,.

note

INCLUDE_PATTERN isn't supported on Shares of DuckLake or Iceberg databases, and a Share that carries a pattern can't be cloned with CREATE DATABASE ... FROM <share> or COPY DATABASE. See limitations.

Table-level security is available on Business and Enterprise plans.

Shorthand convention​

  • If the database name is omitted, a share will be created from the current/active database.
  • If the share name is omitted, the share will be named after the source database.
  • If both database and share names are omitted, the share will be named and created after the current/active database.

Example usage​

-- If ducks_share exists, it will be replaced with a new share.
--A new share URL is returned.
CREATE OR REPLACE SHARE ducks_share;
       
-- If ducks_share exists, nothing is done. Its existing share URL is returned.
--Otherwise, a new share is created and its share URL is returned. 
CREATE SHARE IF NOT EXISTS ducks_share;
USE mydb;
-- Using shorthand: Create a share named ''mydb'' from the current database ''mydb''.
-- Defaults: ACCESS ORGANIZATION, VISIBILITY DISCOVERABLE, UPDATE AUTOMATIC (DuckDB 1.5.5+; MANUAL on 1.5.4 and lower)
CREATE SHARE;

-- Using shorthand: Create a share named ''db2'' from the specified database ''db2''.
-- Defaults: ACCESS ORGANIZATION, VISIBILITY DISCOVERABLE, UPDATE AUTOMATIC (DuckDB 1.5.5+; MANUAL on 1.5.4 and lower)
CREATE SHARE FROM db2;

-- Create a restricted Share and grant it to every preset role.
CREATE SHARE birds_share FROM birds (
    ACCESS RESTRICTED,
    VISIBILITY DISCOVERABLE,
    UPDATE AUTOMATIC
);
GRANT READ ON SHARE birds_share TO ROLE explorer;
note

All Shares created prior to June 6, 2024 are Unrestricted and Hidden. To restrict a legacy Share to your organization, recreate it with ACCESS RESTRICTED, make it Discoverable, and grant READ to the Explorer role.