A simple data access layer and table gateway implementation for MySQL.
npm install mydal
npm install mydal --save
`
Usage
There are two main classes that are used to represent all of the
relationships within the database.
`
TableGateway
JoinTableGateway
`
Additionally, there is a factory called ConnectionPoolFactory for creating
MySQL connection pools.
`
public static create(poolConfig: PoolConfig): Pool;
``
$3
An entity represents a single row within a database. Another name for
this type of object is data transfer object (DTO). Here is an
example entity class for a row in a products table.
`
export class Product {
public id: number;
public name: string;
public description: string;
public price: number;
public created: string;
public updated: string;
public constructor() {
this.created = '';
this.description = null;
this.id = 0;
this.name = '';
this.price = 0;
this.updated = '';
}
}
`
$3
Both of these classes are designed to be wrapped by classes that you
create. The classes that you create can selectively expose the functionality
provided.
The TableGateway class is designed to access all tables
in the database that are not join (or associative) tables.
At a minimum, a class accessed by the TableGateway needs a
primary key. The name of the primary key will default to id
if one is not provided.
Optionally, two additional columns are provided for created
and updated timestamps. The name of those to columns is
configurable by you.
The TableGateway provides the following constructor:
`
// If no primary key is supplied, then "id" is assumed.
constructor(connectionPool: Pool, tableName: string, primaryKey = 'id');
`
The TableGateway provides the following promise style data access and
modification methods:
`
// Returns the inserted row with the inserted primary key (e.g. id) added.
createRow(row: any): Promise;
retrieveRow(id: number): Promise;
updateRow(row: any): Promise;
deleteRow(id: number): Promise;
retrieveRows(fieldName: string, fieldValue: any): Promise;
retrieveRowsByIds(ids: number[]): Promise;
retrieveRowsByIsNull(fieldName: string): Promise;
retrieveRowsByNotEqual(fieldName: string, fieldValue: any): Promise;
setFieldNullWhere(fieldName: string, fieldValue: any): Promise;
deleteRowsBy(fieldName: string, fieldValue: any): Promise;
countRowsByValue(fieldName: string, fieldValue: any): Promise;
`
The the following methods are used to set the optional created and updated timestamp columns names:
`
setCreatedColumnName(value: string): void;
setUpdatedColumnName(value: string): void;
`
$3
`
Name Type Options
----------- --------------- ---------------------------
id interger non-null, autoincrement, pk
name string non-null
description string nullable
price decimal(10,2) non-null
created string non-null
updated string non-null
`
$3
`
import {Pool} from "mysql";
import {TableGateway} from "../../src/TableGateway";
import { Product } from '../entities/Product';
export class ProductsGateway {
private static readonly TABLE_NAME = 'products';
private readonly _tableGateway: TableGateway;
constructor(connectionPool: Pool) {
this._tableGateway = new TableGateway(connectionPool, ProductsGateway.TABLE_NAME);
this._tableGateway.setCreatedColumnName('created');
this._tableGateway.setUpdatedColumnName('updated');
}
/**
* @returns The inserted Product.
*/
public async createRow(product: Product): Promise {
return this._tableGateway.createRow(product);
}
/**
* @returns Retrieves the row with the given id if found, null otherwise.
*/
public async retrieveRow(id: number): Promise {
return this._tableGateway.retrieveRow(id);
}
/**
* @returns Returns the number of affected rows (0 or 1).
*/
public async updateRow(product: Product): Promise {
return this._tableGateway.updateRow(product);
}
/**
* @returns Returns the number of affected rows (0 or 1).
*/
public async deleteRow(id: number): Promise {
return this._tableGateway.deleteRow(id);
}
/**
* @returns Returns an array of products.
*/
public async retrieveByDescription(description: string): Promise {
return this._tableGateway.retrieveRows('description', description);
}
/**
* @returns Returns an array of Products.
*/
public async retrieveByIds(ids: number[]): Promise {
return this._tableGateway.retrieveRowsByIds(ids);
}
/**
* @returns Returns an array of Products.
*/
public async retrieveByNullDescription(): Promise {
return this._tableGateway.retrieveRowsByIsNull('description');
}
/**
* @returns Returns an array of Products.
*/
public async retrieveByDescriptionNotEqual(description: string): Promise {
return this._tableGateway.retrieveRowsByNotEqual('description', description);
}
/**
* @returns Returns the number of affected rows.
*/
public async setDescriptionNullWhereNameIs(value: string): Promise {
return this._tableGateway.setFieldNullWhere('description', value);
}
/**
* @returns Returns the number of affected rows.
*/
public async deleteWhereNameIs(name: string): Promise {
return this._tableGateway.deleteRowsBy('name', name);
}
/**
* @returns Returns the count.
*/
public async countProductsByName(name: string): Promise {
return this._tableGateway.countRowsByValue('name', name);
}
}
`
It is also possible to forgo data mappers and return the results directly.
$3
The JoinTableGateway is used to manage access to an
associative table that represents a many-to-many relationship.
As a review, a join table must consist of two fields, at a minimum.
Each field is a foreign-key to another table. Optionally, a column
with a timestamp to represent the time created is provided as well.
It is assumed that the combination of the two foreign key fields
is unique.
$3
`
import {Pool} from "mysql";
import {JoinTableGateway} from "../../src/JoinTableGateway";
export class OrdersProductsGateway {
private readonly tableName = 'orders_products';
private readonly id1Name = 'table1_id';
private readonly id2Name = 'table2_id';
private joinTableGateway: JoinTableGateway;
constructor(connectionPool: Pool) {
let table = this.tableName;
let id1 = this.id1Name;
let id2 = this.id2Name;
this.joinTableGateway = new JoinTableGateway(connectionPool, table, id1, id2);
this.joinTableGateway.setCreatedColumnName('created');
}
public createRow(id1: number, id2: number): Promise {
return this.joinTableGateway.createRow(id1, id2);
}
public retrieveRow(id1: number, id2: number): Promise {
return this.joinTableGateway.retrieveRow(id1, id2);
}
public deleteRow(id1: number, id2: number): Promise {
return this.joinTableGateway.deleteRow(id1, id2);
}
public retrieveByTable1Id(id1: number): Promise {
return this.joinTableGateway.retrieveById('table1_id', id1);
}
public deleteByTable1Id(id1: number): Promise {
return this.joinTableGateway.deleteById('table1_id', id1);
}
}
``