Version: Previous

Tables - basics

info

Version 7.2 introduced a simplified table-dictionary syntax, where fields are defined inline in the table, rather than in a separate file. We have provided a guide to help you migrate from older projects to the new syntax.

Tables are essential to the data model. In your application's tables-dictionary.kts file, you need to define every table that your application needs.

Each table requires:

a unique name and unique id
one or more fields, and each field must have a specific type.
one or more primary keys, which are the main methods of searching for data in the table.

Here is a very simple example table definition. It contains a single table with four fields in it.

tables {
    table(name = "POSITION", id = 11002) {
        field("ID", INT).autoIncrement().primaryKey()
        field("INSTRUMENT_ID")
        field("QUANTITY", INT)
        field("NOTIONAL", DOUBLE)

    }

}

Let's look at this more closely.

The table has a unique name (POSITION) and ID (1102); the id must be a number; this ensures that you can rename a table without losing the data in it.
The first field, POSITION_ID, uses autoincrement to set the field automatically to the next number in the sequence when a new record is written to the database. So every record will have a unique number. We shall discuss this further in the Advanced page.
The other three fields - have their type specified.
POSITION_ID is also the primary key for this table. So you will be able to search for records using a single POSITION_ID value or a range of values. It is common to use a single sequenced/autoIncrement field as a primaryKey in this way.

Field types

By default, fields are of type STRING. To specify a field of a different type, simply provide the type: for example, to specify an integer:

field("QUANTITY", INT)

These are the supported field types:

Type	Example
STRING	`field("MY_STRING", STRING)`
STRING(maxSize)	`field("MY_STRING", STRING(256))`
DATE	`field("MY_DATE", DATE)`
DATETIME	`field("MY_DATETIME", DATETIME)`
ENUM("VALUE", ...)	`field("MY_ENUM", ENUM("YES", "NO"))`
ENUM("VALUE" to "alias", ...)	`field("MY_ENUM", ENUM("YES" to "Yes", "NO" to "No"))`
LONG	`field("MY_LONG", LONG)`
INT	`field("MY_INT", INT)`
SHORT	`field("MY_SHORT", SHORT)`
DOUBLE	`field("MY_DOUBLE", DOUBLE)`
BIGDECIMAL	`field("MY_BIGDECIMAL", BIGDECIMAL)`
BIGDECIMAL(precision, scale)	`field("MY_BIGDECIMAL", BIGDECIMAL(10, 3))`
NANO_TIMESTAMP	`field("MY_NANO_TIMESTAMP", NANO_TIMESTAMP)`
RAW	`field("MY_RAW", RAW)`
RAW(maxSize)	`field("MY_RAW", RAW(256))`

Most fields take no input; some fields have optional size inputs, and ENUM needs to have its values specified.

STRING defaults to a max size of 64 unless specified
BIGDECIMAL defaults to a database-specific precision and scale

ENUMs with json aliases

In the JVM world, we like to see enum names follow the UPPER_SNAKE_CASE convention. But often, an ENUM field ends up driving a user selection on the front end. So, if you want to present something more human-readable, you could use the following syntax:

field(
    "TRADE_STATUS",
    ENUM("ACCEPTED" to "Trade Accepted", "REJECTED" to "Trade Rejected")
)

With this syntax, your enum values will be ACCEPTED and REJECTED in the back end and in the database. However, in the front end, and JSON schema, the values will be rendered as Trade Accepted and Trade Rejected.

Providing a default value

You can set a default value for a field. In the example below the field is set to DEFAULT if no value is provided. Fields with default values are optional, but not nullable.

field("MY_FIELD").default("DEFAULT")

ENUM fields must always have a default value. This will be set to the first value in the ENUM(...) call. For example, below the MY_ENUM has a default of YES

field("MY_ENUM", ENUM("YES", "NO"))

However, this can be overwritten to NO:

field("MY_ENUM", ENUM("YES", "NO")).default("NO")

Making a field not null

Fields are non-nullable if they:

are included in an index
have a default value
have the .notNull() modifier

Fields with a default are optional; all other non-nullable fields are required. In the example below, we have set PRICE to be non-nullable:

field("PRICE", DOUBLE).notNull()

Marking a field as sensitive

Adding the .sensitive() modifier to a field will mask the field whenever toString() is called on the entity. For example, if the entity is passed to a logger.

field("MY_SECRET").sensitive()

Primary key and other indices

