Statements and Grant kinds

Building a connector, step 7 (SQL track, 2 of 3). Previous: Start a SQL connector.

A SQL connector writes only through statements you declare. An entity's statements create and change its rows, and a Grant kind's add and remove write one Grant. Each statement is your own text; the engine binds every value as a :name parameter.

Write statements

An entity's statements object is keyed by operation. Each statement is one INSERT, UPDATE, DELETE or MERGE, and may bind only the placeholders its operation supplies:

Key	May bind	Must bind
`create`	Any of the entity's columns, as `:<column>`	Nothing
`update`	Any of the entity's columns	The identifier column
`delete`	Only the identifier column	The identifier column
`enable`	Only the identifier column	The identifier column
`disable`	Only the identifier column	The identifier column
`createdId`	Any of the entity's columns, in a `SELECT`	Nothing

A placeholder the input does not carry binds as null. Text inside quotes is not a placeholder, and a Postgres :: cast is not one either. A column that create or update binds is writable; every other column is read-only to provisioning, and the identifier is always read-only. A placeholder must name a declared column, so declare every column a statement binds.

These are the user entity's statements in the application database example, whose ledger_users table also declares full_name and status columns:

{
  "statements": {
    "create": "INSERT INTO ledger_users (email, full_name, status, updated_at) VALUES (:email, :full_name, 'active', now()) RETURNING id",
    "update": "UPDATE ledger_users SET email = :email, full_name = :full_name, updated_at = now() WHERE id = CAST(:id AS integer)",
    "disable": "UPDATE ledger_users SET status = 'suspended', updated_at = now() WHERE id = CAST(:id AS integer) AND status <> 'deleted'",
    "enable": "UPDATE ledger_users SET status = 'active', updated_at = now() WHERE id = CAST(:id AS integer) AND status <> 'deleted'",
    "delete": "UPDATE ledger_users SET status = 'deleted', updated_at = now() WHERE id = CAST(:id AS integer)"
  }
}

The identifier always binds as text, so these statements cast :id to compare it with an integer column. Here delete is a soft delete, the way the application itself deletes a user.

What each operation does

Create runs in one transaction. The engine runs the statement, then finds the new row's identifier: from the row the statement returns (RETURNING on Postgres, OUTPUT INSERTED.id on SQL Server), else from the createdId query, else from the input. It reads the new row back through the entity's sync source, so the new row must pass the entity's where, or the create fails.
Update runs in one transaction. The engine reads the current row, binds every column from it with the input's values on top, runs the statement and reads the row back. A partial update therefore keeps the columns it does not change.
Delete, enable and disable each run in their own transaction. A statement that changed no row fails the operation as not found.

MySQL cannot return the new row from an INSERT, and neither can Oracle here, so give them a createdId query, run in the create's transaction:

Dialect	Finding the new row's identifier
Postgres	`... RETURNING id` in the `create` statement
SQL Server	`... OUTPUT INSERTED.id VALUES (...)` in the `create` statement
MySQL	`"createdId": "SELECT LAST_INSERT_ID() AS id"`
Oracle	A lookup by a unique column, such as `"createdId": "SELECT id FROM app_users WHERE email = :email"`

A RETURNING or OUTPUT clause must return the identifier column by its name. A createdId query's value is taken from the column named like the identifier, or else from its first column.

Account state

A SQL connector never reports an account as disabled. When Owlie looks up one account, a row it can read reads as active, and a row it cannot read, because it is missing or the sync where leaves it out, reads as deleted. Sync reads every row it lists as active too: the connector declares no rule that says otherwise. So after disable sets status = 'suspended', Owlie still sees an active account.

A soft delete that marks the row deleted works well with a where that leaves deleted rows out, as in the application database example. For a disabled state, an integration's admin adds a rule to the account's Sync job, under Derive State, in Account is active when, such as status == 'active'. A single lookup reports the state your code returns if you override user.fetchOne (Handler API).

Sync mode

A SQL connector declares no sync settings, so each install's account Sync job starts in Identity mode: Sync creates or updates an Owlie identity from every row it lists. That suits a table that is a source of people. For an application's own users, the integration's admin opens the account Sync job and, on its Behavior tab, sets Sync mode to Resource, which links rows to existing identities as their accounts instead. Derive State and its Account is active when rule appear in Resource and Hybrid mode only. Say in your connector's README.md which mode it expects.

When Owlie revokes an account, the connector's default is to disable it, or to delete it when the account has a delete statement and no disable statement.

What a new account gets

The engine creates an account with the values provisioning gives create, and binds null for every placeholder the input does not carry. The connector itself supplies no values: the provisioning profile it derives has no baseline attributes. Until an integration's admin maps a value to each column the create statement needs, a column declared NOT NULL, such as email in the example, makes every create fail with the database's error. Admins set these values on the integration's resource, on its Provisioning tab under Baseline attributes, usually from the identity, such as identity.email (Expressions). Tell them in your connector's README.md which columns need a value.

