DuckDB

App · 14 actions

Run SQL on DuckDB in the cloud (MotherDuck): query and change data, explore databases, schemas and tables, and create or drop tables.

Actions

ActionWhat it does
Count rowsCount a table's rows, or only the rows that match column and value pairs.
Create schemaCreate a schema in a database.
Create tableCreate a table from column names and types, with an optional primary key.
Delete rowsDelete every row that matches the column and value pairs.
Describe tableRead a table's or view's columns: names, types, whether they can be empty, defaults and the primary key.
Drop tableDelete a table and every row in it, for good.
Find rowsRead rows from a table, optionally only those matching column and value pairs, sorted and paged.
Insert rowsAdd up to 1000 rows to a table.
List databasesList the databases in the MotherDuck account.
List schemasList the schemas in a database.
List tablesList the tables and views in a database, or in one of its schemas.
Run SQL queryRun one read-only SQL statement (SELECT, WITH, SHOW, DESCRIBE, SUMMARIZE or EXPLAIN) and return its rows.
Run SQL statementRun one statement that changes data or tables: INSERT, UPDATE, DELETE, MERGE, CREATE, DROP or ALTER.
Update rowsSet column values on every row that matches the column and value pairs.

Count rows

Count a table's rows, or only the rows that match column and value pairs.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.
Match rows wherewhereobject
Column and value pairs a row must equal (all of them). Use null for empty values, or a list to match any of its items. Leave empty for every row.

Outputs

countnumber
How many rows
Example output
{
  "count": 42
}

Create schema

Create a schema in a database.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schema nameschematextRequired
The new schema's name.
Skip if it existsif_not_existstrue/false
Do nothing instead of failing when the schema exists. On by default.Default: true.

Outputs

createdtrue/false
True once it ran
databasetext
The database it is in
schematext
The schema's name
Example output
{
  "created": true,
  "database": "my_db",
  "schema": "analytics"
}

Create table

Create a table from column names and types, with an optional primary key.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
Table nametabletextRequired
The new table's name.
ColumnscolumnsobjectRequired
Each column's name and type, such as INTEGER, BIGINT, DOUBLE, DECIMAL(18,3), VARCHAR, BOOLEAN, DATE, TIMESTAMP, UUID, JSON or BLOB.
Primary key columnprimary_keytext
One of the columns above to make the primary key. Optional.
Skip if it existsif_not_existstrue/false
Do nothing instead of failing when the table exists.Default: false.

Outputs

column_countnumber
How many columns
createdtrue/false
True once it ran
databasetext
The database it is in
schematext
The schema it is in
tabletext
The table's name
Example output
{
  "column_count": 2,
  "created": true,
  "database": "my_db",
  "schema": "main",
  "table": "events"
}

Delete rows

Delete every row that matches the column and value pairs.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.
Match rows wherewhereobjectRequired
Column and value pairs a row must equal (all of them). Required: changing every row takes 'Run SQL statement'.

Outputs

rows_deletednumber
How many rows were deleted
Example output
{
  "rows_deleted": 1
}

Describe table

Read a table's or view's columns: names, types, whether they can be empty, defaults and the primary key.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.

Outputs

column_countnumber
How many columns
columnslist
The columns, in order
primary_keylist
The primary key's columns, if any
tabletext
The table's name
Example output
{
  "column_count": 1,
  "columns": [
    {
      "default": null,
      "name": "id",
      "nullable": false,
      "position": 1,
      "primary_key": true,
      "type": "INTEGER"
    }
  ],
  "primary_key": [
    "id"
  ],
  "table": "events"
}

Drop table

Delete a table and every row in it, for good.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.
Skip if missingif_existstrue/false
Do nothing instead of failing when the table is gone. On by default.Default: true.

Outputs

databasetext
The database it was in
droppedtrue/false
True once it ran
schematext
The schema it was in
tabletext
The table's name
Example output
{
  "database": "my_db",
  "dropped": true,
  "schema": "main",
  "table": "events"
}

Find rows

