Defining a SQL Node Type

A custom SQL Node Type gives your team a reusable, SQL-first way to build Nodes. Building one is very similar to building a YAML Node Type, and it is made of the same parts: a Node Metadata Spec plus Create and Run templates. The difference is how Nodes of the type are configured. A YAML Node Type declares config items that render as panel controls; a SQL Node Type declares annotations that Node authors write directly in their SQL.

Before building, read Choosing Between YAML and SQL Node Types to confirm a SQL Node Type fits the pattern you are packaging. Persistent, history-keeping patterns are usually better served by a YAML Node Type.

Create the Node Type

1. In your Workspace, go to Build Settings > Node Types.
2. Select Create Node Type and choose Node V2.
3. Coalesce opens the Node Type Editor with a starter definition and empty Create and Run templates.

The kind is fixed when the type is created. The Version column in the Node Types list shows V2 for SQL Node Types and V1 for YAML Node Types.

Write the Node Metadata Spec

The Node Metadata Spec is the document describing the type, written in YAML in the Node Type Editor. The properties you know from YAML Node Types, such as the name, prefix, and graph color, work the same way. What is new for SQL Node Types is the annotations key:

capitalized: Work
short: WRK
plural: Works
tagColor: '#2EB67D'
annotations:
  node:
    - name: writeMode
      description: How data is written to the target table. Defaults to truncateInsert.
      parameters:
        - name: mode
          type: string
          isRequired: true
          default: truncateInsert
          options: [truncateInsert, append]
    - name: disableTests
      description: Skip all node-level and column-level tests.
    - name: preSQL
      description: SQL statement to run before the load. Repeat to run several, in order.
      allowsMultiple: true
      parameters:
        - name: querySQL
          type: string
          isRequired: true
  column:
    - name: not_null
      description: Column-level test. Fails on rows where the column is NULL.

The annotations key has two sections, node and column, each holding a list of declarations. Declaring an annotation makes it discoverable: it appears in the Annotations panel on every Node of this type, with its description and a copyable snippet with correct quoting. See the Annotation Declarations Reference for every field.

Declarations describe, templates decide

A declaration documents the annotation for Node authors. It does not by itself change any SQL. Every annotation you declare must also be consumed by your Create or Run template, or setting it does nothing. Keep the declared set and the template logic in sync.

New SQL Node Types do not start with an annotations block. Add it as you settle on the options your templates support. The reserved annotations (@id, @nodeType, @description, @materializationType, and the column-level reserved set) are always available and must not be declared again; the panel shows them automatically. Name your annotations with letters, digits, and underscores, avoiding SQL keywords such as unique; Coalesce's own test annotation is @uniqueness for that reason.

Write the Templates

Create and Run templates work exactly as they do for YAML Node Types: Jinja templates that render the DDL for deploy and the DML for run. Everything in Create and Run Templates applies.

The SQL-Node-specific part is where the values come from:

• Node-level annotations arrive as config.<name>: true for a bare annotation, { parameters: [...] } for a parameterized one, and an array of those for a repeatable one.
• Column-level annotations are spread onto the column object, arriving as column.<name> with the same shapes.
• Reserved annotations arrive as real fields: node.materializationType, node.description, column.description, column.nullable, column.defaultValue.
• A declared default is not applied at runtime. Supply the fallback in the template, for example config.writeMode.parameters[0] if config.writeMode is defined else 'truncateInsert'.
• Each source exposes sources[0].cteString, which carries the CTEs from the Node author's SQL, and sources[0].selectModifier, which carries any DISTINCT or similar modifier, so your template can reproduce them in the generated statement.

See A Complete Worked Example in the annotations reference for a full definition, both templates, and a Node SQL file wired together.

What Node Authors Experience

A Node of your type is a single SQL file. The author writes a SELECT, sets options with your declared annotations, and Coalesce infers columns and dependencies from the SQL. The Annotations panel shows them what your type supports. Coalesce manages @id and @nodeType automatically on save.

In Git, each Node of the type is committed as a .sql file, and the Node Type itself is committed as its definition plus template files. See What Gets Committed for the repository layout.

Good Practices

• Declare everything your templates read. Undeclared annotations still hydrate, but authors have no way to discover them, and typos fail silently.
• Prefer several specific annotations over one complex annotation. @testsEnabled plus @tests("...") reads better than a single annotation with many positional parameters.
• Give every parameterized annotation a description and, where the values are a fixed set, options. The panel and copy snippets are your type's documentation surface.
• Validate with a real Node. Create a Node of your type, set each annotation, and check the generated DDL and DML stages before sharing the type.

What's Next

• Annotation Declarations Reference for the full declaration schema.
• SQL Annotations Reference for annotation syntax and hydration shapes.
• Create and Run Templates for template authoring in depth.
• Choosing Between YAML and SQL Node Types if you are deciding which kind to build on.