xansql

Type-safe, event-driven SQL ORM with automatic schema synchronization, composable relations, granular hooks, and optional client execution bridge.

---

Executive Summary

xansql is a minimalist but powerful ORM focusing on:

• Deterministic schema definition (single source of truth) with non-destructive migration.
• Relation traversal via declarative select trees (preventing circular graphs).
• Rich predicate language in where supporting deep EXISTS on nested relations.
• Event system & lifecycle hooks (global + per-model) for observability & cross-cutting concerns.
• Pluggable caching, file storage, fetch bridge (browser safe), and socket integration.
• Lightweight execution pipeline: thin SQL generation, no heavy runtime proxies.

---

Contents

1. Features
2. Architecture Overview
3. Installation
4. Quick Start
5. Configuration Reference
6. Defining Models & Fields
7. Relations
8. Querying & Predicates
9. Aggregation & Helpers
10. Pagination & Convenience APIs
11. Transactions
12. Migrations
13. Events & Hooks
14. File Handling
15. Client Fetch Bridge
16. Caching Interface
17. Dialects & Custom Implementation
18. Error Handling & Validation
19. Security Considerations
20. Performance Guidance
21. FAQ
22. Roadmap
23. License

---

1. Features

• Multi-dialect: MySQL, PostgreSQL, SQLite (custom adapter friendly)
• Auto aliasing + integrity checks
• Declarative relations (xt.schema / array reverse mapping)
• Non-destructive migrate (add/modify/remove columns) + force rebuild
• Granular lifecycle hooks & event emission
• Rich where condition operators (logical AND/OR composition)
• Nested relational filtering through EXISTS semantics
• Aggregation inline or via helper methods
• Optional caching module contract
• Integrated file meta handling & streaming upload abstraction
• Client-side safe execution (no raw SQL leakage) via signed execution meta

---

2. Architecture Overview

Layered components:

• Core: Xansql orchestrates config, model registry, transactions, migration, fetch bridge and events.
• Model: Provides CRUD + query generation + relation resolution.
• Executers: Specialized operation builders (Find / Create / Update / Delete / Aggregate).
• Migration: Computes delta from declared schema vs dialect metadata and issues SQL.
• Types System: Field factories (xt.*) with metadata (length, unique, index, validators, transforms).
• Foreign Resolver: Normalizes forward & reverse relation mapping for join/exists generation.
• Fetch Bridge: Validates request meta for client-originated operations (server controlled).

---

3. Installation

npm install xansql mysql2 pg better-sqlite3
# Or only the drivers you need

SQLite usage recommends better-sqlite3 for synchronous performance.

---

4. Quick Start

import { Xansql, Model, xt } from 'xansql';
import MysqlDialect from 'xansql/dist/libs/MysqlDialect';

const db = new Xansql({
  dialect: MysqlDialect({ host: '127.0.0.1', user: 'root', password: '', database: 'app' })
});

const User = db.model('users', {
  id: xt.id(),
  username: xt.username(),
  email: xt.email().unique(),
  password: xt.password().strong(),
  role: xt.role(['admin', 'member']),
  createdAt: xt.createdAt(),
  updatedAt: xt.updatedAt()
});

await db.migrate();
await User.create({ data: [{ username: 'alice', email: '[email protected]', password: 'Pwd@1234', role: 'member' }] });
const result = await User.find({ where: { username: { equals: 'alice' } } });

---

5. Configuration Reference

new Xansql({
  dialect: MysqlDialect({...}),            // REQUIRED
  fetch: { url: '/xansql', mode: 'production' }, // optional (client bridge)
  socket: { open, message, close },        // optional WebSocket handlers
  cache: { cache, clear, onFind, onCreate, onUpdate, onDelete }, // optional
  file: { maxFilesize, chunkSize, upload, delete }, // optional file storage
  maxLimit: { find, create, update, delete },       // safety caps (default 100)
  hooks: { beforeFind, afterFind, transform, ... }  // global async hooks
});

Required dialect interface:

interface XansqlDialect {
  engine: 'mysql' | 'postgresql' | 'sqlite';
  execute(sql: string): Promise<{ results: any[]; affectedRows: number; insertId: number | null }>;
  getSchema(): Promise<{ [table: string]: { name: string; type: string; notnull: boolean; default_value: any; pk: boolean; index: boolean; unique: boolean }[] }>
}

---

6. Defining Models & Fields

const Post = db.model('posts', {
  id: xt.id(),
  title: xt.title().index(),
  slug: xt.slug().unique(),
  author: xt.schema('users', 'id'),         // FK forward
  tags: xt.array(xt.string(30)),            // array (not in where predicate)
  images: xt.array(xt.file()),              // file metadata entries
  createdAt: xt.createdAt(),
  updatedAt: xt.updatedAt()
});

Per-model hooks:

Post.options.hooks = {
  beforeCreate: async (args) => args,
  transform: async (row) => { delete row.password; return row; }
};

Field factory highlights: id, string, number, boolean, date, enum, array, object, record, tuple, union, file, schema + semantic shortcuts (username, email, password, slug, role, title, amount, etc.). Most fields accept chainable validators (min, max, unique, index, transform).

Foreign key patterns:

• Forward: xt.schema('users','id')
• Reverse (one-to-many): xt.array(xt.schema('posts','id'))

---

7. Relations

Select nested relations:

await User.find({
  select: {
    id: true,
    username: true,
    posts: {
      select: { id: true, title: true },
      where: { title: { contains: 'SQL' } },
      limit: { take: 5 }
    }
  }
});

Circular graphs are rejected early.

