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:
| Mode | Meaning |
|---|---|
managed (default) | ObjectStack owns the schema — DDL + migrations allowed. |
external | A mature external DB — DDL forbidden; a schema mismatch fails boot. |
validate-only | Like 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:
- it is external (
schemaMode !== 'managed'), or - an object explicitly binds to it via
object.datasource === <name>, or - it sets
autoConnect: true, or - a
datasourceMappingrule 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:
| Gate | Connect fails at boot |
|---|---|
external with validation.onMismatch: 'fail' | refuses the boot |
objects bind explicitly via object.datasource | refuses the boot — the error names them |
autoConnect: true with nothing bound | warning; 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:
status | Meaning |
|---|---|
ok | A live driver is registered and routable. |
error | A connect was attempted and failed. statusReason carries the cause. |
blocked | The host's connect policy refused it — a decision, not a fault. It will not clear on its own. |
unvalidated | No 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 name | Variable read at connect |
|---|---|
warehouse | OS_DATASOURCE_WAREHOUSE_PASSWORD |
crm_replica | OS_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 startWhat the slot does and does not do:
- It is a host switch,
OS_DATASOURCE_ENV_CREDENTIALS, which takesonoroff.os serve,os startandos devturn 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
defaultdatasource, which keepsOS_DATABASE_URL. - A declared credential wins. With
external.credentialsReforexternal.connectordeclared, 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
credentialsRefthat 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.
connectorbeside acredentialsRefis refused. Keep the one that should supply the credential. - API-backed datasources only.
connectoris refused on a built-in driver (postgres,mysql,mongodband the rest), which takes a database credential: bind that withcredentialsRef. It is refused on amanageddatasource too, since an API's schema is never ObjectStack's to manage: setschemaMode: 'external'(or'validate-only'). - An instance that can lend. The name must match a
connectorsentry of the stack that has aproviderand anauthother thannone. A catalog descriptor (noprovider) or an instance with noauthis 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, noauth), or a stack with no automation service (@objectstack/service-automationis what lends the credential), leaves the datasource unconnected with the reason, the same connect failure as an unresolvablecredentialsRef. - 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_FAILUREis set), the datasource showserrorin Setup → Datasources and its requests answer503only 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 readsokagain 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-automationdoes; with a lender that does not, or none, the failure stands until a restart. TheDEGRADED BOOTbanner 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
401is obtained afresh once and the request retried, which also picks up a rotated environment variable; a second401answers503.
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 gateWith 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
oracledbpackage. 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
| Key | Meaning |
|---|---|
connectString | An Easy Connect string (host:port/service_name), a tcps://… string, or a TNS descriptor. Wins over the keys below when set. |
host / port / serviceName | Composed into host:port/serviceName when there is no connectString. port defaults to 1521. |
username | The 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 column | Field | Notes |
|---|---|---|
BOOLEAN (23ai) | boolean | Exact. |
NUMBER(1) flag | boolean | Judged 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') | text | Judged incompatible with a boolean field, so bind it as text. A boolean binding would read every non-NULL letter as true. |
DATE | datetime | An Oracle DATE carries a time of day. A date field over it is judged lossy: the time is dropped. |
TIMESTAMP WITH TIME ZONE | datetime | Compared and returned as UTC instants. |
CLOB | textarea | |
RAW(16) | text | Read 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 withINVALID_FILTER, because Oracle would answer it as a comparison with NULL rather than as written. Use$null/$emptyinstead.$emptyon a text column matches exactly the NULL rows there.- Upsert is refused with
NOT_IMPLEMENTED(HTTP 501). The driver emits noMERGE. Use create and update. - JSON-array membership (
$containson 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 areRAW(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 asHEXTORAW(?). That covers aSYS_GUID()key and alookupkeyed 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 withINVALID_FILTER, in a write withVALIDATION_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. AVARCHAR2column that holds UUIDs is still compared as text. - Pattern operators on a
RAW(16)column ($contains,$startsWith,$likeand 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
NUMBERbeyond 2^53 is read as a JavaScript number and loses precision. - Paged reads are stable when the object's
columnMapmaps a column toid. 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
manageddatasource 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.
tediousreads no connection-string grammar, so the config takeshost/port/database/username/instanceNameand refuses aurl/connectionString. The password is refused inline: bind it through the connection form orexternal.credentialsRef. - TLS.
config.sslis tedious'sencrypt. For an on-premises server with a self-signed certificate, set the datasource-levelssl: { enabled: true, rejectUnauthorized: false }(tedious'sencryptplustrustServerCertificate); a CA goes in the same block. Writeenabled: true: the block'senableddefaults tofalse, and a block without it turns encryption off. - Schemas. A SQL Server database is usually several schemas. The Studio Tables
tab,
list_remote_tablesandimport_objectlist every user table asschema.table(dbo.Orders,Sales.Customers) with its primary key and foreign keys, and an imported object getsexternal.remoteSchema/remoteNamefrom it. A bound object withoutremoteSchemaresolves in the login's default schema. - Types.
nvarchar/nchar/ntext/xmlread as text (nvarchar(max)included),bitas boolean,datetime2/datetimeoffset/smalldatetimeas datetime,uniqueidentifieras uuid,money/decimalas decimal. SQL Server'stimestampisrowversion— a binary row counter, not a date. Ajson,tagsormultiselectfield over annvarchar(max)column is accepted as a lossy match (the driver keeps it as JSON text); JSON-array membership ($contains) and$emptyon a multi-value field are refused on SQL Server. - GUIDs. A
uniqueidentifierreads back in the lower-case form (3f2504e0-4f89-…), so a GUID written as a string reads back as that string, and an equality or$infilter matches it in either case. The text operators ($contains,$startsWith,$endsWith,$notContains,$likeand their case-insensitive siblings) match auniqueidentifiercolumn 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;$icontainsand$ilikematch 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/$likeare case-exact even on SQL Server's case-insensitive default collations, and$icontainsfolds A–Z only. This usesTRANSLATE, so the floor is SQL Server 2017. - Paging.
OFFSET … FETCHneeds anORDER BYon SQL Server. An unsorted page is ordered by the column the object'scolumnMapmaps toidwhen 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 asid, and the platform supplies its value: anIDENTITYkey cannot be inserted into, so keep a table keyed that way read-only. - Not on SQL Server:
$contains/$emptyon 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
- Database Drivers — managed multi-datasource routing, read-only via database privileges, and why the platform does not route read replicas.
- OData datasources — the same binding over an OData v4 service, through the shipped OData driver.
- Virtual objects — the same binding served by a plugin driver you write, for a remote that is not a database.
- Datasource reference — every
defineDatasourcefield. - The
examples/app-showcaseshowcase_externaldatasource — a runnable end-to-end demo. - ADR-0015 (federation spec) and ADR-0062 (external-datasource runtime).
Import Mappings
Named, reusable source-column to field projections for CSV/JSON/xlsx import, and the mappingName request that applies one.
OData Datasources
Declare a datasource over an OData v4 or v2 service such as SAP S/4HANA, SAP Gateway or Dynamics 365, bind objects to its entity sets, and read and write them live, with no code per entity set.