This article covers:

1. How to set up a connection to a DuckDB instance
2. How to add datasets
3. How to do connection overrides on a DuckDB connection
4. The supported DuckDB data types and the respective Luzmo data type they are mapped to

1. How to set up a DuckDB connection

To make a connection to DuckDB, navigate to the Connections page, select New Connection, then select DuckDB from the New Connection modal. You'll be asked to provide a host, port, and password:

• host: the publicly reachable HTTPS URL where your DuckDB HTTP server is exposed.
  • Luzmo communicates with DuckDB over HTTP using the httpserver extension, which must be installed and running to make your DuckDB instance reachable.
• port: the HTTP port on which your DuckDB HTTP server is listening.
• password: an API key of your choosing. This key is passed along as an X-API-Key HTTP header with every request. Your HTTP endpoint should use this value to identify the caller and return the appropriate client-specific data.
• database: this field is not used by the DuckDB connector. You may enter any value, but the field must be filled in.
• username: this field is not used by the DuckDB connector. You may enter any value, but the field must be filled in.

To ensure that only the Luzmo application can access your DuckDB instance, it is highly recommended to whitelist Luzmo's range of static IP addresses as described here.

Please refer to the examples in our developer documentation to find out how to create a connection to DuckDB via our API.

2. How to add datasets

1. You can select one or multiple datasets available in your DuckDB instance and link them in Luzmo to ensure they can be used together in a dashboard.
2. You can also add SQL datasets by switching to the SQL tab in the dataset creation modal. While creating or editing a SQL dataset, you can parameterize anything within the query by specifying {{metadata.<parameter name>|<default value>}}. Find out more about parameterized SQL datasets.

Also make sure to check out this article on Preparing your data for analytics.

3. DuckDB connection overrides

When generating an authorization token to grant a user access to your embedded dashboards, it is possible to override the data source properties in the authorization request to dynamically use different properties. Find out more about connection overrides.

When doing connection overrides, it's important that the schema of the tables in the override conforms to the same schema as the original datasets to avoid query failures due to schema mismatches.

The fields available for overriding a DuckDB connection are as follows:

• Connection ID: the ID of the connection to be overridden. Retrieve the ID to specify as detailed here.
• host: the new publicly reachable HTTPS URL of the DuckDB HTTP server to connect to.
• port: the new HTTP port to connect to (optional).
• password: the new API key to pass as the X-API-Key header, used by your endpoint to serve client-specific data.

This is particularly useful in a multi-tenant setup where you maintain a single generic DuckDB connection in Luzmo and override the host and password per end user at authorization time, allowing each customer to see only their own data from their DuckDB instance.

Example of connection overrides in an authorization request:

{
  "type": "embed",
  "username": "< a unique and immutable identifier for your user >",
  "name": "< user name >",
  "email": "< user email >",
  "suborganization": "< a suborganization name >",
  "access": {
    "collections": [
      {
        "id": "<collection_id>",
        "inheritRights": "use"
      }
    ]
  },
  "account_overrides": {
    "<duckdb_generic_connection_id>": {
      "host": "<tenant_specific_duckdb_host_url>",
      "port": "<optional_port_override>",
      "password": "<client_specific_api_key>"
    }
  }
}

4. Supported data types