Mapping¶
Mapping the tables¶
In order to use the tables in queries, you need to map it in your system. To do it, you need to extend the table class that receives two generic arguments, the first one is the connection class, and the second one is a unique name for the table in your system.
import { Table } from "ts-sql-query/Table";
const tCompany = new class TCompany extends Table<DBConnection, 'TCompany'> {
id = this.autogeneratedPrimaryKey('id', 'int');
name = this.column('name', 'string');
parentId = this.optionalColumn('parent_id', 'int');
ubicationLatitude = this.optionalColumn('ubication_latitude', 'string');
ubicationLongitude = this.optionalColumn('ubication_longitude', 'string');
ubicationComment = this.optionalColumn('ubication_comment', 'string');
constructor() {
super('company'); // table name in the database
}
}();
Warning
The constructor of a table must expect no arguments.
Mapping the views¶
In order to use the views in queries, you need to map it in your system. To do it, you need to extend the view class that receives two generic arguments, the first one is the connection class, and the second one is a unique name for the view in your system.
import { View } from "ts-sql-query/View";
const vCustomerAndCompany = new class VCustomerAndCompany extends View<DBConnection, 'VCustomerAndCompany'> {
companyId = this.column('company_id', 'int');
companyName = this.column('company_name', 'string');
customerId = this.column('customer_id', 'int');
customerFirstName = this.column('customer_first_name', 'string');
customerLastName = this.column('customer_last_name', 'string');
customerBirthday = this.optionalColumn('customer_birthday', 'localDate');
constructor() {
super('customer_company'); // view name in the database
}
}();
Warning
The constructor of a view must expect no arguments.
Mapping constant values as view¶
You can use a set of data a a view made of constant values that will be executed in a query. To do it, you need to extend the Values class that receives two generic arguments, the first one is the connection class, and the second one is a unique name for the view in your query.
import { Values } from "ts-sql-query/View";
async function yourBusinessLogic() {
class VCustomerForUpdate extends Values<DBConnection, 'customerForUpdate'> {
id = this.column('int')
firstName = this.column('string')
lastName = this.column('string')
}
const customerForUpdate = Values.create(VCustomerForUpdate, 'customerForUpdate', [
// Include your constant data here, it can be several records but must contains at least one.
{
id: 1,
firstName: 'First Name',
lastName: 'Last Name'
}
])
// Use customerForUpdate as a view in your query
}
Warning
- Do not provide or implement a constructor
- The Values class is similar to the View class, with the difference the functions that allows to create the columns doesn't receive the column name.
- Always create it using
Values.create - This is only supported by PostgreSQL, SQL Server and SQLite
- In PostgreSQL the values included in the query will be forced to generate the type cast; ensure your custom types have a valid type cast. See Column types
Virtual columns¶
You can create virtual columns in a table or view, where the column doesn't exist in the database, but it is expanded during the query construction. You can create a property in your table or view assigning any computation over an already existing column to create a direct virtual column; or, you can call the optionalVirtualColumnFromFragment method to create a column from a SQL fragment.
import { Table } from "ts-sql-query/Table";
const tCustomer = new class TCustomer extends Table<DBConnection, 'TCustomer'> {
id = this.autogeneratedPrimaryKey('id', 'int');
firstName = this.column('first_name', 'string');
lastName = this.column('last_name', 'string');
birthday = this.optionalColumn('birthday', 'localDate');
companyId = this.column('company_id', 'int');
// Direct virtual column
name = this.firstName.concat(' ').concat(this.lastName);
// Virtual column from a sql fragment
age = this.optionalVirtualColumnFromFragment('int', (fragment) => {
return fragment.sql`calculateAge(${this.birthday})`
});
constructor() {
super('customer'); // table name in the database
}
}();
Tip
You can use a virtual column from a SQL fragment to call a function that returns a value, like in this example.
When you use in a query, the virtual column definition is expanded in the generated SQL.
const customersWithAge = connection.selectFrom(tCustomer)
.select({
id: tCustomer.id,
name: tCustomer.name,
age: tCustomer.age,
})
.executeSelectMany();
The executed query is:
The parameters are: [ " " ]
The result type is:
Creating methods that allows to call a procedure¶
import { PostgreSqlConnection } from "ts-sql-query/connections/PostgreSqlConnection";
class DBConnection extends PostgreSqlConnection<'DBConnection'> {
myOwnProcedure(param1: number) {
return this.executeProcedure('myOwnprocedure', [this.const(param1, 'int')]);
}
}
Executing the procedure:
The executed query is:
The parameters are: [ 10 ]
The result type is a promise:
Creating methods that allows to call a function¶
import { PostgreSqlConnection } from "ts-sql-query/connections/PostgreSqlConnection";
class DBConnection extends PostgreSqlConnection<'DBConnection'> {
myOwnFunction(param1: number) {
return this.executeFunction('myOwnFunction', [this.const(param1, 'int')], 'int', 'required');
}
}
Executing the function:
The executed query is:
The parameters are: [ 10 ]
The result type is a promise with the result returned by the function: