Tip: For a faster, more streamlined experience, generate your database tables directly from a record type instead.
One of the most common uses for custom data types (CDTs) is for reading from and writing to a database table or view. CDTs are connected to the database through data stores. The CDT defines the data structure within Appian and how it maps to the database table, while the data store defines how Appian connects with the database.
If you don't have an existing database table, you can use Appian to generate a table for you. While this can be useful if you don't have strict requirements about the table structure, it also makes it very easy to generate an initial version of the table and then make further tweaks in the database. Appian can also update existing tables for you when you update the CDT.
If instead you have an existing database table or view that you need to connect to, see the Mapping CDTs to Pre-defined Database Tables page.
All data types that map to a database table must have a primary key defined. Without a primary key, the data store will fail to publish and Appian will offer to generate one for you. We do not recommend allowing Appian to generate the primary key column because it cannot be queried or referenced by processes, so it is impossible to update any existing value in the database table. Additionally, your data type must have an explicit primary key field in order to be used as the source for a record type.
Primary key values can be either explicitly set or autogenerated by the database. This will determine the behavior when you are attempting to write values to the database:
To set the primary key in your database table, click the primary key icon in the data type designer.
The following sections describe what the generated table and columns will look like based on the column configurations. This will give you an idea of what changes you may need to make to the database table after it has been generated.
The table name is generated based on the CDT name and each column name is generated based on the name of the corresponding field. The names of database tables and columns are truncated if they exceed 27 characters in length in order to maintain compatibility with all supported databases.
Long names (longer than 27 characters) are truncated in the following ways:
If you have two similar long names for columns, it is possible for the two column names to be truncated to the same value. Appian recommends keeping CDT and field names to less than 27 characters when letting Appian generate the table for you. You may also use the @Column(name="columnName") annotation to specify a particular column name before the table is generated.
The casing of table and column names can also differ from your CDT based on your database settings.
When Appian generates a database table for you, the column type is determined based on the type of the field. The column type that is used may differ based on whether you configure your CDT using the data type designer or have modified the XSD to use a different field type or to use JPA annotations.
If you're using the data type designer to create your CDT, the column types will correspond to the Appian types listed in the table below. For fields that are CDT types with a One-to-One, One-to-Many, or Many-to-One relationship defined, the type of the column corresponds to the type of the primary key of the nested CDT.
| Appian Type | MySQL/MariaDB Type | MS SQL Server Type | Oracle Type | PostgreSQL Type | DB2 Type |
|---|---|---|---|---|---|
| Text & Encrypted Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| Number (Integer) | integer | int | number(10,0) | int / int4 | integer |
| Number (Decimal) | double precision | double precision | float (126) | float8 | double |
| Boolean | tinyint(1) | tinyint | number(1,0) | bool | smallint |
| Date | date | datetime | date | date | date |
| Time | datetime (Since 6.6.1) |
datetime | datetime (Since 6.6.1) |
timestamp (no timezone) | datetime (Since 6.6.1) |
| Date and Time | datetime | datetime | timestamp | timestamp (no timezone) | timestamp |
When you create your CDT using the data type designer, each CDT field uses the default XSD type based on the Appian type of the field. If you use an XSD type other than the default, the type of your column will correspond to that XSD type, as described in the table below.
If you want Appian to create a column of a type other than those listed below, you will need to use the @Column(columnDefinition="DATABASE_COLUMN_TYPE") annotation to specify the database column type.
| XSD Type | Appian Type | MySQL/MariaDB Type | MS SQL Server Type | Oracle Type | PostgreSQl Type | DB2 Type |
|---|---|---|---|---|---|---|
| string | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| appian:EncryptedText* | Encrypted Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| boolean | Boolean | tinyint(1) | tinyint | number(1,0) | bool | smallint |
| decimal | Number (Decimal) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
| float | Number (Decimal) | float | float | float | float4 | double |
| double | Number (Decimal) | double precision | double precision | float (126) | float8 | double |
| duration | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | vachar(255) | nvarchar(255) |
| dateTime | Date and Time | datetime | datetime | timestamp | timestamp | timestamp |
| time | Time | datetime (Since 6.6.1) |
datetime | datetime (Since 6.6.1) |
timestamp | datetime (Since 6.6.1) |
| date | Date | date | datetime | date | date | date |
| gYearMonth | Text | date | datetime | date | date | date |
| gYear | Text | date | datetime | date | date | date |
| gMonthDay | Text | date | datetime | date | date | date |
| gDay | Text | date | datetime | date | date | date |
| gMonth | Text | date | datetime | date | date | date |
| hexBinary | Text | array table (tinyint) | array table (tinyint) | array table (number(3,0)) | array table(int2) | array table (smallint) |
| base64Binary | Text | array table (tinyint) | array table (tinyint) | array table (number(3,0)) | array table(int2) | array table (smallint) |
| anyURI | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| QName | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | vachar(255) | nvarchar(255) |
| NOTATION | Text | tinyblob | varbinary(255) | raw(255) | bytea | varchar(255) |
| normalizedString | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| token | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| language | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| NMTOKEN | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| NMTOKENS | Text(Multiple) | array table (varchar(255)) | array table (nvarchar(255)) | array table (varchar2(255 char)) | array table (varchar(255)) | array table (nvarchar(255)) |
| Name | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| NCName | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| ID | Text | varchar(255) not null primary key | nvarchar(255) not null primary key | varchar2(255 char) not null primary key | varchar(255) not null primary key | nvarchar(255) not null primary key |
| IDREF | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| IDREFS | Text(Multiple) | array table (varchar(255)) | array table (nvarchar(255)) | array table (varchar2(255 char)) | array table (varchar(255)) | array table (nvarchar(255)) |
| ENTITY | Text | varchar(255) | nvarchar(255) | varchar2(255 char) | varchar(255) | nvarchar(255) |
| ENTITIES | Text(Multiple) | array table (varchar(255)) | array table (nvarchar(255)) | array table (varchar2(255 char)) | array table (varchar(255)) | array table nvarchar(255) |
| int | Number (Integer) | integer | int | number(10,0) | int4 | integer |
| integer | Number (Integer) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
| nonNegativeInteger | Number (Integer) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
| nonPositiveInteger | Number (Integer) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
| negativeInteger | Number (Integer) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
| long | Number (Integer) | bigint | numeric(19,0) | number(19,0) | int8 | bigint |
| short | Number (Integer) | smallint | smallint | number(5,0) | int2 | smallint |
| byte | Number (Integer) | tinyint(4) | tinyint | number(3,0) | int2 | smallint |
| unsignedLong | Number (Integer) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
| unsignedInt | Number (Integer) | bigint | numeric(19,0) | number(19,0) | int8 | bigint |
| unsignedShort | Number (Integer) | integer | int | number(10,0) | int4 | integer |
| unsignedByte | Number (Integer) | smallint | smallint | number(5,0) | int2 | smallint |
| positiveInteger | Number (Integer) | decimal(19,2) | numeric(19,2) | number(19,2) | numeric(19,2) | decimal(19,2) |
Note: appian:EncryptedText indicates the Appian Encrypted Text data type, which does not have a corresponding XSD primitive type, but maps to the same column types as Text.
Any additional configurations that are configured through the data type designer such as primary key settings, text length, and relationship definitions will be reflected in your database table when it is initially generated. These configurations will also be applied to new columns when they are added to an existing table.
Some configurations, such as text length and whether a primary key value is autogenerated, will not be updated in the database table if they are modified after the table has been generated. You may need to make the corresponding changes in the database table itself.
If you've added additional JPA annotations to your CDT that are not configurable through the data type designer, those will also be reflected on your database table when it is initially generated and when adding new columns. Some annotations may not be reflected when updating existing columns once the table has been generated, so you may need to make the corresponding changes in the database table itself.
To generate a database table using Appian, you can create your CDT using the data type designer and Appian will create a table when you publish the data store. There are two main ways to do this, depending on how you plan to deploy the tables to higher environments:
If you will be creating new tables in higher environments using a script, but want the convenience of Appian generating the initial tables for you:
This will make it easy to create the initial table in the database, but still allow you to control the final database structure.
If you will be letting Appian create tables for you in each of your environments:
When using Appian to update existing database tables, you can update your CDT using the data type designer and allow Appian to generate a table when you publish the data store. There are two main ways to do this, depending on how you plan to deploy the tables to higher environments.
Remember that each time you update a CDT, all dependent objects are updated automatically to reference the latest version of the data type, including any data stores that are used to connect the CDT to a database. If you do not want Appian to modify the database for you, make sure that the data store does not have Automatically Update Database Schema enabled. See Data Stores for more information.
Before updating any data type, be sure you understand the impact it will have on objects that reference that data type, including running processes.
Caution: Individual fields within a CDT do not display when viewing the dependents for that CDT, so there is no way to reliably know what objects are using each field. Therefore, it can be very difficult to correctly update all objects when you change the name or type of a field, or delete a field. Be sure to thoroughly test your changes in order to ensure that you've updated all the appropriate objects.
Additionally, running processes cannot be updated to use the latest version of a CDT. Therefore, changes to a CDT, its underlying database structure, or related artifacts such as rules and subprocesses may cause the running processes to behave unexpectedly.
To update the CDT in Appian:
next to the field you want to remove.If you will be updating tables in higher environments using a script, use the following process:
If you will be letting Appian update tables for you in higher environments, use the following process:
Generating Database Tables from Custom Data Types (CDTs)
