# DuckDB

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

App · 14 actions. Web page: https://docs.lodol.com/docs/integrations/duckdb

## Actions

### Count rows

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

**Inputs**

- Database (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.
- Match rows where (`where`, object): 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**

- `count` (number): How many rows

**Example output**

```json
{
  "count": 42
}
```

### Create schema

Create a schema in a database.

**Inputs**

- Database (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema name (`schema`, text, required): The new schema's name.
- Skip if it exists (`if_not_exists`, true/false): Do nothing instead of failing when the schema exists. On by default. Default: `true`.

**Outputs**

- `created` (true/false): True once it ran
- `database` (text): The database it is in
- `schema` (text): The schema's name

**Example output**

```json
{
  "created": true,
  "database": "my_db",
  "schema": "analytics"
}
```

### Create table

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

**Inputs**

- Database (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table name (`table`, text, required): The new table's name.
- Columns (`columns`, object, required): 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_key`, text): One of the columns above to make the primary key. Optional.
- Skip if it exists (`if_not_exists`, true/false): Do nothing instead of failing when the table exists. Default: `false`.

**Outputs**

- `column_count` (number): How many columns
- `created` (true/false): True once it ran
- `database` (text): The database it is in
- `schema` (text): The schema it is in
- `table` (text): The table's name

**Example output**

```json
{
  "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 (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.
- Match rows where (`where`, object, required): Column and value pairs a row must equal (all of them). Required: changing every row takes 'Run SQL statement'.

**Outputs**

- `rows_deleted` (number): How many rows were deleted

**Example output**

```json
{
  "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 (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.

**Outputs**

- `column_count` (number): How many columns
- `columns` (list): The columns, in order
- `primary_key` (list): The primary key's columns, if any
- `table` (text): The table's name

**Example output**

```json
{
  "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 (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.
- Skip if missing (`if_exists`, true/false): Do nothing instead of failing when the table is gone. On by default. Default: `true`.

**Outputs**

- `database` (text): The database it was in
- `dropped` (true/false): True once it ran
- `schema` (text): The schema it was in
- `table` (text): The table's name

**Example output**

```json
{
  "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 (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.
- Columns (`columns`, list): Only these columns. Leave empty for all of them.
- Match rows where (`where`, object): 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_by`, text): The column to sort by. Leave empty for no order.
- Order (`direction`, text): Which way to sort. Defaults to smallest first. One of: `asc` (Smallest first (default)), `desc` (Largest first).
- Maximum rows (`limit`, number): The most rows to return, 1 to 10000. Defaults to 100. From 1 to 10000.
- Skip rows (`offset`, number): How many rows to skip first, to read the next page. At least 0.

**Outputs**

- `columns` (list): The column names, in order
- `row_count` (number): How many rows came back
- `rows` (list): The rows, by column name
- `truncated` (true/false): True when more rows matched than were returned

**Example output**

```json
{
  "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 (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.
- Rows (`rows`, list, required): The rows to add, for example [{"id": 1, "name": "Ada"}]. A value that is an object is stored as JSON text.

**Outputs**

- `rows_inserted` (number): How many rows were added

**Example output**

```json
{
  "rows_inserted": 1
}
```

### List databases

List the databases in the MotherDuck account.

**Inputs**: None.

**Outputs**

- `count` (number): How many
- `databases` (list): The databases

**Example output**

```json
{
  "count": 1,
  "databases": [
    {
      "name": "my_db",
      "read_only": false,
      "type": "motherduck"
    }
  ]
}
```

### List schemas

List the schemas in a database.

**Inputs**

- Database (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.

**Outputs**

- `count` (number): How many
- `database` (text): The database asked about
- `schemas` (list): The schemas

**Example output**

```json
{
  "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 (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Show (`table_kind`, text): Tables, views or both. Defaults to both. One of: `all` (Tables and views (default)), `table` (Tables only), `view` (Views only).

**Outputs**

- `count` (number): How many
- `tables` (list): The tables

**Example output**

```json
{
  "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 (`sql`, text, required): One SQL statement. Use ? for each value listed under Parameters.
- Parameters (`parameters`, list): The values for the ? marks in the SQL, in order.
- Maximum rows (`max_rows`, number): The most rows to return, 1 to 10000. Defaults to 1000. From 1 to 10000.

**Outputs**

- `columns` (list): The column names, in order
- `row_count` (number): How many rows came back
- `rows` (list): The rows, by column name
- `truncated` (true/false): True when more rows matched than were returned

**Example output**

```json
{
  "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 (`sql`, text, required): One INSERT, UPDATE, DELETE, MERGE, CREATE, DROP or ALTER statement. Use ? for each value listed under Parameters.
- Parameters (`parameters`, list): The values for the ? marks in the SQL, in order.

**Outputs**

- `executed` (true/false): True once it ran
- `rows_affected` (number): Rows inserted, updated or deleted, 0 for CREATE, DROP and ALTER

**Example output**

```json
{
  "executed": true,
  "rows_affected": 1
}
```

### Update rows

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

**Inputs**

- Database (`database`, text): The database to use. Pick one, or leave empty for your MotherDuck default database.
- Schema (`schema`, text): The schema in that database. Leave empty for main.
- Table (`table`, text, required): The table or view. Pick one, or give its name from an earlier step.
- New values (`values`, object, required): The columns to change and what to set them to.
- Match rows where (`where`, object, required): Column and value pairs a row must equal (all of them). Required: changing every row takes 'Run SQL statement'.

**Outputs**

- `rows_updated` (number): How many rows changed

**Example output**

```json
{
  "rows_updated": 1
}
```