When a connector provisions

The connector offers provisioning when the account entity has a create statement or a Grant kind has add and remove. Statements for update, enable, disable or delete alone do not make a connector provision. A connector with any statement needs an account entity. Without a create statement it is Grants-only: accounts are created outside Owlie, and Owlie waits for Sync to find an account before it adds Grants.

Grant kinds

grant_kinds is keyed by Grant kind key, in lowercase letters, digits and _, starting with a letter:

Field	What it does
`source`	The entity that holds the Grant, usually the account entity.
`target`	The entity the Grant is on.
`select`	A `SELECT` (or `WITH`) that returns one row per Grant.
`sourceColumn`	The column of `select` that holds the source record's identifier.
`targetColumn`	The column of `select` that holds the target record's identifier.
`membership`	`true` when the source holds everything the target holds, as in group membership. The default is `false`.
`add`, `remove`	Optional, both or neither. With both, Owlie may write the Grant kind.

{
  "grant_kinds": {
    "has_role": {
      "source": "user",
      "target": "role",
      "select": "SELECT user_id, role_code FROM ledger_user_roles",
      "sourceColumn": "user_id",
      "targetColumn": "role_code",
      "add": "INSERT INTO ledger_user_roles (user_id, role_code) VALUES (CAST(:source AS integer), :target) ON CONFLICT DO NOTHING",
      "remove": "DELETE FROM ledger_user_roles WHERE user_id = CAST(:source AS integer) AND role_code = :target"
    }
  }
}

Sync discovers each Grant kind through its target: for each target record, it reads the sources that hold it, paged by sourceColumn.

SELECT user_id FROM (SELECT user_id, role_code FROM ledger_user_roles) owlie_rel
WHERE role_code = :owlie_target ORDER BY user_id LIMIT :owlie_page_size

Add and remove

add and remove bind exactly :source, the holder's identifier, and :target, the target's identifier, and nothing else. Both bind as text, so cast them where a column is not text.

The engine does not look either record up first. It runs the statement and reads its row count, and the row count is the only evidence it has:

Statement	Changed a row	Changed no row
`add`	Added	Already present
`remove`	Removed	Already absent

Any error fails the write. So:

Make add idempotent in SQL. Without that, adding a Grant that exists fails with the database's unique-key error, because the engine never treats an error as "already present". Forms that work:

Dialect	Idempotent add
Postgres	`INSERT INTO user_roles (user_id, role_code) VALUES (CAST(:source AS integer), :target) ON CONFLICT DO NOTHING`
SQL Server	`INSERT INTO user_roles (user_id, role_code) SELECT CAST(:source AS int), :target WHERE NOT EXISTS (SELECT 1 FROM user_roles WHERE user_id = CAST(:source AS int) AND role_code = :target)`
Oracle	`INSERT INTO user_roles (user_id, role_code) SELECT :source, :target FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM user_roles WHERE user_id = :source AND role_code = :target)`
MySQL	`INSERT IGNORE INTO user_roles (user_id, role_code) VALUES (:source, :target)`

MySQL's IGNORE also turns some other errors into warnings, which would then read as "already present". Make sure nothing else about the row can fail, such as a foreign key to a role that does not exist.

Make remove address the Grant row itself. A remove that matches no row reads as "already absent", so a WHERE with a wrong column, or a remove for a holder that no longer exists, reports the Grant gone. Test every remove on a Grant that exists, and check the row is gone.

The Grant write contract explains why only the write's own answer counts.

Statement safety

The only text the engine writes into a statement is the definition's own, checked when the definition is read:

Identifiers the engine writes itself (column names, identifier, orderKey, updatedAt, sourceColumn and targetColumn) must match ^[A-Za-z_][A-Za-z0-9_$]*$, and are written unquoted. A table may be schema.table.
The install's defaultSchema must match the same pattern. It never appears in a statement: the gateway sets it on the connection (The schema statements run in).
selectStatement, a Grant kind's select, createdId and validationQuery start with SELECT or WITH.
Those statements, and any where (an install's included), contain no ; and no comments (-- or /*).
selectStatement, select, validationQuery and where contain no :name placeholders: the engine binds its own values around them. createdId may bind the entity's columns.
Write statements are one INSERT, UPDATE, DELETE or MERGE, with no ; and no comments, and bind only the placeholders their operation supplies.

Every read runs in its own read-only transaction. SQL Server has no read-only transactions, so reads there run in an ordinary transaction. Every write runs in its own transaction, together with the reads that check it. The gateway adds its own limits on rows, bytes and time.

Next: Validate statements, TLS and credentials

Write statements​

What each operation does​

Account state​

Sync mode​

What a new account gets​

When a connector provisions​

Grant kinds​

Add and remove​

Statement safety​