Start a SQL connector

Building a connector, step 7 (SQL track, 1 of 3). Previous: Choose a kind.

A SQL connector is one file, definition.json, that describes an application whose accounts and access live in its own database tables: which tables or SELECT statements hold each entity, which column identifies a record, which statements create and change rows, and which SELECT lists each Grant with the statements that add and remove it. Owlie derives the connector's full declaration from the definition, and the engine built into Owlie runs every operation. Values always reach the database as bound parameters, never as statement text.

What a SQL connector manages

The SQL kind manages an application's own tables, such as a users table and a user_roles table. Choose it when the application has no API for its access model, or when its tables are the supported way to integrate with it. It does not manage the database's own users and roles (CREATE ROLE, GRANT).

Agree with the application's owner which tables Owlie may read and write. A SQL connector writes past the application's own checks, so a row the application would have refused is your connector's responsibility.

It runs on a private-network gateway

A SQL connector runs only on a private-network gateway: an Owlie agent your organization runs inside its network, which connects to the database and keeps the database password there. Before you can test or install the connector:

your tenant needs an active gateway;
the gateway's operator adds the connector's id to the gateway's allowedCustomConnectors, and lists the database's host:port (for example db.acmeledger.example:5432) in the connector's egressAllowlist;
for installs, the operator grants the connector the gateway secret that holds the database password.

Testing a draft on the gateway runs your unpublished statements against the database, so it needs the custom_connector.publish permission and typed test credentials (In a draft test). The gateway connects with verified TLS unless the install says otherwise, and some TLS choices need the operator's approval (TLS).

Create the connector

The kind is chosen when the connector is created and cannot change later.

In the Connector Builder

Go to Admin > Integrations > Connectors, open the Custom tab and select New connector.
In the New Connector dialog, keep Start from on Blank and choose SQL as the Kind.
Enter a Name and a Key, and optionally a Description. Select Create connector.

The SQL editor opens with a starter definition: one Postgres table, users, read by its id, with an email column. Its tabs are Basics, Connection, Entities, Statements, Grant kinds, Install values, Handlers, Derived manifest, Raw JSON and Files. The builder saves as you type. The strip above the tabs shows the connector's id with a Copy connector id button; send that id to your gateway's operator.

Through the API

Create the connector with createCustomConnector, passing kind: "sql" with the key and name. Its draft starts from the same starter definition. Write your definition.json into the draft with updateCustomConnectorDraft. Build with the API and agents lists the operations and their inputs.

The definition

{
  "engine": 1,
  "dialect": "postgres",
  "description": "Acme Ledger users and roles.",
  "icon": "@acmeledger.example",
  "validationQuery": "SELECT 1 FROM ledger_users LIMIT 1",
  "pageSize": 500,
  "entities": {
    "user": {
      "name": "User",
      "is_account": true,
      "identifier": "id",
      "columns": {
        "id": { "type": "string" },
        "email": { "type": "string", "title": "Email" },
        "updated_at": { "type": "datetime" }
      },
      "sync": { "table": "ledger_users", "orderKey": "id", "updatedAt": "updated_at" }
    }
  }
}

Field	What it is
`engine`	Always `1`, the version of the SQL definition format.
`dialect`	Required and fixed: `postgres`, `mysql`, `mssql` (SQL Server) or `oracle`. Every statement is written for this database.
`description`, `icon`	What the catalog shows.
`validationQuery`	The statement the connection test runs. The default is `SELECT 1`, or `SELECT 1 FROM DUAL` on Oracle. A query on one of the application's tables also proves the connecting user can read it.
`config.properties`	The install fields your definition reads, as JSON Schema (below).
`pageSize`	Rows per page, 1 to 5000. The default is 500.
`entities`	Required, at least one. Keyed by entity key: lowercase letters, digits and `_`, starting with a letter.
`grant_kinds`	Optional. Keyed by Grant kind key (next page).
`custom_ops`, `handlers`	Optional PowerShell scripts, bound as they are for LDAP connectors.

