Data Access

Overview

Access your synchronized data through high-performance DuckDB queries. All endpoints support multi-database access via the ?db={database_id} query parameter.

Endpoints

POST /api/query

Execute SQL queries on DuckDB or MySQL. Authentication: Required Query Parameters:

string

Database ID to query (defaults to default if omitted)

Request Body:

sql

string

required

SQL query to execute

params

array

Optional query parameters for parameterized queries

database

string

default:"duckdb"

Target database: duckdb or mysql

Request Example:

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM User WHERE age > 25 LIMIT 10"
  }'

Parameterized Query Example:

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM User WHERE refId = ? LIMIT 10",
    "params": [123]
  }'

Response:

{
  "result": [
    {
      "refId": "123",
      "name": "John Doe",
      "email": "[email protected]",
      "age": 30
    },
    {
      "refId": "456",
      "name": "Jane Smith",
      "email": "[email protected]",
      "age": 28
    }
  ],
  "database": "duckdb",
  "architecture": "sequential-appender"
}

result

array

Query results as array of objects

database

string

Database engine used: duckdb or mysql

architecture

string

Architecture mode used for the query

MySQL queries are read-only. Only SELECT statements are allowed to prevent accidental data modification.

GET /api/tables

List all replicated tables in DuckDB. Authentication: Required Query Parameters:

string

Database ID (defaults to default if omitted)

Request Example:

curl 'http://localhost:3001/api/tables?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY"

Response:

[
  "User",
  "Product",
  "Order",
  "Customer",
  "Invoice"
]

GET /api/tables/:name/schema

Get table schema information (column names and types). Authentication: Required Path Parameters:

name

string

required

Table name

Query Parameters:

string

Database ID (defaults to default if omitted)

Request Example:

curl 'http://localhost:3001/api/tables/User/schema?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY"

Response:

{
  "columns": [
    {
      "column_name": "refId",
      "column_type": "VARCHAR",
      "null": "NO",
      "key": "PRI",
      "default_value": null,
      "extra": ""
    },
    {
      "column_name": "name",
      "column_type": "VARCHAR",
      "null": "YES",
      "key": "",
      "default_value": null,
      "extra": ""
    },
    {
      "column_name": "email",
      "column_type": "VARCHAR",
      "null": "YES",
      "key": "",
      "default_value": null,
      "extra": ""
    },
    {
      "column_name": "age",
      "column_type": "INTEGER",
      "null": "YES",
      "key": "",
      "default_value": "0",
      "extra": ""
    }
  ]
}

columns

array

Array of column definitions

columns[].column_name

string

Column name

columns[].column_type

string

DuckDB data type (VARCHAR, INTEGER, BIGINT, DECIMAL, etc.)

columns[].null

string

Whether column allows NULL values: YES or NO

columns[].key

string

Primary key indicator: PRI for primary key columns

GET /api/tables/:name/data

Get paginated table data. Authentication: Required Path Parameters:

name

string

required

Table name

Query Parameters:

string

Database ID (defaults to default if omitted)

limit

number

default:"100"

Number of rows to return (max: 10,000)

offset

number

default:"0"

Number of rows to skip for pagination

Request Example:

curl 'http://localhost:3001/api/tables/User/data?db=lms&limit=10&offset=0' \
  -H "Authorization: Bearer $DUCKLING_API_KEY"

Response:

[
  {
    "refId": "123",
    "name": "John Doe",
    "email": "[email protected]",
    "age": 30,
    "createdAt": "2024-01-15T10:00:00Z"
  },
  {
    "refId": "456",
    "name": "Jane Smith",
    "email": "[email protected]",
    "age": 28,
    "createdAt": "2024-01-16T11:30:00Z"
  }
]

Use pagination to retrieve large datasets efficiently. The maximum limit is 10,000 rows per request.

GET /api/tables/:name/count

Get total row count for a table. Authentication: Required Path Parameters:

name

string

required

Table name

Query Parameters:

string

Database ID (defaults to default if omitted)

Request Example:

curl 'http://localhost:3001/api/tables/User/count?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY"

Response:

{
  "count": 50000
}

GET /api/tables/counts/all

