Skip to main content

🍳 Query Cookbook

This reference guide provides production-ready code blocks for standard CRUD operations, complex mathematical evaluations, sorting configurations, and pagination pipelines using the ZTeraDB Query Builder.

📌 Table of Contents​

⚙️ Initial Core Setup​

Every example below assumes an active, pre-configured ZTeraDBConnection lifecycle instance initialized via your global configuration layer:

import { ZTeraDBConfig, ZTeraDBConnection, ZTeraDBQuery } from '@zteradb/client';  // Or using commonJS: const { ZTeraDBQuery } = require('@zteradb/client');

// Initialize connection via localized runtime environments
const config = new ZTeraDBConfig(JSON.parse(process.env.ZTERADB_CONFIG));
const db = new ZTeraDBConnection(config, 'db1.zteradb.com', 7777);

1️⃣ Mutation Operators (INSERT)​

Insert a Single Record​

Builds structured data blocks mapping parameters explicitly to target database storage engines.

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

const result = await db.run(query);

console.log('Generated Primary Key ID: ' + result.last_insert_id);

Equivalent SQL

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

2️⃣ Retrieval Operators (SELECT)​

Select All Table Records​

const query = new ZTeraDBQuery('user').select();
const users = await db.run(query);

Equivalent SQL

SELECT * FROM "user";

Select with Basic Scalar Filtering​

For simple exact-match lookups, pass your payload parameters directly to the .filter() helper method.

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

const users = await db.run(query);

Equivalent SQL

SELECT * FROM "user" WHERE status = TRUE;

Explicit Column Selection​

Reduce wire overhead payloads by picking explicitly specified column arrays.

const query = new ZTeraDBQuery('user')
    .select()
    .fields({ email: 1 }); // Set field map bit-flags to 1 to inclusion-select

const users = await db.run(query);

Equivalent SQL

SELECT email FROM "user";

Row Window Offsetting (Pagination Boundary)​

const query = new ZTeraDBQuery('user')
    .select()
    .limit(0, 10); // API Mapping: limit(int offset, int count)

const users = await db.run(query);

Equivalent SQL

SELECT * FROM "user" LIMIT 10 OFFSET 0;

3️⃣ Advanced Functional Filters​

For complex conditions that extend beyond standard associative key-value loops, inject pre-compiled filter trees directly into .filterCondition().

Inline Mathematical Validations​

// Compiles structural calculation boundary rules
const condition = ZTGT([
    ZTMUL(['price', 'quantity']),
    500
]);

const query = new ZTeraDBQuery('product')
    .select()
    .filterCondition(condition);

const rows = await db.run(query);

Equivalent SQL

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

Case-Insensitive Fuzzy Text Matching​

// Utilizing case-insensitive string matcher helpers
const condition = ZTICONTAINS('name', 'john');

const query = new ZTeraDBQuery('user')
    .select()
    .filterCondition(condition);

const rows = await db.run(query);

Equivalent SQL

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

4️⃣ Update & Delete Mutations​

Conditional Record Updates​

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

const result = await db.run(query);

console.log(result.is_updated ? 'Update Success' : 'No Changes Made');

Equivalent SQL

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

Target Record Hard Erasures​

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

const result = await db.run(query);

Equivalent SQL

DELETE FROM product WHERE id = 'PRODUCT_ID';

5️⃣ Relational Join Operations​

Execute structured data links across foreign key references using nested subquery representations via .relatedFields().

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

const query = new ZTeraDBQuery('order')
    .select()
    .relatedFields({
        user: userFilter // Maps target collection bindings implicitly 
    });

const rows = await db.run(query);

Equivalent SQL

-- Conceptual Engine Join Mapping
SELECT o.*, u.*
FROM "order" o
JOIN "user" u ON o.user_id = u.id
WHERE u.status = TRUE;

6️⃣ Result Set​

Ascending Sorting Sequence​

const query = new ZTeraDBQuery('product')
    .select()
    .sort({ price: 1 }); // 1 signifies Ascending sorting direction

Equivalent SQL

SELECT * FROM product ORDER BY price ASC;

Multi-Key Compound Sorting​

const query = new ZTeraDBQuery('product')
    .select()
    .sort({
        price: 1,    // Ascending
        quantity: -1 // -1 signifies Descending sorting direction
    });

Equivalent SQL

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

Table Matrix Record Counting​

const query = new ZTeraDBQuery('product').count();
const result = await db.run(query);

console.log('Active Table Row Count: ' + result.count);

Equivalent SQL

SELECT COUNT(*) AS count FROM product;

🏆 Unified Master Blueprint Example​

The comprehensive blueprint below combines explicit field lookups, complex mathematical operators, exact-match flags, multi-index sorting arrangements, and strict window pagination limitations into a singular processing chain.

const { ZTeraDBQuery } = require('@zteradb/client'); // Or using ES modules: import { ZTeraDBQuery } from "@zteradb/client";

// 1. Build complex condition logic tree
const mathCondition = ZTGT(['quantity', 10]);

// 2. Aggregate the composite processing sequence
const query = new ZTeraDBQuery('product')
    .select()
    .fields({
        name: 1, 
        price: 1, 
        quantity: 1
    })
    .filterCondition(mathCondition)
    .filter({ status: 'A' })
    .sort({ price: 1 })
    .limit(0, 20);

// 3. Execute query statement via storage connection driver
const products = await db.run(query);

Equivalent SQL

Compiled Pipeline Target Output

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

🎉 Next Steps​