### Clone and Install Esix

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Clone the Esix repository, install dependencies, and start development servers.

```bash
git clone https://github.com/Artmann/esix.git
cd esix

yarn install

yarn dev

yarn build

yarn test

yarn lint

yarn format

yarn typecheck
```

--------------------------------

### Work on Website Package

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Commands to start the development server, build, or start the production server for the website package.

```bash
yarn workspace website dev

cd packages/website

yarn dev

yarn build

yarn start
```

--------------------------------

### Blog API Endpoints Example

Source: https://github.com/artmann/esix/blob/main/README.md

Illustrates common API operations for a blog, including fetching posts, creating new posts, and getting or creating users.

```typescript
// Blog API endpoints
export async function getPosts(req: Request, res: Response) {
  const posts = await Post
    .where('publishedAt', '!=', null)
    .orderBy('publishedAt', 'desc')
    .limit(20)
    .get()

  res.json({ posts })
}

export async function createPost(req: Request, res: Response) {
  const post = await Post.create({
    title: req.body.title,
    content: req.body.content,
    authorId: req.user.id,
    tags: req.body.tags || []
  })

  res.json({ post })
}

export async function getOrCreateUser(req: Request, res: Response) {
  const user = await User.firstOrCreate(
    { email: req.body.email },
    { name: req.body.name, isActive: true }
  )

  res.json({ user, created: user.createdAt === user.updatedAt })
}
```

--------------------------------

### Install dotenv Package

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/configuration.md

Install the dotenv package to manage environment variables for local development.

```bash
npm install dotenv
```

--------------------------------

### Install Esix and MongoDB Driver

Source: https://github.com/artmann/esix/blob/main/README.md

Install the Esix package and the official MongoDB driver using npm or yarn.

```bash
npm install esix mongodb
# or
yarn add esix mongodb
```

--------------------------------

### Fluent Interface Example

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Demonstrates chaining query methods for building complex queries in Esix.

```typescript
const users = await User
  .where('age', '>', 18)
  .where('isActive', true)
  .orderBy('name')
  .limit(10)
  .get()
```

--------------------------------

### Run Development Server with npm, yarn, pnpm, or bun

Source: https://github.com/artmann/esix/blob/main/packages/website/README.md

Use these commands to start the Next.js development server. Open http://localhost:3000 in your browser to view the application.

```bash
npm run dev
# or
yarn dev
# or
pnpm dev
# or
bun dev
```

--------------------------------

### Development Workflow Commands

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Commands for starting all development servers, working on specific packages, or running commands across all packages.

```bash
yarn dev

yarn workspace esix build
yarn workspace website dev

yarn build

yarn test

yarn lint
```

--------------------------------

### Execute Query and Get All Documents with `get()`

Source: https://context7.com/artmann/esix/llms.txt

Runs the constructed query and returns an array containing all documents that match the specified criteria. Useful for fetching multiple records.

```typescript
import { BaseModel } from 'esix'

class BlogPost extends BaseModel {
  public title = ''
  public status = 'draft'
  public categoryId = 0
  public views = 0
}

const posts = await BlogPost
  .where('status', 'published')
  .where('categoryId', 4)
  .where('views', '>', 1000)
  .orderBy('views', 'desc')
  .limit(12)
  .get()

posts.forEach((post) => {
  console.log(`${post.title} — ${post.views} views`)
})
```

--------------------------------

### Comparison Query Examples in TypeScript

Source: https://github.com/artmann/esix/blob/main/CLAUDE.md

Utilize the three-parameter syntax for comparison operators like '>', '>=', '<', '<=', '!='. Examples include filtering users by age and products by price.

```typescript
const adults = await User.where('age', '>', 18).get()
```

```typescript
const seniors = await User.where('age', '>=', 65).get()
```

```typescript
const youngUsers = await User.where('age', '<', 30).get()
```

```typescript
const affordableProducts = await Product.where('price', '<=', 50).get()
```

```typescript
const activeUsers = await User.where('status', '!=', 'banned').get()
```

--------------------------------

### Install Esix with NPM

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/getting-started.md

Use this command to add Esix and MongoDB to your project dependencies when using NPM.

```sh
npm install esix mongodb
```

--------------------------------

### Install Esix with Yarn

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/getting-started.md

Use this command to add Esix and MongoDB to your project dependencies when using Yarn.

```sh
yarn add esix mongodb
```

--------------------------------

### Configure Esix via Environment Variables

Source: https://context7.com/artmann/esix/llms.txt

Esix uses environment variables for configuration. Examples for development and production are shown.

```dotenv
# .env (development)
DB_URL=mongodb://localhost:27017/
DB_DATABASE=myapp_development
DB_MAX_POOL_SIZE=5

# .env (production — MongoDB Atlas)
DB_URL=mongodb+srv://user:password@cluster.mongodb.net/
DB_DATABASE=myapp_production
```

--------------------------------

### Test User Registration with In-Memory Database

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/testing.md

This example demonstrates testing user registration using Esix's in-memory mock adapter. It verifies actual database interactions and data integrity, including password hashing.

