3. Abstract Classes

Advanced Features: Abstract Classes

These abstract classes provide the foundation for building custom implementations and extending the package functionality. They define the structure and contracts that concrete implementations must follow.

Model

The Model abstract class provides an ORM-style interface for database interactions. It offers Active Record pattern functionality, allowing you to define models that represent database tables with built-in methods for querying, inserting, updating, managing relationships, and advanced query expressions.

Key Features:

• Active Record Pattern: Each model instance represents a single database record
• Query Builder Methods: Chainable methods for building complex queries
• Relationship Management: Define and load relationships (hasMany, belongsTo, etc.)
• Automatic Timestamps: Optional automatic created_at and updated_at management
• Primary Key Management: Configurable primary key handling
• Spatial Queries: Built-in near() method for distance-based filtering and ordering
• Full-Text Relevance: Built-in isTextRelevant() method for relevance scoring
• JSON Aggregation: Built-in JsonAggregate() method for aggregating related rows into JSON

Creating a Model:

To create a model, extend the Model class and configure it:

import { Model } from '@kirbkis/database-handler-core';

class User extends Model<UserType> {
  protected configuration = {
    table: 'users',
    primaryKey: 'id',
    incrementing: true,
    keyType: 'number',
    timestamps: true,
    createdAtColumn: 'created_at',
    updatedAtColumn: 'updated_at',
    guarded: ['*'], // Fields that cannot be mass-assigned (Not implemented yet)
    customAdapter: 'default', // Optional: specify adapter
  };
}

Basic Usage:

Fetching Records:

// Find by primary key (returns instance with query set, call first() to execute)
const user = await User.find(1).first();

// Find or throw error
const user = await User.findOrFail(1);

// Get first record
const user = await User.first();

// Get all records
const users = await User.all();

Query Building:

// Where clause (simple key-value)
const adults = await User.where({ age: 18 }).get();

// Where clause (comparison operators)
const adults = await User.where([
  { column: 'age', operator: '>=', value: 18 },
]).get();

// Where by ID shorthand
const user = await User.whereId(42).first();

// Ordering
const users = await User.orderBy('name', 'ASC').get();

// Limit and offset (offset requires limit)
const users = await User.limit(10).offset(20).get();

// Combining methods
const users = await User.where({ status: 'active' })
  .orderBy('created_at', 'DESC')
  .limit(5)
  .get();

Creating and Updating:

// Create a new record
const user = User.set({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
});
await user.save();

// Update existing record (pass new values to update())
const user = await User.find(1).first();
await user.update({ name: 'Jane Doe' });

// Or set attributes then save
user.set({ name: 'Jane Doe' });
await user.save();

Inspecting generated SQL:

const sql = await User.where({ status: 'active' }).limit(10).toSql();

console.log(sql);
// SELECT * FROM "users" WHERE status = @status LIMIT 10

Relationships:

Define relationships in your model:

class User extends Model<UserType> {
  protected configuration = {
    table: 'users',
    primaryKey: 'id',
  };

  // Define a one-to-many relationship
  protected posts() {
    return this.hasMany(new Post(), 'user_id', 'id');
  }

  // Define a belongs-to relationship
  protected role() {
    return this.belongsTo(new Role(), 'role_id', 'id');
  }

  // Define a many-to-many relationship (Not implemented yet)
  protected groups() {
    return this.ManyToMany(
      new Group(),
      'user_groups', // pivot table
      'id', // local key
      'id', // foreign key
      'user_id', // pivot foreign key
      'group_id', // pivot local key
    );
  }
}

// Load relationships
const userWithPosts = await User.with('posts').find(1).first();
const userWithRole = await User.with('role').find(1).first();

// Load relationships with conditions
const userWithActivePosts = await User.with('posts', { status: 'published' })
  .find(1)
  .first();

Spatial / Distance Queries:

Use near() to filter and order records by geographic distance:

const nearbyUsers = await User.near({
  referencePoint: { lat: 48.8566, lon: 2.3522 }, // Paris
  targetColumns: { lat: 'latitude', lon: 'longitude' },
  maxDistance: 10,
  unit: 'km',
  orderByDistance: 'ASC',
  alias: 'distance',
  valueClauseKeywords: { lat: 'ref_lat', lon: 'ref_lon' },
}).get();

// Each result will have a `distance` field with the computed distance
nearbyUsers.forEach((user) => console.log(user.values.distance));

Parameters for near():

Parameter	Type	Description	Optional?
referencePoint	{ lat: number, lon: number }	The origin point to measure distance from	No
targetColumns	{ lat: string, lon: string }	Column names storing lat/lon in the table	No
unit	'km' | 'miles'	Distance unit	No
orderByDistance	'ASC' | 'DESC'	Sort direction by distance	No
valueClauseKeywords	Record<'lon' | 'lat', string>	Parameter name aliases for the query	No
maxDistance	number	Maximum distance filter	Yes
minDistance	number	Minimum distance filter	Yes
alias	string	Alias for the computed distance column (default: 'distance')	Yes

---

Full-Text Relevance Queries:

Use isTextRelevant() to score records by how well they match a search term:

const results = await User.isTextRelevant({
  targetColumns: ['name', 'bio'],
  searchTerm: 'developer',
  minimumRelevance: 0.3,
  alias: 'relevance',
  orderByRelevance: 'DESC',
}).get();

// Each result will include a `relevance` score
results.forEach((user) => console.log(user.values.relevance));

Parameters for isTextRelevant():

Parameter	Type	Description	Optional?
targetColumns	string[]	Columns to search within	No
searchTerm	string	The text to search for	No
minimumRelevance	number	Minimum relevance threshold (default: 1)	Yes
alias	string	Alias for the relevance score column (default: 'relevance')	Yes
orderByRelevance	'ASC' | 'DESC'	Sort direction by relevance (default: 'ASC')	Yes

