Skip to content

DataBase

Access databases directly by creating and connecting to a DataBase instance. It provides an interface similar to well-known Node.js database drivers such as mysql or pg.

Only the system specific driver installation is needed for each supported database. To see the installation and shared library requirements, read the Required libraries section of this documentation.

Supported databases:

  • SQLite
  • MySQL
  • MSSQL*
  • PostgreSQL
  • MS Access (Windows only)
  • Firebird
  • Interbase

Important notice

Databases are currently not supported on Raspberry Pi OS.

*MSSQL on macOS is currently still under development.

Connect to a database

To connect to a database, first set up the connection object. Below is an example of a connection object for a local SQLite database file:

import { DataBase } from 'miletus';

let db = new DataBase('sqlite', {database: 'path_to_my/sqlitedb.db'});

After the connection object is created, call the connect() Promise to connect.

//let db = ...
db.connect();

Perform a query

After a successful connection, use the query(sql) Promise to perform a query in a database.

//by using await
let query = await(db.query('SELECT * FROM MyTable;'));
console.log('affected rows: ' + query.rowCount);
query.result.forEach(record => {
    console.log(record.MyID);
    console.log(record.MyData);
});

//or by using then
db.query('SELECT * FROM MyTable;').then(query => { 
    console.log('affected rows: ' + query.rowCount);
    query.result.forEach(record => {
        console.log(record.MyID);
        console.log(record.MyData);
    });
});

Methods

new DataBase(type, options)

Creates a new database connection.

let db = new DataBase('sqlite', {database: 'path_to_my/sqlitedb.db'});

connect()

Returns Promise<void> - Connect to the database.

query(sql)

  • sql string - SQL query string.

Returns Promise<DatabaseResult> - Resolves with an object which contains the number of affected rows and the result array. See DatabaseResult.

close()

Close the database connection.

Required libraries

Where to install

On Windows the installation folder can be:

  • A folder listed in the PATH enviromental variable, or
  • Application EXE folder

On macOS: /usr/lib or /usr/local/lib

On Linux: /usr/lib/x86_64-linux-gnu or /usr/lib64

SQLite

The following libraries are required for SQLite:

  • Windows: sqlite3.dll

    You can download the x86 and x64 client libraries from the official sqlite download page.

  • macOS: libcgsqlite3.dylib (already included in the application bundle)

  • Linux: libsqlite3.so

    Example installation on Ubuntu
    sudo apt-get install libsqlite3-0
sudo ln -s /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 /usr/lib/x86_64-linux-gnu/libsqlite3.so

MySQL

The following libraries are required for MySQL:

  • Windows: libmysql.dll

    Available in the bin or lib folders of the server installation.

    To connect to a 64-bit version of MySQL you'll also need:

    • VS2015 runtime 64-bit
    • libcrypto-1_1-x64.dll
    • libssl-1_1-x64.dll

  • macOS: libmysqlclient.dylib

    Available in the bin or lib folders of the server installation.

    The installation might not add the libmysqlclient.dylib file to the /usr/lib or /usr/local/lib folder automatically. You can create a symlink to fix this:

    sudo ln -s /usr/local/mysql/lib/libmysqlclient.dylib /usr/local/lib/libmysqlclient.dylib

  • Linux: libmysqlclient.so

    Example installation on Ubuntu
    sudo apt-get install libmysqlclient21
sudo ln -s /usr/lib/x86_64-linux-gnu/libmysqlclient.so.21 /usr/lib/x86_64-linux-gnu/libmysqlclient.so

MSSQL

The following libraries are required for MSSQL:

MS Access

The following library for Windows is required:

  • Windows: One of the Microsoft Access x86 or x64 ODBC drivers. See download here.

PostgreSQL

The following libraries are required for PostgreSQL:

  • Windows: libpq.dll. Using libpq.dll also requires the "Microsoft Visual C++ 2010 Redistributable Package" installed. Ideally, the libpq.dll version should be equal to the server version.

    You can find this file in the server installation bin folder.

  • macOS: libpq.dylib

    You can find this file in the server installation lib folder.

    The installation might not add the libpq.dylib file to the /usr/lib or /usr/local/lib folder automatically. You can create a symlink to fix this:

    sudo ln -s /PostgreSQL installation folder/lib/libpq.dylib /usr/local/lib/libpq.dylib

  • Linux: libpq.so

    Example installation on Ubuntu
    sudo apt-get install libpq5
sudo ln -s /usr/lib/x86_64-linux-gnu/libpq.so.5 /usr/lib/x86_64-linux-gnu/libpq.so

Firebird

The following libraries are required for Firebird:

  • Windows: fbclient.dll

    You can find this file in the server installation bin folder.

  • macOS: libfbclient.dylib (already included in the application bundle)

  • Linux: libfbclient.so

    Example installation on Ubuntu
    sudo apt-get install libfbclient2
sudo ln -s /usr/lib/x86_64-linux-gnu/libfbclient.so.2 /usr/lib/x86_64-linux-gnu/libfbclient.so

Interbase

The following libraries are required for Interbase:

  • Windows:

    • To connect to the server:
      • x86: gds32.dll
      • x86_64: ibclient64.dll
    • To work with the database:
      • x86: ibtogo.dll
      • x86_64: ibtogo64.dll

    Available in a separate trial download. See details here.

  • macOS: libgds.dylib and libibtogo.dylib (already included in the application bundle)

  • Linux: libgds.so and libibtogo.so

    Available in a separate trial download. See details here.