Getting Started

This guide will walk you through using the ORM for a simple two-object app.

Prerequisites

node and npm (verified compatibility between Node versions 14.x to 18.x)

Installing

npm i --save ts-pg-orm @samhuk/data-filter @samhuk/data-query simple-pg-client

Configuration

This section will walk you through setting up an ORM instance.

Defining Objects

We define what our objects are via Data Formats. For our app, we will want to create two Data Formats: "user" and "article".

Our User Data Format can be defined like so:

import { createDataFormat, DataType, EpochSubType, NumSubType, StrSubType } from 'ts-pg-orm'

const BASE_FIELDS = createCommonFields({
  id: { type: DataType.NUM, subType: NumSubType.SERIAL },
  uuid: { type: DataType.STR, subType: StrSubType.UUID_V4, autoGenerate: true },
  dateCreated: { type: DataType.EPOCH, subType: EpochSubType.DATE_TIME_WITH_TIMEZONE, defaultToCurrentEpoch: true },
  dateDeleted: { type: DataType.EPOCH, subType: EpochSubType.DATE_TIME_WITH_TIMEZONE, allowNull: true, excludeFromCreateOptions: true },
})

const USER_DF= createDataFormat('user', {
  ...BASE_FIELDS,
  name: { type: DataType.STR, subType: StrSubType.VAR_LENGTH, maxLen: 50 },
  email: { type: DataType.STR, subType: StrSubType.VAR_LENGTH, maxLen: 100 },
  passwordHash: { type: DataType.STR, subType: StrSubType.FIXED_LENGTH, len: 64 },
})

Our Article Data Format can be defined like so:

const ARTICLE_DF = createDataFormat('article', {
  ...BASE_FIELDS,
  title: { type: DataType.STR, subType: StrSubType.VAR_LENGTH, maxLen: 100 },
  creatorUserId: { type: DataType.NUM, subType: NumSubType.INT },
})

Creating the ORM Instance

We need to load in our Data Formats and define any Relations between them. In our case, they have one relation: Each Article relates to a User by the creatorUserId field. We achieve this like so:

import { createTsPgOrm, RelationType } from 'ts-pg-orm'

const ORM = const ORM = createTsPgOrm([USER_DF, ARTICLE_DF] as const)
  .setRelations([
    {
      type: RelationType.ONE_TO_MANY,
      fromOneField: USER_DF.fieldRefs.id,
      toManyField: ARTICLE_DF.fieldRefs.creatorUserId,
    },
  ] as const)

Here we have created the ORM instance, providing it our two Data Formats and a single "one-to-many" relation from a User to an Article. This is because an Article can only be created by one user, and a User can create many Articles.

Connecting and Provisioning

Before using the ORM instance, it needs to connect to a PostgreSQL database and provision tables for data stores. ts-pg-orm uses simple-pg-client to achieve this. This can be achieved like so (in this example we will use async/await):

import { createConsoleLogEventHandlers } from 'simple-pg-client'

const provisionOrm = async () => {
  const orm = await ORM.connect({
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'postgres',
    db: 'ts-pg-orm-test',
    createDbIfNotExists: true,
    extensions: ['uuid-ossp'],
    events: {
      ...createConsoleLogEventHandlers(),
    },
  })
  await orm.unprovisionStores()
  await orm.provisionStores()
  return orm // Asynchronously return a connected ORM
}

This will use simple-pg-client to connect to the defined PostgreSQL server, create the database if it doesn't already exist, and then create the required tables for the data stores.

Using Data Stores

With the ORM instance created and a connected and provisioned orm created, we can start performing fully auto-completing CRUD operations on data stores.

For more in-depth details about querying, see Querying.

Creating Records

const user1 = await orm.stores.user.create({
  name: 'User 1',
  email: 'user1@email.com',
  passwordHash: '123',
})
const article1 = await orm.stores.article.create({
  title: 'Article 1',
  creatorUserId: user1.id,
  body: 'lorum ipsum',
})

Getting Records

import { Operator } from '@samhuk/data-filter/dist/types'
import { SortingDirection } from '@samhuk/data-query/dist/sorting/types'

const userWithTheirArticles = await orm.stores.user.get({
  fields: ['uuid', 'name', 'email', 'dateCreated'],
  filter: { field: 'dateDeleted', op: Operator.EQUALS, val: null },
  relations: {
    articles: {
      query: {
        filter: { field: 'dateDeleted', op: Operator.EQUALS, val: null },
        page: 1,
        pageSize: 10,
        sorting: [{ field: 'dateCreated', dir: SortingDirection.DESC }],
      },
      fields: ['uuid', 'dateCreated', 'title'],
    },
  },
})

Updating Records

const updatedUser = await orm.stores.user.update({
  record: { name: 'not User 1 anymore' },
  query: {
    filter: { field: 'id', op: Operator.EQUALS, val: 1 },
  },
  return: 'first',
})

Deleting Records

const deletedUser = await orm.stores.user.delete({
  query: {
    filter: { field: 'id', op: Operator.EQUALS, val: 1 },
  },
  return: 'first',
})

Creating Types

We can define useful types from our Data Formats using a collection of generic types provided by ts-pg-orm like so:

import { CreateRecordOptions, ToRecord, ToStores } from 'ts-pg-orm/dist/dataFormat/types/createRecordOptions'

type Stores = ToStores <typeof ORM>
type UserRecord = ToRecord<typeof USER_DF>
/*
type UserRecord = {
    dateDeleted?: string;
    id: number;
    uuid: string;
    dateCreated: string;
    name: string;
    email: string;
    passwordHash: string;
}
*/
type CreateUserRecordOptions = CreateRecordOptions<typeof USER_DF>
type ArticleRecord = ToRecord<typeof ARTICLE_DF>
type CreateArticleRecordOptions = CreateRecordOptions<typeof ARTICLE_DF>