Building a Request

Every request has three levels: the dataset (the request itself), its tables, and each table's fields. POST /v1/generate-dataset and POST /v1/estimate-dataset accept one or more tables in a tables array. POST /v1/generate-table and POST /v1/estimate-table accept one table, with its properties at the top level. See One table, two endpoints for how the two compare, and Endpoints for every endpoint.

This page describes each level in turn, using the following request as the example. It generates a customer table with a sequential ID and a first and last name from US name data:

{
  "seed": 42,
  "tables": [
    {
      "name": "customer",
      "rowCount": 100,
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "map": { "value": { "column": "id", "dataType": "Integer" } }
        },
        {
          "type": "Person",
          "countries": [{ "country": "us" }],
          "map": {
            "firstName": { "column": "first_name", "dataType": "VarChar" },
            "lastName": { "column": "last_name", "dataType": "VarChar" }
          }
        }
      ]
    }
  ]
}

Level 1: the dataset

The top-level object is a DatasetRequest. In the example, it contains seed and tables:

Property	Type	Default	Description
`$schema`	`string?`	none	URL of a JSON Schema for the request, used by an editor to validate and complete a request file. Accepted and ignored, so a file that sets it can be sent unchanged. Only accepted at the top level of the request.
`csvFormat`	`CsvFormatRequest`	see CSV formatting	Formatting settings applied to every table in the request.
`schemaFormat`	`"Json"` / `"Xml"` / `"Yaml"`	`"Json"`	Format of the schema file in a `.csvx` package: `schema.json`, `schema.xml` or `schema.yaml`. It has no effect on a JSON response, which always contains the schema as JSON. `POST /v1/generate-table` does not accept `schemaFormat`, because it does not return a schema. See Schema format and `Accept`.
`schemaName`	`string?`	`"dataset"`	The `name` of the generated schema, in the schema file of a `.csvx` package and in `catalog.schema.name` of a JSON response. It does not change the schema file name. A blank value uses `"dataset"`.
`schemaVersion`	`string`	`"1.0"`	Version of the request contract the request uses.
`seed`	`integer?`	random	Seed for the whole request. The same request with the same `seed` produces the same data. A field's values depend on the seed, its table's name and the columns it maps to, so adding, removing or reordering other fields or tables does not change them, and increasing `rowCount` adds rows after the existing ones without changing them. A field's own `seed` overrides this for that field.
`tables`	`TableRequest[]`	required	The tables to generate. Must contain at least one table. See Level 2 below. A table referenced by a `Reference` field must come before the table that references it.

Required properties only

tables is the only required property. Every other property uses its default:

{
  "tables": [
    {
      "name": "customer",
      "rowCount": 100,
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "map": { "value": { "column": "id", "dataType": "Integer" } }
        }
      ]
    }
  ]
}

With no seed, the request uses a random seed, so each request produces different data. The seed used is returned in the X-Alchemy-Seed response header.

Every property

The same request with every dataset property set:

{
  "$schema": "./alchemy-request.schema.json",
  "schemaVersion": "1.0",
  "seed": 42,
  "schemaFormat": "Yaml",
  "schemaName": "shop",
  "csvFormat": {
    "dateFormat": "MM/dd/yyyy",
    "dateTimeFormat": "MM/dd/yyyy HH:mm:ss",
    "timeFormat": "HH:mm:ss",
    "timestampFormat": "MM/dd/yyyy HH:mm:ss zzz",
    "lineTerminator": "\r\n",
    "quoteCharacter": "\"",
    "fieldDelimiter": ";",
    "escapeCharacter": "\\",
    "encoding": "Utf8Bom"
  },
  "tables": [
    {
      "name": "customer",
      "rowCount": 100,
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "map": { "value": { "column": "id", "dataType": "Integer" } }
        }
      ]
    }
  ]
}

This request produces the same data each time it is sent, because seed is set. With Accept: application/x-csvx, the response is a .csvx package with the schema in schema.yaml, a schema named shop, and CSV files written with the settings in csvFormat. See CSV formatting for each csvFormat property.

Level 2: a table

Each entry in tables is a TableRequest. In the example, it is the customer table:

{
  "name": "customer",
  "rowCount": 100,
  "fields": [ /* ... */ ]
}

