Skip to main content

SQL Column Types

MySQL​

Bit​

The bit type allows creating BIT columns. An optional size attribute allows controlling the number of bits stored in a column, ranging from 1 to 64.

table "t" {
schema = schema.test
column "c1" {
type = bit
}
column "c2" {
type = bit(4)
}
}

Binary​

The varbinary and binary types allow storing binary byte strings.

table "t" {
schema = schema.test
column "c1" {
// Equals to binary(1).
type = binary
}
column "c2" {
type = binary(10)
}
column "c3" {
type = varbinary(255)
}
}

Blob​

The tinyblob, mediumblob, blob and longblob types allow storing binary large objects.

table "t" {
schema = schema.test
column "c1" {
type = tinyblob
}
column "c2" {
type = mediumblob
}
column "c3" {
type = blob
}
column "c4" {
type = longblob
}
}

Boolean​

The bool and boolean types are mapped to tinyint(1) in MySQL. Still, Atlas allows maintaining columns of type bool in the schema for simplicity reasons.

table "t" {
schema = schema.test
column "c1" {
type = bool
}
column "c2" {
type = boolean
}
}

Learn more about the motivation for these types in the MySQL website.

Date and Time​

Atlas supports the standard MySQL types for storing date and time values: time, timestamp, date, datetime, and year.

table "t" {
schema = schema.test
column "c1" {
type = time
}
column "c2" {
type = timestamp
}
column "c3" {
type = date
}
column "c4" {
type = datetime
}
column "c5" {
type = year
}
column "c6" {
type = time(1)
}
column "c7" {
type = timestamp(2)
}
column "c8" {
type = datetime(4)
}
}

Fixed Point (Decimal)​

The decimal and numeric types are supported for storing exact numeric values. Note that in MySQL the two types are identical.

table "t" {
schema = schema.test
column "c1" {
// Equals to decimal(10) as the
// default precision is 10.
type = decimal
}
column "c2" {
// Equals to decimal(5,0).
type = decimal(5)
}
column "c3" {
type = decimal(5,2)
}
column "c4" {
type = numeric
unsigned = true
}
}

Floating Point (Float)​

The float and double types are supported for storing approximate numeric values.

table "t" {
schema = schema.test
column "c1" {
type = float
}
column "c2" {
type = double
}
column "c3" {
type = float
unsigned = true
}
column "c4" {
type = double
unsigned = true
}
}

Enum​

The enum type allows storing a set of enumerated values.

table "t" {
schema = schema.test
column "c1" {
type = enum("a", "b")
}
column "c2" {
type = enum(
"c",
"d",
)
}
}

Integer​

The tinyint, smallint, int, mediumint, bigint integer types are support by Atlas.

table "t" {
schema = schema.test
column "c1" {
type = int
}
column "c2" {
type = tinyint
}
column "c3" {
type = smallint
}
column "c4" {
type = mediumint
}
column "c5" {
type = bigint
}
}

Integer Attributes​

The auto_increment, and unsigned attributes are also supported by integer types.

table "t" {
schema = schema.test
column "c1" {
type = tinyint
unsigned = true
}
column "c2" {
type = smallint
auto_increment = true
}
primary_key {
columns = [column.c2]
}
}

JSON​

The json type allows storing JSON objects.

table "t" {
schema = schema.test
column "c1" {
type = json
}
}

Set​

The set type allows storing a set of values.

table "t" {
schema = schema.test
column "c1" {
type = set("a", "b")
}
column "c2" {
type = set(
"c",
"d",
)
}
}

String​

Atlas supports the standard MySQL types for storing string values. varchar, char, tinytext, mediumtext, text and longtext.

table "t" {
schema = schema.test
column "c1" {
type = varchar(255)
}
column "c2" {
type = char(1)
}
column "c3" {
type = tinytext
}
column "c4" {
type = mediumtext
}
column "c5" {
type = text
}
column "c6" {
type = longtext
}
}

Spatial​

The geometry, point, multipoint, linestring and the rest of the MySQL spatial types are supported by Atlas.

table "t" {
schema = schema.test
column "c1" {
type = geometry
}
column "c2" {
type = point
}
column "c3" {
type = multipoint
}
column "c4" {
type = linestring
}
}

PostgreSQL​

Array​

Atlas supports defining PostgreSQL array types using the sql function.

table "t" {
schema = schema.test
column "c1" {
type = sql("int[]")
}
column "c2" {
type = sql("text[]")
}
column "c3" {
type = sql("int ARRAY")
}
column "c4" {
type = sql("varchar(255)[]")
}
column "c5" {
// The current PostgreSQL implementation
// ignores any supplied array size limits.
type = sql("point[4][4]")
}
}

Bit​

The bit and bit varying types allow creating bit string columns.

table "t" {
schema = schema.test
column "c1" {
// Equals to bit(1).
type = bit
}
column "c2" {
type = bit(2)
}
column "c3" {
// Unlimited length.
type = bit_varying
}
column "c4" {
type = bit_varying(1)
}
}

Boolean​

The boolean type allows creating standard SQL boolean columns.

table "t" {
schema = schema.test
column "c1" {
type = boolean
}
column "c2" {
type = boolean
default = true
}
}

Binary​

The bytea type allows creating binary string columns.

table "t" {
schema = schema.test
column "c1" {
type = bytea
}
}

Date, Time and Interval​

Atlas supports the standard PostgreSQL types for creating date, time and interval columns.

table "t" {
schema = schema.test
column "c1" {
type = date
}
column "c2" {
// Equals to "time without time zone".
type = time
}
column "c3" {
// Equals to "time with time zone".
type = timetz
}
column "c4" {
// Equals "timestamp without time zone".
type = timestamp
}
column "c5" {
// Equals "timestamp with time zone".
type = timestamptz
}
column "c6" {
type = timestamp(4)
}
column "c7" {
type = interval
}
}

Enum​

The enum type allows storing a set of enumerated values.

enum "status" {
schema = schema.test
values = ["on", "off"]
}

table "t1" {
schema = schema.test
column "c1" {
type = enum.status
}
}

table "t2" {
schema = schema.test
column "c1" {
type = enum.status
}
}

Fixed Point (Decimal)​

The decimal and numeric types are supported for storing exact numeric values. Note that in PostgreSQL the two types are identical.

table "t" {
schema = schema.test
column "c1" {
// Equals to decimal.
type = numeric
}
column "c2" {
// Equals to decimal(5).
type = numeric(5)
}
column "c3" {
// Equals to decimal(5,2).
type = numeric(5,2)
}
}

Floating Point (Float)​

The real and double_precision types are supported for storing approximate numeric values.

table "t" {
schema = schema.test
column "c1" {
type = real
}
column "c2" {
type = double_precision
}
column "c3" {
// Equals to real when precision is between 1 to 24.
type = float(10)
}
column "c2" {
// Equals to double_precision when precision is between 1 to 24.
type = float(30)
}
}

Geometric​

Atlas supports the standard PostgreSQL types for creating geometric columns.

table "t" {
schema = schema.test
column "c1" {
type = circle
}
column "c2" {
type = line
}
column "c3" {
type = lseg
}
column "c4" {
type = box
}
column "c5" {
type = path
}
column "c6" {
type = polygon
}
column "c7" {
type = point
}
}

Integer​

The smallint, integer / int, bigint types allow creating integer types.

table "t" {
schema = schema.test
column "c1" {
type = smallint
}
column "c2" {
type = integer
}
column "c3" {
type = int
}
column "c4" {
type = bigint
default = 1
}
}

JSON​

The json and jsonb types allow creating columns for storing JSON objects.

table "t" {
schema = schema.test
column "c1" {
type = json
}
column "c2" {
type = jsonb
}
}

Money​

The money data type allows creating columns for storing currency amount with a fixed fractional precision.

table "t" {
schema = schema.test
column "c1" {
type = money
}
}

Network Address​

The inet, cidr, macaddr and macaddr8 types allow creating network address columns.

table "t" {
schema = schema.test
column "c1" {
type = inet
}
column "c2" {
type = cidr
}
column "c3" {
type = macaddr
}
column "c4" {
type = macaddr8
}
}

Range​

PostgreSQL supports the creation of range types for storing range of values of some element type. Learn more about them in the PostgreSQL website.

table "t" {
schema = schema.test
column "r1" {
type = int4range
}
column "r2" {
type = int8range
}
column "r3" {
type = numrange
}
column "r4" {
type = tsrange
}
column "r5" {
type = tstzrange
}
column "r6" {
type = daterange
}
column "r7" {
type = int4multirange
}
column "r8" {
type = int8multirange
}
column "r9" {
type = nummultirange
}
column "r10" {
type = tsmultirange
}
column "r11" {
type = tstzmultirange
}
column "r12" {
type = datemultirange
}
}

Serial​

PostgreSQL supports creating columns of types smallserial, serial, and bigserial. Note that these types are not actual types, but more like "macros" for creating non-nullable integer columns with sequences attached.

table "t" {
schema = schema.test
column "c1" {
type = smallserial
}
column "c2" {
type = serial
}
column "c3" {
type = bigserial
}
}

String​

The varchar, char, character_varying, character and text types allow creating string columns.

table "t" {
schema = schema.test
column "c1" {
// Unlimited length.
type = varchar
}
column "c2" {
// Alias to character_varying(255).
type = varchar(255)
}
column "c3" {
// Equals to char(1).
type = char
}
column "c4" {
// Alias to character(5).
type = char(5)
}
column "c5" {
type = text
}
}

The tsvector and tsquery data types are designed to store and query full text search. Learn more about them in the PostgreSQL website.

table "t" {
schema = schema.test
column "tsv" {
type = tsvector
}
column "tsq" {
type = tsquery
}
}

UUID​

The uuid data type allows creating columns for storing Universally Unique Identifiers (UUID).

table "t" {
schema = schema.test
column "c1" {
type = uuid
}
column "c2" {
type = uuid
default = sql("gen_random_uuid()")
}
}

XML​

The xml data type allows creating columns for storing XML data.

table "t" {
schema = schema.test
column "c1" {
type = xml
}
}

SQLite​

Values in SQLite are stored in one of the four native types: BLOB, INTEGER, NULL, TEXT and REAL. Still, Atlas supports variety of data types that are commonly used by ORMs. These types are mapped to column affinities based on the rules described in SQLite website.

Blob​

The blob data type allows creating columns with BLOB type affinity.

table "t" {
schema = schema.main
column "c" {
type = blob
}
}

Integer​

The int and integer data types allow creating columns with INTEGER type affinity.

table "t" {
schema = schema.main
column "c" {
type = int
}
}

Numeric​

The numeric and decimal data types allow creating columns with NUMERIC type affinity.

table "t" {
schema = schema.main
column "c" {
type = decimal
}
}

Text​

The text, varchar, clob, character and varying_character data types allow creating columns with text type affinity. i.e. stored as text strings.

table "t" {
schema = schema.main
column "c" {
type = text
}
}

Real​

The real, double, double_precision, and float data types allow creating columns with real type affinity.

table "t" {
schema = schema.main
column "c" {
type = real
}
}

Additional Types​

As mentioned above, Atlas supports variety of data types that are commonly used by ORMs. e.g. Ent.

table "t" {
schema = schema.main
column "c1" {
type = bool
}
column "c2" {
type = date
}
column "c3" {
type = datetime
}
column "c4" {
type = uuid
}
column "c5" {
type = json
}
}