Execute a read-only SQL query

POST

/query

• Production
• Development

Executes a SQL query against MotherDuck and returns results. Only read-only statements are allowed: SELECT, PRAGMA, DESCRIBE, SHOW, WITH.

Each tenant has its own database (warehouse_{bucket}). Use the optional database field to switch to a specific tenant database before querying — otherwise queries run against the default connection (md:).

19 tables available per tenant: person, organisation, member, payment, message, membership_type, ticket_type, booking, ticket, attendee, activity, bank_account, badge, connection, evidence, pledge, grant, group, group_member.

Cross-table joins use uri columns (e.g. member.member = person.uri).

Authorizations

• ApiKeyAuth

Request Body ^required

object

sql

required

SQL statement to execute. Only read-only statements are allowed: SELECT, PRAGMA, DESCRIBE, SHOW, WITH.

string

Example

SELECT * FROM person LIMIT 10

database

Optional database name to switch to before executing the query. Use warehouse_{bucket} format (hyphens replaced with underscores). If omitted, queries run against the default connection.

string

/^[a-zA-Z0-9_]+$/

Example

warehouse_example

Examples

Simple SELECT with database

{
  "sql": "SELECT * FROM person LIMIT 10",
  "database": "warehouse_example"
}

Join members to persons

{
  "sql": "SELECT m.membership_type, p.first_name, p.last_name FROM member m JOIN person p ON m.member = p.uri LIMIT 10",
  "database": "warehouse_example"
}

Describe a table

{
  "sql": "DESCRIBE person",
  "database": "warehouse_example"
}

Responses

200

Query executed successfully

object

data

required

object

columns

required

Column names from the result set.

Array<string>

Example

[
  "uid",
  "first_name",
  "last_name"
]

rows

required

Result rows. Each row is an array of values matching the columns.

Array<Array>

Example

[
  [
    "abc123",
    "Jane",
    "Smith"
  ]
]

Example

{
  "data": {
    "columns": [
      "uid",
      "first_name",
      "last_name"
    ],
    "rows": [
      [
        "abc123",
        "Jane",
        "Smith"
      ]
    ]
  }
}

400

Invalid or disallowed SQL statement

object

error

required

object

code

required

Machine-readable error code.

string

Example

unauthorized

message

required

Human-readable error message.

string

Example

Authentication required

Examples

Missing sql field

{
  "error": {
    "code": "bad_request",
    "message": "Missing 'sql' field"
  }
}

Non-SELECT statement

{
  "error": {
    "code": "bad_request",
    "message": "Only SELECT, PRAGMA, DESCRIBE, SHOW, and WITH statements are allowed (got DROP)"
  }
}

401

Authentication required

object

error

required

object

code

required

Machine-readable error code.

string

Example

unauthorized

message

required

Human-readable error message.

string

Example

Authentication required

Example

{
  "error": {
    "code": "unauthorized",
    "message": "Authentication required"
  }
}

500

Internal server error

object

error

required

object

code

required

Machine-readable error code.

string

Example

unauthorized

message

required

Human-readable error message.

string

Example

Authentication required

Example

{
  "error": {
    "code": "schema_error",
    "message": "Schema application failed"
  }
}