api-v1/db/queryHelper/DEVELOPER_GUIDE.md

# Query Helper - Developer Guide

Complete guide for developers on how to use the JSON-based database query helper in this codebase.

## 📚 Table of Contents

1. [Introduction](#introduction)
2. [Getting Started](#getting-started)
3. [Core Functions](#core-functions)
4. [Real-World Examples](#real-world-examples)
5. [Common Patterns](#common-patterns)
6. [Flow & Architecture](#flow--architecture)
7. [Best Practices](#best-practices)
8. [Troubleshooting](#troubleshooting)

---

## Introduction

The Query Helper is a centralized, SQL-string-free database interface that uses JSON objects to build safe, parameterized queries. It eliminates the need for raw SQL strings and provides a consistent, maintainable way to interact with the database.

### Why Use Query Helper?

✅ **No SQL Strings** - All queries built using JSON objects  
✅ **SQL Injection Safe** - All queries are parameterized  
✅ **Type Safe** - Structured JSON prevents errors  
✅ **Consistent** - Same pattern across all routes  
✅ **Maintainable** - Easy to read and modify  
✅ **Testable** - Easy to mock and test  

---

## Getting Started

### 1. Import the Functions

```javascript
import { select, insert, update, deleteRecord, execute } from '../db/queryHelper/index.js';
```

### 2. Basic Usage Pattern

```javascript
// SELECT example
const users = await select({
    table: 'users',
    where: { deleted: false },
    limit: 10
});

// INSERT example
const user = await insert({
    table: 'users',
    data: { name: 'John', phone_number: '+1234567890' },
    returning: '*'
});
```

---

## Core Functions

### 1. `select(options)` - Retrieve Records

**Purpose:** Fetch records from the database with filtering, sorting, and pagination.

**Options:**
- `table` (string, **required**): Table name
- `columns` (string[] | '*', optional): Columns to select (default: '*')
- `where` (object, optional): WHERE conditions
- `orderBy` (object, optional): `{ column: string, direction: 'asc'|'desc' }`
- `limit` (number, optional): Max records (capped at 100)
- `offset` (number, optional): Skip records
- `joins` (array, optional): Join configurations

**Returns:** `Promise<object[]>` - Array of records

**Example from codebase:**
```javascript
// From routes/userRoutes.js - GET All Users
router.get("/", async (req, res) => {
    try {
        const { limit = 100, offset = 0 } = req.query;
        
        const users = await select({
            table: 'users',
            columns: ['id', 'name', 'phone_number', 'avatar_url', 'language', 'timezone', 'country_code', 'is_active', 'created_at', 'updated_at'],
            where: {
                deleted: false,
            },
            orderBy: {
                column: 'created_at',
                direction: 'desc',
            },
            limit: Math.min(limit, 100),
            offset: offset,
        });

        res.json(users);
    } catch (err) {
        console.error("Error fetching users:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

### 2. `insert(options)` - Create Records

**Purpose:** Insert new record(s) into the database.

**Options:**
- `table` (string, **required**): Table name
- `data` (object | object[], **required**): Data to insert (single object or array for batch)
- `returning` (string[] | '*', optional): Columns to return (PostgreSQL)

**Returns:** `Promise<object | object[]>` - Inserted record(s)

**Example from codebase:**
```javascript
// From routes/userRoutes.js - CREATE User
router.post("/", async (req, res) => {
    try {
        const {
            id, // Optional: if provided, use this UUID; otherwise generate one
            name,
            phone_number,
            avatar_url,
            language,
            timezone,
            country_code = "+91",
        } = req.body;

        // Build user data object
        const userData = {
            name,
            phone_number,
            avatar_url: avatar_url || null,
            language: language || null,
            timezone: timezone || null,
            country_code,
        };

        // If id is provided, include it; otherwise let the database generate one
        if (id) {
            userData.id = id;
        }

        const user = await insert({
            table: 'users',
            data: userData,
            returning: '*',
        });

        res.status(201).json(user);
    } catch (err) {
        console.error("Error creating user:", err);
        // ... error handling
    }
});
```

### 3. `update(options)` - Update Records

**Purpose:** Update existing records in the database.

**Options:**
- `table` (string, **required**): Table name
- `data` (object, **required**): Data to update
- `where` (object, **required**): WHERE conditions (required for safety)
- `returning` (string[] | '*', optional): Columns to return

**Returns:** `Promise<object[]>` - Updated records

**Example from codebase:**
```javascript
// From routes/userRoutes.js - UPDATE User
router.put("/:id", async (req, res) => {
    try {
        const { id } = req.params;
        const { name, phone_number, avatar_url, language, timezone, country_code, is_active } = req.body;

        // Build update data object (only include fields that are provided)
        const updateData = {};
        if (name !== undefined) updateData.name = name;
        if (phone_number !== undefined) updateData.phone_number = phone_number;
        if (avatar_url !== undefined) updateData.avatar_url = avatar_url;
        if (language !== undefined) updateData.language = language;
        if (timezone !== undefined) updateData.timezone = timezone;
        if (country_code !== undefined) updateData.country_code = country_code;
        if (is_active !== undefined) updateData.is_active = is_active;

        const updated = await update({
            table: 'users',
            data: updateData,
            where: {
                id: id,
                deleted: false,
            },
            returning: '*',
        });

        if (updated.length === 0) {
            return res.status(404).json({ error: "User not found" });
        }

        res.json(updated[0]);
    } catch (err) {
        console.error("Error updating user:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

### 4. `deleteRecord(options)` - Delete Records

**Purpose:** Delete records from the database (hard delete).

**Options:**
- `table` (string, **required**): Table name
- `where` (object, **required**): WHERE conditions (required for safety)
- `returning` (string[] | '*', optional): Columns to return

**Returns:** `Promise<object[]>` - Deleted records

**Note:** For soft deletes, use `update()` instead (see example below).

**Example - Soft Delete (Recommended):**
```javascript
// From routes/userRoutes.js - DELETE User (Soft Delete)
router.delete("/:id", async (req, res) => {
    try {
        const { id } = req.params;
        
        const deleted = await update({
            table: 'users',
            data: {
                deleted: true,
            },
            where: {
                id: id,
            },
            returning: ['id'],
        });

        if (deleted.length === 0) {
            return res.status(404).json({ error: "User not found" });
        }

        res.json({ message: "User deleted successfully" });
    } catch (err) {
        console.error("Error deleting user:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

### 5. `execute(options)` - Transactions & Custom Queries

**Purpose:** Execute transactions or custom query builder logic.

**Options:**
- `type` (string, **required**): 'transaction' or 'raw-builder'
- `handler` (function, **required**): Handler function receiving knex/trx instance

**Returns:** `Promise<any>` - Result from handler function

**Example - Transaction:**
```javascript
// Creating a listing with animal in a transaction
const result = await execute({
    type: 'transaction',
    handler: async (trx) => {
        // Insert animal
        const animalResult = await trx('animals')
            .insert({
                species_id: animal.species_id,
                breed_id: animal.breed_id,
                sex: animal.sex,
                // ... other fields
            })
            .returning('*');

        const animal_id = animalResult[0].id;

        // Insert listing
        const listingResult = await trx('listings')
            .insert({
                seller_id: seller_id,
                animal_id: animal_id,
                title: title,
                price: price,
                // ... other fields
            })
            .returning('*');

        return listingResult[0];
    },
});
```

**Example - Custom Query Builder:**
```javascript
// Complex query with multiple conditions on same column
const listings = await execute({
    type: 'raw-builder',
    handler: async (knex) => {
        let query = knex('listings')
            .where('deleted', false)
            .where('status', status);

        if (price_min) {
            query = query.where('price', '>=', price_min);
        }

        if (price_max) {
            query = query.where('price', '<=', price_max);
        }

        return await query
            .orderBy('created_at', 'desc')
            .limit(limit)
            .offset(offset);
    },
});
```

---

## WHERE Clause Operators

### Simple Equality
```javascript
where: { status: 'active' }
// SQL: WHERE status = 'active'
```

### Comparison Operators
```javascript
where: {
    price: { op: '>=', value: 1000 },
    age: { op: '<', value: 65 }
}
// SQL: WHERE price >= 1000 AND age < 65
```

### IN Operator
```javascript
where: {
    id: { op: 'in', value: ['uuid1', 'uuid2', 'uuid3'] }
}
// SQL: WHERE id IN ('uuid1', 'uuid2', 'uuid3')
```

### LIKE Operator
```javascript
where: {
    name: { op: 'ilike', value: '%john%' }
}
// SQL: WHERE name ILIKE '%john%'
```

### BETWEEN Operator
```javascript
where: {
    age: { op: 'between', value: [18, 65] }
}
// SQL: WHERE age BETWEEN 18 AND 65
```

### NULL Checks
```javascript
where: {
    deleted_at: { op: 'isNull' }
}
// SQL: WHERE deleted_at IS NULL
```

### Supported Operators

| Operator | Description | Example |
|----------|-------------|---------|
| `>` | Greater than | `{ op: '>', value: 100 }` |
| `<` | Less than | `{ op: '<', value: 100 }` |
| `>=` | Greater than or equal | `{ op: '>=', value: 100 }` |
| `<=` | Less than or equal | `{ op: '<=', value: 100 }` |
| `!=` or `<>` | Not equal | `{ op: '!=', value: 'deleted' }` |
| `in` | In array | `{ op: 'in', value: [1, 2, 3] }` |
| `notIn` | Not in array | `{ op: 'notIn', value: [1, 2, 3] }` |
| `like` | Case-sensitive LIKE | `{ op: 'like', value: '%test%' }` |
| `ilike` | Case-insensitive LIKE | `{ op: 'ilike', value: '%test%' }` |
| `between` | Between two values | `{ op: 'between', value: [10, 20] }` |
| `isNull` | IS NULL | `{ op: 'isNull' }` |
| `isNotNull` | IS NOT NULL | `{ op: 'isNotNull' }` |

---

## Real-World Examples

### Example 1: GET Endpoint with Filters

```javascript
// GET /users - Get all active users with pagination
router.get("/", async (req, res) => {
    try {
        const { limit = 100, offset = 0, status } = req.query;
        
        const whereConditions = {
            deleted: false,
        };

        // Add optional status filter
        if (status) {
            whereConditions.status = status;
        }

        const users = await select({
            table: 'users',
            columns: ['id', 'name', 'phone_number', 'created_at'],
            where: whereConditions,
            orderBy: {
                column: 'created_at',
                direction: 'desc',
            },
            limit: Math.min(limit, 100), // Cap at 100
            offset: parseInt(offset),
        });

        res.json(users);
    } catch (err) {
        console.error("Error fetching users:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

### Example 2: POST Endpoint with Validation

```javascript
// POST /users - Create a new user
router.post("/", async (req, res) => {
    try {
        const { name, phone_number, country_code = "+91" } = req.body;

        // Validation
        if (!name || !phone_number) {
            return res.status(400).json({ error: "Name and phone_number are required" });
        }

        // Build data object
        const userData = {
            name,
            phone_number,
            country_code,
        };

        // Insert with returning all fields
        const user = await insert({
            table: 'users',
            data: userData,
            returning: '*',
        });

        res.status(201).json(user);
    } catch (err) {
        console.error("Error creating user:", err);
        
        // Handle unique constraint violation
        if (err.code === "23505") {
            return res.status(400).json({
                error: "A user with this phone number already exists",
            });
        }

        res.status(500).json({ error: "Internal server error" });
    }
});
```

### Example 3: PUT Endpoint with Partial Updates

```javascript
// PUT /users/:id - Update user (partial update)
router.put("/:id", async (req, res) => {
    try {
        const { id } = req.params;
        const { name, phone_number, avatar_url } = req.body;

        // Build update data (only include provided fields)
        const updateData = {};
        if (name !== undefined) updateData.name = name;
        if (phone_number !== undefined) updateData.phone_number = phone_number;
        if (avatar_url !== undefined) updateData.avatar_url = avatar_url;

        // Check if any fields to update
        if (Object.keys(updateData).length === 0) {
            return res.status(400).json({ error: "No fields to update" });
        }

        const updated = await update({
            table: 'users',
            data: updateData,
            where: {
                id: id,
                deleted: false, // Ensure not updating deleted users
            },
            returning: '*',
        });

        if (updated.length === 0) {
            return res.status(404).json({ error: "User not found" });
        }

        res.json(updated[0]);
    } catch (err) {
        console.error("Error updating user:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

### Example 4: Complex Query with Multiple Filters

```javascript
// GET /listings - Get listings with multiple filters
router.get("/", async (req, res) => {
    try {
        const { 
            status = "active", 
            species_id, 
            price_min, 
            price_max, 
            limit = 20, 
            offset = 0 
        } = req.query;

        // For complex queries with multiple conditions on same column, use execute()
        const listings = await execute({
            type: 'raw-builder',
            handler: async (knex) => {
                let query = knex('listings')
                    .where('deleted', false)
                    .where('status', status);

                if (species_id) {
                    query = query.where('filter_species_id', species_id);
                }

                if (price_min) {
                    query = query.where('price', '>=', price_min);
                }

                if (price_max) {
                    query = query.where('price', '<=', price_max);
                }

                return await query
                    .orderBy('created_at', 'desc')
                    .limit(Math.min(limit, 100))
                    .offset(offset);
            },
        });

        res.json(listings);
    } catch (err) {
        console.error("Error fetching listings:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

### Example 5: Transaction with Multiple Operations

```javascript
// POST /listings - Create listing with animal and media
router.post("/", async (req, res) => {
    try {
        const { seller_id, title, price, animal, media } = req.body;

        const result = await execute({
            type: 'transaction',
            handler: async (trx) => {
                // 1. Insert animal
                const animalResult = await trx('animals')
                    .insert({
                        species_id: animal.species_id,
                        breed_id: animal.breed_id,
                        sex: animal.sex,
                        age_months: animal.age_months,
                        // ... other fields
                    })
                    .returning('*');

                const animal_id = animalResult[0].id;

                // 2. Insert listing
                const listingResult = await trx('listings')
                    .insert({
                        seller_id: seller_id,
                        animal_id: animal_id,
                        title: title,
                        price: price,
                        status: 'active',
                    })
                    .returning('*');

                const listing_id = listingResult[0].id;

                // 3. Insert media (batch)
                if (media && media.length > 0) {
                    const mediaData = media.map(item => ({
                        listing_id: listing_id,
                        media_url: item.media_url,
                        media_type: item.media_type,
                        is_primary: item.is_primary || false,
                        sort_order: item.sort_order || 0,
                    }));

                    await trx('listing_media').insert(mediaData);
                }

                return listingResult[0];
            },
        });

        res.status(201).json(result);
    } catch (err) {
        console.error("Error creating listing:", err);
        res.status(500).json({ error: "Internal server error" });
    }
});
```

---

## Common Patterns

### Pattern 1: GET with Pagination

```javascript
const { limit = 20, offset = 0 } = req.query;

const results = await select({
    table: 'table_name',
    where: { deleted: false },
    orderBy: { column: 'created_at', direction: 'desc' },
    limit: Math.min(limit, 100), // Always cap limit
    offset: parseInt(offset),
});
```

### Pattern 2: GET Single Record by ID

```javascript
const { id } = req.params;

const records = await select({
    table: 'table_name',
    where: {
        id: id,
        deleted: false, // Always check deleted flag
    },
    limit: 1,
});

if (records.length === 0) {
    return res.status(404).json({ error: "Record not found" });
}

res.json(records[0]);
```

### Pattern 3: Partial Update (PATCH-style)

```javascript
const { id } = req.params;
const updateData = {};

// Only include fields that are provided
if (req.body.name !== undefined) updateData.name = req.body.name;
if (req.body.email !== undefined) updateData.email = req.body.email;

if (Object.keys(updateData).length === 0) {
    return res.status(400).json({ error: "No fields to update" });
}

const updated = await update({
    table: 'table_name',
    data: updateData,
    where: { id: id },
    returning: '*',
});
```

### Pattern 4: Soft Delete

```javascript
// Always use UPDATE for soft deletes, not deleteRecord()
const deleted = await update({
    table: 'table_name',
    data: { deleted: true },
    where: { id: id },
    returning: ['id'],
});
```

### Pattern 5: Batch Insert

```javascript
const items = [
    { name: 'Item 1', value: 100 },
    { name: 'Item 2', value: 200 },
    { name: 'Item 3', value: 300 },
];

const inserted = await insert({
    table: 'table_name',
    data: items, // Pass array for batch insert
    returning: '*',
});
```

---

## Flow & Architecture

### How Query Helper Works

```
┌─────────────────────────────────────────────────────────────┐
│ 1. Route Handler (routes/userRoutes.js)                     │
│    - Receives HTTP request                                   │
│    - Extracts parameters from req.body/req.params           │
└──────────────────────┬──────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ 2. Query Helper Call                                         │
│    - Calls select/insert/update/deleteRecord/execute         │
│    - Passes JSON options object                              │
└──────────────────────┬──────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ 3. Query Helper (db/queryHelper/index.js)                   │
│    - Validates table name (whitelist check)                  │
│    - Validates options (required fields, types)              │
│    - Applies WHERE conditions (JSON → Knex)                  │
│    - Builds query using Knex.js                              │
└──────────────────────┬──────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ 4. Knex.js (db/queryHelper/knex.js)                         │
│    - Converts query builder to parameterized SQL             │
│    - Executes query on PostgreSQL                            │
│    - Returns results                                         │
└──────────────────────┬──────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ 5. Response                                                  │
│    - Query Helper returns results                            │
│    - Route handler formats response                          │
│    - Sends JSON response to client                           │
└─────────────────────────────────────────────────────────────┘
```

### Data Flow Example: Creating a User

```
1. Client Request
   POST /users
   { "name": "John", "phone_number": "+1234567890" }
   │
   ▼
2. Route Handler (userRoutes.js)
   - Extracts: name, phone_number from req.body
   - Builds: userData = { name, phone_number, ... }
   │
   ▼
3. Query Helper Call
   insert({
     table: 'users',
     data: userData,
     returning: '*'
   })
   │
   ▼
4. Query Helper Processing
   - Validates: table 'users' is in whitelist ✓
   - Validates: data is object ✓
   - Builds: Knex insert query
   │
   ▼
5. Knex.js Execution
   - Generates: INSERT INTO users (name, phone_number, ...) VALUES ($1, $2, ...) RETURNING *
   - Executes: Parameterized query on PostgreSQL
   │
   ▼
6. Database Response
   - Returns: { id: 'uuid', name: 'John', phone_number: '+1234567890', ... }
   │
   ▼
7. Route Handler Response
   - Returns: res.status(201).json(user)
   │
   ▼
8. Client Receives
   201 Created
   { "id": "uuid", "name": "John", "phone_number": "+1234567890", ... }
```

### Transaction Flow

```
┌─────────────────────────────────────────────────────────────┐
│ execute({ type: 'transaction', handler: async (trx) => { }) │
└──────────────────────┬──────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ BEGIN TRANSACTION                                           │
└──────────────────────┬──────────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ Execute handler function                                     │
│ - All queries use 'trx' instead of 'db'                     │
│ - If any query fails, entire transaction rolls back          │
└──────────────────────┬──────────────────────────────────────┘
                       │
         ┌─────────────┴─────────────┐
         │                           │
         ▼                           ▼
    ┌─────────┐                 ┌─────────┐
    │ SUCCESS │                 │  ERROR  │
    └────┬────┘                 └────┬────┘
         │                           │
         ▼                           ▼
┌─────────────────┐         ┌─────────────────┐
│ COMMIT          │         │ ROLLBACK         │
│ Return result   │         │ Throw error     │
└─────────────────┘         └─────────────────┘
```

---

## Best Practices

### 1. Always Check for Deleted Records

```javascript
// ✅ Good
const users = await select({
    table: 'users',
    where: {
        id: id,
        deleted: false, // Always include this
    },
});

// ❌ Bad
const users = await select({
    table: 'users',
    where: { id: id }, // Missing deleted check
});
```

### 2. Cap Limits to Prevent Resource Exhaustion

```javascript
// ✅ Good
const limit = Math.min(req.query.limit || 20, 100);

// ❌ Bad
const limit = req.query.limit || 20; // No cap
```

### 3. Use Transactions for Multi-Step Operations

```javascript
// ✅ Good - All or nothing
await execute({
    type: 'transaction',
    handler: async (trx) => {
        await trx('table1').insert(data1);
        await trx('table2').insert(data2);
    },
});

// ❌ Bad - Partial failures possible
await insert({ table: 'table1', data: data1 });
await insert({ table: 'table2', data: data2 });
```

### 4. Handle Partial Updates Correctly

```javascript
// ✅ Good - Only update provided fields
const updateData = {};
if (req.body.name !== undefined) updateData.name = req.body.name;
if (req.body.email !== undefined) updateData.email = req.body.email;

// ❌ Bad - Sends undefined values
const updateData = {
    name: req.body.name, // Could be undefined
    email: req.body.email, // Could be undefined
};
```

### 5. Always Use WHERE Clause for UPDATE/DELETE

```javascript
// ✅ Good - WHERE clause required
await update({
    table: 'users',
    data: { name: 'John' },
    where: { id: userId }, // Required
});

// ❌ Bad - Missing WHERE (will fail with error)
await update({
    table: 'users',
    data: { name: 'John' },
    // Missing where clause
});
```

### 6. Use Returning to Get Updated/Inserted Data

```javascript
// ✅ Good - Get the inserted/updated record
const user = await insert({
    table: 'users',
    data: userData,
    returning: '*', // Get all fields back
});

// ❌ Bad - Don't know what was inserted
await insert({
    table: 'users',
    data: userData,
    // No returning - have to query again
});
```

### 7. Validate Input Before Querying

```javascript
// ✅ Good - Validate first
if (!name || !phone_number) {
    return res.status(400).json({ error: "Name and phone are required" });
}

const user = await insert({
    table: 'users',
    data: { name, phone_number },
});

// ❌ Bad - Let database handle validation
const user = await insert({
    table: 'users',
    data: { name, phone_number }, // Could be null/undefined
});
```

### 8. Use Appropriate Error Handling

```javascript
// ✅ Good - Specific error handling
try {
    const user = await insert({ table: 'users', data: userData });
    res.status(201).json(user);
} catch (err) {
    if (err.code === "23505") {
        return res.status(400).json({ error: "Duplicate entry" });
    }
    console.error("Error:", err);
    res.status(500).json({ error: "Internal server error" });
}

// ❌ Bad - Generic error handling
try {
    const user = await insert({ table: 'users', data: userData });
    res.json(user);
} catch (err) {
    res.status(500).json({ error: "Error" }); // Not helpful
}
```

---

## Troubleshooting

### Error: "Table 'xxx' is not allowed"

**Problem:** Table name not in whitelist.

**Solution:** Add table to `ALLOWED_TABLES` in `db/queryHelper/index.js`

```javascript
const ALLOWED_TABLES = new Set([
    'users',
    'listings',
    // ... add your table here
    'your_table_name',
]);
```

### Error: "WHERE clause is required"

**Problem:** UPDATE or DELETE called without WHERE clause.

**Solution:** Always include WHERE clause:

```javascript
// ✅ Correct
await update({
    table: 'users',
    data: { name: 'John' },
    where: { id: userId }, // Required
});

// ❌ Wrong
await update({
    table: 'users',
    data: { name: 'John' },
    // Missing where
});
```

### Error: "Unsupported operator: xxx"

**Problem:** Using an operator that's not supported.

**Solution:** Check supported operators list. For complex queries, use `execute()`:

```javascript
// For complex queries, use execute()
const results = await execute({
    type: 'raw-builder',
    handler: async (knex) => {
        return await knex('table')
            .where('column', 'your-operator', value);
    },
});
```

### Results Not Found

**Problem:** Query returns empty array but record exists.

**Solution:** Check WHERE conditions, especially `deleted` flag:

```javascript
// Make sure you're not filtering out the record
const users = await select({
    table: 'users',
    where: {
        id: id,
        deleted: false, // Make sure this matches your data
    },
});
```

### Transaction Not Rolling Back

**Problem:** Error in transaction but changes persist.

**Solution:** Ensure all queries use `trx` parameter:

```javascript
// ✅ Correct - Uses trx
await execute({
    type: 'transaction',
    handler: async (trx) => {
        await trx('table1').insert(data1);
        await trx('table2').insert(data2);
    },
});

// ❌ Wrong - Uses db directly (not in transaction)
await execute({
    type: 'transaction',
    handler: async (trx) => {
        await db('table1').insert(data1); // Wrong! Use trx
    },
});
```

---

## Quick Reference

### Import Statement
```javascript
import { select, insert, update, deleteRecord, execute } from '../db/queryHelper/index.js';
```

### Common Patterns

```javascript
// SELECT
const results = await select({
    table: 'table_name',
    where: { deleted: false },
    orderBy: { column: 'created_at', direction: 'desc' },
    limit: 20,
});

// INSERT
const record = await insert({
    table: 'table_name',
    data: { field1: 'value1', field2: 'value2' },
    returning: '*',
});

// UPDATE
const updated = await update({
    table: 'table_name',
    data: { field1: 'new_value' },
    where: { id: recordId },
    returning: '*',
});

// SOFT DELETE
const deleted = await update({
    table: 'table_name',
    data: { deleted: true },
    where: { id: recordId },
});

// TRANSACTION
const result = await execute({
    type: 'transaction',
    handler: async (trx) => {
        // Use trx for all queries
        return await trx('table').insert(data).returning('*');
    },
});
```

---

## Additional Resources

- **API Reference**: See `db/queryHelper/API_REFERENCE.md` for complete method documentation
- **Examples**: See `routes/userRoutes.js` for real implementation examples
- **Migration Guide**: See `db/queryHelper/MIGRATION.md` for migrating from raw SQL

---

## Summary

The Query Helper provides a clean, safe, and maintainable way to interact with the database:

1. **Import** the functions you need
2. **Build** JSON options objects
3. **Call** the appropriate function (select/insert/update/deleteRecord/execute)
4. **Handle** errors appropriately
5. **Return** results to client

No raw SQL strings needed! 🎉