RawSQLDataSource

RawSQLDataSource is a TypeScript sql storage that implements GetDataSource<RawSQLData>, PutDataSource<RawSQLData> and DeleteDataSource.

Usage

const dataSource = new RawSQLDataSource(sqlDialect, sqlInterface, 'employee', ['name', 'age', 'department']);
// New object. Field 'id' will be automatically generated by the database (for example)
const rawData : RawSQLData = {
name: 'John Doe',
age: 42,
departmnet: 'Applied Maths',
};
// Creating a new row
const newEntry = await dataSource.put(rawData);
// Retrieving from the database the object
const result = await dataSource.get(new IdQuery(newEntry.id));
// Deleting the object
await dataSource.delete(new IdQuery(newEntry.id));

Note that the example above is assuming the table employee has a column id of type int autoincrement primary key.

SQL Scheme Assumptions

RawSQLDataSource is assuming the following facts:

  • The table has a column id acting as a primary key.
  • The table has a column created_at and updated_at of type date.
  • Optionally, the table has a column deleted_at of type date. This column must exist if we enable the soft delete feature.

Supported Queries

In order to support basic SQL actions as order by, limit, offset and, most important, where, RawSQLDataSource declares the abstract classes:

  • SQLOrderByQuery
  • SQLOrderByPaginationQuery
  • SQLWhereQuery
  • SQLWherePaginationQuery

Subclasses will define the exact configuration of each action.

OrderBy Queries

There are two order-by type of queries: not paginated and paginated ones:

export abstract class SQLOrderByQuery extends Query implements SQLOrderBy {
abstract orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string;
abstract ascending(): boolean;
}
export abstract class SQLOrderByPaginationQuery extends PaginationOffsetLimitQuery implements SQLOrderBy {
abstract orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string;
abstract ascending(): boolean;
}

Using our initial example of table employee, let's say we want to do the following queries:

select id, created_at, name, age, department from employee order by age asc;
select id, created_at, name, age, department from employee order by age asc limit 10 offset 20;

Then, we would create and use a SQLOrderByQuery and SQLOrderByPaginationQuery respectively as follows:

class OrderByAgeQuery extends SQLOrderByQuery {
orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string {
return 'age';
}
ascending(): boolean {
return true;
}
}
export class OrderByAgePaginationQuery extends SQLOrderByPaginationQuery {
orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string {
return 'age';
}
ascending(): boolean {
return true;
}
}
const results = await dataSource.getAll(new OrderByAgeQuery());
const paginatedResults = await dataSource.getAll(new OrderByAgePaginationQuery(20, 10));

Where Query

There are two where type of queries: not paginated and paginated ones.

export abstract class SQLWhereQuery extends SQLOrderByQuery implements SQLWhere {
abstract where(param?: SQLQueryParamFn, dialect?: SQLDialect): string;
orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string {
return BaseColumnCreatedAt;
}
ascending(): boolean {
return false;
}
}
export abstract class SQLWherePaginationQuery extends SQLOrderByPaginationQuery implements SQLWhere {
abstract where(param?: SQLQueryParamFn, dialect?: SQLDialect): string;
orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string {
return BaseColumnCreatedAt;
}
ascending(): boolean {
return false;
}
}

Note that both queries inherit from OrderBy queries, including ordering functionality as well (and by default using the created_at column to order descending).

Following our initial example, lets try to run this SQL query:

select
id, created_at, name, age, department
from employee
where
age > 10 and department like "Applied Maths"
order by age asc
limit 10 offset 20;

In this case, we will create a new query extending SQLWherePaginationQuery as follows:

class AgeAndDepartmentFilterQuery extends SQLWherePaginationQuery {
constructor(
offset: number,
limit: number,
readonly age: number,
readonly department: string,
) {
super(offset, limit);
}
where(param?: SQLQueryParamFn, dialect?: SQLDialect): string {
return `age > ${param(this.age) and department like ${param(this.department}}`;
}
orderBy(param?: SQLQueryParamFn, dialect?: SQLDialect): string {
return 'age';
}
ascending(): boolean {
return true;
}
}
const results = await dataSource.getAll(new AgeAndDepartmentFilterQuery(20, 10, 10, 'Applied Maths'));
Note

Use the param? method parameter to include new parameters into the query. This function will return automatically the appropiate parameter symbol depending on the dialect ('?' for MySQL and '$1', '$2', ... for PostgreSQL).

Additional Features

Customization of select statement

By default, RawSQLDataSource limits the select to the columns specified in the constructor (in addition to the id, created_at and updated_at).

However, if you have a special need where you want to retrieve more information via a join (for example), you can do it by overriding the method selectSQL:

class EmployeeSQLDataSource extends RawSQLDataSource {
protected selectSQL(): string {
return `select * form (select e.*, d.location as department_location from employee e join department d on e.department = e.name) as t`;
}
}

This select statement is the one used in both get and getAll methods. Therefore, your queries (subclasses of SQLOrderByQuery, SQLOrderByPaginationQuery, SQLWhereQuery, SQLWherePaginationQuery) must use the columns namespace used in the selectSQL method.

Idea: use the select * from (...) as t structure to remove table namespace dependencies. This way your where/orderBy statements won't require table prefixes.

Soft Delete

RawSQLDataSource supports soft deletion of rows. To enable it:

  • Have a deleted_at column (with this or any name) of type date in your database.
  • Call the RawSQLDataSource constructor passing the option true in the softDeleteEnabled option, as well as the name of the column used to store delete_at date.

When soft delete is enabled:

  • Both delete and deleteAll calls will result in an update of the affected rows by setting the date of deletion (instead of deleting the row).
  • Both get and getAll will include an extra where statement delete_at is null to ensure we are not retrieving rows that have been deleted.

It is not supported to undo a soft deletion at this moment. To support it, you must subclass RawSQLDataSource and add the feature. See below how to customize your RawSQLDataSource subclass.

Custom Behavior

Very often you will need to run a very specific SQL query, maybe multiple of them within a transaction and more. If this is the case, then you need to Subclass RawSQLDataSource and add explicit query support.

class EmployeeSQLDataSource extends RawSQLDataSource {
async put(value: RawSQLData, query: Query): Promise<RawSQLData> {
if (query instanceof CreateEmployeeQuery) {
// Do your specific SQL implementation
return this.sqlInterface.transaction(db => {
// Do your custom stuff
});
} else {
return super.put(value, query);
}
}
}

Note that in this example, CreateEmployeeQuery can inherit directly form Query.