ioBroker.sql

3.0.1 verified Sat Apr 25 auth: no javascript

ioBroker adapter to log state history into SQL databases. Current stable version is 3.0.1. Released under Apache-2.0, maintained by the ioBroker team. Supports MySQL, PostgreSQL, Microsoft SQL Server, and SQLite. Provides configurable debouncing, blocktime, change-only recording, minimum difference, and storage retention. Includes Sentry error reporting. Node.js >=16 required. Key differentiator: integration with ioBroker ecosystem for home automation, with flexible SQL backend and advanced filtering.

Common errors

error Error: Cannot find module 'iobroker.sql' ↓

cause Package not installed correctly or node_modules missing

fix

Run 'npm install iobroker.sql' in the ioBroker adapter directory

error Error: connect ECONNREFUSED ... ↓

cause SQL server not running or wrong host/port

fix

Check SQL server status and adapter connection settings

error Error: ER_ACCESS_DENIED_ERROR: Access denied for user ... ↓

cause Invalid SQL credentials or insufficient privileges

fix

Verify username and password, ensure database exists

Warnings

breaking Configuration is only working in the new Admin 5 UI! ↓

fix Update to Admin 5 UI or stay on 1.x

breaking Node.js 14.x is now needed at minimum! ↓

fix Upgrade Node.js to >=14.x

deprecated Alias-ID option is deprecated; consider switching to real alias States. ↓

fix Use ioBroker alias states instead

gotcha Debounce time: if values change more often than the debounce time, no value will be logged. ↓

fix Set debounce time appropriately or use blocktime

gotcha Sentry error reporting is enabled by default starting with js-controller 3.0. ↓

fix Disable via plugin-sentry configuration

Install

npm install iobroker.sql

yarn add iobroker.sql

pnpm add iobroker.sql

Quickstart

Basic instantiation and configuration of ioBroker.sql adapter with default options for SQLite.

const adapter = require('iobroker.sql');

const sqlAdapter = new adapter('sql', {
    systemConfig: true,
    // adapter config
    'DB Type': 'sqlite',
    'Host': 'localhost',
    'Port': 0,
    'Database name': 'iobroker',
    'User': '',
    'Password': '',
    'Encrypt': false,
    'Round real to': 2,
    'Allow parallel requests': false,
    'Do not create database': false,
    'Debounce Time': 0,
    'Blocktime': 0,
    'Record changes only': false,
    'still record the same values (seconds)': 0,
    'Minimum difference from last value': 0,
    'ignore 0 or null values (==0)': false,
    'ignore values below zero (<0)': false,
    'Disable charting optimized logging of skipped values': false,
    'Alias-ID': '',
    'Storage retention': 365,
    'Maximal number of stored in RAM values': 1000
});

sqlAdapter.on('ready', () => {
    // adapter is ready
    console.log('Adapter ready');
});

sqlAdapter.on('objectChange', (id, obj) => {
    // handle object change
});

sqlAdapter.on('stateChange', (id, state) => {
    // handle state change
});