Read rows from a table, optionally only those matching column and value pairs, sorted and paged.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.
Columnscolumnslist
Only these columns. Leave empty for all of them.
Match rows wherewhereobject
Column and value pairs a row must equal (all of them). Use null for empty values, or a list to match any of its items. Leave empty for every row.
Sort byorder_bytext
The column to sort by. Leave empty for no order.
Orderdirectiontext
Which way to sort. Defaults to smallest first.One of: asc (Smallest first (default)), desc (Largest first).
Maximum rowslimitnumber
The most rows to return, 1 to 10000. Defaults to 100.From 1 to 10000.
Skip rowsoffsetnumber
How many rows to skip first, to read the next page.At least 0.

Outputs

columnslist
The column names, in order
row_countnumber
How many rows came back
rowslist
The rows, by column name
truncatedtrue/false
True when more rows matched than were returned
Example output
{
  "columns": [
    "id",
    "name"
  ],
  "row_count": 1,
  "rows": [
    {
      "id": 1,
      "name": "Ada"
    }
  ],
  "truncated": false
}

Insert rows

Add up to 1000 rows to a table. Each row is an object of column names and values; a column a row leaves out is set to empty.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.
RowsrowslistRequired
The rows to add, for example [{"id": 1, "name": "Ada"}]. A value that is an object is stored as JSON text.

Outputs

rows_insertednumber
How many rows were added
Example output
{
  "rows_inserted": 1
}

List databases

List the databases in the MotherDuck account.

Inputs

None.

Outputs

countnumber
How many
databaseslist
The databases
Example output
{
  "count": 1,
  "databases": [
    {
      "name": "my_db",
      "read_only": false,
      "type": "motherduck"
    }
  ]
}

List schemas

List the schemas in a database.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.

Outputs

countnumber
How many
databasetext
The database asked about
schemaslist
The schemas
Example output
{
  "count": 1,
  "database": "my_db",
  "schemas": [
    {
      "name": "main"
    }
  ]
}

List tables

List the tables and views in a database, or in one of its schemas.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
Showtable_kindtext
Tables, views or both. Defaults to both.One of: all (Tables and views (default)), table (Tables only), view (Views only).

Outputs

countnumber
How many
tableslist
The tables
Example output
{
  "count": 1,
  "tables": [
    {
      "database": "my_db",
      "name": "events",
      "schema": "main",
      "type": "table"
    }
  ]
}

Run SQL query

Run one read-only SQL statement (SELECT, WITH, SHOW, DESCRIBE, SUMMARIZE or EXPLAIN) and return its rows.

Inputs

SQLsqltextRequired
One SQL statement. Use ? for each value listed under Parameters.
Parametersparameterslist
The values for the ? marks in the SQL, in order.
Maximum rowsmax_rowsnumber
The most rows to return, 1 to 10000. Defaults to 1000.From 1 to 10000.

Outputs

columnslist
The column names, in order
row_countnumber
How many rows came back
rowslist
The rows, by column name
truncatedtrue/false
True when more rows matched than were returned
Example output
{
  "columns": [
    "id",
    "name"
  ],
  "row_count": 1,
  "rows": [
    {
      "id": 1,
      "name": "Ada"
    }
  ],
  "truncated": false
}

Run SQL statement

Run one statement that changes data or tables: INSERT, UPDATE, DELETE, MERGE, CREATE, DROP or ALTER.

Inputs

SQLsqltextRequired
One INSERT, UPDATE, DELETE, MERGE, CREATE, DROP or ALTER statement. Use ? for each value listed under Parameters.
Parametersparameterslist
The values for the ? marks in the SQL, in order.

Outputs

executedtrue/false
True once it ran
rows_affectednumber
Rows inserted, updated or deleted, 0 for CREATE, DROP and ALTER
Example output
{
  "executed": true,
  "rows_affected": 1
}

Update rows

Set column values on every row that matches the column and value pairs.

Inputs

Databasedatabasetext
The database to use. Pick one, or leave empty for your MotherDuck default database.
Schemaschematext
The schema in that database. Leave empty for main.
TabletabletextRequired
The table or view. Pick one, or give its name from an earlier step.
New valuesvaluesobjectRequired
The columns to change and what to set them to.
Match rows wherewhereobjectRequired
Column and value pairs a row must equal (all of them). Required: changing every row takes 'Run SQL statement'.

Outputs

rows_updatednumber
How many rows changed
Example output
{
  "rows_updated": 1
}

On this page