SQLite Node

A node to perform query in a local sqlite database

Overview

This node allows you to execute SQL queries on a local SQLite database file. It supports various query types such as creating tables, inserting, updating, deleting, and selecting rows. The node is useful for automating database operations within workflows that require interaction with SQLite databases without needing an external database server.

Common scenarios include:

Automating data insertion or updates in a local SQLite database.
Running complex SELECT queries to retrieve data for further processing.
Managing database schema by creating tables or deleting records.
Integrating SQLite-based data storage into larger automation pipelines.

For example, you could use this node to insert new user records into a local database, then select and process those records in subsequent workflow steps.

Properties

Name	Meaning
Database Path	The path to the SQLite database file (e.g., `/path/to/database.sqlite`).
Query Type	The type of SQL query to execute. Options: `AUTO` (auto-detect), `CREATE`, `DELETE`, `INSERT`, `SELECT`, `UPDATE`.
Query	The SQL query string to execute. Supports parameter placeholders using `$` notation (e.g., `$key`).
Args	JSON object containing key-value pairs for query parameters, where keys correspond to placeholders in the query. Example: `{"$key": "value"}`.
Spread Result	(Only for `SELECT` queries) Whether to spread the result into multiple output items instead of one item containing all results.

Output

The node outputs an array of items, each with a json property containing the query result:

For SELECT queries:
- If multiple queries are executed (separated by semicolons), the output is an array of arrays of rows.
- If "Spread Result" is enabled, each row from the result set is output as a separate item.
- Each row is represented as a JSON object with column names as keys.
For INSERT, UPDATE, and DELETE queries:
- Outputs an object with properties:
  - changes: Number of rows affected.
  - last_id: The last inserted row ID (for inserts).
For CREATE and other queries:
- Outputs a message indicating successful execution.

If an error occurs during query execution and "Continue On Fail" is enabled, the output will contain an item with an error field describing the issue.

Dependencies

Requires the better-sqlite3 library for SQLite database interaction.
The node expects access to the local filesystem path where the SQLite database file resides.
No external API keys or online services are required.
Ensure the n8n instance has permission to read/write the specified database file.

Troubleshooting

No database path provided: The node throws an error if the database path is empty. Make sure to specify a valid path.
No query provided: The node requires a non-empty SQL query string.
Invalid SQL syntax or runtime errors: Errors thrown by SQLite (e.g., syntax errors, constraint violations) will be reported. Enable "Continue On Fail" to handle these gracefully.
Parameter mismatch: Ensure that the keys in the Args JSON match the placeholders in the query (placeholders use $ prefix).
File access issues: Verify that the n8n process has appropriate permissions to access the SQLite file.
Multiple SELECT queries: When running multiple SELECT statements separated by semicolons, ensure each query is valid and returns expected results.