Property	Type	Default	Description
`fields`	`FieldRequest[]`	required	The fields that generate this table's columns. Must contain at least one field. See Level 3 below.
`name`	`string`	required	Table name. It is the CSV file name in a `.csvx` package, and the name a `Reference` field uses to refer to this table's columns.
`primaryKey`	`string[]?`	none	Columns that form this table's primary key, in key order. See Declaring a primary key below.
`rowCount`	`integer`	required	Number of rows to generate for this table. At least 1. See How large a request can be below.
`type`	`"Standard"` / `"Link"`	`"Standard"`	The kind of table. `"Standard"` is a table with the properties above. `"Link"` declares a link table, a table copied from the rows of an earlier table.

How large a request can be

Every request is checked against limits on the number of rows, the number of columns in a table, the size of the generated data, the amount of calculation in Calculated fields, and the size of the request body. The limits change over time, so their values are not listed here.

POST /v1/estimate-dataset and POST /v1/estimate-table return the current value of each limit and how much of each limit a request uses, without generating any data. Crucible shows the same information when it estimates a request.

A request that exceeds a limit is rejected and not charged. To generate more data than one request allows, split it across several requests with a different seed for each, so that each request produces different data.

When the service is busy

When the service is busy, a request can be rejected with 503 and a Retry-After header that gives the number of seconds to wait. Retry after that interval rather than immediately. A rejected request is not charged.

A request that reads reference data, such as names, addresses, places, words and sentences, can also be rejected with 503 and the type urn:alchemy:error:reference-data-unavailable while that data is temporarily unavailable. This applies to the reference endpoints as well as to generation and estimates. The same Retry-After header applies, and the request is not charged. A request whose fields need no reference data is not affected.

If a download fails

A response is charged when it is sent. A download that fails after that is still charged, because the service cannot detect a failed download.

The same request can be repeated once without charge, within 60 minutes of the original request. A repeated request with the same seed produces the same data.

The repeat must be identical to the original request. A change to any field, setting, table name or column name makes it a new request, which is charged.

Every response includes the seed it used in the X-Alchemy-Seed header, including responses to requests that set no seed. Send that seed in the repeated request. Without it, a new seed is used, so the request generates different data and is charged.

Only the first repeat is free. Any further repeat is charged.

Declaring a primary key

primaryKey lists the columns that identify a row, and the generated schema declares a primary key constraint on those columns:

{
  "name": "customer",
  "rowCount": 100,
  "primaryKey": ["customer_id"],
  "fields": [ /* ... */ ]
}

primaryKey is set on the table rather than on a column, so a key can have more than one column, as a bridge table between two other tables requires:

{
  "name": "customer_product",
  "rowCount": 600,
  "primaryKey": ["customer_id", "product_id"],
  "fields": [ /* ... */ ]
}

Every column listed must be a column the table generates, or the request is rejected. A key column is never null, so the generated schema declares it not null. The field that writes a key column cannot set nullPercentageMin or nullPercentageMax above 0, and a key column that comes out empty in any row is rejected. The generated values are also checked, and a repeated key is rejected. Set unique on the field, or use a field type that produces distinct values, such as Number in Sequential mode.

Foreign keys have no equivalent property. The schema declares a foreign key for each Reference field in the request.

POST /v1/generate-table accepts primaryKey and applies the same checks, but returns no schema, so the primary key does not appear in its response. See Reading the Response.

One table, two endpoints

POST /v1/generate-table accepts a single TableRequest, with the dataset-level properties $schema, csvFormat, schemaVersion and seed at the same level, instead of a tables array. The table properties are the same for both endpoints. The difference is the structure of the request and the response. Both of the following requests generate the same customer table, with seed 42, a sequential id, and a first and last name from US name data in proper case.

With POST /v1/generate-dataset, the table is an entry in tables:

{
  "seed": 42,
  "tables": [
    {
      "name": "customer",
      "rowCount": 5,
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "map": { "value": { "column": "id", "dataType": "Integer" } }
        },
        {
          "type": "Person",
          "countries": [{ "country": "us" }],
          "firstNameCaseOutput": "Proper",
          "lastNameCaseOutput": "Proper",
          "map": {
            "firstName": { "column": "first_name", "dataType": "VarChar" },
            "lastName": { "column": "last_name", "dataType": "VarChar" }
          }
        }
      ]
    }
  ]
}

