SQL connectors through the API¶

The concept behind tools is in Tools.

A SQL connector gives the model one fixed query against a database of your organisation. You write the query when you register the connector; the model only fills in values. Each connector becomes one tool, sql__<name>. Core connects to your database server itself, over TLS, with a user name and password you store once. Postgres, MySQL, SQL Server and Oracle are supported.

You set up a SQL connector through the API, as below, or in the console under Tools > SQL connectors. There you add one on a page of its own, read a connector's connection, statement and versions, edit it into a new version, delete it, and press Test to have Core log in and run SELECT 1. The password is never shown back; leave it empty when you edit to keep the stored one. A connector installed from a publication cannot be edited or deleted there. Your key needs the tooling capability, and the database server's host must be on your organisation's allowed hosts.

Register a connector¶

curl -s -X POST "$CORE_URL/sql_connectors" \
    -H "Authorization: Bearer $CORE_KEY" -H "Content-Type: application/json" \
    -d '{"name": "orders",
         "description": "Looks up the recent orders of one customer.",
         "dialect": "postgres",
         "host": "db.your-app.example",
         "database": "shop",
         "statement": "SELECT id, status, total FROM {$schema}.orders WHERE customer_id = {customer} ORDER BY created_at DESC",
         "parameters": {"type": "object",
                        "properties": {"customer": {"type": "string", "description": "The customer number"}},
                        "required": ["customer"]},
         "install_values": {"schema": {"type": "identifier", "value": "public"}},
         "max_rows": 20,
         "auth": {"type": "basic", "username": "core_reader", "password": "..."}}'

The answer is 201 with the connector, version 1. Offer it with {"type": "tool", "name": "sql__orders"} in tools[], or include it in a toolset.

The password is stored encrypted and never returned: GET answers the kind, the user name and has_secret. Give Core a database user that may do exactly what the query needs, and for a read tool a user that cannot write.

GET /sql_connectors lists your organisation's connectors, GET /sql_connectors/{id} reads one, PATCH changes one and DELETE removes one. A delete answers 409 naming the agents while an agent's latest version names the tool; remove it there first.

The query¶

The query is fixed; the model only chooses values. statement holds three kinds of placeholder:

Core hands every value to the driver as a bound parameter, never as SQL text. A value in an argument therefore cannot change the query, whatever it contains. That is why a placeholder may only stand where a value goes: after an operator, a comma, a bracket or a word such as WHERE, VALUES or LIMIT.

The one exception is an install value of type identifier: the name of a schema, a table or a column. Core writes it into the query, quoted by the dialect's own rule. Only you set such a name, never the model. In Oracle a quoted name keeps its case, so write it as the server stores it, usually in upper case.

An install value is an object {type, value, allowed, description} whose type is string, number, enum (with allowed) or identifier. Every {$name} needs a value and every value must appear in the query. An install value is not a secret; the password belongs in auth.

You declare call variables in call_variables as for an API connector, and your application sends their values in tool_variables on the request (Make a model call). The model never sees them. An optional variable with no value and no default binds as NULL; a required one the request leaves out answers 400 on tool_variables.<name>.

An optional argument the model leaves out also binds as NULL. When a required argument is missing, nothing runs and the model reads Tool arguments were missing a required value.

Core refuses at registration, with 422 on statement:

• more than one statement;
• a placeholder inside a string, a quoted name or a comment, or where a name goes;
• a placeholder that parameters, install_values or call_variables does not declare;
• an install value the query does not use. A call variable the query does not use is refused too, with 422 on call_variables; an argument in parameters the query does not use is allowed;
• CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE, COMMIT and ROLLBACK;
• anything whose reading depends on the server's settings: in Postgres and MySQL a backslash inside a string, and in MySQL a -- not followed by a space and the comment forms /*!, /*+ and /*M!.

Each dialect adds its own. SQL Server refuses a GO. Oracle refuses a semicolon at the end, a PL/SQL block (BEGIN, DECLARE), RETURNING and a bind variable of your own such as :name.

An argument that is a list is sent as JSON text in SQL Server and Oracle, since those drivers have no list parameter.

Reading and writing¶

With mode: "read" Core accepts only a query that starts with SELECT or WITH (in Postgres also VALUES or TABLE), with no INSERT, UPDATE, DELETE, MERGE or INTO. The query runs in a read-only transaction that is always rolled back. SQL Server has no read-only transaction: there the check on the query is what keeps a read tool from writing. It therefore also refuses EXEC, EXECUTE, OPENROWSET, OPENQUERY and OPENDATASOURCE in a read. Give such a connector a user that cannot write.