---

8. Querying & Predicates

Operators: equals, not, lt, lte, gt, gte, in, notIn, between, notBetween, contains, notContains, startsWith, endsWith, isNull, isNotNull, isEmpty, isNotEmpty, isTrue, isFalse.

• Object => AND
• Array of objects => OR
• Nested relation in where => EXISTS subquery Example:

await Post.find({
  where: {
    author: { username: { startsWith: 'a' } },
    slug: { notContains: 'draft' },
    title: [{ contains: 'Guide' }, { contains: 'Intro' }]
  }
});

---

9. Aggregation & Helpers

Inline:

await User.find({ aggregate: { id: { count: true } } });

Helpers: count(where), min(col, where), max, sum, avg, exists(where).

---

10. Pagination & Convenience

const page = await User.paginate(2, { perpage: 20, where: { role: { equals: 'member' } } });
// { page, perpage, pagecount, rowcount, results }

Also: findOne(args), findById(id, args).

---

11. Transactions

Automatic for create/update/delete unless within chained relation execution. Manual wrapper:

await db.transaction(async () => {
  await User.create({ data: [{ username: 'temp' }] });
  await User.update({ data: { role: 'admin' }, where: { username: 'temp' } });
});

Rollback on error.

---

12. Migrations

await db.migrate();        // sync non-destructively
await db.migrate(true);    // drop + recreate (files cleaned)
const preview = await db.generateMigration(); // array of SQL statements

Rules:

• Skips ID column alterations.
• Adds new columns; drops removed ones; issues ALTER for changed definition.
• Force rebuild executes reverse-order drops then creates.

---

13. Events & Hooks

Events emitted: BEFORE_CREATE, CREATE, BEFORE_UPDATE, UPDATE, BEFORE_DELETE, DELETE, BEFORE_FIND, FIND, BEFORE_AGGREGATE, AGGREGATE, BEFORE_FETCH, FETCH. Usage:

db.on('CREATE', ({ model, results }) => { /* audit */ });

Hooks (global & model-level) allow mutation of args/results or row transform.

---

14. File Handling

Define file fields: xt.file(size?) / arrays. Configure storage:

file: {
  maxFilesize: 2048,        // KB
  chunkSize: 256,           // KB (streaming)
  upload: async (chunk, meta) => {},
  delete: async (filename) => {}
}

Client helpers: uploadFile(file, executeId), deleteFile(name, executeId).

---

15. Client Fetch Bridge

Provide fetch: string | { url, mode }. Client side raw SQL blocked; operations require internally generated executeId (granted per model action via metadata). Server integrates:

const response = await db.onFetch(req.url, {
  body: req.body,
  headers: req.headers,
  cookies: parseCookies(req),
  isAuthorized: async (meta) => {/* check meta.action, meta.model */ return true; }
});

---

16. Caching Interface

Implement partial or full row caching:

cache: {
  cache: async (sql, model) => /* rows or undefined */,
  clear: async (model) => {},
  onFind: async (sql, model, row) => {},
  onCreate: async (model, insertId) => {},
  onUpdate: async (model, rows) => {},
  onDelete: async (model, rows) => {},
}

You decide strategy (memory, redis, browser IndexedDB via example adapters).

---

17. Dialects & Custom Implementation

Built-ins: MysqlDialect, PostgresDialect, SqliteDialect. Custom:

const CustomDialect = () => ({
  engine: 'mysql',
  execute: async (sql) => {/* run */ return { results: [], affectedRows: 0, insertId: 0 };},
  getSchema: async () => ({ /* table: columns[] */ })
});

getSchema must supply column index/unique flags for migration diffing.

---

18. Error Handling & Validation

Common thrown errors:

• Missing dialect or execute function
• Unsupported engine
• Model without ID field
• Duplicate model name / alias collision
• Invalid where operator or disallowed field type in predicate (array/object/record/tuple)
• Circular relation selection / where nesting
• Client usage without fetch configuration
• Raw query attempt from client without executeId
• Invalid foreign key definition

---

19. Security Considerations

• All value interpolation passes through escaping utilities.
• Client cannot send arbitrary SQL (requires signed meta created server-side).
• Hooks & events can enforce auditing, RBAC, masking.
• Password field helper automatically hashes via SHA-256 transform.
• Recommend additional app-layer input validation before invoking ORM.

---

20. Performance Guidance

• Prefer selective select trees over full-table scans.
• Use indexes via field .index() / .unique() early (migration will create).
• Enable caching for heavy read patterns.
• Use pagination helpers (paginate) to avoid large offset scans.
• Keep relation depth shallow to limit EXISTS nesting.
• Batch create with array data for reduced round trips.

---

21. FAQ

Q: Does xansql generate JOINs?
A: Relation filters use EXISTS subqueries; selection fetches related sets separately.

Q: How are reverse (one-to-many) relations defined?
A: xt.array(xt.schema('childTable','id')) inside the parent references children.

Q: Can I rename columns automatically?
A: Rename support is planned (see roadmap). Current diff treats rename as drop + add.

Q: Can I use raw SQL?
A: Server side db.execute(sql) is allowed; client side raw is blocked.

---

22. Roadmap

• Column / index rename migration operations
• CLI code generation & schema inspector
• Enhanced diff reporting (explain changes)
• Advanced relation eager constraints (depth limiting strategies)
• Pluggable authorization middleware bundle

---

23. License

MIT

---

Attributions

Internal field validation leverages concepts from xanv. File handling meta uses securequ upload structures.

---

Need adjustments (badges, examples, tutorials)? Open an issue or contribute.