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 Node.js 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 { ZTeraDBConnection, ZTeraDBQuery } from "zteradb";

const config = JSON.parse(process.env.ZTERADB_CONFIG);

const db = new ZTeraDBConnection(config, "db1.zteradb.com", 7777);

1️⃣ INSERT Examples

✔ Insert a User​

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

const result = await db.run(query);

console.log("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​

const query = new ZTeraDBQuery("user").select();

const users = await db.run(query);

for await (const row of users) {
  console.log(row);
}

Equivalent SQL

SELECT * FROM "user";

✔ Select Users Where Status = True​

const query = new ZTeraDBQuery("user")
  .select()
  .filter({ status: true });

Equivalent SQL

SELECT * FROM "user"
WHERE status = TRUE;

✔ Select Only Email Field​

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

Equivalent SQL

SELECT email FROM "user";

✔ Limit Results​

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

Equivalent SQL

SELECT * FROM "user"
LIMIT 10 OFFSET 0;

3️⃣ SELECT with Advanced Filters

✔ Get Products Where price * quantity > 500​

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

const query = new ZTeraDBQuery("product")
  .select()
  .filterConditions(condition);

Equivalent SQL

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

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

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

const query = new ZTeraDBQuery("user")
  .select()
  .filterConditions(condition);

Equivalent SQL

SELECT * FROM "user"
WHERE LOWER(name) LIKE '%john%';

4️⃣ UPDATE Examples

✔ Update User Status​

const query = new ZTeraDBQuery("user")
  .update()
  .fields({ status: false })
  .filter({ id: 1 });

const result = await db.run(query);

console.log("Updated:", result.is_updated);

Equivalent SQL

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

5️⃣ DELETE Examples

✔ Delete a Product by ID​

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

const result = await db.run(query);

console.log("Deleted:", result.is_deleted);

Equivalent SQL

DELETE FROM product
WHERE id = 'PRODUCT_ID';

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

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

const userFilter = new ZTeraDBQuery("user")
  .select()
  .filter({ status: true });

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

Equivalent SQL (example mapping)

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

7️⃣ Sorting Examples

✔ Sort Products by Price ASC​

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

Equivalent SQL

SELECT * FROM product
ORDER BY price ASC;

✔ Sort by Multiple Fields​

.sort({
  price: 1,
  quantity: -1
});

Equivalent SQL

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

8️⃣ Counting Rows

const query = new ZTeraDBQuery("product").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.

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

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

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 know how to use every major query feature in ZTeraDB — and their SQL equivalents!
Next:
👉 Check quickstart.md for a 10-minute onboarding guide.