By default, a table is a set of columns (fields) where you can insert and maintain a number of rows (records). You need to provide ways to query the database so that a specific record or records can be found. So you need to define one or more indices. Creating good indices is essential to the quality and usability of an application, and we look at a simple example of this at the end of this page.

The most important index (plural: indices) is called the primary key. This is mandatory, and must be defined by one or more fields which have a unique value or combination of values.

In addition to primary keys, there are two types of index that you can specify:

A unique index ensures that no two records in the table can have the same value for the specified field or fields. For example, this could be a TRADE_ID in a TRADES table, where the unique value is generated via autoIncrement or sequence. Or it could be a CURRENCY_SYMBOL in a table of CURRENCIES.
A non-unique index is useful when uniqueness is not important or not possible. For example, you could add a non-unique index on the ORDER_ID field in your TRADE table. This enables you to find all the trades that match a specified order. (ORDER_ID would be unique in the ORDERS table, but the order can be filled by multiple trades.)

Below, we have added a simple primary key to the example above. It is on the POSITION_ID field, so that we can find a specific record or range of records. It is common to use a single sequenced/autoIncrement field as a primaryKey in this way:

table(name = "POSITION", id = 11002) {
    field("ID", INT).primaryKey()
    field("INSTRUMENT_ID")
    field("QUANTITY", INT)
    field("PRICE", DOUBLE).notNull()
    field("NOTIONAL", DOUBLE)

}

As you can see, you can add primary keys and indices on single fields as modifiers. But there are occasions where you might want to declare your indices separately.

When you add a field to an index, that automatically makes the field non-nullable.

Inline indices

There are two ways to add indices to your table:

You can add them inline in the field definition.
Or you can define an index explicitly.

To add an index to a field inline, use one of these modifiers:

Modifier	Effect	Restriction on data	Limitation on table
`.primaryKey()`	Creates a primary key	Unique	Exactly once
`.uniqueIndex()`	Creates a unique index	Unique	Within reason
`.nonUniqueIndex()`	Creates a non-unique index	No Restriction	Within reason

Here is an example of a table that includes all these index types:

table(name = "TRADE", id = 11002) {
    field("ID", INT).autoIncrement().primaryKey()
    field("EXTERNAL_ID").uniqueIndex()
    field("INSTRUMENT_ID").nonUniqueIndex()
    field("QUANTITY", INT)
    field("PRICE", DOUBLE).notNull()
    field("NOTIONAL", DOUBLE)

}

Let's look more closely at this example.

The primary key is on ID; this means that we can read the table by ID.
The same is true for EXTERNAL_ID, which is the external trade identifier.
When you look up a record by ID or EXTERNAL_ID, the database is able to return a record with the value supplied if it exists. At the same time, the database actively ensures that no two records have the same ID or EXTERNAL_ID; this prevents duplication and ensures data integrity.
There is a non-unique index on INSTRUMENT_ID. This means that you can return all trades related to a specified instrument. A non-unique index simply provides a way of searching the table quickly; it provides usability. Non-unique indices make no data integrity guarantees, other than to ensure that a value is provided.

Explicit indices

For some requirements, you must specify indices explicitly - separate from the main field definitions. These requirements are:

specifying an index with more than one field.
specifying a name for the index (which overrides the auto-generated name).

You cannot do either of those things if you specify the index inline.

The table below has exactly the same specification as the previous example, except that the indices have been specified explicitly -- after the field definitions.

table(name = "TRADE", id = 11002) {
    field("ID", INT).autoIncrement()
    field("EXTERNAL_ID")
    field("INSTRUMENT_ID")
    field("QUANTITY", INT)
    field("PRICE", DOUBLE).notNull()
    field("NOTIONAL", DOUBLE)

    primaryKey("ID")

    indices {
        unique("EXTERNAL_ID")
        nonUnique("INSTRUMENT_ID")
    }

}

Indices with multiple fields

Any index you create - the primary key or other unique or non-unique indices - can have multiple fields. This is useful where you need to ensure uniqueness across those fields, or if you want to look up data across multiple files.

Multiple fields are only supported by explicit indices.

table(name = "ORDERS", id = 21003) {
    field("ORDER_ID", INT)
    field("CUSTOMER_ID")
    field("CUSTOMER_ORDER_REF")
    field("PRODUCT_ID")
    field("PRICE", DOUBLE)
    field("QUANTITY", INT)

    primaryKey("ORDER_ID")

    indices {
        unique("CUSTOMER_ID", "CUSTOMER_ORDER_REF")
        nonUnique("PRODUCT_ID", "CUSTOMER_ID")
    }
}

