Skip to content

Table

Jump to bottom
IAmKirbki edited this page Mar 6, 2026 · 2 revisions

Table Class

The Table class provides a high-level interface for common database table operations. It abstracts away SQL syntax by offering methods for fetching records, inserting data, performing joins, and managing table metadata. This class is ideal when you don't want to write raw SQL but still need direct table access without the full ORM features of Models.

Constructor

const table = new Table({ name: string, adapter?: string });

Parameters:

Parameter Type Description Optional?
name string The name of the database table No
adapter string The name of the database adapter to use (uses default if not specified) Yes

Example:

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

// Using default adapter
const usersTable = new Table({ name: 'users' });

// Using a specific adapter
const postsTable = new Table({ name: 'posts', adapter: 'secondary' });

Methods

TableColumnInformation()

Returns raw column information from the database schema.

Parameters:

Parameter Type Description Optional?
tableName string Override the table name to query (defaults to the table's own name) Yes

Returns: Promise<TableColumnInfo[]>

Example:

const columns = await usersTable.TableColumnInformation();
console.log(columns);
// Output: [{ cid: 0, name: 'id', type: 'INTEGER', notnull: 1, pk: 1, dflt_value: null }, ...]

ReadableTableColumnInformation()

Returns formatted, human-readable column information.

Returns: Promise<ReadableTableColumnInfo[]>

Example:

const columns = await usersTable.ReadableTableColumnInformation();
console.log(columns);
// Output: [
//   {
//     name: 'id',
//     type: 'INTEGER',
//     nullable: false,
//     isPrimaryKey: true,
//     defaultValue: null
//   },
//   ...
// ]

exists()

Checks whether the table exists in the database.

Returns: Promise<boolean>

Example:

const tableExists = await usersTable.exists();
if (!tableExists) {
  console.log('Table does not exist yet.');
}

FetchRecords()

Fetches multiple records from the table using the QueryLayers query structure.

Parameters:

Parameter Type Description Optional?
queryLayers QueryLayers Structured query configuration object No

QueryLayers Structure:

type QueryLayers = {
  base: BaseQueryOptions; // FROM, JOIN, WHERE, ORDER BY, expressions, SELECT
  pretty?: PrettyQueryOptions; // HAVING, GROUP BY, additional WHERE/SELECT
  final?: FinalQueryOptions; // LIMIT, OFFSET, ORDER BY overrides
};

Returns: Promise<Record<Type>[]>

Examples:

Fetch all records:

const allUsers = await usersTable.FetchRecords({ base: { from: 'users' } });

Fetch with filtering:

const adults = await usersTable.FetchRecords({
  base: {
    from: 'users',
    where: [{ column: 'age', operator: '>=', value: 18 }],
  },
});

Fetch with ordering and pagination:

const results = await usersTable.FetchRecords({
  base: {
    from: 'users',
    select: ['id', 'name', 'email'],
    where: [
      { column: 'status', operator: '=', value: 'active' },
      { column: 'role', operator: '=', value: 'admin' },
    ],
    orderBy: [{ column: 'name', direction: 'ASC' }],
  },
  final: {
    limit: 10,
    offset: 20,
  },
});

FetchSingleRecord()

Fetches a single record from the table. Internally calls FetchRecords() with limit: 1.

Parameters:

Parameter Type Description Optional?
queryLayers QueryLayers Structured query configuration object No

Returns: Promise<Record<Type> | undefined>

Examples:

Fetch first record:

const user = await usersTable.FetchSingleRecord({ base: { from: 'users' } });

Fetch with filtering:

const admin = await usersTable.FetchSingleRecord({
  base: {
    from: 'users',
    where: [{ column: 'email', operator: '=', value: 'admin@example.com' }],
  },
});

RecordsCount()

Returns the total count of records in the table.

Returns: Promise<number>

Example:

const totalUsers = await usersTable.RecordsCount();
console.log(`Total users: ${totalUsers}`);

CreateRecord()

Inserts a new record into the table.

Parameters:

Parameter Type Description Optional?
values Type Key-value pairs representing the record data No

Returns: Promise<Record<Type> | undefined>

Example:

const newUser = await usersTable.CreateRecord({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
});

console.log('Inserted user:', newUser?.values);

FetchJoined()

Performs JOIN operations with other tables. The results automatically split joined data into separate nested objects for each joined table.

Parameters:

Parameter Type Description Optional?
queryLayers QueryLayers Query configuration with base.joins defined No

Join Configuration (Join type):

Property Type Description
fromTable string The table to join
baseTable string The base (left-hand) table
joinType 'INNER' | 'LEFT' | 'RIGHT' | 'FULL' Type of join
on QueryIsEqualParameter | QueryIsEqualParameter[] Join conditions
where QueryIsEqualParameter | QueryIsEqualParameter[] Optional additional WHERE on the join
name string Optional alias for the join

Returns: Promise<Record<Type>[]>

Examples:

Simple JOIN:

const usersWithPosts = await usersTable.FetchJoined({
  base: {
    from: 'users',
    joins: [
      {
        fromTable: 'posts',
        baseTable: 'users',
        joinType: 'LEFT',
        on: { user_id: 'id' },
      },
    ],
  },
});

// Result structure - joined table data is nested under the joined table's name:
// [
//   {
//     id: 1,
//     name: 'John',
//     posts: { id: 1, title: 'First Post', user_id: 1 }
//   },
//   ...
// ]

Multiple JOINs with filtering:

const results = await usersTable.FetchJoined({
  base: {
    from: 'users',
    joins: [
      {
        fromTable: 'posts',
        baseTable: 'users',
        joinType: 'LEFT',
        on: { user_id: 'id' },
      },
      {
        fromTable: 'comments',
        baseTable: 'posts',
        joinType: 'LEFT',
        on: { post_id: 'id' },
      },
    ],
    where: [{ column: 'users.status', operator: '=', value: 'active' }],
  },
  final: {
    limit: 50,
  },
});

Drop()

Drops the table from the database. Use with caution - this permanently deletes the table and all its data.

Returns: Promise<void>

Example:

await usersTable.Drop();
console.log('Table dropped successfully');

toSql()

Returns the SQL string that would be executed for a given QueryLayers configuration, without actually running the query.

Parameters:

Parameter Type Description Optional?
queryLayers QueryLayers Structured query configuration object No

Returns: Promise<string>

Example:

const sql = await usersTable.toSql({
  base: {
    from: 'users',
    where: [{ column: 'status', operator: '=', value: 'active' }],
  },
  final: { limit: 10 },
});

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

QueryHelperClient (getter)

Returns the internal Query instance used by this table. Useful for advanced operations that bypass the Table abstraction.

Returns: Query

Complete Usage Example

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

// Create a table instance
const usersTable = new Table({ name: 'users' });

// Check if table exists
const exists = await usersTable.exists();
console.log('Table exists:', exists);

// Get table information
const columns = await usersTable.ReadableTableColumnInformation();
console.log('Table columns:', columns);

// Insert a new user
const newUser = await usersTable.CreateRecord({
  name: 'Jane Doe',
  email: 'jane@example.com',
  age: 25,
  status: 'active',
});

// Fetch active users
const activeUsers = await usersTable.FetchRecords({
  base: {
    from: 'users',
    where: [{ column: 'status', operator: '=', value: 'active' }],
    orderBy: [{ column: 'name', direction: 'ASC' }],
  },
  final: { limit: 10 },
});

// Fetch a specific user
const user = await usersTable.FetchSingleRecord({
  base: {
    from: 'users',
    where: [{ column: 'email', operator: '=', value: 'jane@example.com' }],
  },
});

// Get total count
const totalUsers = await usersTable.RecordsCount();

// Join with posts table
const usersWithPosts = await usersTable.FetchJoined({
  base: {
    from: 'users',
    joins: [
      {
        fromTable: 'posts',
        baseTable: 'users',
        joinType: 'LEFT',
        on: { user_id: 'id' },
      },
    ],
    where: [{ column: 'users.status', operator: '=', value: 'active' }],
  },
});

console.log('Users with posts:', usersWithPosts);

Notes

  • All query operations use parameterized queries to prevent SQL injection
  • The where parameter in QueryLayers accepts QueryComparisonParameters[] arrays, supporting operators like =, !=, <, <=, >, >=, LIKE, IN, and NOT IN
  • FetchJoined() automatically organizes results by table, nesting joined table columns under the joined table's name
  • Use base.select to fetch only specific columns for better performance
  • FetchSingleRecord() is a convenience wrapper around FetchRecords() with final.limit: 1
  • toSql() is useful for debugging — it returns the SQL that would run without executing it

Clone this wiki locally