<p align="center"> <strong>Type-safe, event-driven SQL ORM with automatic schema synchronization, composable relations, granular hooks, and optional client execution bridge.</strong> </p>
npm install xansql
Type-safe, event-driven SQL ORM with automatic schema synchronization, composable relations, granular hooks, and optional client execution bridge.
---
select trees (preventing circular graphs).where supporting deep EXISTS on nested relations.---
---
xt.schema / array reverse mapping)where condition operators (logical AND/OR composition)EXISTS semantics---
Xansql orchestrates config, model registry, transactions, migration, fetch bridge and events.xt.*) with metadata (length, unique, index, validators, transforms).---
bash
npm install xansql mysql2 pg better-sqlite3
Or only the drivers you need
`
SQLite usage recommends better-sqlite3 for synchronous performance.---
4. Quick Start
`ts
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: 'a@b.com', password: 'Pwd@1234', role: 'member' }] });
const result = await User.find({ where: { username: { equals: 'alice' } } });
`---
5. Configuration Reference
`ts
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:
`ts
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
`ts
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:
`ts
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:
`ts
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:
`ts
await Post.find({
where: {
author: { username: { startsWith: 'a' } },
slug: { notContains: 'draft' },
title: [{ contains: 'Guide' }, { contains: 'Intro' }]
}
});
`---
9. Aggregation & Helpers
Inline:
`ts
await User.find({ aggregate: { id: { count: true } } });
`
Helpers: count(where), min(col, where), max, sum, avg, exists(where).---
10. Pagination & Convenience
`ts
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:
`ts
await db.transaction(async () => {
await User.create({ data: [{ username: 'temp' }] });
await User.update({ data: { role: 'admin' }, where: { username: 'temp' } });
});
`
Rollback on error.---
12. Migrations
`ts
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:
`ts
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:
`ts
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:
`ts
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:
`ts
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:
`ts
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.