# data held somewhere else (app.DataSources)

> `app.DataSources` registers the places an app's data can live besides its own database — a warehouse, a SaaS table API, anything a package can query. Each entry names a provider class that implements `IEntitySource`, the name the app refers to it by, and the settings it needs. The platform builds the provider from that entry whenever it calls it.

<!-- id: config-data-sources · area: config · stability: stable · html: https://osysharp.com/reference/config/data-sources/ -->

## Summary        {#summary}

Register each source under a name, with its settings. A provider package supplies the class; the app says which
account, which credentials, and what to call it.

```osy syntax
use Acme.Warehouse;

app.Secrets = [ new Secret("WarehouseKey") ];

app.DataSources = [
  new WarehouseSource("Analytics") { Account = "acme-eu", Key = Secret.WarehouseKey },
];
```

## Signature      {#signature}

```osy syntax
app.DataSources = [ new <ProviderClass>("<Name>") { <Setting> = <value>, … }, … ];
```

## Description    {#description}

### One entry per source, by name   {#names}

The name in the parentheses is what the app's entities use to say where they live, so it must be a name — letters,
digits and `_` — and each one may appear once. It is not a constructor argument: the provider class needs no
constructor that takes it.

Two accounts on the same warehouse are two entries of the same class, each with its own name and settings.

### Settings are checked like any other code   {#settings}

What goes in the braces is an ordinary object initializer, checked when the app compiles: a setting the class does
not have, a value of the wrong type, or a `Secret.X` the app has not declared is a compile error. Settings can be text,
numbers, enum members, secrets, and nested objects of the provider's own classes.

The platform keeps the entry, not a built provider. Each time it calls the source it builds a fresh one — the class's
parameterless constructor, then the settings — and a secret is read at that moment, so a secret changed with
`osy secret set` is used from the next call on.

### What a provider class is   {#provider}

A provider implements `IEntitySource` from `Osysharp.Sources`, and says what it can do as a constant:

```osy syntax
using Osysharp.Sources;

public class WarehouseSource : IEntitySource {
  public const QueryFeatures Pushdown = QueryFeatures.Filter | QueryFeatures.OrderBy | QueryFeatures.Paging;
  public const string SecurityKitVersion = "1";   // it passes conformance/plan/1's security cases

  public string Account = "";
  public string Key = "";

  public stream<SourceRow> Query(QueryPlan plan) { … }     // run the plan, yield each row
  public SourceSchema DescribeSchema() { … }               // what the source holds
}
```

`Query` receives a query plan — the entity's address, the columns, the caller's filter and the read rule, grouping,
order and paging — and yields the rows as they arrive. `Pushdown` is read by the compiler, never run: it is how the
platform knows which queries the source can answer.

`SecurityKitVersion` says the provider's translator passes the security conformance kit — the cases in
`conformance/plan/1` that check a read rule is applied, a hidden column is never selected, and counts and paging
happen under the rule. An entity in the source that declares `security { }` compiles only when its provider states
one, because that rule is handed to the provider to apply.

### Entities whose rows live in a source   {#entities}

