tnex overview

Tnex Overview

We interact with the database via Tnex, a typesafe(ish) wrapper around Knex. Tnex's API is very similar to Knex's, with a few differences to ensure type safety (detailed below).

Preamble: Define your table's schema

In order to use Tnex, we must tell it what our table schemas are. For each table in the database, create a class that enumerates that table's columns, then register it with a TableRegistry.

import { number, string, nullable, TableRegistry } from './tnex';

const tables = new TableRegistry();

// First, declare class that matches table's schema
export class Dog {
  // Column names should begin with a table prefix ("dog_" here)
  dog_id = number();
  dog_name = string();
  dog_owner = nullable(number());
}

// Second, create an instance of your table and register it.
export const dog = tables.register(new Dog());

• Every column in your table is a property of your class.
• Each property must be prefixed with the same table prefix. You can choose whatever prefix you want, as long as it's globally unique among your table classes.
• The prefixes should only appear in your class definitions; they shouldn't be part of your actual column names.
• Note the use of the nullable() function to define a nullable column.
• By default, the name of the database table will be inferred from the name of your class (by lower-casing its first letter). If you want to specify a different table name, add it as a second parameter to tables.register(tableInstance, tableName).

Selects

import { dog } from './tables';
import { Tnex, val } from './tnex';

function getOwnedDogs(db: Tnex, owner: number) {
  return db
      .select(dog)
      .columns(
          'dog_id',
          'dog_name',
          )
      .where('dog_owner', '=', val(owner))
      .run();
}

Notes:

• We use our table instance (dog) to specify what table we want to query.
• We use the columns() function to specify which columns we want to select.
• We have to call run() when we've completed our call chain (or we won't get a Promise back).
• Note the use of val() when referencing a value (as opposed to a column name).
• Typescript will automatically infer the return type for us, so we usually don't need to specify one. However, it can sometimes be convenient to declare an explicit type that can be referenced elsewhere.
• The inferred return type of this function is Promise<Pick<Dog, 'dog_id'|'dog_name'>>. The Promise<> part should be familiar. Pick<T, K> means "given an interface T, return a subset of it that includes just the keys K". So Pick<Dog, 'dog_id'|'dog_name'> is equivalent to { dog_id: number, dog_name: string }.

Joins

Tnex supports left and inner joins. The join functions take a table plus a join condition of the form 'new_column', comparison, 'existing_column'.

return db
    .select(dog)
    .join(owner, 'owner_id', '=', 'dog_owner')
    .columns(
        'dog_name',
        'owner_name',
        )
    .run();

Inserts

There are three insert methods: insert() (single row), insertAll (multiple rows), and batchInsert() (hundreds of rows).

Unlike pretty much any other tnex query, you do not need to call run() after an insert call.

import { dog } from './tables';
import { DEFAULT_NUM } from './tnex';

return db
    .insert(dog, {
      dog_id: DEFAULT_NUM,
      dog_name: 'Woofy',
      dog_owner: 547,
    });

return db
    .insertAll(dog, [
        { dog_id: DEFAULT_NUM, dog_name: 'Woofy', dog_owner: 547, },
        { dog_id: DEFAULT_NUM, dog_name: 'Boofy', dog_owner: 17, },
        { dog_id: DEFAULT_NUM, dog_name: 'Snufflekins', dog_owner: 73 },
    ]);

• If you want to use a column's default value, pass one of the special DEFAULT_XXX values. Be careful: if you try to use this on a column that doesn't have a default value, SQL will throw an error.
• Returns a promise to a number or array of numbers (insert() vs. insertAll()). The number is the ID of the created row.

Update

import { dog } from './tables';

return db
    .update(dog, { owner: 547, })
    .where('dog_id', '=', 12)
    .run();

• Returns a promise to the number of updated rows.

Upsert

Like insert(), except that you must specify a third parameter: the name of the id column in your table.

import { dog } from './tables';

return db
    .upsert(dog, {
      dog_id: DEFAULT_NUM,
      dog_name: 'Woofy',
      dog_owner: 547,
    }, 'dog_id');

Subselect joins

Subjoins are performed using the subselect() function.

return db
    .select(dog)
    .join(
        // Declare name of subtable = "youngOwner"
        db.subselect(owner, 'youngOwner')
            // Instead of calling columns(), use columnAs()
            // IMPORTANT: renamed columns must begin with "youngOwner_" or error is thrown
            .columnAs('owner_id', 'youngOwner_id')
            .columnAs('owner_name', 'youngOwner_name')
            .where('owner_age', '<', val(25)),
        // Standard join condition, using declared column
        'youngOwner_id', '=', 'dog_owner')
    .columns(
        'dog_name',
        'youngOwner_name',
        )
    .run();

• You must declare a name for your subtable ('youngOwner' in the example above).
• Instead of columns(), you must declare each selected column individually using columnAs() and specifying a column alias. Your aliases must begin with your subtable's alias (e.g. 'youngOwner_foo').
• Do not call run() at the end of the subselect call chain.

Renamed joins (self joins, multijoins, etc)

If you need to join the same table more than once, use a renamed join via the alias() function. Like subjoins, you must rename the columns you want to use so that they are prefixed with your new table's name.

return db
    .select(award)
    .join(
        db.alias(dog, 'firstPlaceDog')
            .using('dog_id', 'firstPlaceDog_id')
            .using('dog_name', 'firstPlaceDog_name')
        'firstPlaceDog_id', '=', 'award_firstPlaceDog')
    .join(
        db.alias(dog, 'secondPlaceDog')
            .using('dog_id', 'secondPlaceDog_id')
            .using('dog_name', 'secondPlaceDog_name')
        'secondPlaceDog_id', '=', 'award_secondPlaceDog')
    .columns(
        'award_name',
        'firstPlaceDog_name',
        'secondPlaceDog_name',
        )
    .run();