Data Types - OmniQL

All data types supported by OmniQL and their database mappings.

Type Mappings

OmniQL	PostgreSQL	MySQL	MongoDB
`AUTO`	`SERIAL`	`INT AUTO_INCREMENT`	`ObjectId`
`BIGAUTO`	`BIGSERIAL`	`BIGINT AUTO_INCREMENT`	`ObjectId`
`INT`	`INTEGER`	`INT`	`Int32`
`BIGINT`	`BIGINT`	`BIGINT`	`Int64`
`SMALLINT`	`SMALLINT`	`SMALLINT`	`Int32`
`DECIMAL`	`DECIMAL`	`DECIMAL`	`Decimal128`
`NUMERIC`	`NUMERIC`	`DECIMAL`	`Decimal128`
`FLOAT`	`DOUBLE PRECISION`	`DOUBLE`	`Double`
`REAL`	`REAL`	`FLOAT`	`Double`
`STRING`	`VARCHAR`	`VARCHAR(255)`	`String`
`TEXT`	`TEXT`	`TEXT`	`String`
`CHAR`	`CHAR`	`CHAR`	`String`
`BOOLEAN`	`BOOLEAN`	`BOOLEAN`	`Boolean`
`BOOL`	`BOOLEAN`	`BOOLEAN`	`Boolean`
`TIMESTAMP`	`TIMESTAMP`	`TIMESTAMP`	`Date`
`DATETIME`	`TIMESTAMP`	`DATETIME`	`Date`
`DATE`	`DATE`	`DATE`	`Date`
`TIME`	`TIME`	`TIME`	`String`
`JSON`	`JSON`	`JSON`	`Object`
`JSONB`	`JSONB`	`JSON`	`Object`
`UUID`	`UUID`	`CHAR(36)`	`UUID`
`BINARY`	`BYTEA`	`BLOB`	`BinData`
`BLOB`	`BYTEA`	`BLOB`	`BinData`

Numeric Types

AUTO

Auto-incrementing primary key.

:CREATE TABLE User WITH id:AUTO, name:STRING

Database	Output
PostgreSQL	`CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR)`
MySQL	`CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))`

BIGAUTO

Auto-incrementing for large tables.

:CREATE TABLE Event WITH id:BIGAUTO, name:STRING

Database	Output
PostgreSQL	`id BIGSERIAL PRIMARY KEY`
MySQL	`id BIGINT AUTO_INCREMENT PRIMARY KEY`

INT

Standard integer (-2,147,483,648 to 2,147,483,647).

:CREATE TABLE Product WITH id:AUTO, quantity:INT

BIGINT

Large integer for big numbers.

:CREATE TABLE Analytics WITH id:AUTO, views:BIGINT

SMALLINT

Small integer (-32,768 to 32,767).

:CREATE TABLE Rating WITH id:AUTO, score:SMALLINT

DECIMAL / NUMERIC

Exact numeric with precision.

:CREATE TABLE Product WITH id:AUTO, price:DECIMAL
:CREATE TABLE Product WITH id:AUTO, price:DECIMAL(10,2)

Database	Output
PostgreSQL	`price DECIMAL(10,2)`
MySQL	`price DECIMAL(10,2)`

FLOAT

Double-precision floating point.

:CREATE TABLE Sensor WITH id:AUTO, temperature:FLOAT

Database	Output
PostgreSQL	`temperature DOUBLE PRECISION`
MySQL	`temperature DOUBLE`

REAL

Single-precision floating point.

:CREATE TABLE Measurement WITH id:AUTO, value:REAL

Database	Output
PostgreSQL	`value REAL`
MySQL	`value FLOAT`

String Types

STRING

Variable-length string.

:CREATE TABLE User WITH id:AUTO, name:STRING
:CREATE TABLE User WITH id:AUTO, name:STRING(100)

Database	Output
PostgreSQL	`name VARCHAR` or `name VARCHAR(100)`
MySQL	`name VARCHAR(255)` or `name VARCHAR(100)`

TEXT

Unlimited length text.

:CREATE TABLE Post WITH id:AUTO, content:TEXT

CHAR