---

JSON Aggregation:

Use JsonAggregate() to aggregate related rows into a nested JSON structure within the query result:

const usersWithPosts = await User.JsonAggregate({
  table: 'posts',
  columns: ['id', 'title', 'created_at'],
  groupByColumns: ['user_id'],
  alias: 'posts',
}).get();

// Each user will have a `posts` field containing an array of post objects
usersWithPosts.forEach((user) => console.log(user.values.posts));

Parameters for JsonAggregate():

Parameter	Type	Description	Optional?
table	string	The related table to aggregate from	No
columns	string[]	Columns to include from the related table	No
groupByColumns	string[]	Columns to group by (default: [])	Yes
alias	string	Alias for the aggregated JSON column (default: table name)	Yes
nested	NestedJsonAggregateDefinition[]	Nested aggregations within this aggregate	Yes
having	QueryWhereCondition	HAVING clause for the aggregate	Yes

---

Configuration Options:

Option	Type	Description	Default
table	string	The database table name	Required
primaryKey	string	The primary key column name	'id'
incrementing	boolean	Whether the primary key is auto-incrementing	true
keyType	'number' | 'string'	The type of the primary key	'number'
timestamps	boolean	Whether to automatically manage timestamps	true
createdAtColumn	string	The name of the created_at column	'created_at'
updatedAtColumn	string	The name of the updated_at column	'updated_at'
deletedAtColumn	string	The name of the deleted_at column for soft deletes	'deleted_at'
guarded	string[]	Fields that cannot be mass-assigned	['*']
fillable	string[]	Mass assignable attributes (whitelist)	undefined
hidden	string[]	Attributes hidden during serialization	undefined
customAdapter	string	Name of the adapter to use	Uses default

---

SchemaTableBuilder

The SchemaTableBuilder abstract class provides a fluent interface for building database table schemas. It's the foundation for adapter-specific schema builders and must be implemented by each database adapter to handle database-specific syntax.

Purpose:

This class defines the contract that all database adapters must follow when implementing schema building functionality. It provides a consistent API across different database systems while allowing each adapter to generate the appropriate SQL for their specific database.

Implementation:

Each database adapter provides its own concrete implementation. For example:

• BetterSqlite3TableSchemaBuilder for SQLite
• PostgresTableSchemaBuilder for PostgreSQL

Methods to Implement:

All implementations must provide these methods:

Data Type Methods:

• increments() - Auto-incrementing primary key
• uuid(name) - UUID column
• string(name, length?) - String/VARCHAR column
• text(name) - Text column
• integer(name) - Integer column
• decimal(name, precision?, scale?) - Decimal column
• float(name) - Float column
• boolean(name) - Boolean column
• json(name) - JSON column
• enum(name, values) - Enum column
• time(name) - Time column
• timestamp(name) - Timestamp column
• timestamps() - Adds created_at and updated_at columns

Constraint Methods:

• primaryKey() - Mark column as primary key
• nullable() - Allow NULL values
• unique() - Add unique constraint
• defaultTo(value) - Set default value
• foreignKey(referenceTable, referenceColumn) - Add foreign key constraint

Special Methods:

• softDeletes() - Add soft delete column (deleted_at)
• morphs(name) - Add polymorphic relation columns
• build() - Generate the final SQL CREATE TABLE statement

Usage Example:

Note: You don't typically use SchemaTableBuilder directly. Instead, use the adapter's schema builder:

import { BetterSqlite3Adapter } from '@kirbkis/database-handler-better-sqlite3';

const adapter = new BetterSqlite3Adapter();
await adapter.connect('./database.db');

// Create a schema
const schema = adapter.createSchema();

// Define a users table
await schema.createTable('users', (table) => {
  table.integer('id').primaryKey().increments(); // id column (auto-increment primary key)
  table.string('name', 255); // name VARCHAR(255) NOT NULL
  table.string('email').unique(); // email VARCHAR(255) UNIQUE
  table.integer('age').nullable(); // age INTEGER NULL
  table.boolean('active').defaultTo(true);
  table.timestamps(); // created_at and updated_at
});

// Define a posts table with foreign key
await schema.createTable('posts', (table) => {
  table.integer('id').primaryKey().increments();
  table.string('title');
  table.text('content');
  table.integer('user_id').foreignKey('users', 'id');
  table.timestamps();
  table.softDeletes();
});

Chaining Methods:

Most methods return this, allowing you to chain multiple constraints:

await schema.createTable('products', (table) => {
  table.integer('id').primaryKey().increments();
  table.string('sku', 50).unique().nullable();
  table.decimal('price', 10, 2).defaultTo(0.0);
  table.integer('category_id').foreignKey('categories', 'id');
});

Creating Custom Implementations:

If you're creating a custom database adapter, you must extend SchemaTableBuilder and implement all abstract methods:

import { SchemaTableBuilder } from '@kirbkis/database-handler-core';

export class MyCustomTableSchemaBuilder extends SchemaTableBuilder {
  build(): string {
    // Generate CREATE TABLE SQL for your database
    let sql = `CREATE TABLE ${this.tableName} (`;
    // ... build column definitions
    sql += ');';
    return sql;
  }

  increments(): this {
    // Add auto-increment primary key
    return this.addColumn({
      name: 'id',
      datatype: 'INTEGER',
      constraints: ['PRIMARY KEY', 'AUTOINCREMENT'],
    });
  }

  // Implement all other abstract methods...
}

Note:

For making use of custom adapters and their schema builders, refer to the adapter documentation:

• Custom Adapter Guide