Every install also fills in the fields every SQL connector has, which you do not declare:

Install field	What it is
`dialect`	Fixed to the definition's dialect. The install form fills it in; a draft test through the API must send it.
`host`	The database server, as a plain hostname or IP address.
`port`	Optional. The default is 5432 for Postgres, 3306 for MySQL, 1433 for SQL Server and 1521 for Oracle.
`database`	The database name; for Oracle, the service name. Up to 128 letters, digits, `_`, `.`, `$`, `#` and `-`.
`defaultSchema`	Optional, Postgres and Oracle only. The schema every statement runs in: Sync reads, writes and Grants. A table named without a schema resolves there (below).
`ssl`	Optional TLS settings (TLS).
`user`, `password`	The username and password Owlie connects as.

Entities

Each entity is a set of rows with a stable identifier:

Field	What it does
`name`	What Owlie calls the records. The default is the entity key.
`is_account`	`true` for the account entity. At most one entity is the account.
`identifier`	The column whose value identifies a record. It must be one of the entity's columns. Owlie keeps it as text, whatever the column's type.
`columns`	The columns a record carries, keyed by column name.
`sync`	Which rows Sync reads, and in what order.
`statements`	Optional. The statements that create and change rows (next page).

Choose an identifier the application never changes or reuses, usually its primary key.

Columns

Field	Default	What it does
`type`	`string`	`string`, `number`, `boolean`, `datetime`, or `bigint` (`int8` is the same).
`title`		The label Owlie shows.
`sensitive`	`false`	Mark the value as secret or personal data, which Owlie masks in logs.

Values are read like this:

bigint is always text, so a 64-bit integer never loses precision.
number is a number, except an integer too large to hold exactly, which stays text.
boolean is true for 1, t, true, y and yes, in any case, and false otherwise.
datetime is ISO 8601 text; a timestamp column without a time zone is read as UTC.
Column names match case-insensitively, so Oracle's upper-case answers still fill the columns.

A record carries only the columns you declare. A column name must be a plain identifier: letters, digits, _ and $, not starting with a digit. The names owlie_src, owlie_rel and owlie_source are reserved.

Sync

Field	What it does
`table`	The table or view to read, as `table` or `schema.table`.
`selectStatement`	Instead of `table`: a `SELECT` (or `WITH`) whose rows are the records, for joins or computed columns.
`where`	Optional, with `table` only: a condition, without the word `WHERE`, such as `status <> 'deleted'`.
`orderKey`	Required. A unique, orderable column. Every page continues after the last value of the previous page.
`updatedAt`	Optional. A timestamp column that changes whenever the row changes. It enables incremental Sync.

Give exactly one of table and selectStatement. A table named without a schema resolves in the schema statements run in. The engine wraps the source in its own paged query. For a ledger_users table with a where, a page after the first looks like this on Postgres:

SELECT * FROM (SELECT * FROM ledger_users WHERE status <> 'deleted') owlie_src
WHERE id > :owlie_after ORDER BY id LIMIT :owlie_page_size

SQL Server pages with OFFSET 0 ROWS FETCH NEXT ... ROWS ONLY, and Oracle with FETCH FIRST ... ROWS ONLY. So a selectStatement must work as a derived table: give every column a name, and leave out ORDER BY, since the engine orders by orderKey and SQL Server refuses ORDER BY inside a derived table. The same wrapping serves looking up one record by its identifier.

With updatedAt, a full Sync records the newest value it read. The next incremental run reads the rows whose updatedAt is at or after that marker, so a row changed during the last run is read again rather than missed.

A column whose name needs quotes in your database cannot be named in the definition. Rename it with an alias in a selectStatement, such as SELECT "UserId" AS user_id, ....

The schema statements run in

