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
| Action | What it does |
|---|---|
| Count rows | Count a table's rows, or only the rows that match column and value pairs. |
| Create schema | Create a schema in a database. |
| Create table | Create a table from column names and types, with an optional primary key. |
| Delete rows | Delete every row that matches the column and value pairs. |
| Describe table | Read a table's or view's columns: names, types, whether they can be empty, defaults and the primary key. |
| Drop table | Delete a table and every row in it, for good. |
| Find rows | Read rows from a table, optionally only those matching column and value pairs, sorted and paged. |
| Insert rows | Add up to 1000 rows to a table. |
| List databases | List the databases in the MotherDuck account. |
| List schemas | List the schemas in a database. |
| List tables | List the tables and views in a database, or in one of its schemas. |
| Run SQL query | Run one read-only SQL statement (SELECT, WITH, SHOW, DESCRIBE, SUMMARIZE or EXPLAIN) and return its rows. |
| Run SQL statement | Run one statement that changes data or tables: INSERT, UPDATE, DELETE, MERGE, CREATE, DROP or ALTER. |
| Update rows | Set 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - The table or view. Pick one, or give its name from an earlier step.
- Match rows where
whereobject - 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema name
schematextRequired - The new schema's name.
- Skip if it exists
if_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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table name
tabletextRequired - The new table's name.
- Columns
columnsobjectRequired - Each column's name and type, such as INTEGER, BIGINT, DOUBLE, DECIMAL(18,3), VARCHAR, BOOLEAN, DATE, TIMESTAMP, UUID, JSON or BLOB.
- Primary key column
primary_keytext - One of the columns above to make the primary key. Optional.
- Skip if it exists
if_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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - The table or view. Pick one, or give its name from an earlier step.
- Match rows where
whereobjectRequired - 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - The table or view. Pick one, or give its name from an earlier step.
- Skip if missing
if_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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - The table or view. Pick one, or give its name from an earlier step.
- Columns
columnslist - Only these columns. Leave empty for all of them.
- Match rows where
whereobject - 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 by
order_bytext - The column to sort by. Leave empty for no order.
- Order
directiontext - Which way to sort. Defaults to smallest first.One of:
asc(Smallest first (default)),desc(Largest first). - Maximum rows
limitnumber - The most rows to return, 1 to 10000. Defaults to 100.From 1 to 10000.
- Skip rows
offsetnumber - 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - The table or view. Pick one, or give its name from an earlier step.
- Rows
rowslistRequired - 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
- Database
databasetext - 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Show
table_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
- SQL
sqltextRequired - One SQL statement. Use ? for each value listed under Parameters.
- Parameters
parameterslist - The values for the ? marks in the SQL, in order.
- Maximum rows
max_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
- SQL
sqltextRequired - One INSERT, UPDATE, DELETE, MERGE, CREATE, DROP or ALTER statement. Use ? for each value listed under Parameters.
- Parameters
parameterslist - 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
- Database
databasetext - The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema
schematext - The schema in that database. Leave empty for main.
- Table
tabletextRequired - The table or view. Pick one, or give its name from an earlier step.
- New values
valuesobjectRequired - The columns to change and what to set them to.
- Match rows where
whereobjectRequired - 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
}