ObjectStackObjectStack

External datasources — query without ETL

Declare an external database as a datasource and query its tables as ObjectStack objects — visible, auto-connected, and queryable with zero app code.

ObjectStack can treat a mature external database — one it does not own — as a read-only (or, with explicit opt-in, writable) datasource, and expose its tables as normal objects. This is federation: the data stays in the remote database; ObjectStack queries it live through the same ObjectQL / REST surface as native objects.

The headline guarantee: declare an external datasource and it is visible, auto-connected, validated at boot, and queryable — with no application code.

This guide is about reading/writing the live remote tables (federation). To copy rows into ObjectStack-owned tables instead, see seed data / data sync. For the plain multi-datasource routing of managed databases ObjectStack owns, see Database Drivers. When the remote is an OData v4 service (SAP S/4HANA, Dynamics 365), bind it through the shipped OData driver: OData datasources. When it is neither — a REST API, a planning engine, a device gateway — serve the object from your own plugin driver instead: Virtual objects.

1. Declare the datasource

Use defineDatasource with schemaMode: 'external'. The external block carries the federation policy (write gate, boot validation, credentials).

import { defineDatasource } from '@objectstack/spec/data';

export const Warehouse = defineDatasource({
  name: 'warehouse',
  label: 'Analytics Warehouse (Postgres)',
  driver: 'postgres',
  schemaMode: 'external',          // ObjectStack never runs DDL here
  config: { host: 'db.internal', port: 5432, database: 'analytics', username: 'readonly' },
  external: {
    allowWrites: false,                 // read-only (the default)
    credentialsRef: 'sys_secret:9f2c…', // opaque handle minted by the secret store
    validation: { onMismatch: 'fail', checkOnBoot: true },
  },
  active: true,
});

Register it on the stack (array or name-keyed map both work):

export default defineStack({
  datasources: [Warehouse],
  // ...
});

schemaMode:

ModeMeaning
managed (default)ObjectStack owns the schema — DDL + migrations allowed.
externalA mature external DB — DDL forbidden; a schema mismatch fails boot.
validate-onlyLike external, but a mismatch warns instead of failing.

2. Bind objects to the remote tables

A federated object sets datasource to the external datasource and declares its remote binding in external. When the remote table or column names differ from your object/field names, map them with external.remoteName / external.remoteSchema and external.columnMap.

export const Customer = ObjectSchema.create({
  name: 'ext_customer',
  sharingModel: 'private',
  datasource: 'warehouse',
  external: {
    remoteName: 'customers',       // remote TABLE name (object name may differ)
    // remoteSchema: 'public',     // optional schema/namespace (pg/mysql)
    // columnMap: { cust_region: 'region' }, // remoteColumn → localField
  },
  fields: {
    id: { type: 'text' },
    name: { type: 'text' },
    region: { type: 'text' },
  },
});

That's it. GET /api/v1/data/ext_customer now returns live rows from the remote customers table; filters (?region=EU) push down to the remote query.

private keys reads to the row's owner, and only an owner_id declared in fields, or named by an external.columnMap entry, counts as one. Without it a member reads no row, while a holder of View All (viewAllRecords) still reads them all. For a table everyone may read, choose sharingModel: 'public_read'.

The object's own datasource must name a datasource declared schemaMode: 'external' or 'validate-only' (ADR-0015 §4.2). An external binding on 'default' (also the value when datasource is omitted) is refused by ObjectSchema.create(), which sees only the object. A binding on either 'default' or a managed datasource is refused by defineStack and composeStacks (for a datasource the stack, or another input of the composition, declares), and at runtime by PUT /api/v1/meta/object/<name> with 422 INVALID_METADATA. The same save door refuses PUT /api/v1/meta/datasource/<name> that would make a datasource managed while objects bind it with external: change the datasource to external before binding objects, and remove external from the objects before making it managed.

