Frix

Flexible Relational Interface eXtension

Type-safe, auto-generated repository pattern for Kysely

Background & Motivation

Writing database access code in Node.js/TypeScript often means choosing between:

• Raw SQL: Flexible but error-prone, no type safety
• Heavy ORMs: Type-safe but complex, performance overhead
• Query builders: Good balance but still requires boilerplate

Frix brings the Spring Data JPA experience to TypeScript. Define your table types once, and get auto-generated repository methods with full type safety—no code generation step required.

// Define once
const userRepo = createRepository(db, 'users');

// Use immediately - methods are auto-generated
const user = await userRepo.findByEmail('[email protected]');
const activeUsers = await userRepo.findAllByStatusOrderByNameAsc('ACTIVE');
const admins = await userRepo.findAllByRoleIn(['ADMIN', 'SUPER_ADMIN']);

Features

• Auto-generated finder methods - findByX, findAllByX derived from method names
• Comparison operators - GreaterThan, LessThan, In, Like, IsNull, etc.
• Ordering & pagination - OrderByXAsc/Desc, limit, offset
• Batch operations - createMany, updateMany, deleteMany with chunking
• Query builder escape hatches - query(), raw(), count(), exists()
• Transaction support - withTransaction() for atomic operations
• Full TypeScript type safety - Leverages Kysely's type system
• Zero code generation - Works at runtime via Proxy

Performance

Frix is designed for high performance with minimal overhead:

• createMany: ~118,000 ops/sec (10k records)
• findAllBy: ~247,000 ops/sec
• count: ~4,138,000 ops/sec

See BENCHMARKS.md for detailed results and methodology.

Coverage

Code coverage is enforced at 80% threshold for lines, branches, functions, and statements.

Current coverage: 96.11% lines, 90.79% branches, 98.36% functions

# Run tests with coverage
npm run test:coverage

# Run benchmarks
npm run benchmark

Installation

npm install @jt-dev/frix kysely pg

Note: Kysely is a peer dependency and must be installed separately.

Quick Start

1. Define your database types

import type { Generated } from 'kysely';

interface UserTable {
  id: Generated<number>;
  email: string;
  name: string;
  status: 'ACTIVE' | 'INACTIVE';
  created_at: Generated<Date>;
}

interface Database {
  users: UserTable;
}

2. Create a database connection

import { createDatabase } from 'frix';

const db = createDatabase<Database>({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'myapp',
});

3. Create a repository

import { createRepository } from 'frix';

const userRepo = createRepository(db, 'users');

4. Use auto-generated methods

// Basic CRUD
const user = await userRepo.create({ email: '[email protected]', name: 'Alice', status: 'ACTIVE' });
const found = await userRepo.findById(user.id);
await userRepo.update(user.id, { name: 'Alice Smith' });
await userRepo.delete(user.id);

// Derived finders
const byEmail = await userRepo.findByEmail('[email protected]');
const activeUsers = await userRepo.findAllByStatus('ACTIVE');

// Comparison operators
const recentUsers = await userRepo.findAllByIdGreaterThan(100);
const specificStatuses = await userRepo.findAllByStatusIn(['ACTIVE', 'PENDING']);

// Ordering and pagination
const sorted = await userRepo.findAllByStatusOrderByNameAsc('ACTIVE');
const paginated = await userRepo.findAllByStatus('ACTIVE', { limit: 10, offset: 20 });

// Batch operations
const users = await userRepo.createMany([...data], { chunkSize: 1000 });
await userRepo.updateMany({ status: 'INACTIVE' }, { status: 'ACTIVE' });
await userRepo.deleteMany({ status: 'DELETED' });

// Query builder
const count = await userRepo.count({ status: 'ACTIVE' });
const exists = await userRepo.exists({ email: '[email protected]' });
const custom = await userRepo.query()
  .where('created_at', '>', someDate)
  .orderBy('name', 'asc')
  .execute();

Working with Generated Fields

Frix automatically unwraps Kysely's Generated<T> and ColumnType<> types, so you work with primitives:

import type { Generated } from 'kysely';

interface UserTable {
  id: Generated<number>;           // Auto-generated by database
  email: string;
  created_at: Generated<Date>;     // Auto-generated timestamp
}

const userRepo = createRepository(db, 'users');

// Works with primitives (not Generated<number>)
const user = await userRepo.findById(1);        // id: number (not Generated<number>)
const byEmail = await userRepo.findByEmail('[email protected]');

// create() omits Generated fields automatically
const newUser = await userRepo.create({
  email: '[email protected]',
  // No need to provide 'id' or 'created_at'
});

// Returns unwrapped types
console.log(newUser.id);         // number
console.log(newUser.created_at); // Date

Type unwrapping happens at compile-time with zero runtime overhead.

API Reference

createDatabase(config)

Creates a Kysely database instance with PostgreSQL dialect.

const db = createDatabase<Database>({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'myapp',
  max: 10, // connection pool size
});

createRepository(db, tableName, options?)

Creates a repository with auto-generated methods.

const repo = createRepository(db, 'users', {
  idColumn: 'user_id', // default: 'id'
});

Base Methods

| Method | Description | |--------|-------------| | findAll() | Get all rows | | findById(id) | Get by primary key, returns null if not found | | create(data) | Insert and return row | | update(id, data) | Update and return row, returns null if not found | | delete(id) | Delete and return boolean | | save(data) | Upsert (insert or update) |

Return Values

Methods that may not find a result return null (not undefined):

• findById() → Row | null
• findByX() (single finders) → Row | null
• update() → Row | null

This follows the convention that null represents "intentionally absent" while undefined represents "not set".

Batch Methods

| Method | Description | |--------|-------------| | createMany(data[], options?) | Bulk insert with chunking | | updateMany(criteria, data) | Update matching rows | | deleteMany(criteria) | Delete matching rows |

Options for createMany:

• chunkSize: Records per batch (default: 1000)
• skipReturn: Return count instead of rows (faster)

Query Builder Methods

| Method | Description | |--------|-------------| | query() | Get Kysely SelectQueryBuilder | | raw(sqlBuilder) | Execute raw SQL | | count(criteria?) | Count matching rows | | exists(criteria?) | Check if rows exist |

Derived Finder Pattern

Method names are parsed to generate queries:

findBy{Column}                    → WHERE column = ?
findAllBy{Column}                 → WHERE column = ? (returns array)
findBy{Column}And{Column}         → WHERE col1 = ? AND col2 = ?
findBy{Column}GreaterThan         → WHERE column > ?
findBy{Column}LessThanEqual       → WHERE column <= ?
findBy{Column}In                  → WHERE column IN (?)
findBy{Column}Like                → WHERE column LIKE ?
findBy{Column}IsNull              → WHERE column IS NULL
findAllBy{Column}OrderBy{Column}Asc  → ORDER BY column ASC
findAllBy{Column}OrderBy{Column}Desc → ORDER BY column DESC

Column Name Normalization: CamelCase method names automatically map to snake_case columns:

• findByUserId → WHERE user_id = ?
• findByCreatedAt → WHERE created_at = ?

Type-Safe Complex Queries

For complex finder methods (multi-column, ordering, comparisons), define a query interface and use .extend<T>():

// Define your complex queries (like Spring Data JPA)
interface UserQueries {
  findByEmailAndStatus(email: string, status: string): Promise<User | null>;
  findAllByStatusOrderByNameAsc(status: string): Promise<User[]>;
  findByAgeGreaterThan(age: number): Promise<User[]>;
}

// Create typed repository
const userRepo = createRepository(db, 'users').extend<UserQueries>();

// Full type safety - no @ts-expect-error needed!
const user = await userRepo.findByEmailAndStatus('[email protected]', 'ACTIVE');
const sorted = await userRepo.findAllByStatusOrderByNameAsc('ACTIVE');
const adults = await userRepo.findByAgeGreaterThan(18);

The .extend<T>() method:

• Preserves type inference for database and table
• Adds your custom query types
• Returns the same repository instance (zero overhead)
• Is fully optional - simple queries work without it

Transactions

import { withTransaction } from 'frix';

await withTransaction(db, async (trx) => {
  const userRepo = createRepository(trx, 'users');
  const postRepo = createRepository(trx, 'posts');

  const user = await userRepo.create({ ... });
  await postRepo.create({ user_id: user.id, ... });
  // Commits on success, rolls back on error
});

DTO Mapping

Map database rows (snake_case) to DTOs (camelCase) automatically using .withMapper():

AutoMapper - Convention-based Mapping

Automatically converts keys between snake_case and camelCase:

import { createRepository, AutoMapper } from 'frix';

// Create a mapped repository - all operations work with DTOs
const userDtoRepo = createRepository(db, 'users')
  .withMapper(new AutoMapper<UserRow, UserDTO>());

// Read operations return DTOs automatically
const users = await userDtoRepo.findAll();        // UserDTO[]
const user = await userDtoRepo.findById(1);       // UserDTO | null

// Write operations accept DTOs
const newUser = await userDtoRepo.create({
  userName: 'Alice',  // camelCase input
  status: 'ACTIVE',
});

// Access raw repository when needed
const rawUser = await userDtoRepo.raw.findById(1);  // snake_case Row

Chain with .extend<T>() for custom queries (.withMapper() must be last):

const repo = createRepository(db, 'users')
  .extend<UserQueries>()    // Add custom query types
  .withMapper(mapper);       // Terminal operation - returns DTOs

CustomMapper - Explicit Transformations

For custom mapping logic like combining fields or type conversions:

import { createRepository, CustomMapper } from 'frix';

const mapper = new CustomMapper<UserRow, UserSummaryDTO>({
  toDto: (row) => ({
    id: row.id,
    fullName: `${row.first_name} ${row.last_name}`,
    isActive: row.status === 'ACTIVE',
  }),
  toRow: (dto) => ({
    id: dto.id,
    first_name: dto.fullName.split(' ')[0] || '',
    last_name: dto.fullName.split(' ')[1] || '',
    status: dto.isActive ? 'ACTIVE' : 'INACTIVE',
  }),
});

const repo = createRepository(db, 'users').withMapper(mapper);

Pagination

All findAllBy methods support pagination via limit and offset options:

// Basic pagination
const page1 = await userRepo.findAllByStatus('ACTIVE', { limit: 10 });
const page2 = await userRepo.findAllByStatus('ACTIVE', { limit: 10, offset: 10 });
const page3 = await userRepo.findAllByStatus('ACTIVE', { limit: 10, offset: 20 });

// Combined with ordering
const sorted = await userRepo.findAllByStatusOrderByNameAsc('ACTIVE', { limit: 10 });

// With comparison operators
const recent = await userRepo.findAllByCreatedAtGreaterThan(lastWeek, { limit: 50 });

// Pagination helper example
async function getUsers(status: string, page: number, pageSize = 20) {
  return userRepo.findAllByStatus(status, {
    limit: pageSize,
    offset: (page - 1) * pageSize,
  });
}

// Get total for pagination UI
const total = await userRepo.count({ status: 'ACTIVE' });
const totalPages = Math.ceil(total / pageSize);

Health Check

import { checkDatabaseHealth } from 'frix';

const health = await checkDatabaseHealth(db);
console.log(health.healthy); // true/false
console.log(health.latencyMs); // response time

Performance

Frix is designed for high performance:

• Parallel batch execution - createMany executes chunks in parallel via Promise.all
• Chunked batch operations - Handles large datasets without memory issues
• Connection pooling - Built-in via Kysely/pg
• Efficient existence checks - exists() uses LIMIT 1
• No ORM overhead - Direct query building, no entity tracking

Benchmarks

| Operation | Records | Time | |-----------|---------|------| | createMany | 1,000 | < 500ms | | createMany | 10,000 | < 5s | | findAllBy | 1,000 | < 100ms | | updateMany | 1,000 | < 200ms | | count | 10,000 | < 50ms |

Benchmarks run on PostgreSQL 15, see tests/integration for details.

Connection Pool Sizing

When using createMany with large datasets, multiple chunks execute in parallel. Ensure your connection pool is sized appropriately:

const db = createDatabase<Database>({
  // ...
  max: 20, // Increase for large parallel batch operations
});

Recommendation: Set max to at least ceil(expectedRecords / chunkSize) for optimal parallel performance.

Limitations

• PostgreSQL only - MySQL/SQLite support planned
• Peer dependency on Kysely - You manage the Kysely version
• No relations - Use query() for joins

Roadmap

• [ ] MySQL and SQLite dialect support
• [ ] Soft delete support (deletedAt)
• [ ] Automatic audit fields (createdAt, updatedAt)

Contributing

Contributions are welcome! Please follow these steps:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Write tests for your changes
4. Implement the feature
5. Ensure all tests pass (npm test)
6. Run linting (npm run check)
7. Commit with conventional commits (feat: add amazing feature)
8. Push and open a Pull Request

Development Setup

git clone https://github.com/yourusername/frix.git
cd frix
npm install
npm test

Running Integration Tests

docker-compose -f docker-compose.test.yml up -d
npm run test:integration

License

MIT

Frix - Because database access should be simple, type-safe, and fast (when possible).