# Permissions
Source: https://docs.omniql.com/control/permissions
Control access with GRANT and REVOKE
Control database access using DCL (Data Control Language) operations.
## Grant Permissions
```sql theme={}
:GRANT permission ON Entity TO user
```
### Read Permission
```sql theme={}
:GRANT READ ON User TO analyst
```
| Database | Output |
| ---------- | -------------------------------------------------- |
| PostgreSQL | `GRANT SELECT ON users TO analyst` |
| MySQL | `GRANT SELECT ON users.* TO 'analyst'@'localhost'` |
### Write Permission
```sql theme={}
:GRANT WRITE ON Order TO sales_app
```
| Database | Output |
| ---------- | --------------------------------------------- |
| PostgreSQL | `GRANT INSERT, UPDATE ON orders TO sales_app` |
### Delete Permission
```sql theme={}
:GRANT DELETE ON Log TO admin
```
### All Permissions
```sql theme={}
:GRANT ALL ON User TO admin
```
| Database | Output |
| ---------- | ---------------------------------------- |
| PostgreSQL | `GRANT ALL PRIVILEGES ON users TO admin` |
### Multiple Permissions
```sql theme={}
:GRANT READ, WRITE, DELETE ON Order TO app_service
```
### All Tables
```sql theme={}
:GRANT READ ON * TO analyst
```
## Revoke Permissions
```sql theme={}
:REVOKE permission ON Entity FROM user
```
### Single Permission
```sql theme={}
:REVOKE DELETE ON User FROM intern
```
| Database | Output |
| ---------- | ------------------------------------ |
| PostgreSQL | `REVOKE DELETE ON users FROM intern` |
### All Permissions
```sql theme={}
:REVOKE ALL ON User FROM former_employee
```
| Database | Output |
| ---------- | ----------------------------------------------------- |
| PostgreSQL | `REVOKE ALL PRIVILEGES ON users FROM former_employee` |
## User Management
### Create User
```sql theme={}
:CREATE USER john WITH PASSWORD secret123
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------ |
| PostgreSQL | `CREATE USER john WITH PASSWORD 'secret123'` |
| MySQL | `CREATE USER IF NOT EXISTS 'john'@'localhost' IDENTIFIED BY 'secret123'` |
### Alter User Password
```sql theme={}
:ALTER USER john WITH PASSWORD newsecret456
```
| Database | Output |
| ---------- | ------------------------------------------------------------ |
| PostgreSQL | `ALTER USER john WITH PASSWORD 'newsecret456'` |
| MySQL | `ALTER USER 'john'@'localhost' IDENTIFIED BY 'newsecret456'` |
### Drop User
```sql theme={}
:DROP USER john
```
| Database | Output |
| ---------- | ---------------------------------------- |
| PostgreSQL | `DROP USER IF EXISTS john` |
| MySQL | `DROP USER IF EXISTS 'john'@'localhost'` |
## Role Management
### Create Role
```sql theme={}
:CREATE ROLE analyst
```
| Database | Output |
| ---------- | ----------------------------------- |
| PostgreSQL | `CREATE ROLE analyst` |
| MySQL | `CREATE ROLE IF NOT EXISTS analyst` |
### Assign Role to User
```sql theme={}
:ASSIGN ROLE analyst TO john
```
| Database | Output |
| ---------- | --------------------------------------- |
| PostgreSQL | `GRANT analyst TO john` |
| MySQL | `GRANT 'analyst' TO 'john'@'localhost'` |
### Revoke Role from User
```sql theme={}
:REVOKE ROLE analyst FROM john
```
| Database | Output |
| ---------- | ------------------------------------------ |
| PostgreSQL | `REVOKE analyst FROM john` |
| MySQL | `REVOKE 'analyst' FROM 'john'@'localhost'` |
### Drop Role
```sql theme={}
:DROP ROLE analyst
```
| Database | Output |
| ---------- | ----------------------------- |
| PostgreSQL | `DROP ROLE IF EXISTS analyst` |
| MySQL | `DROP ROLE IF EXISTS analyst` |
## Permission Types
| OmniQL | PostgreSQL | MySQL | Description |
| -------- | ---------------- | ---------------- | ----------------- |
| `READ` | `SELECT` | `SELECT` | Read data |
| `WRITE` | `INSERT, UPDATE` | `INSERT, UPDATE` | Create and modify |
| `DELETE` | `DELETE` | `DELETE` | Remove records |
| `ALL` | `ALL PRIVILEGES` | `ALL PRIVILEGES` | Full access |
You can also use native permission names (SELECT, INSERT, UPDATE) directly.
## Complete Examples
### Read-Only Analyst
```sql theme={}
:CREATE ROLE analyst
:GRANT READ ON User TO analyst
:GRANT READ ON Order TO analyst
:GRANT READ ON Product TO analyst
:CREATE USER jane WITH PASSWORD analyst123
:ASSIGN ROLE analyst TO jane
```
### Application Service Account
```sql theme={}
:CREATE ROLE app_service
:GRANT READ, WRITE ON User TO app_service
:GRANT READ, WRITE ON Order TO app_service
:GRANT READ, WRITE ON Product TO app_service
:CREATE USER myapp WITH PASSWORD service456
:ASSIGN ROLE app_service TO myapp
```
### Admin User
```sql theme={}
:CREATE ROLE admin
:GRANT ALL ON User TO admin
:GRANT ALL ON Order TO admin
:GRANT ALL ON Product TO admin
:CREATE USER bob WITH PASSWORD admin789
:ASSIGN ROLE admin TO bob
```
## Database Support
| Feature | PostgreSQL | MySQL | MongoDB |
| ------------------ | ---------- | ----- | ------------ |
| GRANT/REVOKE | Yes | Yes | Via commands |
| CREATE/DROP USER | Yes | Yes | Via commands |
| ALTER USER | Yes | Yes | Via commands |
| CREATE/DROP ROLE | Yes | Yes | Via commands |
| ASSIGN/REVOKE ROLE | Yes | Yes | Via commands |
## MongoDB Note
MongoDB uses role-based access control with built-in roles. OmniQL translates to MongoDB admin commands.
```javascript theme={}
// MongoDB equivalent for CREATE USER
db.createUser({
user: "analyst",
pwd: "secret",
roles: [{ role: "read", db: "myapp" }]
});
```
## Limitations
Not currently supported:
* Column-level permissions
* Schema/database-level permissions
* Sequence permissions
* Role options (SUPERUSER, LOGIN, etc.)
* Multiple tables in single GRANT
## Next Steps
Group operations safely
PostgreSQL specifics
# Transactions
Source: https://docs.omniql.com/control/transactions
Group operations with ACID guarantees
Group multiple operations into atomic units.
## Basic Syntax
```sql theme={}
:BEGIN
:COMMIT
:ROLLBACK
```
## Simple Transaction
```sql theme={}
:BEGIN
:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2
:COMMIT
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------ |
| PostgreSQL | `BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;` |
| MySQL | `START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;` |
## Rollback
Undo all changes in the transaction.
```sql theme={}
:BEGIN
:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2
:ROLLBACK
```
## Savepoints
Create checkpoints within a transaction.
```sql theme={}
:BEGIN
:INSERT Order WITH user_id = 1, total = 99.99
:SAVEPOINT order_created
:INSERT OrderItem WITH order_id = 1, product_id = 5, quantity = 2
:INSERT OrderItem WITH order_id = 1, product_id = 8, quantity = 1
:COMMIT
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------------------- |
| PostgreSQL | `BEGIN; INSERT INTO orders ...; SAVEPOINT order_created; INSERT INTO order_items ...; COMMIT;` |
### Rollback to Savepoint
Undo changes back to a savepoint.
```sql theme={}
:ROLLBACK TO order_created
```
### Release Savepoint
Remove a savepoint (keep the changes).
```sql theme={}
:RELEASE SAVEPOINT order_created
```
## Isolation Levels
Control how transactions see each other's changes. Set isolation level before BEGIN.
```sql theme={}
:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
:BEGIN
:GET Product WHERE id = 1
:UPDATE Product SET quantity = quantity - 1 WHERE id = 1
:COMMIT
```
| Level | Description |
| ------------------ | --------------------------------------------------- |
| `READ UNCOMMITTED` | Can see uncommitted changes from other transactions |
| `READ COMMITTED` | Only sees committed changes (PostgreSQL default) |
| `REPEATABLE READ` | Consistent reads within transaction (MySQL default) |
| `SERIALIZABLE` | Full isolation, transactions appear sequential |
| Database | Output |
| ---------- | ---------------------------------------------------------------------- |
| PostgreSQL | `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; ...` |
| MySQL | `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; ...` |
## Complete Examples
### Money Transfer
```sql theme={}
:BEGIN
:UPDATE Account SET balance = balance - 500 WHERE id = 1 AND balance >= 500
:UPDATE Account SET balance = balance + 500 WHERE id = 2
:INSERT TransactionLog WITH from_account = 1, to_account = 2, amount = 500
:COMMIT
```
### Order with Savepoint
```sql theme={}
:BEGIN
:INSERT Order WITH user_id = 42, status = "pending", total = 0
:SAVEPOINT order_created
:INSERT OrderItem WITH order_id = 1, product_id = 5, quantity = 2, price = 29.99
:INSERT OrderItem WITH order_id = 1, product_id = 8, quantity = 1, price = 49.99
:UPDATE Order SET total = 109.97 WHERE id = 1
:UPDATE Product SET quantity = quantity - 2 WHERE id = 5
:UPDATE Product SET quantity = quantity - 1 WHERE id = 8
:COMMIT
```
### High Isolation Transaction
```sql theme={}
:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
:BEGIN
:GET Account WHERE id = 1
:UPDATE Account SET balance = balance - 100 WHERE id = 1
:COMMIT
```
## Database Support
| Feature | PostgreSQL | MySQL | MongoDB |
| --------------------- | ---------- | ----- | ------------------ |
| BEGIN/COMMIT/ROLLBACK | Yes | Yes | Yes (sessions) |
| SAVEPOINT | Yes | Yes | No |
| ROLLBACK TO | Yes | Yes | No |
| RELEASE SAVEPOINT | Yes | Yes | No |
| Isolation Levels | Yes | Yes | Yes (read concern) |
## MongoDB Note
MongoDB supports multi-document transactions in replica sets and sharded clusters (v4.0+). Savepoints are not supported.
```javascript theme={}
// MongoDB equivalent
session.startTransaction();
db.accounts.updateOne({ _id: 1 }, { $inc: { balance: -100 } });
db.accounts.updateOne({ _id: 2 }, { $inc: { balance: 100 } });
session.commitTransaction();
```
## Limitations
Not currently supported:
* BEGIN READ ONLY
* Inline isolation level (BEGIN ISOLATION LEVEL X)
* Nested transactions
## Next Steps
Control access
PostgreSQL specifics
# MongoDB
Source: https://docs.omniql.com/databases/mongodb
Using OmniQL with MongoDB
MongoDB is a document database that stores data in flexible, JSON-like documents.
## Quick Start
```go theme={}
import (
"context"
"go.mongodb.org/mongo-driver/mongo"
"go.mongodb.org/mongo-driver/mongo/options"
"github.com/omniql-engine/omniql"
)
// Your MongoDB connection
mongoClient, _ := mongo.Connect(context.Background(),
options.Client().ApplyURI("mongodb://localhost:27017"))
db := mongoClient.Database("mydb")
// Wrap with OmniQL
client := oql.WrapMongo(db)
// Query with OmniQL syntax
users, _ := client.Query(":GET User WHERE age > 21")
```
## Type Mappings
| OmniQL | MongoDB |
| ----------- | ------------ |
| `AUTO` | `ObjectId` |
| `BIGAUTO` | `ObjectId` |
| `STRING` | `String` |
| `TEXT` | `String` |
| `CHAR` | `String` |
| `INT` | `Int32` |
| `BIGINT` | `Int64` |
| `SMALLINT` | `Int32` |
| `DECIMAL` | `Decimal128` |
| `NUMERIC` | `Decimal128` |
| `FLOAT` | `Double` |
| `REAL` | `Double` |
| `BOOLEAN` | `Boolean` |
| `BOOL` | `Boolean` |
| `TIMESTAMP` | `Date` |
| `DATETIME` | `Date` |
| `DATE` | `Date` |
| `TIME` | `String` |
| `JSON` | `Object` |
| `JSONB` | `Object` |
| `UUID` | `UUID` |
| `BINARY` | `BinData` |
| `BLOB` | `BinData` |
## Entity Naming
OmniQL entities become collection names (lowercase):
| OmniQL | MongoDB |
| ----------- | ------------ |
| `User` | `users` |
| `Order` | `orders` |
| `OrderItem` | `orderitems` |
## Translation Examples
### CRUD Operations
**GET (find)**
```sql theme={}
:GET User WHERE id = 1
```
```javascript theme={}
db.users.find({ id: 1 })
```
```sql theme={}
:GET User WHERE age > 21 AND status = "active"
```
```javascript theme={}
db.users.find({ age: { $gt: 21 }, status: 'active' })
```
```sql theme={}
:GET id, name, email FROM User WHERE active = true
```
```javascript theme={}
db.users.find({ active: true }, { id: 1, name: 1, email: 1 })
```
**CREATE (insertOne)**
```sql theme={}
:CREATE User WITH name = "John", email = "john@example.com"
```
```javascript theme={}
db.users.insertOne({ name: 'John', email: 'john@example.com' })
```
**BULK INSERT (insertMany)**
```sql theme={}
:BULK INSERT User WITH [name = "Alice", age = 28] [name = "Bob", age = 32]
```
```javascript theme={}
db.users.insertMany([
{ name: 'Alice', age: 28 },
{ name: 'Bob', age: 32 }
])
```
**UPDATE (updateOne)**
```sql theme={}
:UPDATE User SET status = "active" WHERE id = 1
```
```javascript theme={}
db.users.updateOne({ id: 1 }, { $set: { status: 'active' } })
```
**Arithmetic in UPDATE**
```sql theme={}
:UPDATE User SET balance = balance + 100 WHERE id = 1
```
```javascript theme={}
db.users.updateOne({ id: 1 }, { $inc: { balance: 100 } })
```
```sql theme={}
:UPDATE Product SET price = price * 0.9 WHERE category = "sale"
```
```javascript theme={}
db.products.updateOne({ category: 'sale' }, { $mul: { price: 0.9 } })
```
**DELETE (deleteOne)**
```sql theme={}
:DELETE User WHERE id = 1
```
```javascript theme={}
db.users.deleteOne({ id: 1 })
```
**UPSERT**
```sql theme={}
:UPSERT User WITH email = "john@example.com", name = "John" ON email
```
```javascript theme={}
db.users.updateOne(
{ email: 'john@example.com' },
{ $set: { email: 'john@example.com', name: 'John' } },
{ upsert: true }
)
```
**REPLACE (replaceOne)**
```sql theme={}
:REPLACE User WITH id = 1, name = "John", email = "john@example.com"
```
```javascript theme={}
db.users.replaceOne({ id: 1 }, { name: 'John', email: 'john@example.com' })
```
### Filtering (Operators)
| OmniQL | MongoDB |
| ------------- | ----------------- |
| `=` | `$eq` |
| `!=` | `$ne` |
| `>` | `$gt` |
| `>=` | `$gte` |
| `<` | `$lt` |
| `<=` | `$lte` |
| `IN` | `$in` |
| `NOT IN` | `$nin` |
| `LIKE` | `$regex` |
| `IS NULL` | `$eq: null` |
| `IS NOT NULL` | `$ne: null` |
| `AND` | implicit / `$and` |
| `OR` | `$or` |
**Examples:**
```sql theme={}
:GET User WHERE role IN ("admin", "moderator")
```
```javascript theme={}
db.users.find({ role: { $in: ['admin', 'moderator'] } })
```
```sql theme={}
:GET User WHERE age BETWEEN 18 AND 65
```
```javascript theme={}
db.users.find({ age: { $gte: 18, $lte: 65 } })
```
```sql theme={}
:GET User WHERE name LIKE "John%"
```
```javascript theme={}
db.users.find({ name: { $regex: /^John/, $options: 'i' } })
```
```sql theme={}
:GET User WHERE role = "admin" OR role = "moderator"
```
```javascript theme={}
db.users.find({ $or: [{ role: 'admin' }, { role: 'moderator' }] })
```
### Pagination
```sql theme={}
:GET User ORDER BY created_at DESC LIMIT 10 OFFSET 20
```
```javascript theme={}
db.users.find({}).sort({ created_at: -1 }).skip(20).limit(10)
```
### Aggregation
**COUNT**
```sql theme={}
:COUNT * FROM User WHERE active = true
```
```javascript theme={}
db.users.aggregate([
{ $match: { active: true } },
{ $group: { _id: null, result: { $sum: 1 } } }
])
```
**GROUP BY**
```sql theme={}
:COUNT * FROM User GROUP BY status
```
```javascript theme={}
db.users.aggregate([
{ $group: { _id: '$status', result: { $sum: 1 } } }
])
```
**SUM, AVG, MIN, MAX**
```sql theme={}
:SUM total FROM Order WHERE status = "completed"
```
```javascript theme={}
db.orders.aggregate([
{ $match: { status: 'completed' } },
{ $group: { _id: null, result: { $sum: '$total' } } }
])
```
**HAVING**
```sql theme={}
:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10
```
```javascript theme={}
db.users.aggregate([
{ $group: { _id: '$status', result: { $sum: 1 } } },
{ $match: { result: { $gt: 10 } } }
])
```
### Joins (\$lookup)
OmniQL joins translate to MongoDB's `$lookup` aggregation:
```sql theme={}
:INNER JOIN Order User ON Order.user_id = User.id
```
```javascript theme={}
db.orders.aggregate([
{
$lookup: {
from: 'users',
localField: 'user_id',
foreignField: '_id',
as: 'users_joined'
}
},
{ $unwind: '$users_joined' }
])
```
```sql theme={}
:LEFT JOIN Order User ON Order.user_id = User.id
```
```javascript theme={}
db.orders.aggregate([
{
$lookup: {
from: 'users',
localField: 'user_id',
foreignField: '_id',
as: 'users_joined'
}
},
{ $unwind: { path: '$users_joined', preserveNullAndEmptyArrays: true } }
])
```
### Window Functions (MongoDB 5.0+)
```sql theme={}
:ROW NUMBER OVER (PARTITION BY department ORDER BY salary) FROM Employee
```
```javascript theme={}
db.employees.aggregate([
{
$setWindowFields: {
partitionBy: '$department',
sortBy: { salary: 1 },
output: {
rowNumber: { $documentNumber: {} }
}
}
}
])
```
**Supported window functions:**
* ROW NUMBER → `$documentNumber`
* RANK → `$rank`
* DENSE RANK → `$denseRank`
* LAG/LEAD → `$shift`
### Set Operations (MongoDB 4.4+)
```sql theme={}
:UNION (GET User WHERE age > 50) (GET User WHERE role = "premium")
```
```javascript theme={}
db.users.aggregate([
{ $match: { age: { $gt: 50 } } },
{ $unionWith: { coll: 'users', pipeline: [{ $match: { role: 'premium' } }] } }
])
```
### CASE Expressions
```sql theme={}
:GET User WITH CASE WHEN age > 25 THEN "adult" ELSE "minor" END AS category
```
```javascript theme={}
db.users.aggregate([
{
$project: {
category: {
$switch: {
branches: [
{ case: { $gt: ['$age', 25] }, then: 'adult' }
],
default: 'minor'
}
}
}
}
])
```
### Transactions (Replica Set Required)
```sql theme={}
:BEGIN
:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2
:COMMIT
```
```javascript theme={}
session.startTransaction();
db.accounts.updateOne({ id: 1 }, { $inc: { balance: -100 } });
db.accounts.updateOne({ id: 2 }, { $inc: { balance: 100 } });
session.commitTransaction();
```
### Permissions
**CREATE USER:**
```sql theme={}
:CREATE USER analyst WITH PASSWORD secret123
```
```javascript theme={}
db.runCommand({ createUser: 'analyst', pwd: 'secret123', roles: [] })
```
**CREATE ROLE:**
```sql theme={}
:CREATE ROLE readonly
```
```javascript theme={}
db.runCommand({ createRole: 'readonly', privileges: [], roles: [] })
```
**GRANT ROLE:**
```sql theme={}
:ASSIGN ROLE readonly TO analyst
```
```javascript theme={}
db.runCommand({ grantRolesToUser: 'analyst', roles: ['readonly'] })
```
## Supported Operations
| Category | Operations | Support |
| ---------------- | --------------------------------------------------------- | ---------------- |
| CRUD | GET, CREATE, UPDATE, DELETE, UPSERT, BULK INSERT, REPLACE | Full |
| Filtering | All operators (=, !=, IN, BETWEEN, LIKE, IS NULL, etc.) | Full |
| Aggregation | COUNT, SUM, AVG, MIN, MAX | Full |
| Grouping | GROUP BY, HAVING | Full |
| Sorting | ORDER BY, LIMIT, OFFSET | Full |
| Joins | INNER, LEFT, RIGHT, FULL | Via \$lookup |
| Window Functions | ROW NUMBER, RANK, DENSE RANK, LAG, LEAD | MongoDB 5.0+ |
| Set Operations | UNION, UNION ALL | MongoDB 4.4+ |
| Expressions | Arithmetic (+, -, \*, /, %), CASE WHEN | Full |
| Functions | UPPER, LOWER, CONCAT, LENGTH, ABS, ROUND | Full |
| Transactions | BEGIN, COMMIT, ROLLBACK | Replica set only |
| DDL | CREATE/DROP COLLECTION, RENAME, CREATE VIEW | Full |
| DCL | CREATE/DROP USER, CREATE/DROP ROLE, GRANT, REVOKE | Full |
## Limitations
| Feature | Status | Notes |
| ------------------------ | ------------- | --------------------------------- |
| SAVEPOINT | Not supported | MongoDB has no savepoint concept |
| ROLLBACK TO | Not supported | No partial rollback |
| RELEASE SAVEPOINT | Not supported | No savepoints |
| INTERSECT | Not supported | Use aggregation workarounds |
| EXCEPT | Not supported | Use aggregation workarounds |
| CTEs | Not supported | Use aggregation pipelines instead |
| Single-node transactions | Not supported | Requires replica set |
## Version Requirements
| Feature | Minimum MongoDB Version |
| ----------------- | ----------------------- |
| Basic CRUD | 3.6+ |
| Transactions | 4.0+ (replica set) |
| \$unionWith | 4.4+ |
| \$setWindowFields | 5.0+ |
## Next Steps
Redis specifics
Type mappings
# Redis
Source: https://docs.omniql.com/databases/redis
Using OmniQL with Redis
Redis is an in-memory data store used for caching, sessions, and real-time applications.
## Quick Start
```go theme={}
import (
"github.com/redis/go-redis/v9"
"github.com/omniql-engine/omniql"
)
// Your Redis connection
rdb := redis.NewClient(&redis.Options{
Addr: "localhost:6379",
})
// Wrap with OmniQL
client := oql.WrapRedis(rdb, "tenant_1")
// Query with OmniQL syntax
user, _ := client.Query(":GET User WHERE id = 42")
```
## How Redis Works
Redis is **key-value**, not relational. It cannot filter data natively like SQL databases.
| Database | WHERE Filtering |
| ---------- | ------------------------- |
| PostgreSQL | Native (database does it) |
| MySQL | Native (database does it) |
| MongoDB | Native (database does it) |
| Redis | OmniQL handles it in Go |
OmniQL bridges this gap by providing filtering logic that works seamlessly with your queries.
## Data Model
OmniQL maps entities to Redis Hash structures:
| Concept | OmniQL | Redis |
| ------- | ------------------- | ----------------------- |
| Entity | `User` | Hash keys with prefix |
| Record | `User WHERE id = 1` | `HGETALL tenant:user:1` |
| Field | `name`, `email` | Hash fields |
### Key Pattern
```
{tenant}:{entity}:{id}
```
Examples:
```
tenant_1:user:1 → User with id 1
tenant_1:user:2 → User with id 2
tenant_1:order:1001 → Order with id 1001
```
## Query Types
### Direct Key Lookup (Fast)
When you query by `id`, OmniQL translates directly to Redis commands:
```go theme={}
// OmniQL
user, _ := client.Query(":GET User WHERE id = 42")
// Internally executes:
// HGETALL tenant_1:user:42
```
This is **instant** - same performance as native Redis.
### Filtered Query (Scan + Filter)
When you query by other fields, OmniQL scans keys and filters results:
```go theme={}
// OmniQL
users, _ := client.Query(":GET User WHERE age > 21 AND status = \"active\" LIMIT 10")
// Internally:
// 1. SCAN tenant_1:user:*
// 2. HGETALL each key
// 3. Filter using MatchesConditions()
// 4. Return matching results up to LIMIT
```
This works but scans data - use for smaller datasets or with LIMIT.
## CRUD Operations
### GET (HGETALL)
**By ID (direct lookup):**
```go theme={}
user, _ := client.Query(":GET User WHERE id = 1")
// → HGETALL tenant_1:user:1
```
**With filtering:**
```go theme={}
users, _ := client.Query(":GET User WHERE status = \"active\" LIMIT 10")
// → SCAN + HGETALL + filter
```
**All records:**
```go theme={}
users, _ := client.Query(":GET User")
// → SCAN tenant_1:user:* + HGETALL each
```
### CREATE (HMSET)
```go theme={}
result, _ := client.Query(`:CREATE User WITH name:"John", email:"john@example.com", age:30`)
// → HMSET tenant_1:user:{generated_id} name "John" email "john@example.com" age "30"
// result = []map[string]any{{"inserted_id": "uuid-here", "rows_affected": 1}}
```
### UPDATE (HSET)
```go theme={}
result, _ := client.Query(`:UPDATE User SET status:"active" WHERE id = 1`)
// → HSET tenant_1:user:1 status "active"
// result = []map[string]any{{"rows_affected": 1}}
```
### DELETE (DEL)
```go theme={}
result, _ := client.Query(`:DELETE User WHERE id = 1`)
// → DEL tenant_1:user:1
// result = []map[string]any{{"rows_affected": 1}}
```
### BULK INSERT
```go theme={}
result, _ := client.Query(`:BULK INSERT User WITH [name:"Alice", age:28], [name:"Bob", age:32]`)
// → HMSET tenant_1:user:1 name "Alice" age "28"
// → HMSET tenant_1:user:2 name "Bob" age "32"
```
### UPSERT
```go theme={}
result, _ := client.Query(`:UPSERT User WITH id:1, name:"John" ON id`)
// → HMSET tenant_1:user:1 name "John"
```
### DROP TABLE
```go theme={}
result, _ := client.Query(`:DROP TABLE User`)
// → Deletes all keys matching tenant_1:user:*
```
## Filtering Support
OmniQL supports all standard operators for Redis filtering:
| Operator | Example | Supported |
| ------------- | ------------------------------------- | --------- |
| `=` | `status = "active"` | ✅ |
| `!=` | `status != "banned"` | ✅ |
| `>` | `age > 21` | ✅ |
| `<` | `age < 65` | ✅ |
| `>=` | `score >= 100` | ✅ |
| `<=` | `price <= 50` | ✅ |
| `IN` | `status IN ("active", "pending")` | ✅ |
| `NOT IN` | `role NOT IN ("admin", "mod")` | ✅ |
| `BETWEEN` | `age BETWEEN 18 AND 65` | ✅ |
| `LIKE` | `name LIKE "John%"` | ✅ |
| `IS NULL` | `deleted_at IS NULL` | ✅ |
| `IS NOT NULL` | `email IS NOT NULL` | ✅ |
| `AND` | `age > 21 AND active = true` | ✅ |
| `OR` | `status = "active" OR role = "admin"` | ✅ |
### Example with Complex Filter
```go theme={}
users, _ := client.Query(`
:GET User
WHERE age > 21
AND status IN ("active", "pending")
AND email IS NOT NULL
ORDER BY name ASC
LIMIT 10
`)
```
## Aggregations
OmniQL provides aggregation operations:
### COUNT
```go theme={}
result, _ := client.Query(":COUNT User WHERE active = true")
// result = []map[string]any{{"count": 42}}
```
### SUM
```go theme={}
result, _ := client.Query(":SUM balance FROM Account")
// result = []map[string]any{{"sum": 15000.50}}
```
### AVG
```go theme={}
result, _ := client.Query(":AVG age FROM User")
// result = []map[string]any{{"avg": 28.5}}
```
### MIN / MAX
```go theme={}
result, _ := client.Query(":MIN price FROM Product")
// result = []map[string]any{{"min": 9.99}}
result, _ := client.Query(":MAX score FROM Player")
// result = []map[string]any{{"max": 99500}}
```
## Transactions
Redis supports transactions with MULTI/EXEC:
```go theme={}
client.Query(":BEGIN")
client.Query(`:UPDATE User SET login_count:5 WHERE id = 1`)
client.Query(`:UPDATE User SET last_login:"2025-01-15" WHERE id = 1`)
client.Query(":COMMIT")
```
Translates to:
```redis theme={}
MULTI
HSET tenant_1:user:1 login_count "5"
HSET tenant_1:user:1 last_login "2025-01-15"
EXEC
```
**Rollback:**
```go theme={}
client.Query(":BEGIN")
client.Query(`:UPDATE User SET status:"banned" WHERE id = 1`)
client.Query(":ROLLBACK") // Cancels - nothing executed
```
> **Note:** `DISCARD` cancels the transaction before execution. Once `EXEC` runs, changes cannot be rolled back.
## Permissions (ACL)
Redis uses ACL for user management:
### CREATE USER
```go theme={}
client.Query(`:CREATE USER analyst WITH PASSWORD "secret123"`)
// → ACL SETUSER analyst on >secret123
```
### GRANT
```go theme={}
client.Query(`:GRANT READ ON User TO analyst`)
// → ACL SETUSER analyst +hgetall +get
```
### REVOKE
```go theme={}
client.Query(`:REVOKE WRITE ON User FROM analyst`)
// → ACL SETUSER analyst -hset -hmset -del
```
### DROP USER
```go theme={}
client.Query(`:DROP USER analyst`)
// → ACL DELUSER analyst
```
> **Note:** Redis has users with permissions, not roles. `CREATE ROLE`, `DROP ROLE`, `ASSIGN ROLE` are not supported.
## Type Storage
All Redis values are stored as strings:
| OmniQL Type | Redis Storage |
| ----------- | ----------------------- |
| `STRING` | String |
| `INT` | String ("42") |
| `BOOLEAN` | String ("true"/"false") |
| `TIMESTAMP` | String (ISO format) |
| `JSON` | String (serialized) |
| `UUID` | String |
OmniQL automatically converts types when filtering.
## Supported Operations
| Operation | Supported | Notes |
| --------------------- | --------- | -------------------- |
| GET | ✅ | HGETALL |
| CREATE | ✅ | HMSET |
| UPDATE | ✅ | HSET |
| DELETE | ✅ | DEL |
| UPSERT | ✅ | HMSET |
| BULK INSERT | ✅ | Multiple HMSET |
| DROP TABLE | ✅ | DEL pattern |
| COUNT | ✅ | Via OmniQL |
| SUM / AVG / MIN / MAX | ✅ | Via OmniQL |
| WHERE (all operators) | ✅ | Via OmniQL filtering |
| ORDER BY | ✅ | Via OmniQL |
| LIMIT / OFFSET | ✅ | Via OmniQL |
| BEGIN / COMMIT | ✅ | MULTI / EXEC |
| ROLLBACK | ✅ | DISCARD |
| CREATE USER | ✅ | ACL SETUSER |
| GRANT / REVOKE | ✅ | ACL SETUSER |
## Not Supported
| Feature | Reason |
| ----------------- | -------------------------------- |
| JOIN | Key-value model has no relations |
| GROUP BY / HAVING | Use aggregations instead |
| Window functions | No SQL semantics |
| SAVEPOINT | Redis has no partial rollback |
| Roles | Redis has users only, not roles |
## Performance Considerations
| Query Type | Performance | When to Use |
| ---------------------------- | ---------------- | -------------------------- |
| `WHERE id = X` | ⚡ Instant | Always preferred |
| `WHERE field = X LIMIT N` | 🔄 Scan + filter | Small datasets, with LIMIT |
| `WHERE field = X` (no limit) | ⚠️ Full scan | Avoid on large datasets |
### Best Practices
1. **Use ID lookups when possible** - Direct key access is instant
2. **Always use LIMIT** - Prevents scanning entire keyspace
3. **Index hot queries in SQL** - For complex filtering, consider PostgreSQL
4. **Use Redis for what it's good at** - Sessions, caching, counters, real-time data
## When to Use Redis with OmniQL
**Good use cases:**
* Session storage
* User profiles / settings
* Caching layer
* Real-time counters
* Simple CRUD by ID
* Aggregations on bounded datasets
**Consider PostgreSQL instead:**
* Complex queries with multiple filters
* Relational data with joins
* Large datasets requiring full scans
* Data requiring GROUP BY
## Next Steps
Full-featured SQL database
All supported operators
# Go Package
Source: https://docs.omniql.com/integration/go-package
Using OmniQL in your Go application
## Installation
```bash theme={}
go get github.com/omniql-engine/omniql
```
## Quick Start
OmniQL wraps your existing database connection. You bring the driver, we handle the rest.
```go theme={}
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"github.com/omniql-engine/omniql"
)
func main() {
// Your existing database connection
db, _ := sql.Open("postgres", "postgres://localhost/mydb")
// Wrap it with OmniQL
client := oql.WrapSQL(db, "PostgreSQL")
// Query with OmniQL syntax
users, _ := client.Query(":GET User WHERE age > 21")
for _, user := range users {
fmt.Println(user["name"], user["age"])
}
}
```
## Database Wrappers
### PostgreSQL
```go theme={}
import (
"database/sql"
_ "github.com/lib/pq"
"github.com/omniql-engine/omniql"
)
db, _ := sql.Open("postgres", "postgres://user:pass@localhost/mydb")
client := oql.WrapSQL(db, "PostgreSQL")
```
### MySQL
```go theme={}
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"github.com/omniql-engine/omniql"
)
db, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mydb")
client := oql.WrapSQL(db, "MySQL")
```
### MongoDB
```go theme={}
import (
"context"
"go.mongodb.org/mongo-driver/mongo"
"go.mongodb.org/mongo-driver/mongo/options"
"github.com/omniql-engine/omniql"
)
mongoClient, _ := mongo.Connect(context.Background(),
options.Client().ApplyURI("mongodb://localhost:27017"))
db := mongoClient.Database("mydb")
client := oql.WrapMongo(db)
```
### Redis
```go theme={}
import (
"github.com/redis/go-redis/v9"
"github.com/omniql-engine/omniql"
)
rdb := redis.NewClient(&redis.Options{
Addr: "localhost:6379",
})
client := oql.WrapRedis(rdb, "") // Optional tenant prefix as second arg
```
## The Query Method
```go theme={}
func (c *Client) Query(input string) ([]map[string]any, error)
```
All queries return `[]map[string]any` regardless of database type.
### SELECT (GET)
```go theme={}
users, err := client.Query(":GET User WHERE age > 21 ORDER BY name ASC LIMIT 10")
// users = []map[string]any{
// {"id": 1, "name": "Alice", "age": 25},
// {"id": 2, "name": "Bob", "age": 30},
// }
for _, user := range users {
fmt.Printf("%s is %d years old\n", user["name"], user["age"])
}
```
### INSERT (CREATE)
```go theme={}
result, err := client.Query(`:CREATE User WITH name:"Alice", age:28, email:"alice@example.com"`)
// result = []map[string]any{
// {"inserted_id": 3, "rows_affected": 1},
// }
newID := result[0]["inserted_id"]
```
### UPDATE
```go theme={}
result, err := client.Query(`:UPDATE User SET verified:true WHERE id = 3`)
// result = []map[string]any{
// {"rows_affected": 1},
// }
```
### DELETE
```go theme={}
result, err := client.Query(`:DELETE User WHERE status = "inactive"`)
// result = []map[string]any{
// {"rows_affected": 5},
// }
```
### COUNT
```go theme={}
result, err := client.Query(`:COUNT User WHERE active = true`)
// result = []map[string]any{
// {"count": 42},
// }
count := result[0]["count"]
```
## Multi-Tenant Support
```go theme={}
// Set tenant context
client := oql.WrapSQL(db, "PostgreSQL")
client.SetTenant("acme_corp")
// Queries now include tenant context
users, _ := client.Query(":GET User WHERE age > 21")
// Internally adds: WHERE tenant_id = 'acme_corp' AND age > 21
```
## Polyglot Persistence
Use multiple databases with the same query syntax.
```go theme={}
// Setup
pgClient := oql.WrapSQL(pgDB, "PostgreSQL")
mongoClient := oql.WrapMongo(mongoDB)
redisClient := oql.WrapRedis(redisDB, "")
// Same query, any backend
query := ":GET User WHERE status = \"active\" LIMIT 10"
pgUsers, _ := pgClient.Query(query) // PostgreSQL
mongoUsers, _ := mongoClient.Query(query) // MongoDB
redisUsers, _ := redisClient.Query(query) // Redis
// All return []map[string]any
```
## Error Handling
```go theme={}
users, err := client.Query(":GET User WHERE age > 21")
if err != nil {
log.Printf("Query failed: %v", err)
return
}
fmt.Printf("Found %d users\n", len(users))
```
## Complete Example
```go theme={}
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
"github.com/omniql-engine/omniql"
)
func main() {
// Connect to database
db, err := sql.Open("postgres", "postgres://localhost/myapp?sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Wrap with OmniQL
client := oql.WrapSQL(db, "PostgreSQL")
// Create a user
result, err := client.Query(`:CREATE User WITH name:"John", age:25, email:"john@example.com"`)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Created user with ID: %v\n", result[0]["inserted_id"])
// Query users
users, err := client.Query(":GET User WHERE age > 21 ORDER BY name ASC")
if err != nil {
log.Fatal(err)
}
fmt.Printf("Found %d users:\n", len(users))
for _, user := range users {
fmt.Printf(" - %s (%v years old)\n", user["name"], user["age"])
}
// Count users
countResult, _ := client.Query(":COUNT User WHERE active = true")
fmt.Printf("Active users: %v\n", countResult[0]["count"])
// Update user
client.Query(`:UPDATE User SET verified:true WHERE email = "john@example.com"`)
// Delete inactive users
deleteResult, _ := client.Query(`:DELETE User WHERE active = false`)
fmt.Printf("Deleted %v inactive users\n", deleteResult[0]["rows_affected"])
}
```
## Low-Level API
For advanced use cases, you can access the parse and translate steps directly.
### Parse Only
```go theme={}
import "github.com/omniql-engine/omniql"
query, isOQL, err := oql.Parse(":GET User WHERE age > 21")
// query = *models.Query (AST)
// isOQL = true
// err = nil
```
### Translate Only
```go theme={}
import "github.com/omniql-engine/omniql/engine/translator"
// Parse first
query, _, _ := oql.Parse(":GET User WHERE age > 21")
// Then translate to any database
result, _ := translator.Translate(query, "PostgreSQL", "tenant_1")
// Access native query
sql := result.GetRelational().Sql
// → SELECT * FROM "users" WHERE "age" > 21
```
### Database-Specific Accessors
| Database | Result Type | Accessor |
| ---------- | ----------------- | ------------------------------------ |
| PostgreSQL | `RelationalQuery` | `result.GetRelational().Sql` |
| MySQL | `RelationalQuery` | `result.GetRelational().Sql` |
| MongoDB | `DocumentQuery` | `result.GetDocument().Query` |
| Redis | `KeyValueQuery` | `result.GetKeyValue().CommandString` |
## Package Structure
```
github.com/omniql-engine/omniql/
├── oql.go # Parse(), WrapSQL(), WrapMongo(), WrapRedis()
├── client.go # Client struct and Query() method
├── engine/
│ ├── parser/ # Query parser
│ ├── lexer/ # Tokenizer
│ ├── models/ # Query AST
│ ├── translator/ # Database translators
│ ├── builders/ # Query builders (including Redis filters)
│ └── validator/ # Query validators
└── mapping/ # Type and operator mappings
```
## Next Steps
5-minute getting started
Learn GET operations
Redis-specific features
Insert and Update data
# Native Queries
Source: https://docs.omniql.com/integration/native-queries
Using native database drivers alongside OmniQL
OmniQL requires the `:` prefix for all queries. If you need to execute native database commands directly, use your driver alongside OmniQL.
## Pattern
OmniQL wraps your connection but doesn't replace it. Keep both references.
```go theme={}
// Keep both
db, _ := sql.Open("postgres", connString) // Native driver
client := oql.WrapSQL(db, "PostgreSQL") // OmniQL wrapper
// OmniQL
users, _ := client.Query(":GET User WHERE active = true")
// Native
rows, _ := db.Query("SELECT * FROM users WHERE active = true")
```
## PostgreSQL / MySQL
```go theme={}
db, _ := sql.Open("postgres", "postgres://localhost/mydb")
client := oql.WrapSQL(db, "PostgreSQL")
// OmniQL
client.Query(":GET User WHERE age > 21")
// Native
db.Query("SELECT * FROM users WHERE age > 21")
db.Exec("VACUUM ANALYZE users")
```
## MongoDB
```go theme={}
mongoClient, _ := mongo.Connect(ctx, options.Client().ApplyURI("mongodb://localhost:27017"))
db := mongoClient.Database("mydb")
client := oql.WrapMongo(db)
// OmniQL
client.Query(":GET User WHERE age > 21")
// Native
db.Collection("users").Find(ctx, bson.M{"age": bson.M{"$gt": 21}})
db.RunCommand(ctx, bson.D{{"ping", 1}})
```
## Redis
```go theme={}
rdb := redis.NewClient(&redis.Options{Addr: "localhost:6379"})
client := oql.WrapRedis(rdb, "")
// OmniQL
client.Query(":GET User WHERE id = 42")
// Native
rdb.HGetAll(ctx, "user:42")
rdb.Ping(ctx)
```
## Next Steps
Full OmniQL API reference
Learn OmniQL syntax
# Introduction
Source: https://docs.omniql.com/introduction
The universal query compiler for Go
# What is OmniQL?
OmniQL is an open-source **Go library** that compiles a single query language into native PostgreSQL, MySQL, MongoDB, and Redis syntax.
It wraps your existing database connection, allowing you to decouple your business logic from your database implementation.
```go theme={}
// Your database connection
db, _ := sql.Open("postgres", "postgres://localhost/mydb")
// Wrap it with OmniQL
client := oql.WrapSQL(db, "PostgreSQL")
// Query with universal syntax
users, _ := client.Query(":GET User WHERE age > 21")
```
The same query works on any supported database:
| Database | Native Output |
| ---------- | ------------------------------------------------ |
| PostgreSQL | `SELECT * FROM "users" WHERE "age" > 21` |
| MySQL | `SELECT * FROM \`users\` WHERE \`age\` > 21\` |
| MongoDB | `db.users.find({ "age": { "$gt": 21 } })` |
| Redis | `HGETALL` with conditions filtered via Go helper |
## Why OmniQL?
**Problem:** Switching databases (e.g., from Postgres to Mongo) usually requires rewriting your entire data access layer. Supporting multiple databases in one product requires maintaining multiple codebases.
**Solution:** OmniQL acts as a compiler. You write your queries once in OmniQL syntax, and the engine instantly translates them into native commands for your target database.
## Key Features
* **Universal Syntax:** Learn one syntax (`:GET`, `:CREATE`, `:UPDATE`, `:DELETE`) instead of memorizing SQL dialects, BSON maps, and Redis commands.
* **Plug and Play:** Wrap your existing database connection with `WrapSQL()`, `WrapMongo()`, or `WrapRedis()` and start querying immediately.
* **Zero Overhead:** OmniQL translates queries instantly. No ORM magic, no reflection—just native query strings executed by your standard drivers.
* **TrueAST Architecture:** Queries are parsed into a recursive Abstract Syntax Tree, enabling complex nested expressions and type safety.
* **Polyglot Persistence:** Use relational, document, and key-value stores with the same query syntax.
## Supported Databases
| Database | Wrapper | Status |
| ---------- | ------------- | ------------ |
| PostgreSQL | `WrapSQL()` | Full support |
| MySQL | `WrapSQL()` | Full support |
| MongoDB | `WrapMongo()` | Full support |
| Redis | `WrapRedis()` | Full support |
## How It Works
![OmniQL Flow]()
1. **Wrap:** Connect OmniQL to your existing database connection.
2. **Query:** Pass an OmniQL string (starting with `:`) to `client.Query()`.
3. **Compile:** The parser validates syntax and builds the AST.
4. **Translate:** The translator generates native commands for your database.
5. **Execute:** OmniQL runs the query and returns results as `[]map[string]any`.
## Quick Example
```go theme={}
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"github.com/omniql-engine/omniql"
)
func main() {
// Your existing connection
db, _ := sql.Open("postgres", "postgres://localhost/mydb")
// Wrap with OmniQL
client := oql.WrapSQL(db, "PostgreSQL")
// CRUD operations
client.Query(`:CREATE User WITH name:"Alice", age:25`)
users, _ := client.Query(":GET User WHERE age > 21")
for _, user := range users {
fmt.Println(user["name"], user["age"])
}
client.Query(`:UPDATE User SET verified:true WHERE name = "Alice"`)
client.Query(`:DELETE User WHERE active = false`)
}
```
## Next Steps
Install the Go package and run your first query
Learn the : prefix and syntax rules
# Delete
Source: https://docs.omniql.com/mutations/delete
Remove records with DELETE
Remove records using the DELETE operation.
## Basic Syntax
```sql theme={}
:DELETE Entity WHERE condition
:DELETE FROM Entity WHERE condition
```
Both forms are valid. The `FROM` keyword is optional.
## Delete Single Record
```sql theme={}
:DELETE User WHERE id = 1
```
| Database | Output |
| ---------- | -------------------------------- |
| PostgreSQL | `DELETE FROM users WHERE id = 1` |
| MySQL | `DELETE FROM users WHERE id = 1` |
| MongoDB | `db.users.deleteOne({ _id: 1 })` |
| Redis | `DEL users:1` |
## Delete with Conditions
```sql theme={}
:DELETE User WHERE status = "inactive" AND last_login < "2023-01-01"
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------------- |
| PostgreSQL | `DELETE FROM users WHERE status = 'inactive' AND last_login < '2023-01-01'` |
| MongoDB | `db.users.deleteMany({ status: 'inactive', last_login: { $lt: '2023-01-01' } })` |
## Delete with IN
```sql theme={}
:DELETE User WHERE id IN (1, 2, 3, 4, 5)
```
| Database | Output |
| ---------- | -------------------------------------------------------- |
| PostgreSQL | `DELETE FROM users WHERE id IN (1, 2, 3, 4, 5)` |
| MongoDB | `db.users.deleteMany({ _id: { $in: [1, 2, 3, 4, 5] } })` |
## Delete with LIKE
```sql theme={}
:DELETE Log WHERE message LIKE "%debug%"
```
| Database | Output |
| ---------- | ------------------------------------------------------ |
| PostgreSQL | `DELETE FROM logs WHERE message LIKE '%debug%'` |
| MongoDB | `db.logs.deleteMany({ message: { $regex: 'debug' } })` |
## Delete with NULL Check
```sql theme={}
:DELETE User WHERE email IS NULL
```
| Database | Output |
| ---------- | --------------------------------------- |
| PostgreSQL | `DELETE FROM users WHERE email IS NULL` |
| MongoDB | `db.users.deleteMany({ email: null })` |
## Delete with BETWEEN
```sql theme={}
:DELETE Log WHERE created_at BETWEEN "2023-01-01" AND "2023-06-30"
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------- |
| PostgreSQL | `DELETE FROM logs WHERE created_at BETWEEN '2023-01-01' AND '2023-06-30'` |
## Complete Examples
### Remove Expired Sessions
```sql theme={}
:DELETE Session WHERE expires_at < "2024-01-15T00:00:00Z"
```
### Clean Up Old Logs
```sql theme={}
:DELETE Log WHERE created_at < "2023-01-01" AND level = "debug"
```
### Remove Unverified Users
```sql theme={}
:DELETE User WHERE verified = false AND created_at < "2023-12-01"
```
### Cancel Abandoned Orders
```sql theme={}
:DELETE Order WHERE status = "pending" AND created_at < "2024-01-01"
```
### Remove Test Data
```sql theme={}
:DELETE User WHERE email LIKE "%@test.com"
```
## Soft Delete Alternative
Instead of deleting, consider soft delete:
```sql theme={}
-- Soft delete (recommended)
:UPDATE User SET deleted_at:"2024-01-15T10:30:00Z", active:false WHERE id = 1
-- Hard delete (permanent)
:DELETE User WHERE id = 1
```
## Truncate
Remove all records from a table. Both syntaxes work:
```sql theme={}
:TRUNCATE Log
:TRUNCATE TABLE Log
```
| Database | Output |
| ---------- | ------------------------ |
| PostgreSQL | `TRUNCATE TABLE logs` |
| MySQL | `TRUNCATE TABLE logs` |
| MongoDB | `db.logs.deleteMany({})` |
**Warning:** TRUNCATE removes ALL records and cannot be rolled back in most databases.
## Warning
Always include a WHERE clause to avoid deleting all records accidentally.
```sql theme={}
-- DANGEROUS: Deletes ALL users
:DELETE User
-- SAFE: Deletes specific user
:DELETE User WHERE id = 1
```
## Next Steps
Define your schema
Group operations safely
# Insert
Source: https://docs.omniql.com/mutations/insert
Create new records with CREATE
Create new records using the CREATE operation.
## Basic Syntax
```sql theme={}
:CREATE Entity WITH field:value, field:value
```
## Create Single Record
```sql theme={}
:CREATE User WITH name:"John", email:"john@example.com", age:30
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------------ |
| PostgreSQL | `INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30)` |
| MySQL | `INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30)` |
| MongoDB | `db.users.insertOne({ name: 'John', email: 'john@example.com', age: 30 })` |
| Redis | `HMSET users:1 name "John" email "john@example.com" age 30` |
## Data Types
```sql theme={}
-- String
:CREATE User WITH name:"John"
-- Number
:CREATE User WITH age:30
-- Boolean
:CREATE User WITH active:true
-- Null
:CREATE User WITH deleted_at:null
```
## Multiple Fields
```sql theme={}
:CREATE Product WITH
name:"Laptop",
price:999.99,
category:"Electronics",
in_stock:true,
quantity:50
```
## With Timestamps
```sql theme={}
:CREATE User WITH
name:"John",
email:"john@example.com",
created_at:"2024-01-15T10:30:00Z"
```
## Bulk Insert
Insert multiple records at once. Each record is wrapped in square brackets with `=` assignments.
```sql theme={}
:BULK INSERT User WITH
[name = Alice, age = 25]
[name = Bob, age = 30]
[name = Charlie, age = 35]
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------------------------------- |
| PostgreSQL | `INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 35)` |
| MySQL | `INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 35)` |
| MongoDB | `db.users.insertMany([{ name: 'Alice', age: 25 }, { name: 'Bob', age: 30 }, { name: 'Charlie', age: 35 }])` |
## Upsert
Insert or update if exists. Specify conflict field(s) with `ON`.
```sql theme={}
:UPSERT User WITH email:"john@example.com", name:"John Updated", login_count:1 ON email
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------------------------------- |
| PostgreSQL | `INSERT INTO users (...) ON CONFLICT (email) DO UPDATE SET name = 'John Updated', login_count = 1` |
| MySQL | `INSERT INTO users (...) ON DUPLICATE KEY UPDATE name = 'John Updated', login_count = 1` |
| MongoDB | `db.users.updateOne({ email: 'john@example.com' }, { $set: { ... } }, { upsert: true })` |
## Upsert with Composite Key
```sql theme={}
:UPSERT OrderItem WITH order_id:1, product_id:5, quantity:3 ON order_id, product_id
```
## Replace
Delete and insert (MySQL-specific behavior).
```sql theme={}
:REPLACE User WITH id:1, name:"John Replaced", email:"john.new@example.com"
```
| Database | Output |
| ---------- | --------------------------------------------------- |
| PostgreSQL | Uses UPSERT behavior |
| MySQL | `REPLACE INTO users (id, name, email) VALUES (...)` |
| MongoDB | `db.users.replaceOne({ _id: 1 }, { ... })` |
## Complete Examples
### User Registration
```sql theme={}
:CREATE User WITH
email:"jane@example.com",
password_hash:"$2b$10$...",
name:"Jane Doe",
role:"user",
active:true,
created_at:"2024-01-15T10:30:00Z"
```
### E-commerce Order
```sql theme={}
:CREATE Order WITH
user_id:42,
total:149.99,
status:"pending",
shipping_address:"123 Main St",
created_at:"2024-01-15T10:30:00Z"
```
### Bulk Product Import
```sql theme={}
:BULK INSERT Product WITH
[sku = ABC123, name = Widget, price = 9.99, quantity = 100]
[sku = DEF456, name = Gadget, price = 19.99, quantity = 50]
[sku = GHI789, name = Gizmo, price = 29.99, quantity = 25]
```
## Next Steps
Modify existing records
Remove records
# Update
Source: https://docs.omniql.com/mutations/update
Modify existing records with UPDATE
Modify existing records using the UPDATE operation.
## Basic Syntax
```sql theme={}
:UPDATE Entity SET field:value WHERE condition
```
## Update Single Field
```sql theme={}
:UPDATE User SET status:"active" WHERE id = 1
```
| Database | Output |
| ---------- | ---------------------------------------------------------------- |
| PostgreSQL | `UPDATE users SET status = 'active' WHERE id = 1` |
| MySQL | `UPDATE users SET status = 'active' WHERE id = 1` |
| MongoDB | `db.users.updateOne({ _id: 1 }, { $set: { status: 'active' } })` |
| Redis | `HSET users:1 status "active"` |
## Update Multiple Fields
```sql theme={}
:UPDATE User SET name:"John Updated", age:31, updated_at:"2024-01-15T10:30:00Z" WHERE id = 1
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------- |
| PostgreSQL | `UPDATE users SET name = 'John Updated', age = 31, updated_at = '...' WHERE id = 1` |
| MongoDB | `db.users.updateOne({ _id: 1 }, { $set: { name: 'John Updated', age: 31, ... } })` |
## Update with Conditions
```sql theme={}
:UPDATE User SET verified:true WHERE email_confirmed = true AND created_at < "2024-01-01"
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------------------------------- |
| PostgreSQL | `UPDATE users SET verified = true WHERE email_confirmed = true AND created_at < '2024-01-01'` |
| MongoDB | `db.users.updateMany({ email_confirmed: true, created_at: { $lt: '...' } }, { $set: { verified: true } })` |
## Bulk Update
Update multiple records matching condition.
```sql theme={}
:UPDATE Product SET on_sale:true WHERE category = "Electronics"
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------- |
| PostgreSQL | `UPDATE products SET on_sale = true WHERE category = 'Electronics'` |
| MongoDB | `db.products.updateMany({ category: 'Electronics' }, { $set: { on_sale: true } })` |
## Update with IN
```sql theme={}
:UPDATE User SET role:"premium" WHERE id IN (1, 2, 3, 4, 5)
```
| Database | Output |
| ---------- | --------------------------------------------------------------------------------------- |
| PostgreSQL | `UPDATE users SET role = 'premium' WHERE id IN (1, 2, 3, 4, 5)` |
| MongoDB | `db.users.updateMany({ _id: { $in: [1, 2, 3, 4, 5] } }, { $set: { role: 'premium' } })` |
## Set to NULL
```sql theme={}
:UPDATE User SET deleted_at:null WHERE id = 1
```
| Database | Output |
| ---------- | ---------------------------------------------------------------- |
| PostgreSQL | `UPDATE users SET deleted_at = NULL WHERE id = 1` |
| MongoDB | `db.users.updateOne({ _id: 1 }, { $set: { deleted_at: null } })` |
## Increment Values
```sql theme={}
:UPDATE Product SET quantity = quantity + 10 WHERE id = 1
:UPDATE User SET login_count = login_count + 1 WHERE id = 1
```
| Database | Output |
| ---------- | --------------------------------------------------------------- |
| PostgreSQL | `UPDATE products SET quantity = quantity + 10 WHERE id = 1` |
| MongoDB | `db.products.updateOne({ _id: 1 }, { $inc: { quantity: 10 } })` |
## Decrement Values
```sql theme={}
:UPDATE Product SET stock = stock - 1 WHERE id = 1
```
| Database | Output |
| ---------- | ------------------------------------------------------------ |
| PostgreSQL | `UPDATE products SET stock = stock - 1 WHERE id = 1` |
| MongoDB | `db.products.updateOne({ _id: 1 }, { $inc: { stock: -1 } })` |
## Using Functions
```sql theme={}
:UPDATE User SET name = UPPER(name) WHERE id = 1
```
| Database | Output |
| ---------- | -------------------------------------------------- |
| PostgreSQL | `UPDATE users SET name = UPPER(name) WHERE id = 1` |
## Complete Examples
### Soft Delete
```sql theme={}
:UPDATE User SET deleted_at:"2024-01-15T10:30:00Z", active:false WHERE id = 1
```
### Deactivate Inactive Users
```sql theme={}
:UPDATE User SET active:false WHERE last_login < "2023-01-01" AND active = true
```
### Update Order Status
```sql theme={}
:UPDATE Order SET
status:"shipped",
shipped_at:"2024-01-15T10:30:00Z",
tracking_number:"1Z999AA10123456784"
WHERE id = 1001
```
### Price Adjustment
```sql theme={}
:UPDATE Product SET price = price * 1.1 WHERE category = "Electronics"
```
Increases all electronics prices by 10%.
## Warning
Always include a WHERE clause to avoid updating all records accidentally.
```sql theme={}
-- DANGEROUS: Updates ALL users
:UPDATE User SET status:"inactive"
-- SAFE: Updates specific user
:UPDATE User SET status:"inactive" WHERE id = 1
```
## Next Steps
Remove records
Group operations
# Syntax Basics
Source: https://docs.omniql.com/prefix
Understanding the : prefix and syntax rules
# The : Prefix
All OmniQL queries start with a colon (`:`).
```sql theme={}
:GET User WHERE id = 1
```
## Why the Prefix?
OmniQL and native SQL share many keywords (`CREATE`, `UPDATE`, `SELECT`). The prefix acts as a switch, telling the system exactly how to handle the string.
| Query | Behavior |
| -------------- | ------------------------------------ |
| `:GET User...` | ✅ Parsed and translated to target DB |
| `SELECT * ...` | ❌ Error - missing `:` prefix |
All queries must use OmniQL syntax. For native database commands, use your database driver directly.
## Parser Behavior
The `oql.Parse()` function uses this prefix to determine if it should engage the compiler.
```go theme={}
import "github.com/omniql-engine/omniql"
// 1. OmniQL Query (Starts with :)
query, isOQL, err := oql.Parse(":GET User WHERE id = 1")
// isOQL = true
// query = *models.Query (AST)
// 2. Query without prefix = Error
query, isOQL, err := oql.Parse("SELECT * FROM users")
// isOQL = false
// client.Query() will return error: "OmniQL syntax required"
```
## Syntax Pattern
An OmniQL command follows this structure:
```
:OPERATION Entity [clauses]
```
| Part | Description | Example |
| ----------- | ----------------------- | ----------------------------------- |
| `:` | Required Prefix | `:` |
| `OPERATION` | Action to perform | `GET`, `CREATE`, `UPDATE`, `DELETE` |
| `Entity` | Target Table/Collection | `User`, `Product`, `Order` |
| `[clauses]` | Optional modifiers | `WHERE`, `ORDER BY`, `LIMIT` |
## Entity Naming
OmniQL uses PascalCase entity names. The translator automatically lowercases and pluralizes them:
| You Write | Output |
| ----------- | ------------ |
| `User` | `users` |
| `Product` | `products` |
| `OrderItem` | `orderitems` |
> **Note:** The engine lowercases then pluralizes. It does not add underscores (snake\_case).
## Examples
```sql theme={}
-- Queries
:GET User WHERE age > 21
:GET id, name, price FROM Product WHERE active = true
-- Mutations
:CREATE User WITH name:"John", age:30
:UPDATE User SET verified:true WHERE id = 1
:DELETE User WHERE status = "inactive"
-- Schema DDL
:CREATE TABLE User WITH id:AUTO, name:STRING, email:STRING
:CREATE INDEX idx_email ON User (email)
```
## Next Steps
Get running in 5 minutes
Learn WHERE clauses and operators
# Advanced Queries
Source: https://docs.omniql.com/queries/advanced
CTEs, subqueries, set operations, and CASE expressions
Advanced query features for complex data retrieval.
## Common Table Expressions (CTE)
CTEs create temporary named result sets for use in queries.
```sql theme={}
:CTE active_users AS (GET User WHERE active = true)
```
| Database | Output |
| ---------- | ---------------------------------------------------------------- |
| PostgreSQL | `WITH active_users AS (SELECT * FROM users WHERE active = true)` |
| MySQL | `WITH active_users AS (SELECT * FROM users WHERE active = true)` |
### Use Cases
CTEs are useful for:
* Breaking complex queries into readable parts
* Reusing the same subquery multiple times
* Recursive queries
## Subqueries
Filter using results from another query.
```sql theme={}
:SUBQUERY id IN (GET User WHERE active = true)
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM ... WHERE id IN (SELECT id FROM users WHERE active = true)` |
### Example: Users with Orders
```sql theme={}
:SUBQUERY user_id IN (GET Order WHERE total > 100)
```
## EXISTS
Check if a subquery returns any results.
```sql theme={}
:EXISTS (GET User WHERE email = "john@example.com")
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------- |
| PostgreSQL | `SELECT EXISTS(SELECT 1 FROM users WHERE email = 'john@example.com')` |
| MongoDB | `db.users.countDocuments({ email: 'john@example.com' }, { limit: 1 })` |
## Set Operations
Combine results from multiple queries.
### UNION
Combine results, removing duplicates.
```sql theme={}
:UNION (GET User WHERE age > 50) (GET User WHERE role = "premium")
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------------- |
| PostgreSQL | `(SELECT * FROM users WHERE age > 50) UNION (SELECT * FROM users WHERE role = 'premium')` |
### UNION ALL
Combine results, keeping duplicates.
```sql theme={}
:UNION ALL (GET User WHERE department = "sales") (GET User WHERE department = "marketing")
```
### INTERSECT
Return only rows that appear in both queries.
```sql theme={}
:INTERSECT (GET User WHERE age > 30) (GET User WHERE active = true)
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------------------------ |
| PostgreSQL | `(SELECT * FROM users WHERE age > 30) INTERSECT (SELECT * FROM users WHERE active = true)` |
### EXCEPT
Return rows from first query that don't appear in second.
```sql theme={}
:EXCEPT (GET User WHERE active = true) (GET User WHERE role = "banned")
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------------------- |
| PostgreSQL | `(SELECT * FROM users WHERE active = true) EXCEPT (SELECT * FROM users WHERE role = 'banned')` |
## CASE Expressions
Conditional logic within queries. CASE must be used within GET expressions.
```sql theme={}
:GET User WITH CASE WHEN age > 25 THEN "adult" ELSE "minor" END AS category
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT *, CASE WHEN age > 25 THEN 'adult' ELSE 'minor' END AS category FROM users` |
### Multiple Conditions
```sql theme={}
:GET User WITH CASE
WHEN age < 18 THEN "minor"
WHEN age < 65 THEN "adult"
ELSE "senior"
END AS age_group
```
### CASE in WHERE
```sql theme={}
:GET Order WHERE CASE WHEN total > 1000 THEN "large" ELSE "small" END = "large"
```
## Database Support
| Feature | PostgreSQL | MySQL | MongoDB |
| --------- | ---------- | ---------- | --------------- |
| CTE | Yes | Yes (8.0+) | No |
| SUBQUERY | Yes | Yes | Via aggregation |
| EXISTS | Yes | Yes | Via count |
| UNION | Yes | Yes | Via \$unionWith |
| UNION ALL | Yes | Yes | Via \$unionWith |
| INTERSECT | Yes | Yes (8.0+) | Via aggregation |
| EXCEPT | Yes | Yes (8.0+) | Via aggregation |
| CASE | Yes | Yes | Via \$cond |
## Complete Examples
### Active Premium Users with Order Stats
```sql theme={}
:CTE premium AS (GET User WHERE role = "premium" AND active = true)
```
### Users Without Recent Orders
```sql theme={}
:EXCEPT (GET User WHERE active = true) (SUBQUERY user_id IN (GET Order WHERE created_at > "2024-01-01"))
```
### Categorized Products
```sql theme={}
:GET Product WITH
name,
price,
CASE
WHEN price < 10 THEN "budget"
WHEN price < 100 THEN "standard"
ELSE "premium"
END AS tier
```
## Next Steps
ROW NUMBER, RANK, LAG, LEAD
Combine multiple tables
# Query Basics
Source: https://docs.omniql.com/queries/basics
Read data with GET operations
The `GET` operation retrieves data from a table.
## Basic Syntax
```sql theme={}
:GET Entity
:GET Entity WHERE conditions
:GET field1, field2 FROM Entity
:GET field1, field2 FROM Entity WHERE conditions
```
## Get All Records
```sql theme={}
:GET User
```
| Database | Output |
| ---------- | --------------------- |
| PostgreSQL | `SELECT * FROM users` |
| MySQL | `SELECT * FROM users` |
| MongoDB | `db.users.find({})` |
## Select Specific Columns
```sql theme={}
:GET id, name, email FROM User
```
| Database | Output |
| ---------- | ------------------------------------------------- |
| PostgreSQL | `SELECT id, name, email FROM users` |
| MySQL | `SELECT id, name, email FROM users` |
| MongoDB | `db.users.find({}, { id: 1, name: 1, email: 1 })` |
## Simple WHERE Clause
```sql theme={}
:GET User WHERE id = 1
```
| Database | Output |
| ---------- | ---------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE id = 1` |
| MySQL | `SELECT * FROM users WHERE id = 1` |
| MongoDB | `db.users.find({ id: 1 })` |
## Multiple Conditions
```sql theme={}
:GET User WHERE age > 21 AND status = "active"
```
| Database | Output |
| ---------- | ---------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE age > 21 AND status = 'active'` |
| MongoDB | `db.users.find({ age: { $gt: 21 }, status: 'active' })` |
## Combine Columns and Conditions
```sql theme={}
:GET id, name FROM User WHERE active = true
```
| Database | Output |
| ---------- | ----------------------------------------------------- |
| PostgreSQL | `SELECT id, name FROM users WHERE active = true` |
| MongoDB | `db.users.find({ active: true }, { id: 1, name: 1 })` |
## Limit Results
```sql theme={}
:GET User LIMIT 10
:GET User WHERE active = true LIMIT 5
```
| Database | Output |
| ---------- | ------------------------------ |
| PostgreSQL | `SELECT * FROM users LIMIT 10` |
| MySQL | `SELECT * FROM users LIMIT 10` |
| MongoDB | `db.users.find({}).limit(10)` |
## Skip Results (Pagination)
```sql theme={}
:GET User LIMIT 10 OFFSET 20
```
| Database | Output |
| ---------- | ---------------------------------------- |
| PostgreSQL | `SELECT * FROM users LIMIT 10 OFFSET 20` |
| MongoDB | `db.users.find({}).skip(20).limit(10)` |
## Standalone Aggregates
Aggregates can also be used as standalone operations:
```sql theme={}
:COUNT * FROM User
:COUNT * FROM User WHERE active = true
:SUM amount FROM Order WHERE status = "completed"
:AVG price FROM Product
:MIN created_at FROM User
:MAX total FROM Order
```
| Database | Output |
| ---------- | ----------------------------------------------------------- |
| PostgreSQL | `SELECT COUNT(*) FROM users` |
| PostgreSQL | `SELECT SUM(amount) FROM orders WHERE status = 'completed'` |
## DISTINCT
Get unique values:
```sql theme={}
:GET User DISTINCT
:GET role FROM User DISTINCT
```
## Next Steps
Advanced WHERE conditions
ORDER BY operations
# Filtering
Source: https://docs.omniql.com/queries/filtering
Advanced WHERE conditions and operators
Filter data using WHERE clauses with various operators.
## Comparison Operators
```sql theme={}
:GET User WHERE age = 25
:GET User WHERE age != 25
:GET User WHERE age > 21
:GET User WHERE age >= 21
:GET User WHERE age < 65
:GET User WHERE age <= 65
```
| Operator | Meaning |
| -------- | --------------------- |
| `=` | Equal |
| `!=` | Not equal |
| `>` | Greater than |
| `>=` | Greater than or equal |
| `<` | Less than |
| `<=` | Less than or equal |
## Logical Operators
### AND
```sql theme={}
:GET User WHERE age > 21 AND status = "active"
```
| Database | Output |
| ---------- | ---------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE age > 21 AND status = 'active'` |
| MongoDB | `db.users.find({ age: { $gt: 21 }, status: 'active' })` |
### OR
```sql theme={}
:GET User WHERE role = "admin" OR role = "moderator"
```
| Database | Output |
| ---------- | -------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE role = 'admin' OR role = 'moderator'` |
| MongoDB | `db.users.find({ $or: [{ role: 'admin' }, { role: 'moderator' }] })` |
### Combined with Parentheses
```sql theme={}
:GET User WHERE (age > 21 AND status = "active") OR role = "admin"
```
## IN Operator
Match against a list of values.
```sql theme={}
:GET User WHERE role IN ("admin", "moderator", "editor")
```
| Database | Output |
| ---------- | -------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE role IN ('admin', 'moderator', 'editor')` |
| MongoDB | `db.users.find({ role: { $in: ['admin', 'moderator', 'editor'] } })` |
### NOT IN
```sql theme={}
:GET User WHERE status NOT IN ("banned", "suspended")
```
## BETWEEN Operator
Match a range of values.
```sql theme={}
:GET User WHERE age BETWEEN 18 AND 65
```
| Database | Output |
| ---------- | ------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE age BETWEEN 18 AND 65` |
| MongoDB | `db.users.find({ age: { $gte: 18, $lte: 65 } })` |
### Date Range
```sql theme={}
:GET Order WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"
```
### NOT BETWEEN
```sql theme={}
:GET Product WHERE price NOT BETWEEN 10 AND 50
```
## LIKE Operator
Pattern matching for strings.
```sql theme={}
:GET User WHERE name LIKE "John%"
:GET User WHERE email LIKE "%@gmail.com"
:GET User WHERE name LIKE "%smith%"
```
| Pattern | Meaning |
| ------------- | ---------------------- |
| `John%` | Starts with "John" |
| `%@gmail.com` | Ends with "@gmail.com" |
| `%smith%` | Contains "smith" |
| Database | Output |
| ---------- | ---------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE name LIKE 'John%'` |
| MongoDB | `db.users.find({ name: { $regex: '^John' } })` |
### NOT LIKE
```sql theme={}
:GET User WHERE email NOT LIKE "%@test.com"
```
### ILIKE (Case Insensitive)
```sql theme={}
:GET User WHERE name ILIKE "john%"
```
| Database | Output |
| ---------- | ---------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE name ILIKE 'john%'` |
| MySQL | `SELECT * FROM users WHERE LOWER(name) LIKE 'john%'` |
## NULL Checks
```sql theme={}
:GET User WHERE deleted_at IS NULL
:GET User WHERE phone IS NOT NULL
```
| Database | Output |
| ---------- | ---------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE deleted_at IS NULL` |
| MongoDB | `db.users.find({ deleted_at: null })` |
## Complex Example
```sql theme={}
:GET id, name, email FROM User
WHERE status = "active"
AND age BETWEEN 21 AND 45
AND role IN ("user", "premium")
AND email LIKE "%@company.com"
AND deleted_at IS NULL
```
## Next Steps
ORDER BY operations
CTEs, subqueries, and set operations
# Grouping
Source: https://docs.omniql.com/queries/grouping
Aggregate data with GROUP BY
Aggregate data using GROUP BY with aggregate functions.
## Basic Syntax
```sql theme={}
:COUNT * FROM Entity GROUP BY field
:SUM field FROM Entity GROUP BY field
:AVG field FROM Entity GROUP BY field
```
## Aggregate Functions
| Function | Description |
| ------------- | ------------- |
| `COUNT(*)` | Count rows |
| `SUM(column)` | Sum values |
| `AVG(column)` | Average value |
| `MIN(column)` | Minimum value |
| `MAX(column)` | Maximum value |
## Count by Group
```sql theme={}
:COUNT * FROM User GROUP BY status
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------- |
| PostgreSQL | `SELECT status, COUNT(*) FROM users GROUP BY status` |
| MongoDB | `db.users.aggregate([{ $group: { _id: '$status', count: { $sum: 1 } } }])` |
## Sum by Group
```sql theme={}
:SUM amount FROM Order GROUP BY status
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT status, SUM(amount) FROM orders GROUP BY status` |
| MongoDB | `db.orders.aggregate([{ $group: { _id: '$status', total: { $sum: '$amount' } } }])` |
## Average by Group
```sql theme={}
:AVG price FROM Product GROUP BY category
```
| Database | Output |
| ---------- | ------------------------------------------------------------- |
| PostgreSQL | `SELECT category, AVG(price) FROM products GROUP BY category` |
## Min/Max by Group
```sql theme={}
:MIN price FROM Product GROUP BY category
:MAX price FROM Product GROUP BY category
```
## Multiple Columns with GET WITH
For multiple columns with aggregates (except COUNT):
```sql theme={}
:GET Order WITH status, SUM(amount) AS total GROUP BY status
```
| Database | Output |
| ---------- | ----------------------------------------------------------------- |
| PostgreSQL | `SELECT status, SUM(amount) AS total FROM orders GROUP BY status` |
## Group by Multiple Columns
```sql theme={}
:SUM amount FROM Order GROUP BY status, user_id
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------- |
| PostgreSQL | `SELECT status, user_id, SUM(amount) FROM orders GROUP BY status, user_id` |
## HAVING Clause
Filter groups after aggregation.
```sql theme={}
:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------- |
| PostgreSQL | `SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 10` |
## WHERE vs HAVING
* `WHERE` filters rows before grouping
* `HAVING` filters groups after aggregation
```sql theme={}
:SUM amount FROM Order WHERE created_at > "2024-01-01" GROUP BY status HAVING SUM(amount) > 1000
```
## With ORDER BY
```sql theme={}
:COUNT * FROM Product GROUP BY category ORDER BY count DESC
```
## Complete Example
```sql theme={}
:SUM amount FROM Order
WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"
GROUP BY status
HAVING SUM(amount) >= 500
ORDER BY sum DESC
LIMIT 100
```
## Next Steps
Advanced analytics
Create new records
# Joins
Source: https://docs.omniql.com/queries/joins
Combine data from multiple tables
Combine data from multiple tables using JOIN operations.
## Basic Syntax
```sql theme={}
:INNER JOIN Entity1 Entity2 ON field1 = field2
:LEFT JOIN Entity1 Entity2 ON field1 = field2
:RIGHT JOIN Entity1 Entity2 ON field1 = field2
:FULL JOIN Entity1 Entity2 ON field1 = field2
:CROSS JOIN Entity1 Entity2
```
## Inner Join
Returns only matching rows from both tables.
```sql theme={}
:INNER JOIN Order User ON user_id = id
```
| Database | Output |
| ---------- | --------------------------------------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM orders INNER JOIN users ON user_id = id` |
| MySQL | `SELECT * FROM orders INNER JOIN users ON user_id = id` |
| MongoDB | `db.orders.aggregate([{ $lookup: { from: 'users', localField: 'user_id', foreignField: '_id', as: 'user' } }])` |
## Left Join
Returns all rows from the first table, matched rows from second.
```sql theme={}
:LEFT JOIN User Order ON id = user_id
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users LEFT JOIN orders ON id = user_id` |
| MongoDB | `db.users.aggregate([{ $lookup: { from: 'orders', localField: '_id', foreignField: 'user_id', as: 'orders' } }])` |
## Right Join
Returns all rows from the second table, matched rows from first.
```sql theme={}
:RIGHT JOIN Order User ON user_id = id
```
| Database | Output |
| ---------- | ------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM orders RIGHT JOIN users ON user_id = id` |
## Full Join
Returns all rows from both tables.
```sql theme={}
:FULL JOIN Order User ON user_id = id
```
| Database | Output |
| ---------- | ------------------------------------------------------ |
| PostgreSQL | `SELECT * FROM orders FULL JOIN users ON user_id = id` |
## Cross Join
Returns Cartesian product of both tables (no ON clause needed).
```sql theme={}
:CROSS JOIN Product Category
```
## MongoDB Note
MongoDB uses `$lookup` aggregation for joins. OmniQL automatically translates JOIN syntax to the appropriate aggregation pipeline.
## Limitations
Current JOIN implementation has these constraints:
* One JOIN per query (no chained joins)
* No table aliases
* No column selection within JOIN queries
* Field names only (no `Table.field` notation)
For complex multi-table queries, consider using multiple queries or your database driver directly.
## Next Steps
Aggregate with GROUP BY
Advanced analytics
# Sorting
Source: https://docs.omniql.com/queries/sorting
Order results with ORDER BY
Order results using the ORDER BY clause.
## Basic Syntax
```sql theme={}
:GET Entity ORDER BY field
:GET Entity ORDER BY field ASC
:GET Entity ORDER BY field DESC
```
## Ascending Order (Default)
```sql theme={}
:GET User ORDER BY name
:GET User ORDER BY name ASC
```
| Database | Output |
| ---------- | --------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY name ASC` |
| MySQL | `SELECT * FROM users ORDER BY name ASC` |
| MongoDB | `db.users.find({}).sort({ name: 1 })` |
## Descending Order
```sql theme={}
:GET User ORDER BY created_at DESC
```
| Database | Output |
| ---------- | ---------------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY created_at DESC` |
| MongoDB | `db.users.find({}).sort({ created_at: -1 })` |
## Multiple Columns
```sql theme={}
:GET User ORDER BY status ASC, created_at DESC
```
| Database | Output |
| ---------- | ---------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY status ASC, created_at DESC` |
| MongoDB | `db.users.find({}).sort({ status: 1, created_at: -1 })` |
## Expressions in ORDER BY
Sort by calculated values or function results.
```sql theme={}
:GET Product ORDER BY price * quantity DESC
:GET User ORDER BY UPPER(name) ASC
```
| Database | Output |
| ---------- | ------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM products ORDER BY price * quantity DESC` |
| MySQL | `SELECT * FROM products ORDER BY price * quantity DESC` |
### Function in ORDER BY
```sql theme={}
:GET User ORDER BY LENGTH(name) DESC
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY LENGTH(name) DESC` |
| MongoDB | `db.users.aggregate([{ $addFields: { nameLen: { $strLenCP: '$name' } } }, { $sort: { nameLen: -1 } }])` |
## With WHERE Clause
```sql theme={}
:GET User WHERE active = true ORDER BY name ASC
```
| Database | Output |
| ---------- | ----------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE active = true ORDER BY name ASC` |
| MongoDB | `db.users.find({ active: true }).sort({ name: 1 })` |
## With LIMIT
```sql theme={}
:GET User ORDER BY created_at DESC LIMIT 10
```
| Database | Output |
| ---------- | ------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY created_at DESC LIMIT 10` |
| MongoDB | `db.users.find({}).sort({ created_at: -1 }).limit(10)` |
## Pagination Pattern
```sql theme={}
:GET User ORDER BY id ASC LIMIT 20 OFFSET 40
```
| Database | Output |
| ---------- | -------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY id ASC LIMIT 20 OFFSET 40` |
| MongoDB | `db.users.find({}).sort({ id: 1 }).skip(40).limit(20)` |
## Complete Example
```sql theme={}
:GET id, name, email, created_at FROM User
WHERE status = "active"
AND role IN ("user", "premium")
ORDER BY created_at DESC, name ASC
LIMIT 25
OFFSET 50
```
## Next Steps
Combine multiple tables
Aggregate with GROUP BY
# Window Functions
Source: https://docs.omniql.com/queries/window-functions
Advanced analytics with window functions
Perform calculations across rows related to the current row.
## Supported Functions
| Function | Description |
| ------------ | ----------------------- |
| `ROW NUMBER` | Sequential row numbers |
| `RANK` | Rank with gaps for ties |
| `DENSE RANK` | Rank without gaps |
| `LAG` | Previous row value |
| `LEAD` | Next row value |
| `NTILE` | Divide into buckets |
## Basic Syntax
```sql theme={}
:GET Entity WITH *, ROW NUMBER OVER (ORDER BY field) AS row_num
:GET Entity WITH *, RANK OVER (PARTITION BY field ORDER BY field) AS rank
```
Use `*` to include all columns, or specify columns explicitly:
```sql theme={}
:GET Entity WITH id, name, ROW NUMBER OVER (ORDER BY field) AS row_num
```
## ROW NUMBER
Assign sequential numbers to rows.
```sql theme={}
:GET User WITH *, ROW NUMBER OVER (ORDER BY created_at) AS row_num
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------- |
| PostgreSQL | `SELECT *, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num FROM users` |
### With Partition
```sql theme={}
:GET User WITH *, ROW NUMBER OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM users` |
## RANK
Assign rank with gaps for ties.
```sql theme={}
:GET Product WITH *, RANK OVER (PARTITION BY category ORDER BY price DESC) AS rank
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT *, RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank FROM products` |
## DENSE RANK
Assign rank without gaps for ties.
```sql theme={}
:GET Product WITH *, DENSE RANK OVER (ORDER BY price DESC) AS dense_rank
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------------- |
| PostgreSQL | `SELECT *, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank FROM products` |
## Rank Comparison
| Score | ROW NUMBER | RANK | DENSE RANK |
| ----- | ---------- | ---- | ---------- |
| 100 | 1 | 1 | 1 |
| 100 | 2 | 1 | 1 |
| 90 | 3 | 3 | 2 |
| 80 | 4 | 4 | 3 |
## LAG
Access previous row value.
```sql theme={}
:GET Order WITH *, LAG amount OVER (ORDER BY created_at) AS prev_amount
```
| Database | Output |
| ---------- | ----------------------------------------------------------------------------- |
| PostgreSQL | `SELECT *, LAG(amount) OVER (ORDER BY created_at) AS prev_amount FROM orders` |
## LEAD
Access next row value.
```sql theme={}
:GET Order WITH *, LEAD amount OVER (ORDER BY created_at) AS next_amount
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------------ |
| PostgreSQL | `SELECT *, LEAD(amount) OVER (ORDER BY created_at) AS next_amount FROM orders` |
## NTILE
Divide rows into buckets.
```sql theme={}
:GET User WITH *, NTILE 4 OVER (ORDER BY salary) AS quartile
```
| Database | Output |
| ---------- | ------------------------------------------------------------------ |
| PostgreSQL | `SELECT *, NTILE(4) OVER (ORDER BY salary) AS quartile FROM users` |
Divides users into 4 salary quartiles.
## Complete Example
```sql theme={}
:GET Order WITH
id,
user_id,
amount,
ROW NUMBER OVER (PARTITION BY user_id ORDER BY created_at) AS order_num,
RANK OVER (ORDER BY amount DESC) AS amount_rank
WHERE created_at > "2024-01-01"
ORDER BY user_id, created_at
```
## MongoDB Note
MongoDB has limited window function support (5.0+). Complex window functions may require aggregation pipelines.
## Limitations
Current implementation supports:
* PARTITION BY
* ORDER BY within OVER clause
Not currently supported:
* Frame clauses (`ROWS BETWEEN`)
* `FIRST_VALUE`, `LAST_VALUE`
* Aggregate functions as window functions (`SUM() OVER`, `AVG() OVER`)
## Next Steps
Create new records
Modify existing records
# Quickstart
Source: https://docs.omniql.com/quickstart
Get running with OmniQL in 5 minutes
# Quickstart
Get OmniQL running in your Go project in 5 minutes.
## Installation
```bash theme={}
go get github.com/omniql-engine/omniql
```
## Basic Usage
OmniQL wraps your existing database connection.
```go theme={}
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"github.com/omniql-engine/omniql"
)
func main() {
// 1. Your existing database connection
db, _ := sql.Open("postgres", "postgres://localhost/mydb?sslmode=disable")
defer db.Close()
// 2. Wrap it with OmniQL
client := oql.WrapSQL(db, "PostgreSQL")
// 3. Query with OmniQL syntax
users, _ := client.Query(":GET User WHERE age > 21")
// 4. Results are []map[string]any
for _, user := range users {
fmt.Printf("%s is %v years old\n", user["name"], user["age"])
}
}
```
## CRUD Operations
All operations return `[]map[string]any`.
### Create
```go theme={}
result, _ := client.Query(`:CREATE User WITH name:"John", age:30, email:"john@example.com"`)
// result = []map[string]any{
// {"inserted_id": 1, "rows_affected": 1},
// }
fmt.Println("Created user:", result[0]["inserted_id"])
```
### Read
```go theme={}
users, _ := client.Query(":GET User WHERE active = true ORDER BY created_at DESC LIMIT 10")
// users = []map[string]any{
// {"id": 1, "name": "John", "age": 30, "active": true},
// {"id": 2, "name": "Jane", "age": 25, "active": true},
// }
for _, user := range users {
fmt.Println(user["name"])
}
```
### Update
```go theme={}
result, _ := client.Query(`:UPDATE User SET verified:true WHERE id = 1`)
// result = []map[string]any{
// {"rows_affected": 1},
// }
fmt.Println("Updated rows:", result[0]["rows_affected"])
```
### Delete
```go theme={}
result, _ := client.Query(`:DELETE User WHERE status = "inactive"`)
// result = []map[string]any{
// {"rows_affected": 5},
// }
fmt.Println("Deleted rows:", result[0]["rows_affected"])
```
### Count
```go theme={}
result, _ := client.Query(":COUNT User WHERE active = true")
// result = []map[string]any{
// {"count": 42},
// }
fmt.Println("Active users:", result[0]["count"])
```
## Other Databases
### MySQL
```go theme={}
import _ "github.com/go-sql-driver/mysql"
db, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mydb")
client := oql.WrapSQL(db, "MySQL")
users, _ := client.Query(":GET User WHERE age > 21")
```
### MongoDB
```go theme={}
import "go.mongodb.org/mongo-driver/mongo"
mongoClient, _ := mongo.Connect(ctx, options.Client().ApplyURI("mongodb://localhost:27017"))
db := mongoClient.Database("mydb")
client := oql.WrapMongo(db)
users, _ := client.Query(":GET User WHERE age > 21")
```
### Redis
```go theme={}
import "github.com/redis/go-redis/v9"
rdb := redis.NewClient(&redis.Options{Addr: "localhost:6379"})
client := oql.WrapRedis(rdb, "")
users, _ := client.Query(":GET User WHERE id = 42")
```
## Polyglot Persistence
Same query syntax, any database.
```go theme={}
// Setup multiple clients
pgClient := oql.WrapSQL(pgDB, "PostgreSQL")
mongoClient := oql.WrapMongo(mongoDB)
redisClient := oql.WrapRedis(redisDB, "")
// Same query works on all
query := ":GET User WHERE status = \"active\""
pgUsers, _ := pgClient.Query(query)
mongoUsers, _ := mongoClient.Query(query)
redisUsers, _ := redisClient.Query(query)
```
## Error Handling
```go theme={}
users, err := client.Query(":GET User WHERE age > 21")
if err != nil {
log.Printf("Query failed: %v", err)
return
}
fmt.Printf("Found %d users\n", len(users))
```
## Next Steps
Learn GET syntax and filtering
Define tables with DDL
Insert and Update data
Full API reference
# Clauses
Source: https://docs.omniql.com/reference/clauses
Complete clause reference
All clauses supported by OmniQL.
## WHERE
Filter records based on conditions.
```sql theme={}
:GET User WHERE condition
:UPDATE User SET field = value WHERE condition
:DELETE User WHERE condition
```
### Examples
```sql theme={}
:GET User WHERE id = 1
:GET User WHERE age > 21 AND status = "active"
:GET User WHERE role IN ("admin", "moderator")
```
## ORDER BY
Sort results.
```sql theme={}
:GET Entity ORDER BY column
:GET Entity ORDER BY column ASC
:GET Entity ORDER BY column DESC
:GET Entity ORDER BY column1 ASC, column2 DESC
```
### Examples
```sql theme={}
:GET User ORDER BY name
:GET User ORDER BY created_at DESC
:GET User ORDER BY status ASC, name ASC
```
| Database | Output |
| ---------- | ------------------------------------- |
| PostgreSQL | `SELECT * FROM users ORDER BY name` |
| MongoDB | `db.users.find({}).sort({ name: 1 })` |
## LIMIT
Restrict number of results.
```sql theme={}
:GET Entity LIMIT n
```
### Examples
```sql theme={}
:GET User LIMIT 10
:GET User WHERE active = true ORDER BY created_at DESC LIMIT 5
```
| Database | Output |
| ---------- | ------------------------------ |
| PostgreSQL | `SELECT * FROM users LIMIT 10` |
| MySQL | `SELECT * FROM users LIMIT 10` |
| MongoDB | `db.users.find({}).limit(10)` |
## OFFSET
Skip rows for pagination.
```sql theme={}
:GET Entity LIMIT n OFFSET m
```
### Examples
```sql theme={}
:GET User LIMIT 10 OFFSET 0
:GET User LIMIT 10 OFFSET 10
:GET User LIMIT 10 OFFSET 20
```
| Database | Output |
| ---------- | ---------------------------------------- |
| PostgreSQL | `SELECT * FROM users LIMIT 10 OFFSET 20` |
| MongoDB | `db.users.find({}).skip(20).limit(10)` |
### Pagination Pattern
```sql theme={}
-- Page 1
:GET User ORDER BY id LIMIT 20 OFFSET 0
-- Page 2
:GET User ORDER BY id LIMIT 20 OFFSET 20
-- Page 3
:GET User ORDER BY id LIMIT 20 OFFSET 40
```
## GROUP BY
Group rows for aggregation.
```sql theme={}
:COUNT * FROM Entity GROUP BY column
:SUM field FROM Entity GROUP BY column
:GET Entity WITH column, SUM(field) AS alias GROUP BY column
```
### Examples
```sql theme={}
:COUNT * FROM User GROUP BY status
:SUM total FROM Order GROUP BY user_id
:GET Product WITH category, AVG(price) AS avg_price GROUP BY category
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------- |
| PostgreSQL | `SELECT status, COUNT(*) FROM users GROUP BY status` |
| MongoDB | `db.users.aggregate([{ $group: { _id: '$status', count: { $sum: 1 } } }])` |
## HAVING
Filter groups after aggregation.
```sql theme={}
:COUNT * FROM Entity GROUP BY column HAVING COUNT(*) > n
:SUM field FROM Entity GROUP BY column HAVING SUM(field) > n
```
### Examples
```sql theme={}
:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10
:SUM total FROM Order GROUP BY user_id HAVING SUM(total) > 1000
```
| Database | Output |
| ---------- | ------------------------------------------------------------------------- |
| PostgreSQL | `SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 10` |
### WHERE vs HAVING
```sql theme={}
-- WHERE: filters rows BEFORE grouping
:COUNT * FROM Order WHERE created_at > "2024-01-01" GROUP BY status
-- HAVING: filters groups AFTER aggregation
:COUNT * FROM Order GROUP BY status HAVING COUNT(*) > 5
-- Combined
:COUNT * FROM Order
WHERE created_at > "2024-01-01"
GROUP BY status
HAVING COUNT(*) > 5
```
## DISTINCT
Return unique values. Add DISTINCT after the entity.
```sql theme={}
:GET Entity DISTINCT
:GET col1, col2 FROM Entity DISTINCT
```
### Examples
```sql theme={}
:GET User DISTINCT
:GET status FROM User DISTINCT
:GET user_id, status FROM Order DISTINCT
```
| Database | Output |
| ---------- | ------------------------------ |
| PostgreSQL | `SELECT DISTINCT * FROM users` |
| MongoDB | `db.users.distinct()` |
## AS (Alias)
Rename columns in output.
```sql theme={}
:GET Entity WITH column AS alias
:GET Entity WITH SUM(field) AS alias
```
### Examples
```sql theme={}
:GET User WITH id, name AS full_name
:GET Order WITH status, SUM(total) AS revenue GROUP BY status
```
| Database | Output |
| ---------- | ----------------------------------------- |
| PostgreSQL | `SELECT id, name AS full_name FROM users` |
## SET
Specify values for UPDATE.
```sql theme={}
:UPDATE Entity SET field = value WHERE condition
:UPDATE Entity SET field1 = value1, field2 = value2 WHERE condition
```
### Examples
```sql theme={}
:UPDATE User SET name = "John" WHERE id = 1
:UPDATE User SET status = "active", updated_at = CURRENT_TIMESTAMP WHERE id = 1
:UPDATE Product SET price = price * 1.1 WHERE category = "electronics"
```
## WITH
Specify columns for SELECT or values for CREATE.
### In SELECT (columns)
```sql theme={}
:GET Entity WITH column1, column2, column3
:GET Entity WITH column, SUM(field) AS alias GROUP BY column
```
### In CREATE (values)
```sql theme={}
:CREATE Entity WITH field = value, field = value
```
### Examples
```sql theme={}
:GET User WITH id, name, email
:GET Order WITH status, SUM(total) AS revenue GROUP BY status
:CREATE User WITH name = "John", email = "john@example.com", age = 30
:CREATE Product WITH name = "Widget", price = 9.99, quantity = 100
```
COUNT(\*) is not supported in WITH clause. Use standalone syntax: `:COUNT * FROM Entity`
## ON
Specify join conditions or conflict handling.
### In Joins
```sql theme={}
:INNER JOIN Entity1 Entity2 ON field1 = field2
```
### In Upsert
```sql theme={}
:UPSERT Entity WITH fields ON conflict_column
```
### Examples
```sql theme={}
:INNER JOIN Order User ON user_id = id
:UPSERT User WITH email = "john@example.com", name = "John" ON email
```
| Database | Output |
| ---------- | --------------------------------------------------------------- |
| PostgreSQL | `INSERT INTO users (...) ON CONFLICT (email) DO UPDATE SET ...` |
| MySQL | `INSERT INTO users (...) ON DUPLICATE KEY UPDATE ...` |
## OVER (Window Functions)
Define window for window functions.
```sql theme={}
:ROW NUMBER OVER (ORDER BY column) FROM Entity
:RANK OVER (PARTITION BY column ORDER BY column) FROM Entity
```
### Examples
```sql theme={}
:ROW NUMBER OVER (ORDER BY created_at) FROM User
:RANK OVER (PARTITION BY department ORDER BY salary DESC) FROM User
:DENSE RANK OVER (PARTITION BY category ORDER BY price) FROM Product
```
## PARTITION BY
Divide rows into groups for window functions. Used inside OVER.
```sql theme={}
:FUNCTION OVER (PARTITION BY column ORDER BY column) FROM Entity
```
### Examples
```sql theme={}
:ROW NUMBER OVER (PARTITION BY department ORDER BY name) FROM User
:LAG salary OVER (PARTITION BY department ORDER BY hire_date) FROM Employee
```
## TO / FROM
Used in DCL for permission targets.
```sql theme={}
:GRANT permission ON Entity TO user
:REVOKE permission ON Entity FROM user
:ASSIGN ROLE role TO user
:REVOKE ROLE role FROM user
```
### Examples
```sql theme={}
:GRANT READ ON User TO analyst
:REVOKE DELETE ON Order FROM intern
:ASSIGN ROLE admin TO john
```
## Clause Order
Clauses must appear in this order:
```sql theme={}
:GET Entity
WITH columns
WHERE conditions
GROUP BY column
HAVING condition
ORDER BY column
LIMIT n
OFFSET m
```
### Complete Example
```sql theme={}
:SUM total FROM Order
WHERE created_at > "2024-01-01"
GROUP BY user_id
HAVING SUM(total) >= 500
ORDER BY sum DESC
LIMIT 100
OFFSET 0
```
## Clause Summary
| Clause | Purpose | Used With |
| ------------ | ----------------------- | ------------------------------------ |
| WHERE | Filter rows | GET, UPDATE, DELETE, COUNT, SUM, AVG |
| ORDER BY | Sort results | GET |
| LIMIT | Restrict count | GET |
| OFFSET | Skip rows | GET |
| GROUP BY | Group for aggregation | COUNT, SUM, AVG, MIN, MAX |
| HAVING | Filter groups | Aggregates with GROUP BY |
| DISTINCT | Unique values | GET |
| WITH | Columns or values | GET, CREATE |
| SET | Update values | UPDATE |
| ON | Join/conflict condition | JOIN, UPSERT |
| AS | Column alias | GET |
| OVER | Window definition | Window functions |
| PARTITION BY | Window grouping | Window functions |
| TO | Permission target | GRANT, ASSIGN ROLE |
| FROM | Permission source | REVOKE, REVOKE ROLE, COUNT, SUM, AVG |
## Next Steps
Type reference
Operator reference
# Data Types
Source: https://docs.omniql.com/reference/data-types
Complete data type reference
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.
```sql theme={}
: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.
```sql theme={}
: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).
```sql theme={}
:CREATE TABLE Product WITH id:AUTO, quantity:INT
```
### BIGINT
Large integer for big numbers.
```sql theme={}
:CREATE TABLE Analytics WITH id:AUTO, views:BIGINT
```
### SMALLINT
Small integer (-32,768 to 32,767).
```sql theme={}
:CREATE TABLE Rating WITH id:AUTO, score:SMALLINT
```
### DECIMAL / NUMERIC
Exact numeric with precision.
```sql theme={}
: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.
```sql theme={}
:CREATE TABLE Sensor WITH id:AUTO, temperature:FLOAT
```
| Database | Output |
| ---------- | ------------------------------ |
| PostgreSQL | `temperature DOUBLE PRECISION` |
| MySQL | `temperature DOUBLE` |
### REAL
Single-precision floating point.
```sql theme={}
:CREATE TABLE Measurement WITH id:AUTO, value:REAL
```
| Database | Output |
| ---------- | ------------- |
| PostgreSQL | `value REAL` |
| MySQL | `value FLOAT` |
## String Types
### STRING
Variable-length string.
```sql theme={}
: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.
```sql theme={}
:CREATE TABLE Post WITH id:AUTO, content:TEXT
```
### CHAR
Fixed-length string.
```sql theme={}
:CREATE TABLE Country WITH code:CHAR(2), name:STRING
```
## Boolean Types
### BOOLEAN / BOOL
True or false values.
```sql theme={}
: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
```sql theme={}
: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.
```sql theme={}
: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).
```sql theme={}
:CREATE TABLE Event WITH id:AUTO, event_time:DATETIME
```
| Database | Output |
| ---------- | ---------------------- |
| PostgreSQL | `event_time TIMESTAMP` |
| MySQL | `event_time DATETIME` |
### DATE
Date only (no time).
```sql theme={}
:CREATE TABLE Event WITH id:AUTO, event_date:DATE
```
### TIME
Time only (no date).
```sql theme={}
:CREATE TABLE Schedule WITH id:AUTO, start_time:TIME
```
### Date Literals in Queries
```sql theme={}
: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.
```sql theme={}
:CREATE TABLE User WITH id:AUTO, metadata:JSON
```
| Database | Output |
| ---------- | ----------------- |
| PostgreSQL | `metadata JSON` |
| MySQL | `metadata JSON` |
| MongoDB | `Object` (native) |
### JSONB
PostgreSQL optimized binary JSON.
```sql theme={}
:CREATE TABLE User WITH id:AUTO, metadata:JSONB
```
| Database | Output |
| ---------- | ---------------- |
| PostgreSQL | `metadata JSONB` |
| MySQL | `metadata JSON` |
## UUID Type
Universally unique identifier.
```sql theme={}
:CREATE TABLE User WITH id:UUID, name:STRING
```
| Database | Output |
| ---------- | ------------- |
| PostgreSQL | `id UUID` |
| MySQL | `id CHAR(36)` |
| MongoDB | `UUID` |
### UUID in Queries
```sql theme={}
:GET User WHERE id = "550e8400-e29b-41d4-a716-446655440000"
```
## Binary Types
### BINARY / BLOB
Binary data (files, images).
```sql theme={}
: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:
```sql theme={}
: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](/schema/advanced) for more on constraints.
## Type with Size
Specify size in parentheses:
```sql theme={}
: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
Create tables with types
Query operators
# Operators
Source: https://docs.omniql.com/reference/operators
Complete operator reference
All operators supported by OmniQL.
## Comparison Operators
| Operator | Description | Example |
| -------- | --------------------- | ---------------------------- |
| `=` | Equal | `WHERE age = 25` |
| `!=` | Not equal | `WHERE status != "inactive"` |
| `>` | Greater than | `WHERE age > 21` |
| `>=` | Greater than or equal | `WHERE age >= 18` |
| `<` | Less than | `WHERE price < 100` |
| `<=` | Less than or equal | `WHERE quantity <= 10` |
### Examples
```sql theme={}
:GET User WHERE age = 25
:GET User WHERE age != 25
:GET User WHERE age > 21
:GET User WHERE age >= 18
:GET User WHERE age < 65
:GET User WHERE age <= 30
```
## Logical Operators
| Operator | Description | Example |
| -------- | --------------------- | -------------------------------------- |
| `AND` | Both conditions true | `WHERE age > 21 AND active = true` |
| `OR` | Either condition true | `WHERE role = "admin" OR role = "mod"` |
| `NOT` | Negate condition | `WHERE NOT status = "banned"` |
### Examples
```sql theme={}
:GET User WHERE age > 21 AND status = "active"
:GET User WHERE role = "admin" OR role = "moderator"
:GET User WHERE NOT deleted = true
:GET User WHERE (age > 21 AND status = "active") OR role = "admin"
```
### Precedence
`NOT` > `AND` > `OR`
Use parentheses to control order:
```sql theme={}
-- Without parentheses: AND evaluated first
:GET User WHERE a = 1 OR b = 2 AND c = 3
-- Equivalent to: a = 1 OR (b = 2 AND c = 3)
-- With parentheses: OR evaluated first
:GET User WHERE (a = 1 OR b = 2) AND c = 3
```
## Arithmetic Operators
| Operator | Description | Example |
| -------- | -------------- | ------------------ |
| `+` | Addition | `price + tax` |
| `-` | Subtraction | `total - discount` |
| `*` | Multiplication | `quantity * price` |
| `/` | Division | `total / count` |
| `%` | Modulo | `id % 2` |
### Examples
```sql theme={}
:GET Product WITH name, price, price * 1.1 AS with_tax
:UPDATE Product SET price = price * 0.9 WHERE category = "sale"
:UPDATE Account SET balance = balance + 100 WHERE id = 1
:GET User WHERE id % 2 = 0
```
## Range Operators
### BETWEEN
```sql theme={}
:GET User WHERE age BETWEEN 18 AND 65
:GET Order WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"
```
| Database | Output |
| ---------- | ------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE age BETWEEN 18 AND 65` |
| MySQL | `SELECT * FROM users WHERE age BETWEEN 18 AND 65` |
| MongoDB | `db.users.find({ age: { $gte: 18, $lte: 65 } })` |
### NOT BETWEEN
```sql theme={}
:GET Product WHERE price NOT BETWEEN 10 AND 50
```
## Set Operators
### IN
```sql theme={}
:GET User WHERE role IN ("admin", "moderator", "editor")
:GET Order WHERE status IN ("pending", "processing")
```
| Database | Output |
| ---------- | -------------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE role IN ('admin', 'moderator', 'editor')` |
| MySQL | `SELECT * FROM users WHERE role IN ('admin', 'moderator', 'editor')` |
| MongoDB | `db.users.find({ role: { $in: ['admin', 'moderator', 'editor'] } })` |
### NOT IN
```sql theme={}
:GET User WHERE status NOT IN ("banned", "suspended")
```
| Database | Output |
| ---------- | ----------------------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE status NOT IN ('banned', 'suspended')` |
| MongoDB | `db.users.find({ status: { $nin: ['banned', 'suspended'] } })` |
## Pattern Operators
### LIKE
| Pattern | Meaning |
| ------- | -------------------------- |
| `%` | Any sequence of characters |
| `_` | Any single character |
```sql theme={}
:GET User WHERE name LIKE "John%"
:GET User WHERE email LIKE "%@gmail.com"
:GET User WHERE name LIKE "%smith%"
:GET User WHERE code LIKE "A_123"
```
| Database | Output |
| ---------- | ---------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE name LIKE 'John%'` |
| MySQL | `SELECT * FROM users WHERE name LIKE 'John%'` |
| MongoDB | `db.users.find({ name: { $regex: '^John' } })` |
### NOT LIKE
```sql theme={}
:GET User WHERE email NOT LIKE "%@test.com"
```
### ILIKE (Case Insensitive)
```sql theme={}
:GET User WHERE name ILIKE "john%"
```
| Database | Output |
| ---------- | ---------------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE name ILIKE 'john%'` |
| MySQL | `SELECT * FROM users WHERE LOWER(name) LIKE 'john%'` |
Note: ILIKE is PostgreSQL-native. MySQL translates to LIKE with LOWER().
## NULL Operators
### IS NULL
```sql theme={}
:GET User WHERE deleted_at IS NULL
```
| Database | Output |
| ---------- | ---------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE deleted_at IS NULL` |
| MySQL | `SELECT * FROM users WHERE deleted_at IS NULL` |
| MongoDB | `db.users.find({ deleted_at: null })` |
### IS NOT NULL
```sql theme={}
:GET User WHERE phone IS NOT NULL
```
| Database | Output |
| ---------- | --------------------------------------------- |
| PostgreSQL | `SELECT * FROM users WHERE phone IS NOT NULL` |
| MongoDB | `db.users.find({ phone: { $ne: null } })` |
## Operator Summary by Database
| Operator | PostgreSQL | MySQL | MongoDB |
| ------------- | ------------- | ------------- | -------------------- |
| `=` | `=` | `=` | `$eq` |
| `!=` | `!=` | `!=` | `$ne` |
| `>` | `>` | `>` | `$gt` |
| `>=` | `>=` | `>=` | `$gte` |
| `<` | `<` | `<` | `$lt` |
| `<=` | `<=` | `<=` | `$lte` |
| `IN` | `IN` | `IN` | `$in` |
| `NOT IN` | `NOT IN` | `NOT IN` | `$nin` |
| `BETWEEN` | `BETWEEN` | `BETWEEN` | `$gte/$lte` |
| `LIKE` | `LIKE` | `LIKE` | `$regex` |
| `ILIKE` | `ILIKE` | `LIKE` | `$regex` with i flag |
| `IS NULL` | `IS NULL` | `IS NULL` | `null` |
| `IS NOT NULL` | `IS NOT NULL` | `IS NOT NULL` | `$ne: null` |
| `AND` | `AND` | `AND` | implicit |
| `OR` | `OR` | `OR` | `$or` |
| `NOT` | `NOT` | `NOT` | `$not` |
## Limitations
Not currently supported in OmniQL (use native SQL):
* JSON operators (`->`, `->>`, `@>`, `?`)
* Array operators (`ANY`, `ALL`)
* String concatenation (`||`)
* EXISTS / NOT EXISTS subqueries
## Next Steps
All clause reference
Type reference
# Advanced Schema
Source: https://docs.omniql.com/schema/advanced
Column constraints
## Column Constraints
OmniQL supports three column constraints using colon syntax:
```sql theme={}
:CREATE TABLE User WITH
id:AUTO,
email:STRING:NOTNULL:UNIQUE,
name:STRING:NOTNULL
```
| Constraint | Syntax | Effect |
| ----------- | ------------- | --------------------- |
| Not Null | `:NOTNULL` | Column cannot be NULL |
| Unique | `:UNIQUE` | Values must be unique |
| Primary Key | `:PRIMARYKEY` | Column is primary key |
### Multiple Constraints
Chain multiple constraints with colons:
```sql theme={}
:CREATE TABLE Product WITH
id:AUTO,
sku:STRING:NOTNULL:UNIQUE,
name:STRING:NOTNULL,
price:DECIMAL
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL | `CREATE TABLE products (id SERIAL PRIMARY KEY, sku VARCHAR NOT NULL UNIQUE, name VARCHAR NOT NULL, price DECIMAL)` |
| MySQL | `CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, price DECIMAL)` |
## Database-Specific Features
For advanced features like sequences, custom types, triggers, and stored procedures, see database-specific documentation:
* [PostgreSQL Features](/databases/postgresql) - Sequences, ENUM types, domains, triggers, policies
* [MySQL Features](/databases/mysql)
* [MongoDB Features](/databases/mongodb)
* [Redis Features](/databases/redis)
## Limitations
Not supported in OmniQL (use native SQL):
* CHECK constraints
* Foreign key references (REFERENCES)
* ON DELETE / ON UPDATE actions
* DEFAULT values
* Composite primary keys
## Next Steps
PostgreSQL-specific features
Group operations safely
# Indexes
Source: https://docs.omniql.com/schema/indexes
Optimize query performance with indexes
Indexes speed up data retrieval at the cost of slower writes.
## Basic Syntax
```sql theme={}
:CREATE INDEX Entity index_name:column
:CREATE INDEX Entity index_name:column UNIQUE
:DROP INDEX Entity index_name
```
## Create Index
```sql theme={}
:CREATE INDEX User idx_email:email
```
| Database | Output |
| ---------- | ----------------------------------------- |
| PostgreSQL | `CREATE INDEX idx_email ON users (email)` |
| MySQL | `CREATE INDEX idx_email ON users (email)` |
## Unique Index
```sql theme={}
:CREATE INDEX User idx_email:email UNIQUE
```
| Database | Output |
| ---------- | ------------------------------------------------ |
| PostgreSQL | `CREATE UNIQUE INDEX idx_email ON users (email)` |
| MySQL | `CREATE UNIQUE INDEX idx_email ON users (email)` |
## Drop Index
```sql theme={}
:DROP INDEX User idx_email
```
| Database | Output |
| ---------- | -------------------------------- |
| PostgreSQL | `DROP INDEX IF EXISTS idx_email` |
| MySQL | `DROP INDEX idx_email ON users` |
## When to Use Indexes
| Use Case | Recommendation |
| ---------------------- | -------------------------- |
| Frequent WHERE clauses | Index the filtered column |
| Unique constraints | Use UNIQUE index |
| Foreign keys | Index the reference column |
| Sorting | Index the ORDER BY column |
## Index Best Practices
**Do:**
* Index columns used in WHERE clauses
* Index columns used in JOIN conditions
* Index columns used in ORDER BY
* Use unique indexes for email, username, etc.
**Don't:**
* Over-index (slows down writes)
* Index rarely queried columns
* Index very small tables
## Complete Examples
### User Table
```sql theme={}
:CREATE INDEX User idx_email:email UNIQUE
:CREATE INDEX User idx_status:status
:CREATE INDEX User idx_created:created_at
```
### Order Table
```sql theme={}
:CREATE INDEX Order idx_user:user_id
:CREATE INDEX Order idx_status:status
:CREATE INDEX Order idx_created:created_at
```
## Database Support
| Feature | PostgreSQL | MySQL | MongoDB |
| ------------------- | ---------- | ----- | ---------- |
| Single column index | ✅ | ✅ | Via driver |
| UNIQUE modifier | ✅ | ✅ | Via driver |
| DROP INDEX | ✅ | ✅ | Via driver |
For MongoDB indexes, use native driver methods.
## Limitations
Current index implementation supports:
* Single column indexes
* UNIQUE constraint
For advanced indexes (composite, partial, full-text, GIN), use native SQL.
## Next Steps
Create virtual tables
Group operations safely
# Tables
Source: https://docs.omniql.com/schema/tables
Create and manage database tables
Create and manage tables using DDL operations.
## Create Table
```sql theme={}
:CREATE TABLE Entity WITH column:TYPE, column:TYPE:CONSTRAINT
```
## Basic Table
```sql theme={}
: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
```sql theme={}
:CREATE TABLE User WITH id:AUTO, name:STRING(100), description:TEXT
```
## With Constraints
Constraints are added after the type using colons:
```sql theme={}
: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
```sql theme={}
:CREATE TABLE User WITH id:AUTO, email:STRING:NOTNULL:UNIQUE
```
## Complete Example
```sql theme={}
:CREATE TABLE User WITH
id:AUTO,
email:STRING:NOTNULL:UNIQUE,
name:STRING:NOTNULL,
active:BOOLEAN,
created_at:TIMESTAMP
```
## Drop Table
```sql theme={}
:DROP TABLE User
```
| Database | Output |
| ---------- | ------------------ |
| PostgreSQL | `DROP TABLE users` |
| MySQL | `DROP TABLE users` |
| MongoDB | `db.users.drop()` |
## Alter Table
### Add Column
```sql theme={}
:ALTER TABLE User ADD name:STRING
```
| Database | Output |
| ---------- | ------------------------------------------- |
| PostgreSQL | `ALTER TABLE users ADD COLUMN name VARCHAR` |
### Drop Column
```sql theme={}
:ALTER TABLE User DROP name
```
### Rename Column
```sql theme={}
:ALTER TABLE User RENAME name:full_name
```
### Modify Column Type
```sql theme={}
:ALTER TABLE User MODIFY name:TEXT
```
## Rename Table
```sql theme={}
: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`:
```sql theme={}
:CREATE COLLECTION User
:DROP COLLECTION User
```
## Next Steps
Create virtual tables
Optimize query performance
# Views
Source: https://docs.omniql.com/schema/views
Create virtual tables with views
Views are virtual tables based on query results.
## Basic Syntax
```sql theme={}
:CREATE VIEW ViewName AS GET Entity WHERE condition
:ALTER VIEW ViewName AS GET Entity WHERE condition
:DROP VIEW ViewName
```
## Create View
```sql theme={}
:CREATE VIEW ActiveUser AS GET User WHERE active = true
```
| Database | Output |
| ---------- | ------------------------------------------------------------------- |
| PostgreSQL | `CREATE VIEW activeuser AS SELECT * FROM users WHERE active = true` |
| MySQL | `CREATE VIEW activeuser AS SELECT * FROM users WHERE active = true` |
## View with Columns
```sql theme={}
:CREATE VIEW UserSummary AS GET id, name, email FROM User WHERE active = true
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------- |
| PostgreSQL | `CREATE VIEW usersummary AS SELECT id, name, email FROM users WHERE active = true` |
## View with Aggregation
```sql theme={}
:CREATE VIEW OrderTotals AS GET Order WITH user_id, SUM(amount) AS total_spent GROUP BY user_id
```
| Database | Output |
| ---------- | ---------------------------------------------------------------------------------------------------- |
| PostgreSQL | `CREATE VIEW ordertotals AS SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id` |
Views with COUNT(\*) require native SQL. Use SUM, AVG, MIN, MAX with OmniQL views.
## Query a View
Views are queried like regular tables.
```sql theme={}
:GET ActiveUser WHERE role = "admin"
:GET OrderTotals WHERE total_spent > 1000
```
## Alter View
Update an existing view definition.
```sql theme={}
:ALTER VIEW ActiveUser AS GET User WHERE active = true AND verified = true
```
| Database | Output |
| ---------- | -------------------------------------------------------------------------------------------------- |
| PostgreSQL | `CREATE OR REPLACE VIEW activeuser AS SELECT * FROM users WHERE active = true AND verified = true` |
| MySQL | `CREATE OR REPLACE VIEW activeuser AS SELECT * FROM users WHERE active = true AND verified = true` |
## Drop View
```sql theme={}
:DROP VIEW ActiveUser
```
| Database | Output |
| ---------- | -------------------------------- |
| PostgreSQL | `DROP VIEW IF EXISTS activeuser` |
| MySQL | `DROP VIEW IF EXISTS activeuser` |
## Complete Examples
### Active Premium Users
```sql theme={}
:CREATE VIEW PremiumUser AS GET id, name, email, created_at FROM User WHERE role = "premium" AND active = true
```
### Low Stock Products
```sql theme={}
:CREATE VIEW LowStock AS GET id, name, sku, quantity FROM Product WHERE quantity < 10 AND active = true
```
### Revenue by Status
```sql theme={}
:CREATE VIEW RevenueByStatus AS GET Order WITH status, SUM(total) AS revenue GROUP BY status
```
## Limitations
Current view implementation supports:
* Simple SELECT queries with WHERE, ORDER BY, LIMIT
* Column selection
* Aggregations with SUM, AVG, MIN, MAX
Not currently supported in OmniQL views:
* COUNT(\*) aggregations (use native SQL)
* Views with JOINs (use native SQL)
* Materialized views (PostgreSQL-specific, use native SQL)
* CREATE OR REPLACE (use ALTER VIEW instead)
## MongoDB Note
MongoDB does not support traditional views. Use aggregation pipelines or read-only collections for similar functionality.
## Next Steps
Optimize query performance
Group operations safely