Utility types¶

The utility types in ts-sql-query allow you to extract and manipulate type-level information about your database objects. They are especially useful for advanced type inference, type-safe inserts, updates, and dynamic projections.

Columns¶

Column keys¶

This type returns the key names (i.e., property names) of the columns defined in an object such as a table or view definition.

import { ColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerColumns = "id" | "firstName" | "lastName" | "companyId" | "birthday" | "name" | "age"
type tCustomerColumns = ColumnKeys<typeof tCustomer>;

Writable column keys¶

This type returns the key names (i.e., property names) of the columns defined in an object such as a table or view definition that can be used in an insert or update. This type is analogous to ColumnKeys but ignoring computed and virtual columns.

import { WritableColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerWritableColumns = "id" | "firstName" | "lastName" | "companyId" | "birthday"
type tCustomerWritableColumns = WritableColumnKeys<typeof tCustomer>;

Writable shape¶

This type returns the insert or update shape where the property names in the value object and the mapped column names are the same, like {property: 'property'}.

import { WritableShapeFor } from 'ts-sql-query/extras/types';

type WritableCompanyShape = WritableShapeFor<typeof tCompany>;

Id column keys¶

This type returns the key names (i.e., property names) of the id / primary keys columns defined in an object such as a table or view definition. This type is analogous to ColumnKeys but returning primary key columns.

import { IdColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerColumns = "id"
type tCustomerIdColumns = IdColumnKeys<typeof tCustomer>;

Autogenerated id column keys¶

This type returns the key names (i.e., property names) of the id / primary keys columns marked as autogenerated defined in an object such as a table or view definition. This type is analogous to ColumnKeys but returning autogenerated primary key columns.

import { AutogeneratedIdColumnKeys } from 'ts-sql-query/extras/types';

// Alias to type tCustomerAutogeneratedIdColumns = "id"
type tCustomerAutogeneratedIdColumns = AutogeneratedIdColumnKeys<typeof tCustomer>;

Provided id column keys¶

This type returns the key names (i.e., property names) of the id / primary keys columns not marked as autogenerated (the value must be provided) defined in an object such as a table or view definition. This type is analogous to ColumnKeys but returning non-autogenerated primary key columns.

import { ProvidedIdColumnKeys } from 'ts-sql-query/extras/types';

// In case tCustomer were not using an autogenerated primary key this will be an alias to type tCustomerAutogeneratedIdColumns = "id"
type tCustomerProvidedIdColumns = ProvidedIdColumnKeys<typeof tCustomer>;

Insert¶

Insertable values¶

This type returns the object type with the values that can be used in an insert for the provided table.

import { InsertableValues } from 'ts-sql-query/extras/types';

type InsertableCompany = InsertableValues<typeof tCompany>;

If you use a shape in your query you must use:

import { InsertableValuesShapedAs } from 'ts-sql-query/extras/types';

const myCustomerShape = {
    newFirstName: 'firstName',
    newLastName: 'lastName'
}

type InsertableShapedCompany = InsertableValuesShapedAs<typeof tCustomer, typeof myCustomerShape>;

Insertable row¶

This type returns the same type expected by the set clause in an insert.

Note

The difference with InsertableValues is this type admits valid SQL objects that you can use in the insert sentence, where InsertableValues does not.

import { InsertableRow } from 'ts-sql-query/extras/types';

type InserableCompanyRow = InsertableRow<typeof tCompany>;

If you use a shape in your query you must use:

import { InsertableRowShapedAs } from 'ts-sql-query/extras/types';

const myCustomerShape = {
    newFirstName: 'firstName',
    newLastName: 'lastName'
}

type InserableShapedCompanyRow = InsertableRowShapedAs<typeof tCustomer, typeof myCustomerShape>;

Updatable values in case of conflict on insert¶

This type returns the object type with the values that can be used in an insert's onConflictDoUpdateSet for the provided table.

import { UpdatableOnInsertConflictValues } from 'ts-sql-query/extras/types';

type UpdatableCompanyOnInsertConflict = UpdatableOnInsertConflictValues<typeof tCompany>;

If you use a shape in your query you must use:

import { UpdatableOnInsertConflictValuesShapedAs } from 'ts-sql-query/extras/types';

const myCustomerShape = {
    newFirstName: 'firstName',
    newLastName: 'lastName'
}

type UpdatableShapedCompanyOnInsertConflict = UpdatableOnInsertConflictValuesShapedAs<typeof tCustomer, typeof myCustomerShape>;

Updatable row in case of conflict on insert¶

This type returns the same type expected by the onConflictDoUpdateSet clause in an insert.

Note

The difference with UpdatableOnInsertConflictValues is this type admits valid SQL objects that you can use in the onConflictDoUpdateSet sentence, where UpdatableOnInsertConflictValues does not.

import { UpdatableOnInsertConflictRow } from 'ts-sql-query/extras/types';

type UpdatableCompanyOnInsertConflictRow = UpdatableOnInsertConflictRow<typeof tCompany>;

If you use a shape in your query you must use:

import { UpdatableOnInsertConflictRowShapedAs } from 'ts-sql-query/extras/types';

const myCustomerShape = {
    newFirstName: 'firstName',
    newLastName: 'lastName'
}

type UpdatableShapedCompanyOnInsertConflictRow = UpdatableOnInsertConflictRowShapedAs<typeof tCustomer, typeof myCustomerShape>;

Update¶

Updatable values¶

This type returns the object type with the values that can be used in an update for the provided table.

import { UpdatableValues } from 'ts-sql-query/extras/types';

type UpdatableCompany = UpdatableValues<typeof tCompany>;

If you use a shape in your query you must use:

import { UpdatableValuesShapedAs } from 'ts-sql-query/extras/types';

const myCustomerShape = {
    newFirstName: 'firstName',
    newLastName: 'lastName'
}

type UpdatableShapedCustomer = UpdatableValuesShapedAs<typeof tCustomer, typeof myCustomerShape>;

Updatable row¶

This type returns the same type expected by the set clause in an update.

Note

The difference with UpdatableValues is this type admits valid SQL objects that you can use in the update sentence, where UpdatableValues does not.

import { UpdatableRow } from 'ts-sql-query/extras/types';

type UpdatableCompanyRow = UpdatableRow<typeof tCompany>;

If you use a shape in your query you must use:

import { UpdatableRowShapedAs } from 'ts-sql-query/extras/types';

const myCustomerShape = {
    newFirstName: 'firstName',
    newLastName: 'lastName'
}

type UpdatableShapedCustomerRow = UpdatableRowShapedAs<typeof tCustomer, typeof myCustomerShape>;

Select¶

Selected values¶

This type returns the result type of a select on a table or view where all its columns are included.

import { SelectedValues } from 'ts-sql-query/extras/types';

type Company = SelectedValues<typeof tCompany>;

Note

If you query project optional values in objects as always-required properties, use SelectedValuesProjectedAsNullable instead.

Selected row¶

This type returns the result type of a select on a table or view where all its columns are included.

Note

This is just an alias to SelectedValues for coherence and compatibility reasons.

import { SelectedRow } from 'ts-sql-query/extras/types';

type CompanyRow = SelectedRow<typeof tCompany>;

Note

If you query project optional values in objects as always-required properties, use SelectedRowProjectedAsNullable instead.