n8n-nodes-sqlite3

This is an n8n community node to perform CRUD operations on a Remote SQLite Database via SSH.

It allows you to manage SQLite databases hosted on remote servers (e.g., VPS, Shared Hosting) without downloading the file.

Prerequisites

1. SSH Access to the remote server.
2. sqlite3 binary must be installed and available in the PATH on the remote server.
   • Test it by running ssh user@host "sqlite3 --version"
   • If it's not in PATH, you might need to symlink it or use the full path (currently not configurable, defaults to sqlite3).

Operations

1. Execute Query

Run any raw SQL query. Useful for complex JOINs, GROUP BY, or subqueries.

• Query: Type your SQL (e.g., SELECT * FROM users JOIN orders ON users.id = orders.user_id).

2. Insert

Insert data from n8n items into a table.

• Conflict Mode:
  • Abort: Fail if a constraint is violated (default).
  • Ignore: Skip row on conflict (INSERT OR IGNORE).
  • Replace: Overwrite existing row (INSERT OR REPLACE).

3. Select

Read data from a table with advanced options.

• Columns: Specify columns (e.g., id, name, email) or use *. Support for aggregates (e.g., COUNT(*)).
• Distinct: Toggle SELECT DISTINCT to remove duplicates.
• Filters: Add multiple conditions (AND logic):
  • Operators: =, !=, <, <=, >, >=, LIKE, NOT LIKE, GLOB, IN, NOT IN, BETWEEN, IS NULL, IS NOT NULL.
• Sort: Order results by one or more columns (ASC/DESC).
• Limit & Offset: Control pagination.

4. Update

Update rows in a table based on conditions.

• Filters: Specify which rows to update (WHERE clause).
• Data: The fields in your input JSON will be set in the database.

5. Delete

Delete data or tables.

• Mode:
  • Delete Rows: Remove specific rows based on Filters.
  • Drop Table: Remove the entire table and its structure.

6. Vacuum

• Run VACUUM command to rebuild the database file and reclaim space.

Credentials

Requires SQLite SSH Credentials:

• Host
• Port
• Username
• Password OR Private Key

Output Format

The node attempts to use sqlite3 -json for parsing results.

• JSON (Preferred): Requires SQLite 3.33.0+ on the remote server.
• CSV (Fallback): Can be selected in "Options" if your remote SQLite version is older. Note: CSV parsing is basic.

License

MIT