With Accept: application/x-csvx, the response is a .csvx package containing manifest.json, schema.json and tables/customer.csv. See Reading the Response for the package contents. tables/customer.csv contains:

id	first_name	last_name
1	Rumualdo	Husong
2	Paralee	Dembicki
3	Narcisa	Zoucha
4	Meshelle	Mcclemens
5	Kaycee	Hanshaw

With POST /v1/generate-table, the table properties and seed are at the top level:

{
  "seed": 42,
  "name": "customer",
  "rowCount": 5,
  "fields": [
    {
      "type": "Number",
      "mode": "Sequential",
      "start": 1,
      "map": { "value": { "column": "id", "dataType": "Integer" } }
    },
    {
      "type": "Person",
      "countries": [{ "country": "us" }],
      "firstNameCaseOutput": "Proper",
      "lastNameCaseOutput": "Proper",
      "map": {
        "firstName": { "column": "first_name", "dataType": "VarChar" },
        "lastName": { "column": "last_name", "dataType": "VarChar" }
      }
    }
  ]
}

With Accept: text/csv, the response is the same CSV data, byte for byte, with no manifest and no schema:

id	first_name	last_name
1	Rumualdo	Husong
2	Paralee	Dembicki
3	Narcisa	Zoucha
4	Meshelle	Mcclemens
5	Kaycee	Hanshaw

Without an Accept header, both endpoints return JSON. See Reading the Response.

Use generate-table for the rows of a single table. Use generate-dataset for several tables in one request, for Reference fields between tables, or for the schema. See Endpoints for the full request and response details of both.

Level 3: a field

Each entry in fields is a field. In the example, these are the Number field that produces id and the Person field that produces first_name and last_name. The Number field:

{
  "type": "Number",
  "mode": "Sequential",
  "start": 1,
  "map": { "value": { "column": "id", "dataType": "Integer" } }
}

Every field has a type, one of the field types, and settings specific to that type, such as mode and start for Number. Every field type also accepts these settings:

Property	Type	Default	Description
`map`	`object`	required	Maps the field's sub-fields to destination columns. See Mapping sub-fields to columns below.
`nullPercentageMax`	`number`	`0`	Upper bound (0-100) of the percentage of this field's rows that should be null. See `nullPercentageMin`.
`nullPercentageMin`	`number`	`0`	Lower bound (0-100) of the percentage of this field's rows that should be null. The rate is fixed once for the whole field from this range, not recalculated per row. Set equal to `nullPercentageMax` for an exact rate.
`seed`	`integer?`	the request's `seed`	Per-field seed, overriding the request's `seed` for this field only.
`unique`	`boolean`	`false`	Whether every generated value must differ from the others in the table. The number of distinct values a field can produce limits this. See each field type's page.

Each field type's page in the Field Reference lists these settings with its own.

Writing dates and times

A date is written 2024-01-01. A time is written 08:00:00. A date and time is written 2024-01-01 08:00:00 or 2024-01-01T08:00:00, so a value from generated data can be used in a request without changes. Fractions of a second are accepted after the seconds.

Dates and times in a request have no time zone. A value written with an offset or a Z, such as 2024-03-31T10:00:00+02:00, is rejected. A Timestamp field sets its time zones through its own settings.

Mapping sub-fields to columns

A field produces one or more sub-fields. Most field types produce one, named value. Others produce several. map sets the column name and data type for each sub-field to include:

{
  "type": "Person",
  "countries": [{ "country": "us" }],
  "map": {
    "firstName": { "column": "first_name", "dataType": "VarChar" },
    "lastName": { "column": "last_name", "dataType": "VarChar" },
    "dob": { "column": "date_of_birth", "dataType": "Date" }
  }
}

A sub-field that is not in map is not written to a column. Person above produces only firstName, lastName and dob. It can also produce middleName, sex, race, ethnicity and others, but those are not mapped. Each field type's page lists its sub-fields.

dataType sets how the value is formatted in the CSV and the column type recorded in the response. Use the data type of the destination column.

Data types

dataType accepts one of these names, in any case:

