Better SQLite Node icon

Better SQLite Node

A node to perform query in a local sqlite database
Join our community

Overview

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

Common scenarios include:

  • Managing local data storage for small applications or automation tasks.
  • Running complex SELECT queries with parameters to retrieve filtered data.
  • Performing batch inserts or updates in a local SQLite database.
  • Creating or modifying database schema dynamically as part of a workflow.

For example, you could use this node to select user records matching certain criteria, insert new log entries, or update configuration settings stored in a SQLite file.

Properties

Name Meaning
Database Path The filesystem path to the SQLite database file to connect to (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. Supports parameter placeholders prefixed with $ (e.g., $key).
Args JSON object containing key-value pairs for query parameters. Keys correspond to placeholders in the query.
Spread Result (Only for SELECT queries) Whether to output each row as a separate item instead of one item with all rows.

Output

The node outputs items with a json property containing the query result:

  • For SELECT queries:
    • If "Spread Result" is false, the output is a single item with json set to an array of rows returned by the query.
    • If "Spread Result" is true, each row is output as a separate item with json containing that row's data.
  • For INSERT, UPDATE, and DELETE queries, the output contains an object with:
    • changes: Number of rows affected.
    • last_id: The last inserted row ID (if applicable).
  • For CREATE and other queries, the output contains a message indicating success.

If an error occurs during execution, the node either throws an error or outputs an item with an error field describing the problem, depending on the "Continue On Fail" setting.

Dependencies

  • Requires the better-sqlite3 library for SQLite database interaction.
  • The SQLite database file must be accessible at the specified path on the machine running n8n.
  • No external API keys or credentials are needed since it operates on local files.

Troubleshooting

  • No database path provided: The node will throw an error if the database path is empty. Ensure the path is correct and accessible.
  • No query provided: An error is thrown if the query string is empty.
  • File access issues: Permissions or incorrect paths can cause failures opening the database file.
  • SQL syntax errors: Malformed queries will cause errors from SQLite; verify your SQL syntax.
  • Parameter mismatches: Ensure that all placeholders in the query have corresponding keys in the Args JSON.
  • Large result sets: Spreading large SELECT results into many items may impact performance.

To resolve errors, check the error messages returned in the output or thrown exceptions, verify input properties, and confirm the database file's accessibility.

Links and References

Discussion