```typescript
// ✅ Good: Using in-memory database
describe('User Registration', () => {
  beforeEach(async () => {
    // Setup in-memory database
    Object.assign(process.env, {
      DB_ADAPTER: 'mock',
      DB_DATABASE: `test-${v4()}`
    })
  })

  it('should create a new user with hashed password', async () => {
    const userData = {
      email: 'test@example.com',
      password: 'password123',
      name: 'Test User'
    }

    const user = await UserService.register(userData)

    // Test the actual database state
    expect(user.email).toBe('test@example.com')
    expect(user.password).not.toBe('password123') // Should be hashed
    
    // Verify user was actually saved
    const savedUser = await User.findBy('email', 'test@example.com')
    expect(savedUser).toBeTruthy()
  })
})
```

--------------------------------

### Configure Esix Mock Adapter for Testing

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/testing.md

Set up the mock adapter in your test environment by configuring environment variables. This is typically done in a test setup file using `beforeEach`.

```typescript
import { beforeEach, afterEach } from 'vitest'
import { v4 } from 'uuid'

beforeEach(() => {
  Object.assign(process.env, {
    DB_ADAPTER: 'mock',
    DB_DATABASE: `test-${v4()}`
  })
})

afterEach(async () => {
  // Clean up connections
  await ConnectionHandler.close()
})
```

--------------------------------

### Equality Query Example in TypeScript

Source: https://github.com/artmann/esix/blob/main/CLAUDE.md

Use the two-parameter syntax for equality checks in queries. This example filters users by 'status' equal to 'active'.

```typescript
const activeUsers = await User.where('status', 'active').get()
```

--------------------------------

### Load Environment Variables in Application

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/configuration.md

Import and load environment variables from a .env file at the start of your application using dotenv/config.

```typescript
import 'dotenv/config'
// Your Esix code here
```

--------------------------------

### Aggregation Examples

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Perform various aggregation operations like count, sum, average, max, min, percentile, and complex pipeline aggregations. Chaining with query methods is also supported.

```typescript
// Count all users
const userCount = await User.count()
```

```typescript
// Sum all order amounts
const totalSales = await Order.sum('amount')
```

```typescript
// Get average user age
const avgAge = await User.average('age')
```

```typescript
// Find maximum score
const highScore = await Test.max('score')
```

```typescript
// Get 95th percentile response time
const p95 = await ResponseTime.percentile('value', 95)
```

```typescript
// Complex aggregations with MongoDB pipeline
const results = await User.aggregate([
  { $group: { _id: '$department', count: { $sum: 1 } } }
])
```

```typescript
// Chaining with query methods
const avgAgeForAdults = await User.where('age', '>=', 18).average('age')
```

--------------------------------

### Query a Book by ID

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/getting-started.md

Retrieve a single book record from the database using its ID with the `find` static method. This example assumes an Express.js request context.

```typescript
import { Request, Response } from 'express'
import Book from './book'

async function showBook(request: Request, response: Response): Promise<void> {
  const book = await Book.find(request.params.id)

  response.json({
    book
  })
}
```

--------------------------------

### Get the First Matching Record

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/retrieving-models.md

Retrieve the first model that satisfies the specified query conditions using the `first` method. This is useful for getting the most recent item after sorting.

```typescript
const latestPost = await BlogPost.where('status', 'published')
  .orderBy('createdAt', 'desc')
  .first()

console.log(latestPost.title)
```

--------------------------------

### Get the First Matching Document with `first()`

Source: https://context7.com/artmann/esix/llms.txt

Executes the query and returns only the first document that matches the criteria, or `null` if no documents are found. This method implicitly applies `limit(1)`.

```typescript
import { BaseModel } from 'esix'

class BlogPost extends BaseModel {
  public title = ''
  public status = 'draft'
  public publishedAt = 0
}

// First published post in descending date order
const latest = await BlogPost
  .where('status', 'published')
  .orderBy('publishedAt', 'desc')
  .first()

if (latest) {
  console.log(latest.title) // "My Latest Post"
} else {
  console.log('No published posts found')
}
```

--------------------------------

### QueryBuilder.first() — Get the first matching document

Source: https://context7.com/artmann/esix/llms.txt

Executes the query and returns only the first document that matches the criteria. If no documents match, it returns `null`. This method implicitly applies `limit(1)`.

```APIDOC
## `QueryBuilder.first()` — Get the first matching document

Executes the query and returns the first result, or `null` if no documents match. Automatically applies `limit(1)` internally.

### Example Usage:

```ts
// First published post in descending date order
const latest = await BlogPost
  .where('status', 'published')
  .orderBy('publishedAt', 'desc')
  .first()

if (latest) {
  console.log(latest.title) // "My Latest Post"
} else {
  console.log('No published posts found')
}
```
```

--------------------------------

### Avoid Mocking Repository Layer for Testing

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/testing.md

This example shows an anti-pattern of mocking the repository layer, which only verifies mock interactions rather than actual application logic and data persistence.

```typescript
// ❌ Avoid: Mocking the repository layer
describe('User Registration (with mocks)', () => {
  it('should create a new user', async () => {
    const mockUser = { id: '1', email: 'test@example.com' }
    jest.spyOn(User, 'create').mockResolvedValue(mockUser)
    
    const user = await UserService.register(userData)
    
    // This only tests that the mock was called correctly,
    // not that your actual business logic works
    expect(User.create).toHaveBeenCalledWith(userData)
  })
})
```

--------------------------------

### QueryBuilder.limit(n) / QueryBuilder.skip(n) — Pagination

Source: https://context7.com/artmann/esix/llms.txt

These methods are used for implementing pagination. `limit(n)` restricts the number of results returned, while `skip(n)` sets the starting position for the results. They are typically used together to fetch data in pages.

```APIDOC
## `QueryBuilder.limit(n)` / `QueryBuilder.skip(n)` — Pagination

Limits the number of results returned and offsets the starting position. Used together to implement page-based pagination.

### Example Usage:

```ts
// Page 1
const page1 = await BlogPost
  .where('categoryId', 4)
  .orderBy('publishedAt', 'desc')
  .limit(PAGE_SIZE)
  .get()

// Page 2
const page2 = await BlogPost
  .where('categoryId', 4)
  .orderBy('publishedAt', 'desc')
  .skip(PAGE_SIZE)
  .limit(PAGE_SIZE)
  .get()

// Arbitrary page
function getPage(page: number, perPage = 10) {
  return BlogPost
    .orderBy('publishedAt', 'desc')
    .skip((page - 1) * perPage)
    .limit(perPage)
    .get()
}

const page3 = await getPage(3, 10) // items 21–30
```
```

--------------------------------

### Advanced Filtering and Limiting Records

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/retrieving-models.md

Chain `where`, `orderBy`, and `limit` methods on a model to build complex queries. The `get` method executes the query and returns the results.

```typescript
const blogPosts = await BlogPost.where('status', 'published')
  .where('categoryId', 4)
  .orderBy('publishedAt', 'desc')
  .limit(12)
  .get()

blogPosts.forEach((post) => console.log(post.title))
```

--------------------------------

### Count Models

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/retrieving-models.md

Use the `count` aggregate function to get the total number of models that match the specified criteria. Requires a `where` clause to filter.

```ts
await Product.where('category', 'lamps').count()
```

--------------------------------

### Chaining Multiple Query Conditions in TypeScript

Source: https://github.com/artmann/esix/blob/main/CLAUDE.md

Chain multiple 'where' clauses to build complex queries with combined conditions. This example filters users within a specific age range.

```typescript
const workingAge = await User
  .where('age', '>=', 18)
  .where('age', '<=', 65)
  .get()
```

--------------------------------

### Filter Documents with BaseModel.where()

Source: https://context7.com/artmann/esix/llms.txt

Use `where()` to filter documents based on field conditions. It supports two-parameter equality checks and three-parameter comparison operators. Multiple `where` calls are combined with AND logic. Use `.get()` to execute the query and retrieve results, or `.first()` to get a single result.

```typescript
import { BaseModel } from 'esix'

class Product extends BaseModel {
  public name = ''
  public price = 0
  public inStock = true
  public categoryId = 0
}

// Equality filter (2-param)
const inCategory = await Product.where('categoryId', 3).get()

// Comparison operators (3-param)
const affordable  = await Product.where('price', '<=', 50).get()
const expensive   = await Product.where('price', '>', 200).get()
const notExact    = await Product.where('price', '!=', 99.99).get()

// Chained conditions (AND)
const results = await Product
  .where('inStock', true)
  .where('price', '>=', 10)
  .where('price', '<', 100)
  .where('categoryId', 3)
  .orderBy('price', 'asc')
  .limit(20)
  .get()

// Terminal: .first() returns T | null
const cheapest = await Product
  .where('inStock', true)
  .orderBy('price', 'asc')
  .first()

console.log(cheapest?.name)  // "Budget Widget"
console.log(cheapest?.price) // 4.99
```

--------------------------------

### Work on Esix Package

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Navigate to the Esix package directory and run build, test, or lint commands.

```bash
cd packages/esix

yarn build

yarn test

yarn lint
```

--------------------------------

### Build & Test Commands (Root)

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Commands to build, lint, format, test, and check types for all packages using Turborepo.

```bash
yarn build
yarn lint
yarn format
yarn test
yarn typecheck
yarn docs:build
yarn docs:serve
```

--------------------------------

### Build & Test Commands (Esix Package)

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Commands to build, lint, format, and test the Esix package specifically.

```bash
yarn build
yarn lint
yarn format
yarn test
yarn test path/to/file.spec.ts
yarn test -t "test name pattern"
yarn typecheck
```

--------------------------------

### Configure .env File

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/configuration.md

Create a .env file in your project root to store local development environment variables for database connection.

```dotenv
DB_URL=mongodb://localhost:27017/
DB_DATABASE=myapp_development
DB_MAX_POOL_SIZE=5
```

--------------------------------

### Environment Variables for Mock Adapter

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/testing.md

Alternatively, configure the mock adapter using environment variables, often stored in a `.env.test` file.

```bash
# .env.test (optional - can be set programmatically as shown above)
DB_ADAPTER=mock
DB_DATABASE=test_database
```

--------------------------------

### Extract a Single Field with `pluck()`

Source: https://context7.com/artmann/esix/llms.txt

Retrieve an array of values for a specific field from all matching documents. This is useful for getting lists of names, IDs, or other single-attribute data.

```typescript
import { BaseModel } from 'esix'

class Product extends BaseModel {
  public name = ''
  public price = 0
  public categoryId = 0
}

await Product.create({ name: 'Widget 1', price: 79.99, categoryId: 1 })
await Product.create({ name: 'Widget 2', price: 44.99, categoryId: 1 })
await Product.create({ name: 'Chair 1',  price: 49.99, categoryId: 2 })

// All names
const names = await Product.pluck('name')
// ["Widget 1", "Widget 2", "Chair 1"]

// Prices in ascending order
const prices = await Product.orderBy('price').pluck('price')
// [44.99, 49.99, 79.99]

// Names in a specific category
const categoryNames = await Product
  .where('categoryId', 1)
  .pluck('name')
// ["Widget 1", "Widget 2"]
```

--------------------------------

### Running Tests with Yarn

Source: https://github.com/artmann/esix/blob/main/CONTRIBUTING.md

Commands for running tests using Yarn. Supports running all tests, specific files, pattern matching, and watch mode.

```bash
# Run all tests
yarn test

# Run specific test file
yarn test path/to/file.spec.ts

# Run tests matching a pattern
yarn test -t "where method"

# Watch mode
yarn test --watch
```

--------------------------------

### Create and Persist a New Document with BaseModel.create()

Source: https://context7.com/artmann/esix/llms.txt

Creates and persists a new document, merging provided attributes over defaults. Returns the fully persisted instance.

```typescript
import { BaseModel } from 'esix'

class Product extends BaseModel {
  public name = ''
  public price = 0
  public inStock = true
  public categoryId = 0
}

// Create with explicit attributes — defaults fill in missing fields
const product = await Product.create({
  name: 'Ergonomic Chair',
  price: 299.99,
  categoryId: 5
})

console.log(product.id)        // "5f5a41cc3eb990709eafda43"
console.log(product.name)      // "Ergonomic Chair"
console.log(product.inStock)   // true  (default value preserved)
console.log(product.createdAt) // 1672574400000

// Create with a custom id
const withCustomId = await Product.create({
  id: 'chair-ergonomic-pro',
  name: 'Ergonomic Pro',
  price: 499.99,
  categoryId: 5
})

console.log(withCustomId.id) // "chair-ergonomic-pro"
```

--------------------------------

### Basic Pagination with Skip and Limit

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/pagination.md

Use skip to offset results and limit to control page size for common pagination needs.

```typescript
const page = 2
const perPage = 10
const offset = (page - 1) * perPage

const products = await Product.where('category', 'electronics')
  .skip(offset)
  .limit(perPage)
  .get()

console.log(`Showing page ${page} with ${products.length} items`)
```

--------------------------------

### Pluck a Single Attribute

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/retrieving-models.md

Extract an array of values for a specific attribute from a set of models using the `pluck` method. This is useful for getting lists of names, IDs, or other single fields.

```typescript
const productNames = await Product.where('category', 'lamps').pluck('name')

productNames.forEach((name) => console.log(name))
```

--------------------------------

### Load Environment Variables

Source: https://context7.com/artmann/esix/llms.txt

Import 'dotenv/config' in your entry point to load environment variables from a .env file.

```typescript
// Load .env in your entry point
import 'dotenv/config'
```

--------------------------------

### Pagination with 'skip' and 'limit'

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/retrieving-models.md

Implement pagination by using the `skip` method to define the offset and the `limit` method to set the number of records per page. Calculate the offset based on the current page number.

```typescript
const page = 2
const perPage = 10
const offset = (page - 1) * perPage

const products = await Product.where('category', 'electronics')
  .skip(offset)
  .limit(perPage)
  .get()
```

--------------------------------

### Create New Models with `create` or `save`

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/inserting-and-updating-models.md

Use the `create` method to insert a new model with specified attributes, or instantiate a model and use its `save` method for insertion. If the model lacks an ID, a new ObjectId will be generated. The `createdAt` property is automatically populated if not provided.

```typescript
const firstPost = await BlogPost.create({ title: 'My First Blog Post!' })

const secondPost = new BlogPost()

secondPost.title = 'My Second Blog Post!'

await secondPost.save()
```

--------------------------------

### instance.save()

Source: https://context7.com/artmann/esix/llms.txt

Saves the current instance to the database. If the instance does not have an `id`, it is inserted as a new document and `createdAt` is set. If an `id` already exists, the document is upserted and `updatedAt` is set to the current timestamp.

```APIDOC
## instance.save() - Persist a new or updated document

Saves the instance to the database. If the instance has no `id`, it is inserted as a new document and `createdAt` is set. If it already has an `id`, the document is upserted and `updatedAt` is set to the current timestamp.

### Method

`instance.save()`

### Parameters

This method does not take any parameters.

### Request Example

```ts
import { BaseModel } from 'esix'

class Author extends BaseModel {
  public name = ''
  public bio = ''
}

// Insert a new document via save()
const author = new Author()
author.name = 'Jane Austen'
author.bio = 'English novelist known for Pride and Prejudice.'
await author.save()
// author.id is now set, author.createdAt is now set

// Update an existing document
const existing = await Author.find(author.id)
if (existing) {
  existing.bio = 'Author of six major novels.'
  await existing.save()
  // existing.updatedAt is now set to Date.now()
}
```

### Response

- **id** (string) - The unique identifier for the document (set on insert or existing).
- **createdAt** (number) - The Unix timestamp in milliseconds when the document was created (set on insert).
- **updatedAt** (number) - The Unix timestamp in milliseconds when the document was last updated (set on update).
```

--------------------------------

### Set DB_DATABASE Environment Variable

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/configuration.md

Specify the database name to connect to using the DB_DATABASE environment variable.

```bash
DB_DATABASE=myapp_production
```

--------------------------------

### Complete Pagination Service

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/pagination.md