Mark an entity with the source it lives in, where it is at that source, and the columns that identify a row — or let
`osy import-schema` write them from what the source describes ([Importing a data source's schema](https://osysharp.com/reference/local/importing-a-schema/)):

```osy syntax
[DataSource(DataSource.Analytics), ExternalName("SALES.ORDERS")]
entity Order {
  [Key, ExternalName("ORDER_ID")] string Number;
  string  Region;
  decimal Total;
  security { allow read where Region == user.Region; }
}
```

- `[DataSource]` names a source from `app.DataSources`. The entity gets no table: its rows are read from the source.
- `[ExternalName]` on the entity is its address at the source — `SALES.ORDERS`, `main.sales.orders`, whatever the
  provider understands. On a member, it is the column's name there. Without it, the declared name is used.
- `[Key]` marks the column or columns that identify a row, and it is required. The platform derives each row's `Id`
  from them, so a row read twice is the same row — you can open it, and point at it by its key.
- Their rows change **as a set**: `Where(…).Update(…)`, `Where(…).Delete()` and `Other.Where(…).Insert(o => new Order { … })`
  are one statement the source runs ([[#writing]]). `new`, assigning a field of a row you read, and `Delete()` on one row
  are compile errors — the platform holds no copy of a source's row to change.
- One of the platform's own entities cannot refer to an entity in a source, or the other way round: no store holds
  both sides of that join. Keep the source's key as a plain value instead. Two entities of the **same** source can
  refer to each other, as the next section shows.

### References within one source   {#references}

An entity in a source can refer to another entity of the same source. The reference's `[ExternalName]` is the column
holding the target's key:

```osy syntax
[DataSource(DataSource.Analytics), ExternalName("SALES.CUSTOMERS")]
entity Customer {
  [Key, ExternalName("CODE")] string Code;
  string Name;
  string Region;
  security { allow read where Region == user.Region; }
}

[DataSource(DataSource.Analytics), ExternalName("SALES.ORDERS")]
entity Order {
  [Key, ExternalName("ORDER_ID")] string Number;
  [ExternalName("CUSTOMER_CODE")] Customer? Customer;
  decimal Total;
  security { allow read when IsAuthenticated; }
}
```

- Following the reference in a query — `Order.Where(o => o.Customer.Name == "Acme")`, `.Select(o => o.Customer.Name)`,
  `.OrderBy(o => o.Customer.Name)`, a grouping key or an aggregate — sends **one** plan to the source, with a join for
  each reference the query follows. A chain (`o.Customer.Country.Name`) is a join per step. The provider must declare
  `QueryFeatures.Joins`.
- Every join is a LEFT join over only the target rows **the reading person may read**. An order whose customer column
  is empty, names no customer, or names one the reader may not read keeps its row, and reads that customer's columns
  as `null` — so `o.Customer.Name == "Blue"` selects no order whose customer is hidden from them.
- Reading `order.Customer` on a row you already have reads the customer from the source by its key, under the
  customer's read rule. A customer the reader may not read gives them none of its values.
- A reference holds one key: the target declares exactly one `[Key]`. A collection (`Order[] Orders;` on `Customer`)
  is refused — declare the reference on the other side and query by it.

### Reading them   {#reading}

A read is written the way it is for any entity — `Order.Where(…)`, `.First()`, `.Count()`, `.Any()`, `.Sum(…)`,
`.Select(o => o.Number)`, `.GroupBy(o => o.Region).Select(g => new { Region = g.Key, Total = g.Sum(o => o.Total) })` —
and the platform sends it to the source as a plan:

- The plan names the entity and each column by its `[ExternalName]`, carries your filter, and carries **the read rule
  that applies to whoever is asking** as its own `Security` clause. A caller who may read only their region's orders
  sends a plan that says so, and the provider applies it with the filter. An `Id` is never in a plan: the source has
  no such column.
- The provider yields one `SourceRow` per row, its `Values` in the plan's column order, each as the plan contract's
  text for its type — `"10.50"` for a decimal, `"2026-09-14T08:00:00.0000000Z"` for an instant, `"true"` for a bool.
  The platform turns them back into typed values.
- A `Select` asks the source for only the columns it names, whatever it calls them — `new { N = o.Number }` asks
  for `Number` — so a wide table hands back what the read uses.
- A value computed from the row is worked out by the source when the provider declares `QueryFeatures.Computed`:
  arithmetic on numbers (`o.Total * o.Quantity`, `-o.Total`, `o.Quantity / 2`), `??` and `?:` — in a `Where`, a
  `Select`, an `OrderBy`, a `GroupBy` key or a total (`Order.Sum(o => o.Total * o.Quantity)`). It means what it means in
  C#: whole-number division truncates toward zero, a remainder keeps the sign of the number divided, and an arithmetic
  on `null` is `null`. Text joined to text (`o.Region + "-" + o.Status`) is worked out there too, a `null` part read as
  empty, as C# reads it. Joining text to a number, a bool or a date, and the bitwise operators, are compile errors:
  compute them in memory after `.ToList()`.
- A group's median and percentiles (`g.Median(o => o.Total)`, `g.Percentile(o => o.Total, 0.9)`) are asked of the source
  when the provider declares `QueryFeatures.Percentiles`: a `Percentile` aggregate with its `Fraction` (plan 1.9), the
  continuous percentile of a number column, answered to ten decimal places — the Snowflake and Databricks packages do.
  A percentile of a duration, or with a provider that does not declare it, is worked out by the platform over the rows.
- `Select(…).Distinct()` asks the source for the distinct rows, and `Select(…).Distinct().Count()` for how many there
  are: two rows are the same when every selected column is equal, strings compared exactly (`"eu"` is not `"EU"`) and
  `null` equal to `null`. A distinct read is ordered by the columns it selects; ordering it by another column is a
  compile error. A column hidden from the reader for only some rows cannot be in a distinct read, because the source
  would combine rows by a value the reader may not see.
- A grouping is answered one row per group: its key columns, then each aggregate, in the plan's order. A `.Where` after
  the grouped `Select` — `.Where(x => x.Revenue > 100)` — is a condition on the groups, sent with the grouping; the read
  rule still applies to the rows before they are grouped, so a total never includes a row the reader may not read.
  An average in a grouping is its sum and count too, and a condition on one — `.Where(x => x.Avg > 100)` — is sent as
  the sum against 100 times the count, which needs `Computed` as well as `Having`.
- A count, an existence check or a `Sum`/`Min`/`Max` is answered with **one** row holding one value. An `Average` is
  asked for as the sum and the count of its column, in one row, and the platform divides them as C#'s `decimal` divides —
  so an average carries every digit a `decimal` holds, whichever engine summed it.
- Each row gets its `Id` from the source, the entity and its key values, so reading the same row twice — in one
  function or in two — gives the same `Id`, and the two reads are the same object. A field mask applies to these rows
  exactly as it does to a table's.

A provider stops being asked for rows as soon as the reader has what it needs: `First()` over a source that pages
through a warehouse ends the paging.

A read fails, naming the source and the entity, when the provider answers with a row of the wrong width, a row with
no value for a `[Key]` column, two rows with the same key, a value that is not its column type's text, or an aggregate
that is not one row of one value. It is refused, naming the column, when a `[Key]` column is hidden from the caller —
without its key a row has no identity to read it by.

### Changing rows   {#writing}

A provider whose class implements `IWritableEntitySource` — `IEntitySource` plus `int Execute(QueryPlan plan)` — and
declares `QueryFeatures.Writes` in its `Pushdown` takes updates and deletes over its entities:

```osy syntax
int closed = Order.Where(o => o.Region == "EU" && o.Total == 0m).Update(o => { o.Status = "Closed"; });
int dropped = Order.Where(o => o.Status == "Void").Delete();
```

Each is ONE statement the source runs, answering how many rows it changed — the same meaning as on the platform's own
entities:

- The rows changed are the ones the condition admits, within the caller's read rule, within their update or delete
  rule — for an update, the rule for the columns it assigns. Every value assigned reads the row as it was before.
- A caller with no update or delete grant at all changes nothing: the answer is `0`, and nothing is sent.
- It runs at once, outside the unit of work, and a run that resumes does not run it again.
- The audit trail records the **statement** — who ran it, the source, the condition and rules as sent, what it assigned
  and how many rows changed, with any value the app redacts redacted — as one entry whose action is `SourceUpdate` or
  `SourceDelete`. A source returns no rows from a write, so there is no before and after per row.

A write whose provider only reads (`IEntitySource`), or does not declare `Writes`, is a compile error naming which.

**A page of rows.** `OrderBy(…).Take(n)` — and `Skip(n)` — before `.Update(…)` or `.Delete()` changes only the rows that page
chooses, as one statement, when the provider declares `PagedWrites`: the page is taken from the rows the reading person may
read and the condition admits, and the update or delete rule then narrows within it, so a refused row is left rather than
replaced by the next. Order by columns that decide every tie, or which rows the page holds is up to the source.

**Creating rows.** `Deal.Where(d => d.Stage == "Won").Insert(d => new ArchivedDeal { Number = d.Number, Region = d.Region })`
creates one `ArchivedDeal` for every won deal, as one statement the source runs, when both entities live in the same
source and its provider declares `Inserts`. The create rule of the entity inserted into is part of the statement, written
over the values each row is given; when it refuses any of the rows, nothing is created and the call fails naming the rule.
An insert from one of the app's own entities into a source's, or the other way round, is a compile error — read the rows
first. A list the function holds is inserted the same way — `numbers.Insert(n => new ArchivedDeal { Number = n, Region = "EU" })`
is one statement carrying every value, under the same create rule. An `OrderBy(…).Take(n)` page before `.Insert(…)` copies
only that page, as it changes only that page before `.Update(…)` or `.Delete()`. `onConflict:` is not sent to a source yet.

### Queries the source cannot answer   {#pushdown}

A query over one of these entities — in a function, or read by a component (`live var n = Order.Count();`) — is
checked when the app compiles, against the provider's `Pushdown`. Filtering,
ordering, a limit or offset (`First()` sends one), counting and totalling, grouping, text matching
(`StartsWith`, `EndsWith`, `Contains`), following a reference (`Joins`), distinct rows (`Distinct`), a condition on groups
(`Having`), a value computed from the row (`Computed`), an update or delete (`Writes`), another entity read inside the query
(`Subqueries`), `Union`/`Concat`/`Intersect`/`Except` (`SetOperations`), a `Window.*` value in a projection (`Windows`) and
`Join`/`LeftJoin`/`SelectMany` over another entity of the same source (`ValueJoins`) each need
their feature declared. A query that needs one the provider does not
declare is a compile error naming it — read what the source can answer, then do the rest in memory:

```osy syntax
var eu = Order.Where(o => o.Region == "EU").ToList();   // Filter
var count = eu.Count;                                   // counted in memory — no Aggregates needed
```

A query can read another entity of the same source inside its condition — `Note.Where(n => Order.Any(o => o.Region ==
n.Region))`, and a count, total, least, greatest or average of one — as part of the one statement the source runs. An entity
in another source, or one of the platform's own, is refused there: read it with its own query first. `Include` loads the
rows a reference names in one read by their keys per reference path, under the target's read rule, so reading the
reference in memory asks the source nothing more: `Order.Where(o => o.Region == "EU").Include(o => o.Customer).ToList()`.

### What is refused   {#refused}

| you write | why |
|---|---|
| an entry with no name, or anything else between the parentheses | the name is what the app refers to; settings go in the braces |
| the same name twice | one name, one source |
| a class that does not implement `IEntitySource` | there would be nothing for the platform to call |
| an interface, an abstract or a generic class, or one whose every constructor takes arguments | the platform has to build the class, with nothing to pass it |
| an entity in a source without `[Key]`, or `[Key]` on an entity that is not in one | a source's row is identified by its own key, and the platform's rows by `Id` |
| a relation between an entity in a source and one of the platform's own, in either direction; or inheritance, `[Principal]`, `semantic`, `[Index]`, `[Unique]`, `[Counter]`, `[Searchable]` on an entity in a source | each needs the platform's own storage, which a source's rows are not in |
| on an entity in a source: a collection of another entity, a reference to an entity in another source, or a reference to one with more than one `[Key]` | a source joins one column to one row of its own |
| `new`, assigning a field of a row that was read, or `Delete()` on one row, of an entity in a source | a source's rows change as a set — `Where(…).Update(…)` or `Where(…).Delete()` — and are created in the source system |
| `Where(…).Update(…)` or `Where(…).Delete()` where the provider does not implement `IWritableEntitySource`, or does not declare `Writes` | nothing would run the statement |

## Examples       {#examples}

```osy test app=config-data-sources
using Osysharp.Sources;

// A source holding a fixed list — the smallest thing that is a provider.
class Catalog : IEntitySource {
  public string Label = "";

  public stream<SourceRow> Query(QueryPlan plan) {
    yield return new SourceRow { Values = [Label] };
  }

  public SourceSchema DescribeSchema() {
    return new SourceSchema { Tables = [ new SourceTable { Name = Label } ] };
  }
}

app.DataSources = [ new Catalog("Products") { Label = "products" } ];

// The source's rows, as an entity: its address there, and the column that identifies a row.
[DataSource(DataSource.Products), ExternalName("products")]
entity Product {
  [Key] string Sku;
  string Name;
  [ExternalName("category_code")] Category? Category;   // a reference within the same source: the column holding its key
}

[DataSource(DataSource.Products), ExternalName("categories")]
entity Category {
  [Key] string Code;
  string Title;
}
```

## See also       {#see-also}
- [warehouse-time budgets (a data-source query is budgeted before it leaves)](https://osysharp.com/reference/config/source-budget/) — every query to one of these is metered and refused before it leaves when the day's
  warehouse-time budget is spent; an app has one from the moment it exists
- [declaring secrets (app.Secrets)](https://osysharp.com/reference/config/secrets/) — declaring the secrets a source's settings read
- [your own file store (app.BlobStore)](https://osysharp.com/reference/config/blob-store/) — the other provider seam: where file content lives
- [[Flags] — a value that holds several members](https://osysharp.com/reference/enum/flags/) — `[Flags]`, which `QueryFeatures` is
- [Importing a data source's schema](https://osysharp.com/reference/local/importing-a-schema/) — generating these entities from what a source describes