Database & Storage Nodes

Execute SQL and NoSQL database operations with caching support

Database & Storage Nodes

The Database Node gives you powerful access to your databases—run queries, insert records, update data, execute transactions. Support for SQL (PostgreSQL, MySQL, SQLite) and NoSQL (MongoDB) databases.

Database Node

When to use: When you need to query, insert, update, or delete data from your databases.

The Database Node supports SQL and NoSQL operations. Configure your database connection, then execute any operation you need.

Configuration

Configuration:
  database_type: postgresql | mysql | sqlite | mongodb
  operation: query | insert | update | delete | transaction
  query: "SELECT * FROM users WHERE id = @id"
  parameters:
    id: "{{ input.user_id }}"

PostgreSQL Examples

Example 1: Query Users

When to use: Fetch data for processing, validation, or enrichment.

Database Configuration:

database_type: postgresql
operation: query
query: |
  SELECT id, name, email, created_at
  FROM users
  WHERE status = @status
  ORDER BY created_at DESC
  LIMIT 10
parameters:
  status: "{{ input.filter_status }}"

Incoming data:

{
  "filter_status": "active"
}

Result:

{
  "rows": [
    { "id": 1, "name": "Alice", "email": "alice@example.com", "created_at": "2025-01-15" },
    { "id": 2, "name": "Bob", "email": "bob@example.com", "created_at": "2025-01-16" },
    { "id": 3, "name": "Charlie", "email": "charlie@example.com", "created_at": "2025-01-17" }
  ],
  "row_count": 3
}

Loop through these results with a Loop Node to process each user!

Example 2: Insert New Record

When to use: Store new data—user registrations, orders, submissions, etc.

Incoming data:

{
  "name": "Diana",
  "email": "diana@example.com",
  "phone": "555-0104",
  "source": "signup_form"
}

Database Configuration:

database_type: postgresql
operation: insert
query: |
  INSERT INTO users (name, email, phone, source, created_at)
  VALUES (@name, @email, @phone, @source, NOW())
  RETURNING id, created_at
parameters:
  name: "{{ input.name }}"
  email: "{{ input.email }}"
  phone: "{{ input.phone }}"
  source: "{{ input.source }}"

Result:

{
  "rows": [
    { "id": 456, "created_at": "2025-02-10T14:30:00Z" }
  ],
  "row_count": 1
}

The RETURNING clause returns the inserted row so you can access the new id.

Example 3: Update Record

When to use: Modify existing data—change status, update profiles, record activity, etc.

Incoming data:

{
  "user_id": 123,
  "new_status": "premium",
  "stripe_customer_id": "cus_abc123"
}

Database Configuration:

database_type: postgresql
operation: update
query: |
  UPDATE users
  SET status = @status, stripe_id = @stripe_id, updated_at = NOW()
  WHERE id = @user_id
  RETURNING id, status, updated_at
parameters:
  user_id: "{{ input.user_id }}"
  status: "{{ input.new_status }}"
  stripe_id: "{{ input.stripe_customer_id }}"

Result:

{
  "rows": [
    { "id": 123, "status": "premium", "updated_at": "2025-02-10T14:30:00Z" }
  ],
  "row_count": 1
}

Example 4: Delete Record

When to use: Remove data—user requests, data cleanup, etc.

Incoming data:

{
  "user_id": 456,
  "reason": "account_closure"
}

Database Configuration:

database_type: postgresql
operation: delete
query: |
  DELETE FROM users WHERE id = @user_id
  RETURNING id
parameters:
  user_id: "{{ input.user_id }}"

Example 5: Complex Query with Joins

When to use: Get related data from multiple tables.

Database Configuration:

database_type: postgresql
operation: query
query: |
  SELECT
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.created_at > @start_date
  GROUP BY u.id, u.name
  ORDER BY total_spent DESC
parameters:
  start_date: "{{ input.date_from }}"

Perfect for analytics, reporting, customer insights, etc.

Example 6: Transaction (Multiple Operations)

When to use: Ensure atomicity—all operations succeed or all fail.

Incoming data:

{
  "from_user_id": 123,
  "to_user_id": 456,
  "amount": 50
}

Database Configuration:

database_type: postgresql
operation: transaction
queries:
  - |
    UPDATE user_accounts
    SET balance = balance - @amount
    WHERE user_id = @from_user_id
  - |
    UPDATE user_accounts
    SET balance = balance + @amount
    WHERE user_id = @to_user_id
  - |
    INSERT INTO transactions (from_user_id, to_user_id, amount, created_at)
    VALUES (@from_user_id, @to_user_id, @amount, NOW())
parameters:
  from_user_id: "{{ input.from_user_id }}"
  to_user_id: "{{ input.to_user_id }}"
  amount: "{{ input.amount }}"

Either both users get updated AND the transaction is logged, or the entire operation rolls back. No partial transfers!

MySQL Examples

Example 1: Query Orders