Fixed-length string.

:CREATE TABLE Country WITH code:CHAR(2), name:STRING

Boolean Types

BOOLEAN / BOOL

True or false values.

:CREATE TABLE User WITH id:AUTO, active:BOOLEAN
:CREATE TABLE User WITH id:AUTO, verified:BOOL

Database	Output
PostgreSQL	`active BOOLEAN`
MySQL	`active BOOLEAN`
MongoDB	`Boolean`

Boolean in Queries

:GET User WHERE active = true
:GET User WHERE verified = false
:UPDATE User SET active = true WHERE id = 1

Date and Time Types

TIMESTAMP

Date and time.

:CREATE TABLE User WITH id:AUTO, created_at:TIMESTAMP

Database	Output
PostgreSQL	`created_at TIMESTAMP`
MySQL	`created_at TIMESTAMP`
MongoDB	`Date`

DATETIME

Date and time (MySQL uses DATETIME, PostgreSQL uses TIMESTAMP).

:CREATE TABLE Event WITH id:AUTO, event_time:DATETIME

Database	Output
PostgreSQL	`event_time TIMESTAMP`
MySQL	`event_time DATETIME`

DATE

Date only (no time).

:CREATE TABLE Event WITH id:AUTO, event_date:DATE

TIME

Time only (no date).

:CREATE TABLE Schedule WITH id:AUTO, start_time:TIME

Date Literals in Queries

:GET Order WHERE created_at > "2024-01-01"
:GET Order WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"

JSON Types

JSON

Standard JSON data.

:CREATE TABLE User WITH id:AUTO, metadata:JSON

Database	Output
PostgreSQL	`metadata JSON`
MySQL	`metadata JSON`
MongoDB	`Object` (native)

JSONB

PostgreSQL optimized binary JSON.

:CREATE TABLE User WITH id:AUTO, metadata:JSONB

Database	Output
PostgreSQL	`metadata JSONB`
MySQL	`metadata JSON`

UUID Type

Universally unique identifier.

:CREATE TABLE User WITH id:UUID, name:STRING

Database	Output
PostgreSQL	`id UUID`
MySQL	`id CHAR(36)`
MongoDB	`UUID`

UUID in Queries

:GET User WHERE id = "550e8400-e29b-41d4-a716-446655440000"

Binary Types

BINARY / BLOB

Binary data (files, images).

:CREATE TABLE File WITH id:AUTO, content:BINARY
:CREATE TABLE Image WITH id:AUTO, data:BLOB

Database	Output
PostgreSQL	`content BYTEA`
MySQL	`content BLOB`
MongoDB	`BinData`

Type with Constraints

Use colons to add constraints after the type:

:CREATE TABLE User WITH
  id:AUTO,
  email:STRING:NOTNULL:UNIQUE,
  name:STRING:NOTNULL,
  role:STRING

Constraint	Syntax	Effect
Not Null	`:NOTNULL`	Column cannot be NULL
Unique	`:UNIQUE`	Values must be unique
Primary Key	`:PRIMARYKEY`	Column is primary key

See Advanced Schema for more on constraints.

Type with Size

Specify size in parentheses:

:CREATE TABLE User WITH
  id:AUTO,
  name:STRING(100),
  code:CHAR(2),
  price:DECIMAL(10,2)

Limitations

Not currently supported:

DEFAULT values
CHECK constraints
Array types
Type casting

For these features, use native SQL.

Next Steps

Tables

Create tables with types

Operators

Query operators

​Type Mappings

​Numeric Types

​AUTO

​BIGAUTO

​INT

​BIGINT

​SMALLINT

​DECIMAL / NUMERIC

​FLOAT

​REAL

​String Types

​STRING

​TEXT

​CHAR

​Boolean Types

​BOOLEAN / BOOL

​Boolean in Queries

​Date and Time Types

​TIMESTAMP

​DATETIME

​DATE

​TIME

​Date Literals in Queries

​JSON Types

​JSON

​JSONB

​UUID Type

​UUID in Queries

​Binary Types

​BINARY / BLOB

​Type with Constraints

​Type with Size

​Limitations

​Next Steps