On Postgres and Oracle, the install's defaultSchema is the schema every statement runs in. The gateway sets it on each database connection it opens, before the connection runs anything, so a table named without a schema resolves there everywhere: in the engine's own reads of sync.table, and in every statement you write (a selectStatement, a Grant kind's select, add and remove, the write statements, createdId and validationQuery). A table named with its schema, such as audit.ledger_users, is always that table. So a create that writes ledger_users and the engine's read back of the new row find the same table.

On Postgres, the schema is the connection's whole search_path: public is not searched after it. A table missing from the schema fails as not found, rather than reading or writing a table of the same name in public. Built-in functions and types always resolve. Name an extension's functions and types that live in public with their schema, such as public.crypt(...). A schema that does not exist fails the connection with an error that names it and the database.
On Oracle, it is the session's current schema. A schema that does not exist fails the connection. The user Owlie connects as still needs privileges on the schema's tables.
The name is a plain identifier: letters, digits, _ and $, not starting with a digit, up to 63 characters on Postgres and 128 on Oracle. It means what the same name means unquoted in a statement, so Ledger and ledger are the same schema. A schema whose name needs quotes cannot be set here.

On Postgres, the user Owlie connects as needs USAGE on the schema: without it the connection fails with an error that says so. Because public is not searched, also check these:

An extension's operators live in the schema the extension was installed in. With citext installed in public, = on a citext column silently compares as plain, case-sensitive text. Install the extension in the connector's schema, or name the operator with its schema, such as email OPERATOR(public.=) :email.
A trigger or function that runs as part of a statement resolves the names in its body with the connection's search_path, unless it sets its own. Give it one with ALTER FUNCTION ... SET search_path = ..., or name the tables in its body with their schema.
A connection pooler in transaction or statement mode, such as PgBouncer, does not keep a connection's settings between transactions, so a later statement can run without the schema. Connect to the database directly, or through a pooler in session mode.

Validate the statements after you set it (Validate statements): a table the schema lacks fails there. Left empty, statements resolve a table named without a schema the way the database does for the user Owlie connects as: that user's default schema, or on Postgres its search_path. When the schema differs per environment, set defaultSchema in each environment's install.

MySQL and SQL Server connectors have no defaultSchema. On MySQL a schema is a database, so set database. On SQL Server the default schema belongs to the database user: qualify table names, such as dbo.ledger_users, or set the default schema of the user Owlie connects as.

Install-editable values

Three values may differ per install: pageSize, and each entity's sync.table and sync.where. They decide which rows sync, not the shape of a record. Everything else, including the identifier, the columns, the order key and every statement, is fixed, because the engine checks statement text when it reads the definition and derives each entity's record schema from its columns.

To make one editable, declare an install field with a default (the seed) and write { "$oxl": "config.<name>" } in its place:

{
  "config": {
    "properties": {
      "userFilter": {
        "type": "string",
        "title": "Which users sync",
        "description": "A condition on ledger_users, without WHERE.",
        "default": "status <> 'deleted'"
      }
    }
  },
  "entities": {
    "user": {
      "sync": { "table": "ledger_users", "where": { "$oxl": "config.userFilter" }, "orderKey": "id" }
    }
  }
}

An install-editable where or table is SQL text that the integration's admins write, and it runs inside the engine's reads as the database user the install connects with. It passes the same checks as a condition in the definition (no ;, no comments, no placeholders), but a condition can still read any table that user can read, for example through a subquery. Give that user only the rights the connector needs (The username and password).

In the Connector Builder, the Install values tab has a switch for Page size and for each entity's table and filter. How installs receive their copy, and what happens when you change a seed later, is the same for every kind: see What an install sees and When you change a seed later.

Next: Statements and Grant kinds

What a SQL connector manages​

It runs on a private-network gateway​

Create the connector​

In the Connector Builder​

Through the API​

The definition​

Entities​

Columns​

Sync​

The schema statements run in​

Install-editable values​