database_type: mysql
operation: query
query: |
  SELECT id, customer_id, total, status, created_at
  FROM orders
  WHERE status = @status AND created_at BETWEEN @start_date AND @end_date
  ORDER BY created_at DESC
parameters:
  status: "{{ input.order_status }}"
  start_date: "{{ input.date_from }}"
  end_date: "{{ input.date_to }}"

Example 2: Insert and Get Auto-Increment ID

database_type: mysql
operation: insert
query: |
  INSERT INTO orders (customer_id, total, status)
  VALUES (@customer_id, @total, 'pending')
parameters:
  customer_id: "{{ input.customer_id }}"
  total: "{{ input.total }}"

Returns the auto-increment id in the result.

MongoDB Examples

Example 1: Find Documents

When to use: Query NoSQL collections.

Database Configuration:

database_type: mongodb
operation: query
collection: users
query:
  status: "active"
  created_at:
    $gt: "{{ input.date_from }}"
projection:
  _id: 1
  name: 1
  email: 1
limit: 100

Result:

{
  "documents": [
    { "_id": "...", "name": "Alice", "email": "alice@example.com" },
    { "_id": "...", "name": "Bob", "email": "bob@example.com" }
  ],
  "count": 2
}

Example 2: Insert Document

database_type: mongodb
operation: insert
collection: orders
document:
  customer_id: "{{ input.customer_id }}"
  items: "{{ input.items }}"
  total: "{{ input.total }}"
  status: "pending"
  created_at: "{{ now() }}"

Example 3: Update Document

database_type: mongodb
operation: update
collection: users
filter:
  _id: "{{ input.user_id }}"
update:
  $set:
    status: "{{ input.new_status }}"
    updated_at: "{{ now() }}"

Example 4: Aggregation Pipeline

database_type: mongodb
operation: query
collection: orders
pipeline:
  - $match:
      created_at:
        $gte: "{{ input.start_date }}"
  - $group:
      _id: "$customer_id"
      total_spent: { $sum: "$total" }
      order_count: { $sum: 1 }
  - $sort:
      total_spent: -1
  - $limit: 10

Returns top 10 customers by spending!

SQLite Examples

Example 1: Simple Query

database_type: sqlite
operation: query
query: |
  SELECT * FROM products
  WHERE category = @category
  ORDER BY price ASC
parameters:
  category: "{{ input.category }}"

Great for local development and testing!

Common Database Patterns

Pattern 1: Query and Process

Start
  ↓
Database Query (fetch records)
  ↓
Loop (for each record)
  ├─ Transform (normalize data)
  ├─ AI Agent (analyze/classify)
  ├─ Validate (check business rules)
  └─ Next record
     ↓
     Merge (collect results)
     ↓
     Database Insert (store processed data)

Pattern 2: Data Enrichment

Start (webhook with order)
  ↓
Database Query (get customer details)
  ↓
Merge (combine webhook + customer data)
  ↓
HTTP Request (call fraud detection API)
  ↓
Set (compute shipping cost)
  ↓
Database Insert (save enriched order)

Pattern 3: Atomic Transaction

Start
  ↓
Database Transaction
  ├─ Deduct from account A
  ├─ Add to account B
  ├─ Log transaction
  └─ Update balance cache
     ↓
     Success/Failure (transaction committed or rolled back)

Pattern 4: Real-Time Sync

Webhook Trigger (external system update)
  ↓
Database Query (get current state)
  ↓
Data Transform (map to schema)
  ↓
Database Update/Insert (sync data)
  ↓
Notification (notify team of change)

Database Best Practices

Performance

  1. Add indexes on columns you filter by (WHERE id = ..., WHERE status = ...)
  2. Use LIMIT when you only need a few records
  3. Avoid N+1 queries - use joins instead of looping and querying per row
  4. Parameterize queries - always use @param syntax, never string concatenation

Security

  1. Use parameterized queries - prevents SQL injection
  2. Never expose raw connection strings - use DeepChain credential vault
  3. Apply principle of least privilege - database user should only access needed tables

Reliability

  1. Use transactions for multi-step operations
  2. Set timeouts on long-running queries
  3. Add error handling - queries can fail (timeouts, constraints, etc.)
  4. Monitor slow queries - add logging to identify bottlenecks

Optimization Examples

Example: Batch Insert (Fast)

database_type: postgresql
operation: insert
query: |
  INSERT INTO users (name, email) VALUES
  (@name1, @email1),
  (@name2, @email2),
  (@name3, @email3)

Faster than 3 separate inserts!

Example: Using Indexes

query: |
  CREATE INDEX idx_users_status ON users(status);
  CREATE INDEX idx_orders_created_at ON orders(created_at);

Queries with WHERE status = 'active' now execute 100x faster!

Example: Caching with Cache Node

Database Query (expensive)
  ├─ Success → Cache Storage (set, TTL 3600)
  └─ Use result

Next request:
  Cache Storage (get)
  ├─ Cache hit → Return cached data
  └─ Cache miss → Query database again

Next Steps