Store data as documents (JSON)

By default a data template generates a .sql script: a re-runnable MERGE holding your rows as SQL literals. Set OutputFormat="json" and DataStar stores the rows as a data document instead, a .json file, and generates the SQL when it deploys.

The rows are the same. What changes is what lands in version control, and therefore what you review, diff and merge.

<DataComponent Category="reference-data" Name="country"
               OutputFormat="json" Version="2">
  <Table Id="1" Name="COUNTRY">
    <Column Name="COUNTRY_CODE" PrimaryKey="true"/>
  </Table>
</DataComponent>

Why you might want this

A MERGE script is written for a database to run, not for a person to read. One changed value can move a VALUES list, and a reordered extract can rewrite a file that means exactly what it meant before. Reviewing it means reading SQL to find the data.

A data document puts the rows at the end, one per line, in a stable order, so a pull request shows a row added and a row changed rather than a rewritten script. Two people changing different rows of the same component can merge cleanly, because DataStar ships a merge driver that merges by row instead of by line (you register it with git once; see that page).

What a document looks like

The file is laid out for reading in a diff: the component's identity first, then what the template says about the table, then the data. The example is components/reference-data/country.json, captured from SQL Server:

{
  "formatVersion": 2,
  "category": "reference-data",
  "name": "country",
  "variables": { "${name}": "country" },
  "template": {
    "file": "countries.xml",
    "version": 2,
    "fingerprint": "3fa9c2e81b04...",
    "script": { ... }
  },
  "sourceVendor": "Microsoft",
  "tables": [
    {
      "id": 1,
      "schema": "dbo",
      "name": "COUNTRY",
      "columns": [
        { "name": "COUNTRY_CODE", "dataType": "varchar", "length": 2, "primaryKey": true, "kind": "String" },
        { "name": "COUNTRY_NAME", "dataType": "varchar", "length": 60, "kind": "String" },
        { "name": "REGION", "dataType": "varchar", "length": 10, "kind": "String" }
      ]
    }
  ],
  "data": [
    {
      "id": 1,
      "table": "COUNTRY",
      "columns": ["COUNTRY_CODE", "COUNTRY_NAME", "REGION"],
      "rows": [
        ["FR", "France", "EMEA"],
        ["GB", "United Kingdom", "EMEA"]
      ]
    }
  ]
}

template names the template file and its Version, a fingerprint of the part of the template that shapes the SQL, and under script (abbreviated above) that part itself: the script surface. It is what lets the document inflate with no template folder present.
sourceVendor is the database vendor the rows were captured from. The column types in tables are that vendor's.
tables describes each table the template declares, one line per column. id is the template's Table Id, which is what joins a table to its data.
data holds the rows, one per line, as arrays in the order columns gives. Each block is labelled with the table's name only ("table": "COUNTRY"); the schema is in tables, since it can differ between environments and would otherwise show up when comparing documents from two of them. id is what joins a block to its table. Nulls are written as null, whole numbers as numbers, and everything else (strings, decimals, dates) as strings, so a value diffs as text.

You do not edit a document by hand. Extract the component, or for a changeset template pull your changes, and DataStar writes it.

What lands in your workspace

	Path
A `.sql` template's component	`components/<category>/<name>.sql`
A `json` template's component	`components/<category>/<name>.json`