With mode: "write" the query may also start with one of these:

INTO is refused anywhere except in an INSERT, a REPLACE or a MERGE, since SELECT ... INTO creates a table and MySQL's INTO OUTFILE writes a file on the server. The query runs in one transaction that is committed. Core never retries a call. In SQL Server a write tool refuses dynamic SQL (EXEC (...), sp_executesql) and EXECUTE AS.

TLS¶

Core always talks to your database encrypted; there is no setting without TLS.

ca_pem is your own server's certificate authority, as PEM. Leave it out to check the certificate against the authorities the deployment trusts. A MySQL server that offers no TLS is refused before Core logs in.

What the model gets back¶

The answer is structured: {columns, rows, row_count, truncated}, with each row a list of values in the order of columns. The model reads it as compact JSON; a direct call answers it in structured.

{"columns": ["id", "status", "total"],
 "rows": [["A-1042", "shipped", "129.95"], ["A-1038", "open", "18.50"]],
 "row_count": 2, "truncated": false}

• A decimal is text, so no digit is lost; a time is ISO 8601; bytes are [binary].
• At most max_rows rows come back, and at most what fits in a tool result (32 KiB). truncated: true says rows were left out.
• A query that answers no rows, such as an UPDATE, gives the number of rows it changed in row_count.

When a call fails, the model reads one fixed sentence, never the server's error message, which can hold the values the query ran with. The turn goes on.

The audit trail and the turn's trace name the kind of failure and the server's error code (the SQLSTATE class or the error number), never the message itself.

Test a connector¶

POST /sql_connectors/{id}/test logs in and runs SELECT 1, under the same host rules as a call. Nothing is stored.

curl -s -X POST "$CORE_URL/sql_connectors/sqlc_01J.../test" \
    -H "Authorization: Bearer $CORE_KEY"

The answer is always 200:

{"object": "sql_connector_test", "ok": true, "code": null}

On a failure ok is false and code says why: egress_denied (the host or its address is not allowed), unreachable, auth_failed, timeout or refused.

Allowed hosts¶

Core reaches your database only when its host name is on your organisation's allowed hosts, the same list an API connector uses (Allowed destinations). The egress field on every read says per host whether it is allowed (allowed) and whether the deployment blocks it (blocked).

The deployment blocks an address in a private network, a loopback address and a name that exists only inside a network, such as localhost or a name ending in .internal. Your database server must therefore be reachable at an address Core may dial. When the server sends Core on to another host, as an Azure SQL gateway or an Oracle listener does, that host must be allowed too.

region says where the server is served, as for an API connector (Where the host is served).

Versions¶

A connector is versioned. A PATCH that changes what the tool does (the query, the arguments, the server, the mode, the limits, the install values, the call variables or background) makes a new version. A PATCH of only auth, region, labels or metadata makes none.

GET /sql_connectors/{id}/versions lists the versions, newest first (?order=asc for oldest first), and GET /sql_connectors/{id}/versions/{version} reads one. Pin a version on a request:

{ "type": "tool", "name": "sql__orders", "version": 2 }

Without version a turn gets the latest (Pin a connector version).

Background, labels and a picture¶

• Background. background decides whether the model may start the tool as a task, with the same fields as for an API connector (Run a tool in the background). It belongs to the version.
• Labels. labels is your organisation's own classification, which rules about people are written over (Labels on everything). For a request that names a person, the same rules over sql__<name> decide whether GET /sql_connectors lists the connector and whether it may be changed or deleted (Registrations).
• Picture. PUT /tools/sql__orders/cover puts a picture on the tool, which the console shows beside its name: PNG, JPEG or WebP as the raw body, at most 512 KiB unless your operator set another limit. DELETE removes it. The model never sees the picture.

Share in a publication¶

A SQL connector can go into a publication as a member of kind sql_connector. As for an API connector, say what happens to the login:

{"kind": "sql_connector", "id": "sqlc_01J...", "auth": "parameter", "auth_parameter": "database_login"}

Every install value becomes a parameter of the bundle, with your value as its default. Whoever installs it fills in their own schema name or value, and Core checks the query with it again at installation. A copy installed with auth: "none" cannot log in: a request that offers its tool answers 503 with The SQL connector's stored credential could not be read. Choose parameter when every installer should use a login of their own. A copy installed under the Hide contents term answers the query, the host, the database and the arguments as null; the tool works as usual. You cannot change a copy from a publication, its picture included: such a PUT or DELETE answers 409.

Errors at registration¶

Every route has a twin under /tenants/{tenant_id}/sql_connectors for an administrator working with an administrator token, with the same fields and answers.