SQLite Node icon

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 such as creating tables, inserting, updating, deleting, and selecting rows. The node is useful for automating database operations within workflows, enabling integration of SQLite data manipulation without needing external database services.

Common scenarios include:

  • Running SELECT queries to fetch data from a local SQLite database.
  • Inserting or updating records based on workflow inputs.
  • Creating or modifying database schema dynamically.
  • Deleting specific rows based on conditions.

For example, you could use this node to query user information stored in a local SQLite file and then process the results further in your workflow.

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 binding with named parameters (e.g., @key).
Args JSON object containing key-value pairs for query parameters to bind in the SQL query.
Spread Result (Only for SELECT queries) Whether to spread the result into multiple output items instead of one item containing all results.
Additional Options Collection of extra options. Currently supports: Use Default Bindings - whether to use default bindings for the SQLite library (useful outside Docker).

Output

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

  • For SELECT queries:
    • If "Spread Result" is enabled, each row from the result set becomes a separate output item with its data under json.
    • Otherwise, all rows are returned together inside a single item’s json field as an array.
  • 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 and "Continue On Fail" is enabled, the output item will contain an error field describing the issue.

The node does not output binary data.

Dependencies

  • Requires the better-sqlite3 library bundled with the node for SQLite operations.
  • No external API keys or online services are needed.
  • The node expects access to the local filesystem path where the SQLite database file resides.
  • Optionally, when running outside of the provided Docker image, you may enable "Use Default Bindings" to adjust native bindings for compatibility.

Troubleshooting

  • No database path provided: The node throws an error if the database path property is empty. Ensure the path is correct and accessible.
  • No query provided: An error is thrown if the query string is empty.
  • Query syntax errors: Errors from malformed SQL or invalid parameters will be reported. Check the SQL syntax and parameter names.
  • Parameter binding issues: Make sure the keys in the Args JSON match the named parameters in the query (without $ signs).
  • File access errors: If the SQLite file cannot be opened (missing file, permission denied), the node will fail.
  • Native bindings issues: When running outside the Docker environment, enabling "Use Default Bindings" can resolve native module loading problems.
  • Use the "Continue On Fail" option in the workflow to handle errors gracefully and inspect error messages in output items.

Links and References

Discussion