Skip to main content

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. LiveCodes runs PostgreSQL in the browser using PGlite (lightweight Postgres packaged as Wasm).

Note

Please note that LiveCodes also supports SQL using SQLite.

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 PostgreSQL are identical to those for SQL using SQLite. So the same code can be used for both engines.

Limitations​

Currently parameters are not supported.

Example Usage​

show code
import { createPlayground } from 'livecodes';

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

Custom Settings​

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

  • dbName: a database name that allows persisting data in the browser in IndexedDB (see PGlite).

  • 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
{
  "postgresql": {
    "dbName": "mydb",
    "scriptURLs": ["https://myserver.com/sql.sql"]
  }
}

Language Info​

Name​

postgresql

Aliases/Extensions​

pg.sql, pgsql, pgsql.sql, pgsql, pg, pglite, pglite.sql, postgresql, postgres, postgre.sql, postgresql.sql

Editor​

script

Compiler​

PGlite

Version​

@electric-sql/pglite: v0.1.5

Code Formatting​

using sql-formatter

Starter Template​

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

Sponsors