xansql

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.