A reusable service for paginating queries, including total count and metadata for navigation. Ensure consistent sorting for stable results.

```typescript
class PaginationService {
  static async paginate<T>(
    query: QueryBuilder<T>,
    page: number = 1,
    perPage: number = 10
  ) {
    const offset = (page - 1) * perPage

    // Get total count for pagination info
    const total = await query.count()

    // Get the actual results
    const data = await query.skip(offset).limit(perPage).get()

    return {
      data,
      pagination: {
        page,
        perPage,
        total,
        totalPages: Math.ceil(total / perPage),
        hasNextPage: page < Math.ceil(total / perPage),
        hasPreviousPage: page > 1
      }
    }
  }
}

// Usage
const result = await PaginationService.paginate(
  User.where('status', 'active'),
  2,
  20
)

console.log(result.data) // Array of 20 users
console.log(result.pagination) // Pagination metadata
```

--------------------------------

### Retrieve or Create Models with `firstOrCreate`

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/inserting-and-updating-models.md

Use `firstOrCreate` to find a model by specified attributes or create it if it doesn't exist. You can provide additional attributes for creation as a second argument. If the second argument is omitted, attributes from the first argument are used for creation.

```typescript
const flight = await Flight.firstOrCreate({
  name: 'London to Paris'
})

const flight = await Flight.firstOrCreate(
  { name: 'London to Paris' },
  { delayed: 1, arrival_time: '11:30' }
)
```

--------------------------------

### Create Book Records in MongoDB

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/getting-started.md

Use the `create` static method on your model to insert new records into the database. Ensure the Book model is imported.

```typescript
import Book from './book'

async function createBooks(): Promise<void> {
  await Book.create({
    isbn: '978-0525536291',
    title: 'The Vanishing Half'
  })
  
  await Book.create({
    isbn: '978-0525521143',
    title: 'The Glass Hotel'
  })
}
```

--------------------------------

### Set MongoDB Connection String

Source: https://github.com/artmann/esix/blob/main/README.md

Set the DB_URL environment variable to your MongoDB connection string. Esix will automatically connect when needed.

```bash
export DB_URL=mongodb://localhost:27017/myapp
```

--------------------------------

### Perform Database Aggregations

Source: https://github.com/artmann/esix/blob/main/README.md

Use various methods to count, sum, average, find min/max, calculate percentiles, and perform custom aggregations on your data.

```typescript
// Count documents
const userCount = await User.count()
const activeCount = await User.where('isActive', true).count()

// Sum values
const totalSales = await Order.sum('amount')

// Calculate averages
const avgAge = await User.average('age')

// Find min/max
const lowestPrice = await Product.min('price')
const highestScore = await Test.max('score')

// Percentiles
const p95ResponseTime = await ResponseTime.percentile('value', 95)

// Custom aggregations
const results = await User.aggregate([
  { $group: { _id: '$department', count: { $sum: 1 } } }
])
```

--------------------------------

### Persist New or Updated Document with instance.save()

Source: https://context7.com/artmann/esix/llms.txt

Saves the instance to the database. Inserts a new document if no `id` exists, otherwise upserts the existing document. Sets `createdAt` on insert and `updatedAt` on update.

```typescript
import { BaseModel } from 'esix'

class Author extends BaseModel {
  public name = ''
  public bio = ''
}

// Insert a new document via save()
const author = new Author()
author.name = 'Jane Austen'
author.bio = 'English novelist known for Pride and Prejudice.'
await author.save()
// author.id is now set, author.createdAt is now set

// Update an existing document
const existing = await Author.find(author.id)
if (existing) {
  existing.bio = 'Author of six major novels.'
  await existing.save()
  // existing.updatedAt is now set to Date.now()
}
```

--------------------------------

### Esix Advanced Queries: Pagination

Source: https://github.com/artmann/esix/blob/main/README.md

Implement pagination by using 'limit()' to set the number of records per page and 'skip()' to define the offset.

```typescript
// Pagination
const page1 = await Post.limit(10).get()
const page2 = await Post.skip(10).limit(10).get()
```

--------------------------------

### Retrieve All Documents with BaseModel.all()

Source: https://context7.com/artmann/esix/llms.txt

Use `all()` to fetch all documents within a collection. It returns an array of model instances.

```typescript
import { BaseModel } from 'esix'

class Flight extends BaseModel {
  public name = ''
  public delayed = 0
  public arrival_time = ''
}

const flights = await Flight.all()
// [
//   { id: '...', name: 'Indian Air 9600', delayed: 0, ... },
//   { id: '...', name: 'Flight 714 to Sydney', delayed: 0, ... }
// ]

flights.forEach((flight) => {
  console.log(`${flight.name} — arrives at ${flight.arrival_time}`)
})
```

--------------------------------

### BaseModel.create(attributes)

Source: https://context7.com/artmann/esix/llms.txt

Creates and persists a new document in the database. It merges provided attributes over the model's declared defaults and returns the fully persisted instance, including the generated `id` and `createdAt`.

```APIDOC
## BaseModel.create(attributes) - Create a new document

Creates and persists a new document, merging provided attributes over the model's declared defaults. Returns the fully persisted instance including the generated `id` and `createdAt`.

### Method

`BaseModel.create(attributes)`

### Parameters

- **attributes** (object) - Required - An object containing the attributes for the new document.

### Request Example

```ts
import { BaseModel } from 'esix'

