Using SQLite with Node-RED

SQLite is a lightweight, self-contained database that does not require a server. It is ideal for quick setups, small applications, and IoT devices, as it stores data directly in files and is easy to manage.

Getting Started

Prerequisites

Before you begin, make sure you have the following:

  • Ensure you have a running Node-RED instance. The quickest and easiest way to have a manageable and scalable Node-RED instance is by signing up on FlowFuse and creating an instance.
  • Install the node-red-node-sqlite package using the Palette Manager. This is the official Node-RED SQLite node maintained by the Node-RED team, ensuring reliability and compatibility.

Configuring SQLite in Node-RED

  1. Drag the sqlite node onto the Node-RED canvas and double-click it.
  2. In the Database field, click the + icon to add a new configuration. Enter the database name.
    • Example: yourdbname
    • For a temporary database /tmp/yourdbname.
  3. Select the appropriate Mode/Permissions:
    • Read-Write-Create
    • Read-Write
    • Read-Only
  4. Click Add, then Done to save the configuration.

Understanding Types of SQL Queries Available

The SQLite node provides four options to specify the SQL query type:

  • Fixed Statement – A static SQL query defined directly in the node.
  • Via msg.topic – Accepts the SQL query dynamically from the incoming message's msg.topic.
  • Prepared Statement – Uses placeholders in the SQL query, with values supplied via msg.params for safer and reusable queries.
  • Batch Without Response – Executes multiple queries in a batch without returning any results, useful for bulk inserts or updates.

Most users are familiar with Fixed Statement and Via msg.topic, so let us explore the other two options in more detail.

Prepared Statement

Prepared statements let you safely insert values into queries without worrying about SQL injection. Instead of concatenating strings, you use placeholders and pass the actual values separately.

There are two ways to pass parameters:

1. Using Named Parameters (Object)

Use $ placeholders in your query and pass values as an object in msg.params:

// Function node before sqlite
msg.params = {
  $id: 1,
  $name: "John Doe"
}
return msg;

SQL Query:

INSERT INTO user_table (user_id, user) VALUES ($id, $name);

2. Using Positional Parameters (Array)

Use item-number placeholders ($1, $2, etc.) and pass the values as an array in msg.params.

// Function node before sqlite
msg.params = [1, "John Doe"];
return msg;

SQL Query:

INSERT INTO user_table (user_id, user) VALUES ($1, $2);

Batch Without Response

Batch mode allows you to execute multiple SQL statements in a single operation without retrieving any results. This is ideal for bulk inserts, updates, deletes, or schema changes where you don't need data returned.

The SQL statements are dynamically generated in code and passed as a single string in msg.topic, with each statement separated by a semicolon.

Example - Simple Batch:

// Function node before sqlite
// Generate multiple SQL statements
msg.topic = `
    INSERT INTO user_table (user_id, user) VALUES (1, 'Alice');
    INSERT INTO user_table (user_id, user) VALUES (2, 'Bob');
    UPDATE user_table SET user = 'Alice Smith' WHERE user_id = 1;
`;
return msg;

Example - Programmatically Generated Batch:

// Function node before sqlite
// Build SQL from array of data
const users = [
    { id: 1, name: 'Alice' },
    { id: 2, name: 'Bob' },
    { id: 3, name: 'Charlie' }
];

// Generate INSERT statements dynamically
const inserts = users.map(u => 
    `INSERT INTO user_table (user_id, user) VALUES (${u.id}, '${u.name}');`
).join('\n');

msg.topic = inserts;
return msg;

Note: If any of the values used to build batch SQL statements come from user input or external sources, always sanitize them or switch to prepared statements. Building SQL strings directly can expose your application to SQL injection vulnerabilities.

Complete CRUD Operations Example

This section demonstrates a simple workflow for Create, Read, Update, and Delete operations using SQLite in Node-RED. Each operation will demonstrate a different SQL query type available in the SQLite node, giving you practical experience with all four methods: Fixed Statement, Via msg.topic, Prepared Statement, and Batch Without Response with important CRUD operations.

Note: The following examples use a devices table instead of the user_table shown in the Prepared Statement and Batch examples above. This provides a more complete, real-world scenario for demonstrating CRUD operations. The importable flow at the end of this section includes all the necessary table creation and operations for the devices table.

Create Table

  1. Drag an inject node onto the canvas.
  2. Drag the sqlite node onto the canvas and double-click it.
  3. Select the correct SQLite configuration from the database dropdown.
  4. Choose fixed statement as the SQL query type.
  5. Enter the following SQL in the query field:
CREATE TABLE IF NOT EXISTS devices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id TEXT NOT NULL UNIQUE,
    name TEXT,
    status TEXT,
    location TEXT,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP
);
  1. Add a debug node and connect all nodes.
  2. Deploy the flow and click the inject button.

Insert

  1. Drag an inject node onto the canvas.
  2. Drag a function node and add the following code:
const devices = [
    { id: 'DEV002', name: 'Pressure Sensor', status: 'offline', location: 'Factory Floor 2' },
    { id: 'DEV003', name: 'Humidity Sensor', status: 'online', location: 'Warehouse 1' },
    { id: 'DEV004', name: 'Vibration Sensor', status: 'online', location: 'Factory Floor 3' },
    { id: 'DEV005', name: 'Flow Sensor', status: 'offline', location: 'Plant 1' },
    { id: 'DEV006', name: 'Level Sensor', status: 'online', location: 'Tank 1' }
];

const batchSQL = devices.map(d =>
    `INSERT INTO devices (device_id, name, status, location) VALUES 
    ('${d.id}', '${d.name}', '${d.status}', '${d.location}');`
).join('\n');

msg.topic = batchSQL;
return msg;
  1. Drag the sqlite node and configure it:
    • Select batch without response as the SQL query type.
  2. Add a debug node and connect all nodes.
  3. Deploy the flow and click the inject button.

Read

  1. Drag an inject node onto the canvas.
  2. Drag the sqlite node onto the canvas and double-click it.
  3. Select the correct SQLite configuration from the database dropdown.
  4. Choose fixed statement as the SQL query type.
  5. Enter the following SQL in the query field:
SELECT * FROM devices;
  1. Add a debug node and connect all nodes.
  2. Deploy the flow and click the inject button.

Update

  1. Drag an inject node onto the canvas.
  2. Drag a change node and configure it:
    • Set msg.params to ["offline", "DEV002"] (type: JSON)
  3. Drag the sqlite node and configure it:
    • Select prepared statement as the SQL query type.
    • Enter the SQL query:
UPDATE devices SET status = $1 WHERE device_id = $2;
  1. Add a debug node and connect all nodes.
  2. Deploy the flow and click the inject button.

Delete

  1. Drag an inject node onto the canvas.
  2. Drag a change node and configure it:
    • Set msg.params to {} (type: JSON)
    • Set msg.params.$device_id to DEV006 (type: string)
  3. Drag the sqlite node and configure it:
    • Select prepared statement as the SQL query type.
    • Enter the SQL query:
DELETE FROM devices WHERE device_id = $id;
  1. Add a debug node and connect all nodes.
  2. Deploy the flow and click the inject button.

Below is the complete flow created throughout this guide. You can import it into Node-RED and experiment with it.