Documentation Index
Fetch the complete documentation index at: https://docs.omniql.com/llms.txt
Use this file to discover all available pages before exploring further.
Create and manage tables using DDL operations.
Create Table
:CREATE TABLE Entity WITH column:TYPE, column:TYPE:CONSTRAINT
Basic Table
:CREATE TABLE User WITH id:AUTO, name:STRING, email:STRING
| Database | Output |
|---|
| PostgreSQL | CREATE TABLE users (id SERIAL, name VARCHAR, email VARCHAR) |
| MySQL | CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255)) |
| MongoDB | Creates collection on first insert |
Data Types
| OmniQL Type | 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 |
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 |
DECIMAL | DECIMAL | DECIMAL | Decimal128 |
NUMERIC | NUMERIC | DECIMAL | Decimal128 |
FLOAT | DOUBLE PRECISION | DOUBLE | Double |
REAL | REAL | FLOAT | Double |
BINARY | BYTEA | BLOB | BinData |
BLOB | BYTEA | BLOB | BinData |
With Size
:CREATE TABLE User WITH id:AUTO, name:STRING(100), description:TEXT
With Constraints
Constraints are added after the type using colons:
:CREATE TABLE User WITH id:AUTO, name:STRING:NOTNULL, email:STRING:UNIQUE
| Database | Output |
|---|
| PostgreSQL | CREATE TABLE users (id SERIAL, name VARCHAR NOT NULL, email VARCHAR UNIQUE) |
Multiple Constraints
:CREATE TABLE User WITH id:AUTO, email:STRING:NOTNULL:UNIQUE
Complete Example
:CREATE TABLE User WITH
id:AUTO,
email:STRING:NOTNULL:UNIQUE,
name:STRING:NOTNULL,
active:BOOLEAN,
created_at:TIMESTAMP
Drop Table
| Database | Output |
|---|
| PostgreSQL | DROP TABLE users |
| MySQL | DROP TABLE users |
| MongoDB | db.users.drop() |
Alter Table
Add Column
:ALTER TABLE User ADD name:STRING
| Database | Output |
|---|
| PostgreSQL | ALTER TABLE users ADD COLUMN name VARCHAR |
Drop Column
:ALTER TABLE User DROP name
Rename Column
:ALTER TABLE User RENAME name:full_name
Modify Column Type
:ALTER TABLE User MODIFY name:TEXT
Rename Table
:RENAME TABLE User TO Customer
| Database | Output |
|---|
| PostgreSQL | ALTER TABLE users RENAME TO customers |
| MySQL | RENAME TABLE users TO customers |
MongoDB Collections
For MongoDB, use COLLECTION instead of TABLE:
:CREATE COLLECTION User
:DROP COLLECTION User
Next Steps
Views
Create virtual tables
Indexes
Optimize query performance