class Product extends BaseModel {
  public name = ''
  public price = 0
  public inStock = true
  public categoryId = 0
}

// Create with explicit attributes — defaults fill in missing fields
const product = await Product.create({
  name: 'Ergonomic Chair',
  price: 299.99,
  categoryId: 5
})

console.log(product.id)        // "5f5a41cc3eb990709eafda43"
console.log(product.name)      // "Ergonomic Chair"
console.log(product.inStock)   // true  (default value preserved)
console.log(product.createdAt) // 1672574400000

// Create with a custom id
const withCustomId = await Product.create({
  id: 'chair-ergonomic-pro',
  name: 'Ergonomic Pro',
  price: 499.99,
  categoryId: 5
})

console.log(withCustomId.id) // "chair-ergonomic-pro"
```

### Response

- **id** (string) - The unique identifier for the created document.
- **createdAt** (number) - The Unix timestamp in milliseconds when the document was created.
- **[other fields]** - All fields defined in the model, including defaults and provided attributes.
```

--------------------------------

### Testing with Esix Mock Adapter

Source: https://context7.com/artmann/esix/llms.txt

Activate the in-memory MongoDB mock by setting `DB_ADAPTER=mock`. Each test suite uses a unique `DB_DATABASE` name for complete isolation without requiring explicit teardown logic.

```typescript
import { describe, it, expect, beforeEach, afterAll } from 'vitest'
import { v4 as uuid } from 'uuid'
import { BaseModel, connectionHandler } from 'esix'

class User extends BaseModel {
  public email = ''
  public name = ''
  public role = 'user'
}

class Post extends BaseModel {
  public title = ''
  public authorId = ''
  public published = false
}

beforeEach(() => {
  Object.assign(process.env, {
    DB_ADAPTER: 'mock',
    DB_DATABASE: `test-${uuid()}` // isolated per test
  })
})

afterAll(async () => {
  await connectionHandler.closeConnections()
})
```

```typescript
describe('User Registration', () => {
  it('creates a user and finds them by email', async () => {
    const user = await User.create({
      email: 'alice@example.com',
      name: 'Alice',
      role: 'admin'
    })

    expect(user.id).toBeDefined()
    expect(user.createdAt).toBeGreaterThan(0)
    expect(user.updatedAt).toBeNull()

    const found = await User.findBy('email', 'alice@example.com')
    expect(found?.name).toBe('Alice')
    expect(found?.role).toBe('admin')
  })

  it('firstOrCreate does not duplicate records', async () => {
    const a = await User.firstOrCreate({ email: 'bob@example.com' }, { name: 'Bob' })
    const b = await User.firstOrCreate({ email: 'bob@example.com' }, { name: 'Bob 2' })

    expect(a.id).toBe(b.id)
    expect(b.name).toBe('Bob') // original record unchanged
    expect(await User.count()).toBe(1)
  })
})
```

```typescript
describe('Post Queries', () => {
  it('filters and counts published posts', async () => {
    const author = await User.create({ email: 'writer@example.com', name: 'Writer' })

    await Post.create({ title: 'Draft Post',     authorId: author.id, published: false })
    await Post.create({ title: 'Live Post 1',    authorId: author.id, published: true })
    await Post.create({ title: 'Live Post 2',    authorId: author.id, published: true })

    const publishedCount = await Post
      .where('authorId', author.id)
      .where('published', true)
      .count()

    expect(publishedCount).toBe(2)

    const titles = await Post
      .where('published', true)
      .orderBy('title', 'asc')
      .pluck('title')

    expect(titles).toEqual(['Live Post 1', 'Live Post 2'])
  })
})
```

--------------------------------

### Pagination with Sorting

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/pagination.md

Implement pagination with consistent sorting using orderBy to ensure stable and predictable results.

```typescript
const getBlogPosts = async (page: number = 1, perPage: number = 10) => {
  const offset = (page - 1) * perPage

  return await BlogPost.where('status', 'published')
    .orderBy('createdAt', 'desc') // Consistent sorting
    .skip(offset)
    .limit(perPage)
    .get()
}
```

--------------------------------

### BaseModel.all()

Source: https://context7.com/artmann/esix/llms.txt

Retrieves all documents in the collection, returning them as an array of model instances.

```APIDOC
## `BaseModel.all()` — Retrieve all documents

Returns every document in the collection as an array of model instances.

### Method
`BaseModel.all()`

### Response
#### Success Response
- Returns an array of all model instances in the collection.

### Request Example
```ts
import { BaseModel } from 'esix'

class Flight extends BaseModel {
  public name = ''
  public delayed = 0
  public arrival_time = ''
}

const flights = await Flight.all()
// [
//   { id: '...', name: 'Indian Air 9600', delayed: 0, ... },
//   { id: '...', name: 'Flight 714 to Sydney', delayed: 0, ... }
// ]

flights.forEach((flight) => {
  console.log(`${flight.name} — arrives at ${flight.arrival_time}`)
})
```
```

--------------------------------

### QueryBuilder.get() — Execute and return all matching documents

Source: https://context7.com/artmann/esix/llms.txt

This method executes the query built so far and returns an array containing all the documents that match the specified criteria. It's the standard way to retrieve multiple records.