Get row counts for all tables in parallel. Authentication: Required Query Parameters:

string

Database ID (defaults to default if omitted)

Request Example:

curl 'http://localhost:3001/api/tables/counts/all?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY"

Response:

{
  "User": 50000,
  "Product": 10000,
  "Order": 150000,
  "Customer": 25000,
  "Invoice": 120000
}

This endpoint executes COUNT queries in parallel for optimal performance.

Query Examples

Star Schema Joins

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT c.region, date_trunc('"'"'day'"'"', o.order_date) AS day, SUM(o.amount) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL 7 DAY GROUP BY c.region, day ORDER BY day DESC"
  }'

Performance: ~200ms (was 2-5s on MySQL)

Time-Range Queries

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT COUNT(*), AVG(amount) FROM orders WHERE order_date BETWEEN '"'"'2024-01-15'"'"' AND '"'"'2024-01-16'"'"'"
  }'

Performance: ~50ms (was 1-2s on MySQL)

Analytical Aggregations

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT product_category, COUNT(*) as order_count, SUM(amount) as total_revenue FROM orders GROUP BY product_category"
  }'

Window Functions

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT refId, name, email, ROW_NUMBER() OVER (PARTITION BY boardId ORDER BY createdAt) as rank FROM User"
  }'

Time-Series Analytics

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H "Authorization: Bearer $DUCKLING_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT DATE_TRUNC('"'"'day'"'"', createdAt) as date, COUNT(*) as daily_signups FROM User WHERE createdAt >= CURRENT_DATE - INTERVAL '"'"'30 days'"'"' GROUP BY date ORDER BY date"
  }'

Performance Benefits

5-10x Faster Queries

DuckDB’s columnar storage delivers 5-10x better query performance compared to MySQL for analytical workloads.

Column Pruning

Only scans needed columns, reducing I/O and memory usage.

Compressed Storage

Built-in compression reduces storage footprint by 60-94%.

In-Process Queries

No network overhead - DuckDB runs in the same process as your application.

Error Handling

400 Bad Request - Missing SQL:

{
  "error": "SQL query is required"
}

400 Bad Request - Invalid Identifier:

{
  "error": "Invalid identifier: table_name"
}

500 Internal Server Error:

{
  "error": "Query execution failed: no such table: InvalidTable"
}

Best Practices

Use Parameterized Queries - Prevent SQL injection with parameter binding
Limit Result Sets - Use LIMIT clauses to avoid memory issues
Index Primary Keys - Ensure tables have primary keys for optimal performance
Use DuckDB for Analytics - Route analytical queries to DuckDB, transactional queries to MySQL
Monitor Query Performance - Check /metrics endpoint for slow queries

REST API

WebSocket SDK

CLI Reference

Overview

Endpoints

POST /api/query

GET /api/tables

GET /api/tables/:name/schema

GET /api/tables/:name/data

GET /api/tables/:name/count

GET /api/tables/counts/all

Query Examples

Star Schema Joins

Time-Range Queries

Analytical Aggregations

Window Functions

Time-Series Analytics

Performance Benefits

5-10x Faster Queries

Column Pruning

Compressed Storage

In-Process Queries

Error Handling

Best Practices

Build docs developers (and LLMs) love

REST API

WebSocket SDK

CLI Reference

​Overview

​Endpoints

​POST /api/query

​GET /api/tables

​GET /api/tables/:name/schema

​GET /api/tables/:name/data

​GET /api/tables/:name/count

​GET /api/tables/counts/all

​Query Examples

​Star Schema Joins

​Time-Range Queries

​Analytical Aggregations

​Window Functions

​Time-Series Analytics

​Performance Benefits

5-10x Faster Queries

Column Pruning

Compressed Storage

In-Process Queries

​Error Handling

​Best Practices

Build docs developers (and LLMs) love

Overview

Endpoints

POST /api/query

GET /api/tables

GET /api/tables/:name/schema

GET /api/tables/:name/data

GET /api/tables/:name/count

GET /api/tables/counts/all

Query Examples

Star Schema Joins

Time-Range Queries

Analytical Aggregations

Window Functions

Time-Series Analytics

Performance Benefits

Error Handling

Best Practices