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 an n8n workflow without needing an external database server.

Common scenarios include:

Running SELECT queries to retrieve data from a local SQLite database.
Automating data insertion or updates based on workflow inputs.
Managing database schema by creating tables or deleting records.
Executing multiple SELECT queries in one go and optionally spreading results into separate items.

Example use case: You have a local SQLite database storing customer orders. This node can be used to fetch orders matching certain criteria, update order statuses, or insert new orders as part of an automated process.

Properties

Name	Meaning
Database Path	The file path to the SQLite database file (e.g., `/path/to/database.sqlite`).
Query Type	The type of SQL query to execute. Options: `AUTO` (detect automatically), `CREATE`, `DELETE`, `INSERT`, `SELECT`, `UPDATE`.
Query	The SQL query string to run against the database.
Args	JSON object containing parameters to bind to the query placeholders (e.g., `{ "$key": "value" }`).
Spread Result	(Only for SELECT queries) Whether to spread the result rows into multiple output items.

Output

The node outputs an array of items where each item contains a json field with the query result:

For SELECT queries, the output is either:
- A single item with json containing the full result set (array of rows), or
- Multiple items each containing one row if "Spread Result" is enabled.
For INSERT, UPDATE, and DELETE queries, the output includes an object with:
- changes: Number of rows affected.
- last_id: The last inserted row ID (for inserts).
For CREATE and other queries, the output contains a message indicating success.
In case of errors, the output may contain an error message under json.error.

The node does not output binary data.

Dependencies

Requires the better-sqlite3 library bundled with the node for SQLite interaction.
No external services or API keys are needed.
The SQLite database file must be accessible at the specified path on the machine running n8n.

Troubleshooting

No database path provided: The node will throw an error if the database path property is empty. Ensure the path is correct and accessible.
No query provided: An error occurs if the query string is empty.
Invalid SQL syntax or runtime errors: Errors from SQLite (e.g., malformed queries, constraint violations) will be thrown. Check the query syntax and database schema.
Incorrect args format: The Args property must be valid JSON mapping parameter names to values. Invalid JSON will cause parsing errors.
File access issues: If the SQLite file is locked or inaccessible due to permissions, the node will fail to open the database.
When using AUTO query type, ensure the query starts with a recognizable keyword (SELECT, INSERT, etc.) for proper detection.

To resolve errors, verify input properties, check database accessibility, and validate SQL statements.