```APIDOC
## `QueryBuilder.get()` — Execute and return all matching documents

Executes the accumulated query and returns all matching model instances as an array.

### Example Usage:

```ts
const posts = await BlogPost
  .where('status', 'published')
  .where('categoryId', 4)
  .where('views', '>', 1000)
  .orderBy('views', 'desc')
  .limit(12)
  .get()

posts.forEach((post) => {
  console.log(`${post.title} — ${post.views} views`)
})
```
```

--------------------------------

### Set DB_URL Environment Variable

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/configuration.md

Configure the MongoDB connection URL using the DB_URL environment variable. Supports local or MongoDB Atlas connections.

```bash
DB_URL=mongodb://localhost:27017/
# or for MongoDB Atlas
DB_URL=mongodb+srv://username:password@cluster.mongodb.net/
```

--------------------------------

### Basic Aggregation Methods in TypeScript

Source: https://github.com/artmann/esix/blob/main/CLAUDE.md

Use these static methods on model classes for common aggregation tasks. Ensure the model class is properly defined and connected to the database.

```typescript
// Count all users
const userCount = await User.count()
```

```typescript
// Sum all order amounts
const totalSales = await Order.sum('amount')
```

```typescript
// Get average user age
const avgAge = await User.average('age')
```

```typescript
// Find maximum score
const highScore = await Test.max('score')
```

```typescript
// Get 95th percentile response time
const p95 = await ResponseTime.percentile('value', 95)
```

--------------------------------

### Retrieve All Records

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/retrieving-models.md

Fetch all documents from a collection using the `all` method. This is suitable for smaller datasets or when you need the entire collection.

```typescript
const flights = await Flight.all()

flights.forEach((flight) => {
  console.log(flight.name)
})
```

--------------------------------

### Aggregation Methods

Source: https://context7.com/artmann/esix/llms.txt

Provides statistical aggregate methods like count, sum, average, min, max, and percentile on collections or filtered sets.

```APIDOC
## Aggregation Methods

### Description
Statistical aggregate methods available on both `BaseModel` (operate on the full collection) and `QueryBuilder` (operate on the filtered set). All return a `Promise<number>`.

### Methods
- **count()**: Returns the number of documents.
- **sum(field)**: Returns the sum of values for a given field.
- **average(field)**: Returns the average of values for a given field.
- **min(field)**: Returns the minimum value for a given field.
- **max(field)**: Returns the maximum value for a given field.
- **percentile(field, percentile)**: Returns the value at a specific percentile for a given field.

### Example
```ts
import { BaseModel } from 'esix'

class ResponseTime extends BaseModel {
  public value = 0
  public statusCode = 200
  public endpoint = 'index'
}

const q = ResponseTime.where('statusCode', 200)

const total   = await q.count()                     // 5
const sum     = await q.sum('value')                // 1102
const avg     = await q.average('value')            // 220.4
const minimum = await q.min('value')                // 78
const maximum = await q.max('value')                // 401
const median  = await q.percentile('value', 50)     // 232

// Static methods (no filter — full collection)
const allCount = await ResponseTime.count()         // 6
```
```

--------------------------------

### Define a Book Model in TypeScript

Source: https://github.com/artmann/esix/blob/main/packages/website/content/docs/getting-started.md

Create a Book model by extending Esix's BaseModel. This automatically includes 'id', 'createdAt', and 'updatedAt' fields.

```typescript
import { BaseModel } from 'esix'

export default class Book extends BaseModel {
  public isbn = ''
  public title = ''
}
```

--------------------------------

### Perform Statistical Aggregations with BaseModel and QueryBuilder

Source: https://context7.com/artmann/esix/llms.txt

Utilize aggregation methods like `count`, `sum`, `average`, `min`, `max`, and `percentile` on `BaseModel` for full collection statistics or `QueryBuilder` for filtered sets. All methods return a `Promise<number>`. Ensure data is seeded before performing aggregations.

```typescript
import { BaseModel } from 'esix'

class ResponseTime extends BaseModel {
  public value = 0
  public statusCode = 200
  public endpoint = 'index'
}

// Seed data
await Promise.all([
  ResponseTime.create({ statusCode: 200, value: 78,  endpoint: '/api/users' }),
  ResponseTime.create({ statusCode: 200, value: 265, endpoint: '/api/users' }),
  ResponseTime.create({ statusCode: 200, value: 401, endpoint: '/api/posts' }),
  ResponseTime.create({ statusCode: 404, value: 22,  endpoint: '/missing' }),
  ResponseTime.create({ statusCode: 200, value: 126, endpoint: '/api/users' }),
  ResponseTime.create({ statusCode: 200, value: 232, endpoint: '/api/posts' }),
])

const q = ResponseTime.where('statusCode', 200)

const total   = await q.count()                     // 5
const sum     = await q.sum('value')                // 1102
const avg     = await q.average('value')            // 220.4
const minimum = await q.min('value')                // 78
const maximum = await q.max('value')                // 401
const median  = await q.percentile('value', 50)     // 232
const p95     = await q.percentile('value', 95)     // 401

// Static methods (no filter — full collection)
const allCount = await ResponseTime.count()         // 6
const allMax   = await ResponseTime.max('value')    // 401
```

--------------------------------

### BaseModel.where() and QueryBuilder methods

Source: https://context7.com/artmann/esix/llms.txt

Provides methods for filtering and querying documents. `where` can be used for equality or comparison, and chained methods like `whereIn`, `whereNotIn`, `orderBy`, and `limit` allow for complex queries.