(components is the workspace's Component Location; see workspace settings.)

The component keeps its identity either way: it is the same component, in the same category, with the same name. Only the file it is committed as differs, so a category can hold a mix of both while you migrate.

It still deploys as SQL

Nothing downstream needs to understand JSON. A data document is inflated to the same MERGE script it would have been, and it is the script that runs.

Inflation happens:

when you deploy, so the deployment runs SQL as it always did;
when datastar --command-name build packages a release, so the package holds scripts and needs no templates when it lands;
when you ask to see it, through View Inflated SQL on a component, or Open Script on a deployment item.

In the Deployment view you can read either side: Open Script shows the SQL the item will run, and Open Document (JSON) shows the rows it carries. They answer different questions, so both are offered where there are two things to see.

It inflates without your templates

A document carries the part of its template that shapes the script (the script block above), so it can be turned back into SQL with no template folder present. That matters on a build agent, which has your repository but not your workspace.

The consequence worth knowing: a document is inflated as it was extracted, not as your template reads today. If the template has changed shape since, DataStar warns (Template 'countries.xml' has changed since the document was captured) and still writes the SQL the document was extracted under. To pick up the template change, extract the component again.

Documents from before the script surface

A document extracted with an early 3.1 preview carries no script block. It inflates only where the template can be found by name, and a changed template is then an error rather than a warning. Extract such components again once, and they carry their surface from then on.

The description of a template's tables and columns (template, sourceVendor and tables above) is the same for every component the template generates, so DataStar writes it once and has each document point at it. This is the default; you do not need to ask for it.

<DataComponent Category="reference-data" Name="country"
               OutputFormat="json" Version="2">

The document then names its definition instead of carrying it, and reads as formatVersion 3:

{
  "formatVersion": 3,
  "category": "reference-data",
  "name": "country",
  "variables": { "${name}": "country" },
  "definition": "countries.8c0d8d23ab2f",
  "data": [ ... ]
}

and the definition sits in a definitions folder at the root of the workspace, under the category's folder:

components/reference-data/country.json           ->  "definition": "countries.8c0d8d23ab2f"
definitions/reference-data/countries.8c0d8d23ab2f.json

{
  "formatVersion": 3,
  "kind": "definition",
  "hash": "8c0d8d23ab2f...",
  "template": { "file": "countries.xml", "version": 2, "fingerprint": "3fa9c2e81b04...", "script": { ... } },
  "sourceVendor": "Microsoft",
  "tables": [ ... ]
}

The id is the template's file name without its extension (countries.xml gives countries) and the first twelve characters of a SHA-256 of the definition's content, so the same description always produces the same file. The hash covers what describes the schema and the script; it leaves out licensedTo, which names the organisation the licence is for, so renewing a licence under a new name does not make every definition new. Two branches extracting the same schema write the same definition; a different schema is always a different file, and both can sit side by side while a change moves through your branches. Once a template's documents on every branch point at the new definition, the old one is unused and can be pruned.

A document finds its definition by looking for a definitions folder above its own: the workspace root for a component, and the same for the drafts area, a personal workspace, or a package that keeps the layout.

Commit a definition with the documents that point at it

A document whose definition is missing cannot be read by anyone who fetches it. When you commit, DataStar checks the staged documents: if a definition is in the workspace but not staged, it offers Stage and Commit; if a definition is not in the workspace at all, the commit is refused until you extract the component again or unstage the document.

Definitions are written by DataStar and named by their content, so never edit, rename or copy one by hand: a definition that does not match its hash is refused. A document's definition must be a plain file name; one that carries a path (../x, a drive, a folder) is refused rather than followed out of the definitions folder.

Use datastar --command-name definitions to list what points at what, to remove definitions nothing points at any more, or to convert documents between the shared and inline forms.

When you want each document to stand alone

Definition="inline" puts the description back inside every document (formatVersion 2, as in the first example), so a document can be read with nothing beside it. Worth it for a template that generates one component, or a handful, where a second file costs more than the repetition saves.

<DataComponent Category="reference-data" Name="country"
               OutputFormat="json" Definition="inline" Version="2">

Definition means nothing on a template that writes .sql; a script has no definition to place.

Cross-vendor extracts

A document records the vendor it was captured from. Inflating it for that vendor needs nothing else.

Inflating it for the other vendor needs a live connection to a database of that vendor, because the column types have to come from somewhere and the ones in the document are the vendor's it was captured from. Without one, DataStar refuses rather than guessing: 'reference-data/country' was captured from Oracle, so inflating it for Microsoft needs a live Microsoft database to take its column types from. A changeset never crosses vendors, with or without a connection.

Migrating an existing `.sql` template

Switching a template to documents is one attribute, but it changes the file every one of its components is committed as, so do it as one change.

Check Version. A data template that does not set Version now scripts as version 2 on SQL Server (deletes identified from the loaded rows, cascades only where a table sets DeleteCascade="true"). If you relied on the older single MERGE, set Version="1" explicitly before you touch anything else. Oracle scripts are the same either way. See the schema.
Set OutputFormat="json" on the template. Decide whether the definition is shared (the default) or inline.
Extract every component of the template again. Each extract writes components/<category>/<name>.json, writes or reuses the shared definition, and deletes the .sql it replaces. The Extracted column updates as usual.
Commit the three halves together: the template, the new .json files and the deleted .sql files, plus the definitions folder. The commit view offers to stage a definition you left out.
Make sure everyone is on 3.1. An older DataStar reads a shared-definition document as format version 3 is not supported and a changeset as an unknown operation, so a branch holding either cannot be opened until the client is updated.

What does not need doing:

Existing deployment files keep working. An item pins components/reference-data/country.sql at a commit, and build reads each item as a blob at its pinned revision, so a release built before the switch still finds its script. A deployment file written after the switch pins the .json, and the Deployment view marks the old path as deleted upstream if you open a file that mixes the two.
Nothing in the target database changes. The inflated SQL is the script the template would have written.

You can convert between shared and inline later without extracting again: datastar --command-name definitions --operation share (or inline) rewrites the documents of every template whose attribute says so, and reads each one back to make sure nothing changed but its form.

Packaging without build

The release command inflates documents when it deploys, so a package that holds .json documents still runs. It finds a shared definition by looking for a definitions/<category>/<id>.json above the document. A package laid out by GitSources.Tools or the Azure Get Sources task holds <category>/<file> and nothing above it, so a shared-definition document arrives without its definition and the deployment fails with points at shared definition '...', which was not found. Either package with build, which inflates at build time and ships SQL, or copy the workspace's definitions folder to the root of the package.

Troubleshooting

You see	It means	What to do
`The data document ... points at shared definition '<id>', which was not found (looked in ...)`	The document was fetched without its definition, or the `definitions` folder is missing where the document is being read.	`Restore definitions/<category>/<id>.json from version control, or extract the component again to recreate it.` In a pipeline, package with `build` or copy `definitions` to the package root.
`... which version control does not hold at <commit>` (from `build`)	The commit that added the document did not include its definition.	Commit the definition, or extract the component again and commit both.
`Shared definition '...' does not match its content hash, so it was edited or damaged`	Someone edited or hand-copied a definition.	Restore it from version control.
`The data document '...' names shared definition '<id>', which is not a definition id`	The document's `definition` is not a plain file name, so it was edited by hand or damaged.	Extract the component again, or restore the document from version control.
`Template '<file>' has changed since the document was captured (structural fingerprint differs)`	The template has moved on since the extract. A warning when the document carries its surface; an error for an early-preview document.	Extract the component again to inflate under the current template. `(its Version changed from 1 to 2)` names the one attribute when that is all that changed.
`'<category>/<name>' carries no template to inflate with, and one cannot be resolved by the name '<file>'`	A document without a script surface, and no template folder to fall back on.	Extract the component again, or give `inflate` a `--template-directory`.
`'<category>/<name>' was captured from Oracle, so inflating it for Microsoft needs a live Microsoft database`	A cross-vendor inflate with no connection.	Deploy against a connection of the target vendor, or extract from that vendor.
`format version 3 is not supported` (an older DataStar)	A client older than 3.1 opened a shared-definition document.	Update the client; 3.1 reads format versions 1 to 3.
Commit refused: `These documents point at shared definitions that are not in the workspace`	The definition file has gone from the working tree.	Extract the components again to recreate it, or unstage the documents.
`Stage Shared Definitions` prompt on commit	The definition exists but is not staged.	Choose Stage and Commit.

What to do next

Deploy only your rows with changesets, which builds on documents.
datastar CLI reference for build, inflate, definitions and merge.
Data component schema for OutputFormat, Definition and DeployMode.

Why you might want this​

What a document looks like​

What lands in your workspace​

It still deploys as SQL​

It inflates without your templates​

Sharing one definition between components​

When you want each document to stand alone​

Cross-vendor extracts​

Migrating an existing .sql template​

Troubleshooting​

What to do next​