Skip to main content

DuckDB

DuckDB is an in-process SQL OLAP (analytical) database management system. LiveCodes runs DuckDB in the browser using DuckDB-Wasm (DuckDB packaged as Wasm).

Note

Please note that LiveCodes also supports SQL using SQLite and PostgreSQL.

Usage​

The SQL code runs (in the browser) and the output is produced as a JSON object. This JSON object is added to the result page in a script block with type application/json.

Helper methods are provided for easy access/rendering of the JSON object (see below).

Helper Methods​

The JavaScript object livecodes.sql is globally available in the result page. This can be used in script blocks in the markup editor (page HTML - see HTML editor is example usage). It provides the following methods for easy access/rendering of the JSON object:

  • livecodes.sql.getResult

    Type: () => Promise<{ data: Array<{ columns: string[]; values: unknown[][]; }>}>:

    Returns a promise that resolves to the JSON object. The object has a single property data which is an array of objects, each representing the output of a query (e.g. SELECT * FROM table). Each object has two properties columns (an array of column names) and values (an array of arrays of values).

    In case of errors, the promise rejects with the error message.

    Example:

    HTML
    <script>
      livecodes.sql
        .getResult()
        .then((result) => {
          console.log(result);
          // { data: [{ columns: ['columnA', 'columnB'], values: [['row 1 - value A', 'row 1 - value B'], ['row 2 - value A', 'row 2 - value B']] }] }
        })
        .catch((error) => {
          console.error(error);
          // 'error message'
        });
    </script>

  • livecodes.sql.getResultAsObjects

    Type: () => Promise<{ [key: string]: unknown; }[][]>:

    Returns a promise that resolves to the data as an array (representing queries/tables) of arrays (representing rows) of objects. Each object has key/value pairs for the column names and their values.

    In case of errors, the promise rejects with the error message.

    Example:

    HTML
    <script>
      livecodes.sql
        .getResultAsObjects()
        .then((result) => {
          console.log(result);
          // [[{ columnA: 'row 1 - value A', columnB: 'row 1 - value B' }, { columnA: 'row 2 - value A', columnB: 'row 2 - value B' }]]
        })
        .catch((error) => {
          console.error(error);
          // 'error message'
        });
    </script>

  • livecodes.sql.render: (element?: HTMLElement | string) => Promise<void>:

    Accepts a single parameter which can be a DOM element or a CSS selector and renders the JSON object as HTML table(s) in that element. If no element is specified, it renders the table(s) in document.body.

    Example:

    HTML
    <div id="tables"></div>
    <script>
      livecodes.sql.render('#tables');
    </script>
Note

Helper methods for DuckDB are identical to those for SQL using SQLite and PostgreSQL. So the same code can be used for all engines.

Limitations​

Currently parameters are not supported.

Example Usage​

show code
import { createPlayground } from 'livecodes';

const options = {
  "template": "duckdb"
};
createPlayground('#container', options);

Custom Settings​

Custom settings added to the property duckdb are used during running the SQL code. It is a JSON object with the following properties:

  • scriptURLs: An array of URLs to SQL scripts that should be loaded before running the SQL code.

Please note that custom settings should be valid JSON (i.e. functions are not allowed).

Example:

Custom Settings
{
  "duckdb": {
    "scriptURLs": ["https://myserver.com/sql.sql"]
  }
}

Language Info​

Name​

duckdb

Aliases/Extensions​

duckdb.sql, duckdb

Editor​

script

Compiler​

DuckDB-Wasm

Version​

@duckdb/duckdb-wasm: v1.32.0

Code Formatting​

using sql-formatter

Starter Template​

https://livecodes.io/?template=duckdb

Sponsors