Data type	Contains
`Boolean`	True or false.
`Byte`	A whole number from 0 to 255.
`Short`	A 16-bit whole number.
`Integer`	A 32-bit whole number.
`Long`	A 64-bit whole number.
`Decimal`	An exact number with a fixed precision and scale.
`Float`	A single-precision floating-point number.
`Double`	A double-precision floating-point number.
`Char`	Fixed-length text.
`VarChar`	Variable-length text.
`Clob`	Long text.
`Date`	A calendar date.
`Time`	A time of day.
`DateTime`	A date and time of day.
`Timestamp`	A date and time of day with a UTC offset.
`Interval`	A length of time.
`Guid`	A 128-bit unique identifier.
`Geometry`	A shape in planar coordinates.
`Geography`	A shape on the surface of the earth.

Any other name is rejected, and the error lists these names. A field accepts only the data types its values can be written as. For example, a Number field cannot be mapped to a Date column, and the error identifies the column and the data type it was given.

Hidden columns

A column mapping with "hidden": true is generated as normal, but is not included in the CSV or the generated schema:

{
  "type": "Person",
  "countries": [{ "country": "us" }],
  "map": {
    "firstName": { "column": "first_name", "dataType": "VarChar", "hidden": true },
    "lastName": { "column": "last_name", "dataType": "VarChar", "hidden": true }
  }
}

Hidden columns are used with Calculated fields, which compute a column's value from other columns in the same row. For example, to output only full_name, generate first_name and last_name as hidden columns and compute full_name from them.

A hidden column still applies to unique, can be referenced by a Reference field in a later table, and can be read by name by a Calculated field declared after it. See Reading other columns. It cannot be listed in a table's primaryKey, because it is not in the output. A table with only hidden columns is rejected.

CSV formatting

csvFormat is set at the top level of the request and applies to every table. It sets how values are written in the CSV. Every property is optional, and an unset property uses its default:

{
  "seed": 1,
  "csvFormat": { "dateFormat": "MM/dd/yyyy", "fieldDelimiter": ";" },
  "tables": [ /* ... */ ]
}

Property	Default
`dateFormat`	`yyyy-MM-dd`
`dateTimeFormat`	`yyyy-MM-dd HH:mm:ss`
`encoding`	`Utf8`
`escapeCharacter`	`"`
`fieldDelimiter`	`,`
`lineTerminator`	`\n`
`quoteCharacter`	`"`
`timeFormat`	`HH:mm:ss`
`timestampFormat`	`yyyy-MM-dd HH:mm:ss zzz`

fieldDelimiter, quoteCharacter and escapeCharacter are each a single character, and none of them can be a line break. fieldDelimiter must differ from both quoteCharacter and escapeCharacter, because a CSV reader could not otherwise find the end of a value. lineTerminator is \n, \r\n or \r. Each date and time format may use only the pattern letters for its own kind of value, so dateFormat cannot include a time of day. A setting that does not follow these rules is rejected with 400.

By default, a quote inside a quoted value is written twice, as RFC 4180 specifies. This is the result of escapeCharacter being the same as quoteCharacter, which is the default.

Set escapeCharacter to a different character for a CSV reader that expects a quote to be preceded by an escape character. A quote inside a value is then written after the escape character, and an escape character inside a value is written twice, so it is not read as an escape. Only change it when the reader expects this convention, because the two conventions interpret the same characters differently.

encoding sets the character encoding of the CSV files in a .csvx package, and of the CSV that POST /v1/generate-table returns for Accept: text/csv:

Value	Writes
`Utf8`	UTF-8 with no byte order mark.
`Utf8Bom`	UTF-8 preceded by a byte order mark. Spreadsheet applications use the mark to recognize UTF-8, so accented and non-Latin names open correctly.
`Utf16`	UTF-16 little-endian, preceded by a byte order mark.
`Utf16BigEndian`	UTF-16 big-endian, preceded by a byte order mark.

All four are Unicode encodings, so every generated value can be written in any of them. CSV returned as a csv string in a JSON response is always UTF-8, the same as the rest of the JSON.

POST /v1/generate-dataset returns all of these settings, including defaults, as csvFormat in its response, so a tool reading the data can use the same settings. See Reading the CSV.

Table of Contents