```APIDOC
## `BaseModel.where(key, value)` / `BaseModel.where(key, operator, value)` — Filter documents

Returns a `QueryBuilder` filtered by the given condition. Supports two-parameter equality syntax and three-parameter comparison syntax with operators `=`, `!=`, `<>`, `>`, `>=`, `<`, `<=`. Multiple `where` calls are ANDed together.

### Method
`BaseModel.where(key: string, value: any)` or `BaseModel.where(key: string, operator: string, value: any)`

### Parameters
#### Path Parameters
- **key** (string) - Required - The field name to filter on.
- **value** (any) - Required - The value to compare against (for equality or comparison).
- **operator** (string) - Optional - The comparison operator (`=`, `!=`, `<>`, `>`, `>=`, `<`, `<=`).

## `QueryBuilder.whereIn(key, values)` / `whereNotIn(key, values)` — Array membership queries

Filters documents where a field's value is (or is not) in a given array. `whereIn` with an empty array returns no results; `whereNotIn` with an empty array returns all documents.

### Method
`QueryBuilder.whereIn(key: string, values: any[])` or `QueryBuilder.whereNotIn(key: string, values: any[])`

### Parameters
#### Path Parameters
- **key** (string) - Required - The field name to check.
- **values** (any[]) - Required - An array of values to check for membership.

## `QueryBuilder.orderBy(key, order?)` — Sort results

Sorts the result set by a model field. Defaults to ascending order. Multiple `orderBy` calls sort by multiple fields.

### Method
`QueryBuilder.orderBy(key: string, order?: 'asc' | 'desc')`

### Parameters
#### Path Parameters
- **key** (string) - Required - The field name to sort by.
- **order** (string) - Optional - The sort order, either 'asc' (ascending) or 'desc' (descending). Defaults to 'asc'.

## `QueryBuilder.limit(count)` — Limit results

Limits the number of results returned by the query.

### Method
`QueryBuilder.limit(count: number)`

### Parameters
#### Path Parameters
- **count** (number) - Required - The maximum number of results to return.

### Terminal Operations
- **`.get()`**: Executes the query and returns an array of model instances.
- **`.first()`**: Executes the query and returns the first model instance, or `null` if no results are found.

### Request Example
```ts
import { BaseModel } from 'esix'

class Product extends BaseModel {
  public name = ''
  public price = 0
  public inStock = true
  public categoryId = 0
}

// Equality filter (2-param)
const inCategory = await Product.where('categoryId', 3).get()

// Comparison operators (3-param)
const affordable  = await Product.where('price', '<=', 50).get()
const expensive   = await Product.where('price', '>', 200).get()
const notExact    = await Product.where('price', '!=', 99.99).get()

// Chained conditions (AND)
const results = await Product
  .where('inStock', true)
  .where('price', '>=', 10)
  .where('price', '<', 100)
  .where('categoryId', 3)
  .orderBy('price', 'asc')
  .limit(20)
  .get()

// Terminal: .first() returns T | null
const cheapest = await Product
  .where('inStock', true)
  .orderBy('price', 'asc')
  .first()

console.log(cheapest?.name)  // "Budget Widget"
console.log(cheapest?.price) // 4.99

// Example using whereIn and whereNotIn
class User extends BaseModel {
  public name = ''
  public role = 'user'
}

const selectedUsers = await User.whereIn('id', [
  '5f0aeaeacff57e3ec676b340',
  '5f0aefba348289a81889a920',
  '5f0aefba348289a81889a921'
]).get()

const regularUsers = await User.whereNotIn('role', ['admin', 'moderator']).get()
```
```

--------------------------------

### BaseModel.aggregate

Source: https://context7.com/artmann/esix/llms.txt

Allows direct execution of raw MongoDB aggregation pipelines for complex data manipulation and analysis.

```APIDOC
## BaseModel.aggregate(stages)

### Description
Passes an array of MongoDB aggregation pipeline stages directly to the driver. Returns the raw result array. Useful for complex group-by, lookup, or projection operations not covered by built-in methods.

### Parameters
#### stages
- **(Array<object>)** - Required - An array of MongoDB aggregation pipeline stages.

### Example
```ts
import { BaseModel } from 'esix'

class Order extends BaseModel {
  public customerId = ''
  public amount = 0
  public status = 'pending'
  public region = 'us'
}

// Group orders by region and calculate totals
const regionStats = await Order.aggregate([
  { $match: { status: 'completed' } },
  { $group: {
      _id: '$region',
      totalRevenue: { $sum: '$amount' },
      orderCount:   { $sum: 1 },
      avgOrder:     { $avg: '$amount' }
  }},
  { $sort: { totalRevenue: -1 } }
])
```
```

--------------------------------

### Chaining Aggregation with Query Methods

Source: https://github.com/artmann/esix/blob/main/CLAUDE.md

Combine query builder methods with aggregation functions to perform targeted calculations on filtered datasets. This allows for more specific data analysis.

```typescript
// Chaining with query methods
const avgAgeForAdults = await User.where('age', { $gte: 18 }).average('age')
```

--------------------------------

### Esix Query Builder: Multiple Conditions

Source: https://github.com/artmann/esix/blob/main/README.md

Combine multiple 'where' clauses to build complex queries with AND logic.

```typescript
const youngActiveUsers = await User
  .where('isActive', true)
  .where('age', '<', 25)
  .get()
```