Description

SQLite Manager (n8n community node)

Manage local SQLite databases – including vector search with sqlite-vec – directly from your n8n workflows.

This package bundles a single node (sqliteManager) that can:

• discover and manage SQLite database files stored on the n8n host
• create or delete databases with optional bootstrap SQL
• execute arbitrary SQL (including vector KNN queries via sqlite-vec)
• return query results as structured JSON for downstream nodes

Use case examples

• Maintain lightweight knowledge bases for AI agents hosted in n8n
• Share local cache tables between workflows without an external DB
• Run ad‑hoc data migrations or maintenace scripts inside a workflow

• Requirements
• Installation
• Configuration
• Node operations
• Usage examples
• Development
• Support
• Version history

Requirements

The node does not require external credentials or APIs. All operations happen on the local file system of the n8n host/container.

Installation

From the n8n UI (recommended)

1. Open Settings → Community Nodes → Install.
2. Enter the package name: n8n-nodes-rckflr-sqlitemanager.
3. Acknowledge the community-node warning banner and confirm the install.
4. Reload the editor if the new icon does not appear immediately.

From the command line

cd ~/.n8n
pnpm install n8n-nodes-rckflr-sqlitemanager
pnpm build

Restart the n8n instance afterwards.

Docker / self-hosted images

Add the package to the N8N_COMMUNITY_NODES_INSTALL environment variable:

N8N_COMMUNITY_NODES_INSTALL=n8n-nodes-rckflr-sqlitemanager

The included Dockerfile in this repository demonstrates how we compile sqlite-vec at build time and mount persistent volumes for SQLite data and user-installed Python packages.

Configuration

The node looks up its working directory from the following environment variables:

Ensure that the directory referenced by SQLITE_BASE_DIR is writable by the user running n8n (inside Docker, usually the node user).

Node operations

Vector Operations

The node provides optimized operations for vector workflows:

Batch Insert Vectors: Efficiently insert large numbers of embeddings by batching multiple INSERT statements together. This dramatically improves performance compared to individual inserts.

Vector Search: Simplified interface for KNN similarity search with support for:

• Multiple distance metrics (cosine, L2, inner product)
• Additional WHERE filters to combine with vector search
• Customizable result fields
• Query vector from input data or JSON array

Example vector search query generated by the node:

SELECT id, text, distance
FROM vec_articles
WHERE headline_embedding MATCH json_array(0.1, 0.2, 0.3, ...)
  AND k = 5
ORDER BY distance;

The node automatically preloads the sqlite-vec library before executing any SQL statement.

Usage examples

1. Building a lightweight embeddings store

1. Use Create Database with an initialSql that defines a vec0 virtual table.
2. Insert embeddings generated elsewhere (or from an HTTP node).
3. Run Execute Query with a MATCH clause to retrieve similar content.

2. ETL on local CSV drops

1. Start the workflow when a new file arrives (e.g. via SFTP or HTTP).
2. Load the data into a temporary SQLite database with staging tables.
3. Run transformation SQL, then export the result to the next node.
4. Clean up the temporary database with Delete Database.

3. Operational maintenance

Schedule the node (using Cron plus Execute Query) to vacuum or analyze databases, and funnel results into Slack/email notifications if anomalies are detected.

4. Batch insert embeddings workflow

1. Generate embeddings from multiple documents using an AI node (e.g., OpenAI Embeddings)
2. Use Batch Insert Vectors operation to insert all embeddings at once
   • Set vectorField to the field name containing your embeddings (e.g., embedding)
   • Set metadataFields to text,source,timestamp to store additional context
   • Batch size of 500 provides good performance for most use cases
3. Result shows total vectors inserted and number of batches executed

5. Semantic search workflow

1. Generate query embedding from user input
2. Use Vector Search operation:
   • Set queryVector to reference the embedding field from previous node
   • Set k to desired number of results (e.g., 10)
   • Optionally add filters like category = "documentation"
   • Choose distance metric (cosine recommended for most embeddings)
3. Results include matched records with distance scores

Performance Optimization

PRAGMA Settings for Vector Operations

Add these to your initialSql when creating databases for better vector search performance:

-- Increase cache size for better read performance (in KB)
PRAGMA cache_size = -64000;  -- 64 MB cache

-- Enable memory-mapped I/O for faster reads
PRAGMA mmap_size = 268435456;  -- 256 MB

-- Optimize for speed over safety (acceptable for embeddings cache)
PRAGMA synchronous = NORMAL;
PRAGMA journal_mode = WAL;

-- Create your vec0 table
CREATE VIRTUAL TABLE embeddings USING vec0(
    id INTEGER PRIMARY KEY,
    embedding FLOAT[384],
    text TEXT,
    source TEXT
);

Batch Insert Best Practices

• Batch Size: Use 500-1000 vectors per batch for optimal performance
• Transaction Wrapping: The node automatically batches inserts; no need for explicit transactions
• Memory Considerations: Larger batches use more memory but reduce round-trips to SQLite

Vector Search Optimization Tips

• Distance Metric Selection:

  • cosine: Best for normalized embeddings (OpenAI, Cohere, most modern models)
  • l2: Euclidean distance for spatial data
  • inner_product: When embeddings are not normalized

• Pre-filtering: Use additionalFilters to reduce search space:

  category = "tech" AND date > '2024-01-01'
  

• Index Optimization: sqlite-vec automatically maintains indexes; no manual tuning needed

• Result Set Size: Keep k values reasonable (10-100) for best performance

Development

pnpm install
pnpm lint
pnpm build
pnpm dev   # launches a local n8n instance with hot-reload for the node

We rely on the official @n8n/node-cli for builds, linting, and publishing. Make sure you run pnpm build before committing to keep the transpiled artifacts in sync.

Automated tests

Run the end-to-end regression suite (requires sqlite3 and a compiled sqlite-vec library):

SQLITE_VEC_LIBRARY=/usr/local/lib/sqlite-extensions/vec0 \
SQLITE_VEC_ENTRYPOINT=sqlite3_vec_init \
pnpm test

The test script spins up a temporary database, creates a vec0 virtual table, inserts fixture vectors, and asserts that the nearest-neighbour query returns the expected record.

Project structure

nodes/
  SqliteManager/
    SqliteManager.node.ts    # Source TypeScript implementation
    SqliteManager.node.json  # Metadata consumed by n8n
    sqliteManager.svg        # Icon (light theme)
    sqliteManager.dark.svg   # Icon (dark theme)
dist/                        # Build output generated by `pnpm build`

Verification checklist for n8n

• Package name starts with n8n-nodes-.
• README includes installation, usage, compatibility, and support information.
• MIT license file present.
• n8n field in package.json declares provided nodes.
• npm run lint and npm run build succeed.
• No bundled secrets or credentials.

Support

Please open an issue on GitHub for bug reports or feature requests:
https://github.com/MauricioPerera/n8n-nodes-rckflr-sqlitemanager/issues

For consultancy or commercial support, reach out to Mauricio Perera at mauricioperera@gmail.com.

Version history

See CHANGELOG.md for a detailed release log.

• 0.1.0 – Initial public release submitted for n8n community verification.

License

Released under the MIT License.