# Query Helper - Quick Reference Card

One-page quick reference for daily development.

## Import

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

## SELECT

```javascript
const results = await select({
    table: 'users',
    columns: ['id', 'name'], // or '*'
    where: { deleted: false },
    orderBy: { column: 'created_at', direction: 'desc' },
    limit: 20,
    offset: 0,
});
```

## INSERT

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

// Batch
const items = await insert({
    table: 'items',
    data: [{ name: 'Item 1' }, { name: 'Item 2' }],
    returning: '*',
});
```

## UPDATE

```javascript
const updated = await update({
    table: 'users',
    data: { name: 'Jane' },
    where: { id: userId, deleted: false }, // WHERE required
    returning: '*',
});
```

## DELETE (Soft Delete Recommended)

```javascript
// Soft delete (recommended)
const deleted = await update({
    table: 'users',
    data: { deleted: true },
    where: { id: userId },
});

// Hard delete (use with caution)
const deleted = await deleteRecord({
    table: 'users',
    where: { id: userId }, // WHERE required
});
```

## TRANSACTION

```javascript
const result = await execute({
    type: 'transaction',
    handler: async (trx) => {
        const user = await trx('users').insert(userData).returning('*');
        await trx('listings').insert({ ...listingData, seller_id: user[0].id });
        return user[0];
    },
});
```

## WHERE Operators

```javascript
// Simple
where: { status: 'active' }

// Comparison
where: { price: { op: '>=', value: 1000 } }

// IN
where: { id: { op: 'in', value: ['uuid1', 'uuid2'] } }

// LIKE
where: { name: { op: 'ilike', value: '%john%' } }

// BETWEEN
where: { age: { op: 'between', value: [18, 65] } }

// NULL
where: { deleted_at: { op: 'isNull' } }
```

## Common Patterns

### 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),
    offset: parseInt(offset),
});
```

### GET Single by ID
```javascript
const records = await select({
    table: 'table_name',
    where: { id: id, deleted: false },
    limit: 1,
});
if (records.length === 0) return res.status(404).json({ error: "Not found" });
res.json(records[0]);
```

### Partial Update
```javascript
const updateData = {};
if (req.body.name !== undefined) updateData.name = req.body.name;
if (req.body.email !== undefined) updateData.email = req.body.email;

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

## Important Rules

1. ✅ Always include `deleted: false` in WHERE for SELECT/UPDATE
2. ✅ WHERE clause is **required** for UPDATE/DELETE
3. ✅ Cap limits: `Math.min(limit, 100)`
4. ✅ Use transactions for multi-step operations
5. ✅ Use `returning: '*'` to get inserted/updated data
6. ✅ Validate input before querying
7. ✅ Handle errors appropriately

## Error Codes

- `23505` - Unique constraint violation (duplicate)
- `23503` - Foreign key violation
- `22P02` - Invalid UUID format
- `42P01` - Table doesn't exist

## File Structure

```
db/queryHelper/
├── index.js          # Main implementation
├── knex.js           # Knex configuration
├── README.md         # Overview
├── API_REFERENCE.md  # Complete API docs
├── DEVELOPER_GUIDE.md # This comprehensive guide
└── QUICK_REFERENCE.md # This file
```