In the above definition, CUSTOMER_ORDER_REF is a field that could hold a unique order reference number or ID provided by the customer when they place the order.

The unique index on CUSTOMER_ID and CUSTOMER_ORDER_REF ensures that the same customer cannot place two different orders with the same reference number.

Also, the index on CUSTOMER_ID and PRODUCT_ID enables us to find which customers have ordered a specific product.

The example above has a primary key with a single field. But the syntax is the same for primary keys that have multiple keys:

table(name = "ORDERS", id = 21003) {
    field("ORDER_ID", INT)
    field("CUSTOMER_ID")
    field("CUSTOMER_ORDER_REF")
    field("PRODUCT_ID")
    field("PRICE", DOUBLE)
    field("QUANTITY", INT)

    primaryKey("CUSTOMER_ID", "CUSTOMER_ORDER_REF")
}

Default index name

By default, names are auto-generated for all indices. In most cases, these names work well.

The name matters because it is how you refer to the index in your code; for more information see operations and indices

Names are auto-generated in the following format: [TABLE_NAME]_BY_[FIELD_1](_[FIELD_N]).

Here, FIELD_1 - FIELD_N represents all the fields that constitute the key or index, specified in order.

There is an exception. If the field names start with the table name (e.g., POSITION and POSITION_ID), the table name gets stripped. Consequently, the index will be called POSITION_BY_ID.

Let's see some examples. Below, the table name is POSITION. The generated names are shown in the comment above each primary key.

//POSITION_BY_ID
field("POSITION_ID").primaryKey()
// - or -
primaryKey("POSITION_ID")

//POSITION_BY_OTHER_FIELD
field("OTHER_FIELD").primaryKey()
// - or -
primaryKey("OTHER_FIELD")

//POSITION_BY_OTHER_FIELD_ANOTHER_FIELD
primaryKey("OTHER_FIELD", "ANOTHER_FIELD")

Setting an index name

If you do not like the default name generated for an index, you can go back and specify a more useful name.

To do this, you must specify the index explicitly. You can then use the .name(...) function to assign a name:

table(name = "TRADE", id = 11002) {
    field("ID", INT)
    field("EXTERNAL_ID")
    field("INSTRUMENT_ID")
    field("QUANTITY", INT)
    field("PRICE", DOUBLE)
    field("NOTIONAL", DOUBLE)

    primaryKey("ID").name("TRADE_BY_PRIMARY_KEY")

    indices {
        unique("EXTERNAL_ID").name("TRADE_BY_EXTN")
        nonUnique("INSTRUMENT_ID").name("TRADE_BY_INSTRUMENT")
    }

}

Index example

Good indices make your tables more useful. They are a vital aspect of the quality of an application. Consider the following scenario where you have projects and users.

You have a PROJECTS table where each project has a unique PROJ_ID.
You have a USERS table where each project has a unique USER_ID.

There is a many-to-many relationship between the two tables - projects can have more than one user, and users can belong to more than one project.

Now, if you want to allocate users to projects, you need a third table: PROJECT_USERS.

In the PROJECT_USERS table, you only need two fields:

PROJ_ID
USER_ID

Each record in this table states that PROJ_ID = x has USER_ID = y.

It is essential that each unique combination of PROJ_ID and USER_ID can only occur once in the table. So, we define the primary key on PROJ_ID and USER_ID. We can rely on the database to ensure uniqueness; any attempt to insert a record with a PROJ_ID and USER_ID combination that already exists will fail.

Here is our table definition for PROJECT_USERS:

table(name = "PROJECT_USERS", id = 11020) {
    field("PROJ_ID")
    field("USER_ID").nonUniqueIndex()

    primaryKey("PROJ_ID", "USER_ID")
}

In the above example:

To find the users associated with a specific project, you can make a query on the primary key specifying only the PROJ_ID.
To find if a specific user is associated with a specific project, you can make query on the primary key specifying both the PROJ_ID and USER_ID.
However, you cannot skip a field and query the primary key on only USER_ID. So, to make this possible, we have provided a non-unique index on USER_ID. This enables us to find all projects associated with a specific user.

Field types​

ENUMs with json aliases​

Providing a default value​

Making a field not null​

Marking a field as sensitive​

Primary key and other indices​

Inline indices​

Explicit indices​

Indices with multiple fields​

Default index name​

Setting an index name​

Index example​