Skip to main content

ZTeraDB Query Examples

This file contains real-world examples of all major operations in ZTeraDB:

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
  • Complex Filters
  • Sorting
  • Pagination

These examples are written for Python developers and show the SQL that the ZTeraDB query represents. Adjust table/column names to match your actual schema as needed.

📦 Setup: Create a Connection (Used in All Examples)

import os
import asyncio
from zteradb.zteradb_config import ZTeraDBConfig, Options, ENVS, ResponseDataTypes
from zteradb.zteradb_connection import ZTeraDBConnectionAsync
from zteradb.zteradb_query import ZTeraDBQuery

config = ZTeraDBConfig(
    client_key=os.getenv("CLIENT_KEY"),
    access_key=os.getenv("ACCESS_KEY"),
    secret_key=os.getenv("SECRET_KEY"),
    database_id=os.getenv("DATABASE_ID"),
    env=ENVS(os.getenv("ZTERADB_ENV", "dev")),
    response_data_type=ResponseDataTypes("json"),
    options=Options(connection_pool=dict(min=1, max=5))
)

db = ZTeraDBConnectionAsync(
    os.getenv("ZTERADB_HOST"),
    int(os.getenv("ZTERADB_PORT")),
    config
)

1️⃣ INSERT Examples

✔ Insert a User​

query = (
    ZTeraDBQuery("user")
    .insert()
    .fields({
        "email": "john@example.com",
        "password": "hashed_pw",
        "status": True
    })
)

result = await db.run(query)
print("Inserted ID:", result["last_insert_id"])

Equivalent SQL

INSERT INTO "user" (email, password, status)
VALUES ('john@example.com', 'hashed_pw', TRUE);

2️⃣ SELECT Examples

✔ Select All Users​

query = ZTeraDBQuery("user").select()
users = await db.run(query)
print(users)

Equivalent SQL

SELECT * FROM "user";

✔ Select Users Where Status = True​

query = (
    ZTeraDBQuery("user")
    .select()
    .filter({"status": True})
) 

users = await db.run(query)

Equivalent SQL

SELECT * FROM "user"
WHERE status = TRUE;

✔ Select Only Email Field​

query = (
    ZTeraDBQuery("user")
    .select()
    .fields({"email": 1})
)

users = await db.run(query)

Equivalent SQL

SELECT email FROM "user";

✔ Limit Results​

query = (
    ZTeraDBQuery("user")
    .select()
    .limit(0, 10)
)

users = await db.run(query)

Equivalent SQL

-- limit(start, count) where start=0, count=10
SELECT * FROM "user"
LIMIT 10 OFFSET 0;

3️⃣ SELECT with Advanced Filters

✔ Get Products Where price * quantity > 500​

from zteradb.filter_condition import ZTGT, ZTMUL

condition = ZTGT([
    ZTMUL(["price", "quantity"]),
    500
])

query = (
    ZTeraDBQuery("product")
    .select()
    .filterCondition(condition)
)

rows = await db.run(query)

Equivalent SQL

SELECT * FROM product
WHERE (price * quantity) > 500;

✔ Find Users with name containing "john" (case-insensitive)​

from zteradb.filter_condition import ZTICONTAINS

condition = ZTICONTAINS("name", "john")

query = (
    ZTeraDBQuery("user")
    .select()
    .filterCondition(condition)
)

rows = await db.run(query)

Equivalent SQL

-- case-insensitive search (works in PostgreSQL)
SELECT * FROM "user"
WHERE LOWER(name) LIKE '%john%';

(On MySQL use LOWER(name) LIKE '%john%' or name ILIKE '%john%' on PostgreSQL for case-insensitive depending on DB.)

4️⃣ UPDATE Examples

✔ Update User Status​

query = (
    ZTeraDBQuery("user")
    .update()
    .fields({"status": False})
    .filter({"id": 1})
)

result = await db.run(query)
print("Updated:", result.get("is_updated"))

Equivalent SQL

UPDATE "user"
SET status = FALSE
WHERE id = 1;

5️⃣ DELETE Examples

✔ Delete a Product by ID​

query = (
    ZTeraDBQuery("product")
    .delete()
    .filter({"id": "PRODUCT_ID"})
)

result = await db.run(query)
print("Deleted:", result.get("is_deleted"))

Equivalent SQL

DELETE FROM product
WHERE id = 'PRODUCT_ID';

6️⃣ Example with Related Fields (Join-like)

Get all orders where related user's status = true.

userFilter = (
    ZTeraDBQuery("user")
    .select()
    .filter({"status": True})
)

query = (
    ZTeraDBQuery("order")
    .select()
    .relatedFields({"user": userFilter})
)

rows = await db.run(query)

Equivalent SQL (example mapping)

-- Assuming `order.user_id` references `user.id`
SELECT o.*, u.*
FROM "order" o
JOIN "user" u ON o.user_id = u.id
WHERE u.status = TRUE;

(Adjust column/table names to your schema.)

7️⃣ Sorting Examples

✔ Sort Products by Price ASC​

query = (
    ZTeraDBQuery("product")
    .select()
    .sort({"price": 1})
)

rows = await db.run(query)

Equivalent SQL

SELECT * FROM product
ORDER BY price ASC;

✔ Sort by Multiple Fields​

query = (
    ZTeraDBQuery("product")
    .select()
    .sort({
        "price": 1,
        "quantity": -1
    })
)

rows = await db.run(query)

Equivalent SQL

SELECT * FROM product
ORDER BY price ASC, quantity DESC;

8️⃣ Counting Rows

query = ZTeraDBQuery("product").count()

result = await db.run(query)
print("Count:", result["count"])

Equivalent SQL

SELECT COUNT(*) AS count FROM product;

9️⃣ Full Example: Combined Query

Get available products where quantity > 10, sorted by price, first 20 rows.

from zteradb.filter_condition import ZTGT

condition = ZTGT(["quantity", 10])

query = (
    ZTeraDBQuery("product")
    .select()
    .fields({"name": 1, "price": 1, "quantity": 1})
    .filterCondition(condition)
    .filter({"status": "A"})
    .sort({"price": 1})
    .limit(0, 20)
)

rows = await db.run(query)

Equivalent SQL

SELECT name, price, quantity
FROM product
WHERE quantity > 10
  AND status = 'A'
ORDER BY price ASC
LIMIT 20 OFFSET 0;

🎉 End of Examples

You now have SQL equivalents for every major ZTeraDB query pattern shown above!
Next:
👉 Check Quick Start for a 10-minute onboarding guide.