Writing SQL

<!-- TIER:1 -->

Writing SQL for RDBMS Integrations

RDBMS exports and imports use SQL queries to read from and write to relational databases. Queries are Handlebars templates -- the platform evaluates expressions like {{{record.fieldName}}} at runtime, then executes the resulting SQL against the connected database.

This skill covers what SQL to write and how to write it. For which adaptor type to use, connection setup, and queryType selection, see the related skills below.

Quick Reference

Context decision matrix

Key syntax rules

1. Always use `record.` prefix (AFE 2.0). Never bare fieldName or data.fieldName.
2. Prefer triple braces `{{{ }}}` for values in SQL. Double braces {{ }} auto-wrap values in single quotes in RDBMS context -- this silently corrupts numeric values and breaks SQL syntax.
3. Add your own quotes for strings: '{{{record.name}}}'. Triple braces give you full control.
4. No quotes for numbers: {{{record.quantity}}}.
5. Import `query` is an array of strings, not a single string: ["INSERT INTO ..."].
6. Nested fields use dot notation: {{{record.address.city}}}.

Related skills

• configuring-exports > RDBMS -- adaptorType, delta/once mode, export-level config
• configuring-imports > RDBMS -- queryType decision tree, bulkInsert/bulkLoad config
• writing-handlebars -- full Handlebars syntax, helpers, block expressions
• configuring-connections > RDBMS -- database connection setup

Schema reference

• rdbms-export.yml -- export query and once fields
• rdbms-import.yml -- import query, queryType, bulkInsert, bulkLoad fields

<!-- TIER:2 -->

How to Write a SQL Query

1. Discover the schema

Query the live database to find tables and columns before writing SQL:

celigo metadata types <connectionId>                    # List tables
celigo metadata fields <connectionId> <tableName>       # List columns and types

For Snowflake, use fully qualified names: database.schema.table.

2. Determine the query pattern

Exports -- what kind of data fetch?

Imports -- what kind of write operation?

3. Write the SQL

Follow the database-specific dialect patterns below. Use {{{record.fieldName}}} for runtime values.

4. Test the query

# Test an export query -- invoke returns real data
celigo exports invoke <exportId>

# Test an import -- submit test records
echo '[{"name":"test","email":"test@example.com"}]' | celigo imports invoke <importId>

Export Query Patterns

Standard SELECT

SELECT id, name, email, status
FROM customers
WHERE status = 'ACTIVE'
ORDER BY id

Delta export (incremental)

Use {{lastExportDateTime}} (platform-injected, not from a record). The token resolves to the timestamp of the last successful export run.

SELECT id, name, email, updated_at
FROM customers
WHERE updated_at > '{{lastExportDateTime}}'
ORDER BY updated_at ASC

For databases that need specific timestamp formats:

-- Snowflake
WHERE updated_at > TO_TIMESTAMP('{{lastExportDateTime}}', 'YYYY-MM-DD HH24:MI:SS')

-- MySQL
WHERE updated_at > STR_TO_DATE('{{lastExportDateTime}}', '%Y-%m-%d %H:%i:%s')

-- PostgreSQL
WHERE updated_at > '{{lastExportDateTime}}'::timestamp

Once export (mark-as-processed)

Two queries work together. The export rdbms.query fetches unprocessed records; rdbms.once.query marks each one after successful export.

{
  "type": "once",
  "rdbms": {
    "query": "SELECT id, name, email FROM orders WHERE exported = false",
    "once": {
      "query": "UPDATE orders SET exported = true WHERE id = {{{record.id}}}"
    }
  }
}

JOINs

SELECT o.id, o.order_date, c.name AS customer_name, c.email,
       p.product_name, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'SHIPPED'
  AND o.order_date > '{{lastExportDateTime}}'

Aggregations

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value
FROM orders
WHERE status IN ('COMPLETED', 'SHIPPED')
GROUP BY customer_id
HAVING SUM(total) > 1000

Import Query Patterns

All import queries use {{{record.fieldName}}} to inject values from incoming records. The query field is an array of strings.

INSERT (per_record)

{
  "rdbms": {
    "queryType": ["per_record"],
    "query": ["INSERT INTO customers (name, email, phone) VALUES ('{{{record.name}}}', '{{{record.email}}}', '{{{record.phone}}}')"]
  }
}

UPDATE (per_record)

{
  "rdbms": {
    "queryType": ["per_record"],
    "query": ["UPDATE customers SET name = '{{{record.name}}}', email = '{{{record.email}}}' WHERE id = {{{record.id}}}"]
  }
}

UPSERT -- database-specific

MySQL (ON DUPLICATE KEY UPDATE):

{
  "rdbms": {
    "queryType": ["per_record"],
    "query": ["INSERT INTO customers (id, name, email) VALUES ({{{record.id}}}, '{{{record.name}}}', '{{{record.email}}}') ON DUPLICATE KEY UPDATE name = '{{{record.name}}}', email = '{{{record.email}}}'"]
  }
}

PostgreSQL (ON CONFLICT):

{
  "rdbms": {
    "queryType": ["per_record"],
    "query": ["INSERT INTO customers (id, name, email) VALUES ({{{record.id}}}, '{{{record.name}}}', '{{{record.email}}}') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email"]
  }
}

Snowflake (MERGE):

{
  "rdbms": {
    "queryType": ["per_record"],