field.columnName no longer exists. It was removed from FieldSchema in the 16.x line (#2377, ADR-0049) — the SQL driver always used the field key as the physical column, so a custom column name was silently ignored — and the ADR-0062 D7 lint that rejected it on external objects was removed with it. external.columnMap (remoteColumn → localField) is the single, authoritative column mapping for a federated object.

3. Auto-connect — no onEnable needed

At boot the runtime builds a live driver for the datasource, connects it, and registers its federated objects' read metadata — automatically. You do not need an onEnable hook or ctx.drivers.register(...).

A declared datasource auto-connects when it is meaningfully addressed:

  1. it is external (schemaMode !== 'managed'), or
  2. an object explicitly binds to it via object.datasource === <name>, or
  3. it sets autoConnect: true, or
  4. a datasourceMapping rule routes at least one object to it.

A managed datasource that nothing routes to stays metadata-only — visible in Setup, but not connected. Use autoConnect: true to opt such a datasource into a live connection at boot.

A mapping rule is routing, not a hint. If a datasourceMapping rule routes an object to a datasource that cannot be connected, the boot fails with the connect error, and a query against that object throws rather than resolving the default store. Before v17 it fell through silently: the app booted clean, /ready answered 200, and the object's rows were written to the default database instead of the one it declared. If you want a declared datasource that routes nothing, remove the mapping rule rather than relying on the fall-through.

Escape hatch. An onEnable hook calling ctx.drivers.register(driver) is still supported for advanced cases — e.g. a driver built dynamically at runtime from external configuration. Auto-connect is idempotent with it (whichever registers the datasource name first wins), so the two never conflict.

A connected external datasource is also visible in Setup → Integrations → Datasources (stamped origin: code, read-only in the UI) and via GET /api/v1/datasources and GET /api/v1/meta/datasource, where an admin can run the "Sync objects" wizard.

A datasource created in Setup (POST /api/v1/datasources) is a datasource item of the environment's metadata. Under the group and isolated tenancy postures only the platform operator creates, changes or removes one, or migrates its credential; any other caller is refused 403, naming the posture, whatever it holds, as at the /meta doors (tenancy modes).

When auto-connect fails

An object that binds explicitly via datasource: '…' has no fallback — it never falls through to the default driver, so an unconnected datasource means every read and write of that object fails. The boot therefore refuses to start when a datasource in that position cannot be connected (#3758), rather than leaving a server that looks healthy and errors only on the affected pages:

GateConnect fails at boot
external with validation.onMismatch: 'fail'refuses the boot
objects bind explicitly via object.datasourcerefuses the boot — the error names them
autoConnect: true with nothing boundwarning; left unconnected

This covers every reason a connect can fail — unreachable database, unresolvable credentialsRef, unsupported driver — because the bound objects are equally dead in each case. Every gated datasource is attempted before the boot aborts, so one failed start reports all the misconfigured ones.

OS_ALLOW_DRIVER_CONNECT_FAILURE=1 boots anyway — the same flag as the engine-level driver-connect guard — in an explicitly degraded state announced by a DEGRADED BOOT banner. The datasource stays unconnected for the process lifetime: nothing re-runs the connect, so queries against its objects keep failing until the server is restarted. The one exception is a plugin-driver datasource whose connector instance could not lend, which recovers on its own once the instance does (Borrowing a connector instance's credential). Do not set it in production.

A package installed into a running runtime (os package install, which calls POST /api/v1/marketplace/install-local) gets the same treatment at install time and at every restart: its declared datasources are registered as origin: code, read-only, and auto-connected by the same gates. There is no boot left to refuse, so a datasource that cannot be connected is installed with its failed status instead, and reads of its bound objects answer 503 ERR_DATASOURCE_UNAVAILABLE. The install answer lists each datasource's verdict under datasources. A package declaring a datasource name the runtime already holds is refused 409.

Seeing the state of a datasource

Every connect attempt's verdict is retained, so a datasource that is down no longer has to be diagnosed by restarting the server and re-reading boot logs (#3827).

Setup → Integrations → Datasources and GET /api/v1/datasources report a status per datasource:

statusMeaning
okA live driver is registered and routable.
errorA connect was attempted and failed. statusReason carries the cause.
blockedThe host's connect policy refused it — a decision, not a fault. It will not clear on its own.
unvalidatedNo connect attempted: a managed datasource left metadata-only by the gate above, or a runtime row nobody has tested.

statusReason is operator-facing and may name hosts, ports, or internal plans — this surface is already admin-gated.

Note that checkDriversHealth() (and therefore /ready) cannot see these: a datasource that never connected was never registered as a driver, so it is absent from the health probe rather than reported unhealthy. Readiness is deliberately not gated on them — an optional datasource being down must not pull an otherwise-working replica out of the load balancer.

What a query against an unusable datasource says

An object bound to a datasource with no live driver fails with ERR_DATASOURCE_UNAVAILABLE (HTTP 503), and the message says which situation it is (#3828): refused by policy, or a connect that failed. A failed connect is recorded the same way whether it failed at startup under OS_ALLOW_DRIVER_CONNECT_FAILURE, at a package install or in Setup, so the message names no origin; it says a restart re-runs the connect only while that flag is set, and then only for a datasource declared in code. A datasource name that was never declared still gets the original is not registered — that one really is an authoring bug, and there is nothing to add.

The error never carries the underlying cause: connect errors routinely contain hosts, ports and DSNs, and a policy's reason is written for operators. Both stay in the logs and the admin list. A host that wants to tell tenants something specific sets publicReason on its connect decision — opt-in, and the only string that reaches an end user:

const policy: DatasourceConnectPolicy = {
  canConnect: (ds) =>
    allowedFor(tenant, ds)
      ? { allow: true }
      : {
          allow: false,
          // operator-facing: logs + the Setup datasource list only
          reason: `egress allow-list miss for ${ds.name} (${tenant.id}, plan=${tenant.plan})`,
          // tenant-facing: appended to the query-time error
          publicReason: 'External datasources require the Scale plan.',
        },
};

4. Credentials

Never inline a password. Put a reference in external.credentialsRef and store the secret in the secret store (the same SecretBinder / ICryptoProvider the runtime-admin "Add Datasource" wizard uses). The credential is resolved to cleartext at connect, before the driver is built. A datasource defined in code reads an environment variable instead; see A datasource defined in code.

The shipped binder encrypts the cleartext into a sys_secret row and mints an opaque handle — sys_secret:<id> — as the credentialsRef; that is the only form it resolves, so a hand-written path like secret:warehouse/password will not dereference. A host that wants a different scheme (a vault path, say) injects its own resolver via new DatasourceAdminServicePlugin({ secrets }).

Resolution is fail-closed: if a credentialsRef is declared but no secret store is configured, or the secret cannot be resolved/decrypted, the datasource is left unconnected with a clear error — never connected without the credential. An unresolvable credential is a connect failure like any other, so it fails the boot for the datasources listed under When auto-connect fails and degrades with a warning otherwise. (Credential-less drivers such as SQLite simply have no credentialsRef.)

A datasource defined in code: the environment variable

The handle above is minted by the Setup → Datasources form, which cannot edit a datasource defined in code (defineDatasource, or a *.datasource.ts file in a package). A code author leaves the credential out of the declaration entirely, and the runtime reads it at connect from an environment variable whose name the platform derives from the datasource name:

Datasource nameVariable read at connect
warehouseOS_DATASOURCE_WAREHOUSE_PASSWORD
crm_replicaOS_DATASOURCE_CRM_REPLICA_PASSWORD

The name is upper-cased and every character outside A-Z0-9 becomes _. The value is handed to the driver as the connection's secret: the password for postgres, mysql and mongodb, the authToken for turso. It is never logged, stored or answered.

import { defineDatasource } from '@objectstack/spec/data';

export const Warehouse = defineDatasource({
  name: 'warehouse',
  driver: 'postgres',
  // No password here: it is read from OS_DATASOURCE_WAREHOUSE_PASSWORD at connect.
  config: { host: 'db.internal', database: 'warehouse', username: 'svc_app' },
});
OS_DATASOURCE_WAREHOUSE_PASSWORD='…' os start

What the slot does and does not do:

  • It is a host switch, OS_DATASOURCE_ENV_CREDENTIALS, which takes on or off. os serve, os start and os dev turn it on unless you set it yourself. Every other composition (a host that builds its own kernel, a cloud runtime) leaves it off, so the variable is never read there. Any other value is refused at boot.
  • It only serves datasources the host registers from code. That means the ones in the booted artifact and in packages installed locally. A datasource created through Setup or the API under the same name never reads the variable. Nor does the default datasource, which keeps OS_DATABASE_URL.
  • A declared credential wins. With external.credentialsRef or external.connector declared, the variable is not consulted. If that reference fails to resolve, the datasource stays unconnected (fail-closed, as above) and does not fall back to the environment.
  • An unset variable leaves the connect as it was. The driver connects without a secret; if the database refuses, the datasource is unconnected and its reason names the variable to set.
  • Two datasources that derive one variable are refused. Valid datasource names are lower-case, so this cannot happen between two declarations that pass the schema; the guard covers a host that hands connect() other names.
  • A plugin driver that resolves its credential per request (the OData driver resolves credentialsRef that way) does not read this slot: it feeds only the secret a driver receives when its connection is built.

Who can read the variable. The derived name is the only key, so any code datasource the host registers under that name reads it. Under os serve that includes every package installed into the environment: a package can declare a datasource of any free name and so read the matching OS_DATASOURCE_<NAME>_PASSWORD. Install only packages you trust with the credentials in that process's environment. On a host where several tenants' artifacts share one process, a process-wide variable would be readable by every tenant artifact that declares the name. That is why the switch is off outside the CLI host; turn it on there only when one operator owns every artifact the process loads.

Borrowing a connector instance's credential

An API-backed datasource, one served by a plugin driver such as the OData driver, has a second channel. When a connector instance already authorizes the vendor, name it in external.connector and the datasource borrows that instance's credential (ADR-0015 amendment D7). The credential is set once, where the connector's own is set (What credentialRef resolves through), and it serves both the connector's flow actions and the datasource's records.

import { defineStack } from '@objectstack/spec';

export default defineStack({
  manifest: { id: 'com.example.sales', name: 'Sales', version: '1.0.0', type: 'app' },
  connectors: [
    {
      name: 'erp_api',
      label: 'ERP API',
      type: 'api',
      provider: 'rest',
      providerConfig: { baseUrl: 'https://erp.example.com' },
      auth: { type: 'bearer', credentialRef: 'ERP_API_TOKEN' },
    },
  ],
  datasources: [
    {
      name: 'erp',
      driver: 'erp',                  // the OData plugin's name, i.e. the `driver.erp` service
      schemaMode: 'external',
      config: {},
      external: {
        connector: 'erp_api',         // no credentialsRef: exactly one channel
        validation: { onMismatch: 'warn', checkOnBoot: false },
      },
    },
  ],
});

The instance's auth decides what is sent: a bearer token, basic auth, or an API key in its header or query parameter. The rules:

  • One channel. connector beside a credentialsRef is refused. Keep the one that should supply the credential.
  • API-backed datasources only. connector is refused on a built-in driver (postgres, mysql, mongodb and the rest), which takes a database credential: bind that with credentialsRef. It is refused on a managed datasource too, since an API's schema is never ObjectStack's to manage: set schemaMode: 'external' (or 'validate-only').
  • An instance that can lend. The name must match a connectors entry of the stack that has a provider and an auth other than none. A catalog descriptor (no provider) or an instance with no auth is refused when the stack is validated, and a datasource created or edited under Setup → Integrations → Datasources is judged the same way when it is saved.
  • Fail-closed at connect. An instance that cannot lend when the datasource connects (not declared, disabled with enabled: false, no auth), or a stack with no automation service (@objectstack/service-automation is what lends the credential), leaves the datasource unconnected with the reason, the same connect failure as an unresolvable credentialsRef.
  • Recovery without a restart. The connect judges the instance once, but the plugin driver stays registered and borrows again on each request. So when that failure did not stop the boot (no gate in When auto-connect fails refuses it, or OS_ALLOW_DRIVER_CONNECT_FAILURE is set), the datasource shows error in Setup → Datasources and its requests answer 503 only until the instance is repaired. The first borrow the lender grants for that instance after the repair, which the next request makes, clears the failure, and the datasource reads ok again with no restart. A grant for a different instance leaves it standing. A datasource reconnected or edited in between records a result of its own instead. This needs a lender that reports its grants, as @objectstack/service-automation does; with a lender that does not, or none, the failure stands until a restart. The DEGRADED BOOT banner says which applies: it names the instance whose next granted borrow clears the failure, or says the server has to be restarted.
  • A credential not set yet does not fail the connect. Each request is answered 503 ERR_DATASOURCE_UNAVAILABLE, with the reference named in the server log, until an administrator sets it; the next request after that reads with it, with no restart.
  • Rotation. A credential set again in the connector credential store reaches the next request. One the upstream refuses with a 401 is obtained afresh once and the request retried, which also picks up a rotated environment variable; a second 401 answers 503.

5. Writes (double opt-in)

Federation is read-only by default. To allow writes, both the datasource and the object must opt in:

defineDatasource({ /* ... */ external: { allowWrites: true } });   // datasource gate
ObjectSchema.create({ /* ... */ datasource: 'warehouse', external: { remoteName: 'orders', writable: true } }); // object gate

With either gate off, insert/update/delete on the federated object is rejected.

The /* ... */ above still has to carry a sharingModel — a custom (non-sys_) object that declares no OWD is an os validate error (security-owd-unset), federated or not. See the full object above.

This gate is federation-only — it does nothing on a managed datasource. allowWrites answers who owns this external database, not is this connection read-only. You cannot even declare it on a local database — the parse rejects an external block whose schemaMode is managed — and ObjectQLEngine.assertWriteAllowed returns early for managed (or an absent schemaMode) before it reads the flag at all.

A managed datasource has no platform read-only gate, deliberately: read-only for a database ObjectStack owns is a database account privilege (GRANT SELECT). See Read-only: grant it at the database, not in metadata for why an application-layer flag is the wrong boundary (#4584).

6. Analytics over external objects

Dashboards and reports over a federated object aggregate against the correct remote table/columns: the analytics layer routes such queries through the driver's physical-table resolution (honoring remoteName / remoteSchema) rather than the object name, so they never hit the wrong table.

7. Oracle Database

Oracle is a federation-only dialect. ObjectStack reads an existing Oracle schema live and writes to it when both write gates are on, but it never owns one. A managed Oracle datasource, including one that leaves schemaMode at its default, is refused when the stack is defined. Every DDL operation is refused by the driver too.

import { defineDatasource } from '@objectstack/spec/data';

export const Erp = defineDatasource({
  name: 'erp',
  label: 'ERP (Oracle)',
  driver: 'oracle',                 // alias: 'oracledb'
  schemaMode: 'external',           // required: 'external' or 'validate-only'
  config: { host: 'erp-db.internal', port: 1521, serviceName: 'ERPPDB1', username: 'erp_ro' },
  external: {
    allowWrites: false,
    credentialsRef: 'sys_secret:4b1e…', // the password is the bound secret, never inline
  },
});

Requirements

  • Oracle Database 12c Release 1 or later. Pages are read with OFFSET … ROWS FETCH NEXT … ROWS ONLY, which 12c introduced. Verified live on Oracle Database 23ai Free.
  • The oracledb package. It is an optional install of @objectstack/driver-sql (npm install oracledb). It runs in Thin mode, which is pure JavaScript and needs no Oracle Client libraries. Without the package, the datasource's connect fails with an error that names that install.

The config block

KeyMeaning
connectStringAn Easy Connect string (host:port/service_name), a tcps://… string, or a TNS descriptor. Wins over the keys below when set.
host / port / serviceNameComposed into host:port/serviceName when there is no connectString. port defaults to 1521.
usernameThe database user. Its schema is the one the Tables tab lists.

password and url are refused by name. The password is the bound secret, and an Oracle connect string is not a URL.

TLS is chosen by the connect string: connectString: 'tcps://erp-db.internal:2484/ERPPDB1'. The server is verified against the host's trusted certificate authorities. If the database's certificate comes from a private certificate authority, put that CA in a PEM wallet (a directory holding ewallet.pem) on the host and name the directory in the connect string, which reaches the driver unchanged: 'tcps://erp-db.internal:2484/ERPPDB1?wallet_location=/etc/oracle/wallet', or (SECURITY=(MY_WALLET_DIRECTORY=/etc/oracle/wallet)) inside a descriptor. This route follows node-oracledb's Thin-mode connect-string handling and has not been tested against a live TLS listener here. A datasource ssl block and ca, cert, key or rejectUnauthorized config keys are refused rather than silently dropped, because the driver reads TLS only from the connect string.

Binding tables. Oracle stores an unquoted identifier in UPPER case, so an imported table's columns are CUST_NO, FULL_NAME, and so on. The Tables tab and import_object map each one to a lower-case field through external.columnMap. The driver quotes the catalog spelling back in every statement. The Tables tab lists the tables of the connecting user's current schema. Reach another owner's table with an object whose remoteName is OWNER.TABLE.

How values read and compare

Remote columnFieldNotes
BOOLEAN (23ai)booleanExact.
NUMBER(1) flagbooleanJudged lossy by the type matrix: it is an integer column. It reads as true / false, and a write sends 1 / 0.
CHAR(1) flag ('Y' / 'N')textJudged incompatible with a boolean field, so bind it as text. A boolean binding would read every non-NULL letter as true.
DATEdatetimeAn Oracle DATE carries a time of day. A date field over it is judged lossy: the time is dropped.
TIMESTAMP WITH TIME ZONEdatetimeCompared and returned as UTC instants.
CLOBtextarea
RAW(16)textRead as a lower-case UUID string, and filtered and written by that string (see below). A lookup, master_detail or user field over it is exact. A text field is judged lossy, so a draft marks it for review: $contains and the other pattern operators do not match it as the string it reads.

The driver pins every session to UTC. Thin mode decodes DATE and TIMESTAMP values with the server process's local clock, and the driver repairs that on read. The repair is exact except during the hour a daylight-saving spring-forward skips in the process's own time zone. Run the server with TZ=UTC and there is no such hour.

What Oracle answers differently

  • '' is NULL in Oracle. A filter that compares against an empty string (= '', $ne: '', $in: [''], a range bound of '') is refused with INVALID_FILTER, because Oracle would answer it as a comparison with NULL rather than as written. Use $null / $empty instead. $empty on a text column matches exactly the NULL rows there.
  • Upsert is refused with NOT_IMPLEMENTED (HTTP 501). The driver emits no MERGE. Use create and update.
  • JSON-array membership ($contains on a multi-value field) is refused. There is no Oracle construct modelled for it.
  • A RAW(16) column is filtered and written by its UUID string. The driver reads which of an object's columns are RAW(16) from the catalog, and binds a comparison value (=, $ne, $in, the range operators), a written value and a record's key for those columns as HEXTORAW(?). That covers a SYS_GUID() key and a lookup keyed by one. A UUID is accepted with hyphens (the form a read returns) or as its 32 hex digits, in either case. Any other value is refused before the statement is sent: in a filter with INVALID_FILTER, in a write with VALIDATION_ERROR. So is a create that gives no id on an object keyed by such a column, because the id the driver would mint is not a UUID. A VARCHAR2 column that holds UUIDs is still compared as text.
  • Pattern operators on a RAW(16) column ($contains, $startsWith, $like and the rest) are sent as text patterns. Oracle compares them with the column's hex text: 32 upper-case digits, no hyphens. A fragment of the UUID as it reads back matches only when it has no hyphen and the operator ignores case.
  • A NUMBER beyond 2^53 is read as a JavaScript number and loses precision.
  • Paged reads are stable when the object's columnMap maps a column to id. That column breaks ties after the caller's own sort. Without a mapped key, a paged read has no unique order, and the server logs a warning once per object.

8. SQL Server (federation only)

Microsoft SQL Server is a federation-only driver: point an external (or validate-only) datasource at it and its tables become objects, read live — and, with the double opt-in above, written live. The usual reason is an older line-of-business system (an ERP, a MES, a HIS) whose database is federated in place rather than migrated. It is served by @objectstack/driver-sql over knex's mssql client, so install its optional peer tedious next to it.

import { defineDatasource } from '@objectstack/spec/data';

export const LegacyErp = defineDatasource({
  name: 'legacy_erp',
  label: 'Legacy ERP (SQL Server)',
  driver: 'mssql',                     // aliases: 'sqlserver', 'tedious'
  schemaMode: 'external',              // required — see below
  config: {
    host: 'erp-db.internal',
    port: 1433,                        // ignored when instanceName is set
    database: 'ERP',
    username: 'objectstack_ro',
    // instanceName: 'SQLEXPRESS',     // a named instance, via SQL Server Browser
    ssl: true,                         // tedious `encrypt`; required by Azure SQL
  },
  external: {
    credentialsRef: 'sys_secret:7c1e…', // the password — never written inline
    validation: { onMismatch: 'fail', checkOnBoot: true },
  },
  active: true,
});

What is different from the other SQL drivers:

  • Federation only. The platform never builds its own schema on SQL Server, so a managed datasource on this driver is refused when the stack is parsed, with a message naming the scope, and no boot host offers it as the platform database. If you need ObjectStack's own tables on SQL Server, that is a separate feature request.
  • Discrete fields, no URL. tedious reads no connection-string grammar, so the config takes host / port / database / username / instanceName and refuses a url / connectionString. The password is refused inline: bind it through the connection form or external.credentialsRef.
  • TLS. config.ssl is tedious's encrypt. For an on-premises server with a self-signed certificate, set the datasource-level ssl: { enabled: true, rejectUnauthorized: false } (tedious's encrypt plus trustServerCertificate); a CA goes in the same block. Write enabled: true: the block's enabled defaults to false, and a block without it turns encryption off.
  • Schemas. A SQL Server database is usually several schemas. The Studio Tables tab, list_remote_tables and import_object list every user table as schema.table (dbo.Orders, Sales.Customers) with its primary key and foreign keys, and an imported object gets external.remoteSchema / remoteName from it. A bound object without remoteSchema resolves in the login's default schema.
  • Types. nvarchar / nchar / ntext / xml read as text (nvarchar(max) included), bit as boolean, datetime2 / datetimeoffset / smalldatetime as datetime, uniqueidentifier as uuid, money / decimal as decimal. SQL Server's timestamp is rowversion — a binary row counter, not a date. A json, tags or multiselect field over an nvarchar(max) column is accepted as a lossy match (the driver keeps it as JSON text); JSON-array membership ($contains) and $empty on a multi-value field are refused on SQL Server.
  • GUIDs. A uniqueidentifier reads back in the lower-case form (3f2504e0-4f89-…), so a GUID written as a string reads back as that string, and an equality or $in filter matches it in either case. The text operators ($contains, $startsWith, $endsWith, $notContains, $like and their case-insensitive siblings) match a uniqueidentifier column as that same lower-case text, LOWER(CONVERT(nvarchar(36), col)), so a fragment copied from a read finds its row. The case-exact operators follow that read form, so $startsWith: '3f2504e0' matches and $startsWith: '3F2504E0' does not; $icontains and $ilike match either case. The driver learns which columns are GUIDs from the remote catalog (INFORMATION_SCHEMA.COLUMNS) the first time a read uses a text operator on the object, never from the shape of the value you filter by.
  • Text matching follows the platform's contract, not the database's collation: $contains / $startsWith / $endsWith / $like are case-exact even on SQL Server's case-insensitive default collations, and $icontains folds A–Z only. This uses TRANSLATE, so the floor is SQL Server 2017.
  • Paging. OFFSET … FETCH needs an ORDER BY on SQL Server. An unsorted page is ordered by the column the object's columnMap maps to id when there is one, else by the table's own single-column primary key, read from the catalog; a table with neither (a heap, a composite key) pages without a stable order, and the driver logs that once per object.
  • Writes use INSERT … OUTPUT inserted.* (routed through a temp table, so a table with triggers accepts it too), and a created record comes back with the values SQL Server filled in. The remote table needs a column the object reads as id, and the platform supplies its value: an IDENTITY key cannot be inserted into, so keep a table keyed that way read-only.
  • Not on SQL Server: $contains / $empty on a multi-value (JSON-array) field is refused rather than approximated, and date buckets in reports are computed by the engine rather than pushed down.

Multi-tenant hosts

A self-hosted single-environment runtime connects external datasources out of the box. A multi-tenant host can bind a stricter connect policy (egress allow-list / per-tenant quota) that is consulted before any connection is opened — the same single connect path, no fork. This is a host-composition concern; the open-core default allows all connects (subject to the gating above).

See also

On this page