# @dbcube/query-builder

Fluent, engine-agnostic query builder for **MySQL, PostgreSQL, SQLite and MongoDB**, powered by the DBCube Rust query engine (persistent daemon mode, sub-millisecond dispatch).

> Most applications should install [`dbcube`](https://www.npmjs.com/package/dbcube) (the ORM), which re-exports this builder together with schema tooling.

## Installation

```bash
npm install @dbcube/query-builder
```

## Configuration

`dbcube.config.js` in your project root:

```js
module.exports = function (config) {
    config.set({
        databases: {
            myapp: {
                type: "mysql", // mysql | postgres | sqlite | mongodb
                config: {
                    HOST: process.env.DB_HOST,
                    USER: process.env.DB_USER,
                    PASSWORD: process.env.DB_PASSWORD,
                    DATABASE: process.env.DB_NAME,
                    PORT: 3306
                }
            }
        }
    });
};
```

## Usage

```js
const { Database } = require('@dbcube/query-builder');
const db = new Database('myapp');
```

### Reads

```js
const users = await db.table('users')
    .select(['id', 'name'])
    .where('age', '>', 25)
    .whereIn('status', ['active', 'trial'])
    .orderBy('created_at', 'DESC')
    .limit(10)
    .offset(20)
    .get();

const user  = await db.table('users').find(5);          // by id
const one   = await db.table('users').where('email', '=', 'a@b.c').first();
const any   = await db.table('users').where('age', '>', 100).exists(); // boolean
const page  = await db.table('posts').paginate(2, 20);  // {items, total, totalPages, hasNext, hasPrev}
```

### Aggregations

Aggregations execute immediately and return a number — they are not chainable:

```js
const total   = await db.table('users').count();
const maxAge  = await db.table('users').max('age');
const avgAge  = await db.table('users').where('status', '=', 'active').avg('age');
```

### Writes

`insert()` takes an array. `update()` and `delete()` **require** a WHERE:

```js
await db.table('users').insert([{ name: 'Ada', email: 'ada@example.com' }]);
await db.table('users').where('id', '=', 1).update({ status: 'inactive' });
await db.table('users').where('status', '=', 'banned').delete();
await db.table('temp_imports').truncate(); // the only write allowed without WHERE

await db.table('settings').upsert([{ key: 'theme', value: 'dark' }], ['key']);
await db.table('products').where('id', '=', 7).increment('stock', 5);
```

### Transactions

```js
await db.transaction(async (trx) => {
    await trx.table('accounts').where('id', '=', 1).update({ balance: 50 });
    await trx.table('accounts').where('id', '=', 2).update({ balance: 150 });
    // any throw → automatic ROLLBACK
});
```

### Raw SQL

```js
const rows = await db.raw('SELECT * FROM users WHERE age > ?', [25]);
```

### Eager loading

Relations resolve from the `foreign` definitions in your `.cube` files — one batched query per relation, no N+1:

```js
const users = await db.table('users').with('orders').get();
const posts = await db.table('posts')
    .with('author', { table: 'users', foreignKey: 'author_id', type: 'one' })
    .get();
```

### More

`whereGroup`, `orWhere`, `whereBetween`, `whereNotIn`, `join/leftJoin/rightJoin`, `groupBy`, `having`, `selectRaw`, `distinct`, `chunk(size, cb)` for large datasets, computed fields (`useComputes()`) and runtime triggers (`useTriggers()`).

## Documentation

Full docs: https://dbcube.org

## License

MIT