Models

What is a Model?

A Model is a class that maps to a database table (or collection when using MongoDB). You can create a Model by defining a new class and marking it with @Model():

import { Model, PrimaryColumn, Column } from "alapa";

@Model()
export class User extends Model {
  @PrimaryColumn()
  id: number;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  isActive: boolean;
}

This will create the following database table:

+-------------+--------------+----------------------------+
|                          user                           |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| firstName   | varchar(255) |                            |
| lastName    | varchar(255) |                            |
| isActive    | boolean      |                            |
+-------------+--------------+----------------------------+

Basic Models consist of columns and relations. Each Model MUST have a primary column (or ObjectId column if using MongoDB).

Each Model must be registered in your data source options:

import { DataSource } from "alapa";
import { User } from "./model/User";

const myDataSource = new DataSource({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "test",
  password: "test",
  database: "test",
  models: [User],
});

Or you can specify the whole directory with all Models inside - and all of them will be loaded:

import { DataSource } from "alapa";

const dataSource = new DataSource({
  type: "mysql",
  host: "localhost",
  port: 3306,
  username: "test",
  password: "test",
  database: "test",
  models: ["model/*.js"],
});

If you want to use an alternative table name for the User Model, you can specify it in @Model: @Model("my_users"). If you want to set a base prefix for all database tables in your application, you can specify modelPrefix in data source options.

When using a Model constructor, its arguments must be optional. Since ORM creates instances of Model classes when loading from the database, it is not aware of your constructor arguments.

Learn more about parameters @Model in Decorators reference.

Model columns

Since database tables consist of columns, your Models must consist of columns too. Each Model class property you mark with @Column will be mapped to a database table column.

Primary columns

Each Model must have at least one primary column. There are several types of primary columns:

@PrimaryColumn() creates a primary column that takes any value of any type. You can specify the column type. If you don't specify a column type, it will be inferred from the property type. The example below will create id with int as the type, which you must manually assign before saving:

import { Model, bTableModel, PrimaryColumn } from "alapa";

@TableModel()
export class User extends Model {
  @PrimaryColumn()
  id: number;
}

@PrimaryColumn() creates a primary column whose value will be automatically generated with an auto-increment value. It will create an int column with auto-increment/serial/sequence/identity (depending on the database and configuration provided). You don't have to manually assign its value before saving - the value will be automatically generated:

import { Model, PrimaryColumn, TableModel } from "alapa";

@TableModel()
export class User extends Model {
  @PrimaryColumn()
  id: number;
}

@PrimaryColumn("uuid") creates a primary column whose value will be automatically generated with uuid. Uuid is a unique string id. You don't have to manually assign its value before saving - the value will be automatically generated:

import { Model, PrimaryColumn, TableModel } from "alapa";

@TableModel()
export class User extends Model {
  @PrimaryColumn("uuid")
  id: string;
}

You can have composite primary columns as well:

import { Model, PrimaryColumn } from "alapa";

@Model()
export class User {
  @PrimaryColumn()
  firstName: string;

  @PrimaryColumn()
  lastName: string;
}

When you save Models using save, it always tries to find a record in the database with the given Model id (or ids). If id/ids are found, it will update this row in the database. If there is no row with the id/ids, a new row will be inserted.

To find a Model by id, you can use manager.findOneBy or repository.findOneBy. Example:

// Find one by id with a single primary key
const person = await dataSource.manager.findOneBy(Person, { id: 1 });
const person = await dataSource.getRepository(Person).findOneBy({ id: 1 });

// Find one by id with composite primary keys
const user = await dataSource.manager.findOneBy(User, {
  firstName: "Timber",
  lastName: "Saw",
});
const user = await dataSource.getRepository(User).findOneBy({
  firstName: "Timber",
  lastName: "Saw",
});

Special columns

There are several special column types with additional functionality available:

@CreateDateColumn is a special column that is automatically set to the Model's insertion date. You don't need to set this column - it will be automatically set.
@UpdateDateColumn is a special column that is automatically set to the Model's update time each time you call save on the manager or repository. You don't need to set this column - it will be automatically set.
@DeleteDateColumn is a special column that is automatically set to the Model's delete time each time you call soft-delete on the manager or repository. You don't need to set this column - it will be automatically set. If the @DeleteDateColumn is set, the default scope will be "non-deleted".
@VersionColumn is a special column that is automatically set to the version of the Model (incremental number) each time you call save on the manager or repository. You don't need to set this column - it will be automatically set.

Spatial columns

MS SQL, MySQL, MariaDB, PostgreSQL, and CockroachDB all support spatial columns. Alapa's support for each varies slightly between databases, particularly as the column names vary between databases.

For full implementation examples and other column type details, refer to the official documentation.

Note: String, numeric and heterogeneous enums are supported.

Using array with enum values:

export type UserRoleType = "admin" | "editor" | "ghost",

@Model
export class User {

    @PrimaryColumn()
    id: number;

    @Column({
        type: "enum",
        enum: ["admin", "editor", "ghost"],
        default: "ghost"
    })
    role: UserRoleType
}

`set` column type

set column type is supported by mariadb and mysql. There are various possible column definitions:

Using typescript enums:

export enum UserRole {
  ADMIN = "admin",
  EDITOR = "editor",
  GHOST = "ghost",
}

@Model
export class User {
  @PrimaryColumn()
  id: number;

  @Column({
    type: "set",
    enum: UserRole,
    default: [UserRole.GHOST, UserRole.EDITOR],
  })
  roles: UserRole[];
}

Using array with set values:

export type UserRoleType = "admin" | "editor" | "ghost",

@Model
export class User {

    @PrimaryColumn()
    id: number;

    @Column({
        type: "set",
        enum: ["admin", "editor", "ghost"],
        default: ["ghost", "editor"]
    })
    roles: UserRoleType[]
}

`simple-array` column type

There is a special column type called simple-array which can store primitive array values in a single string column. All values are separated by a comma. For example:

@Model
export class User {
  @PrimaryColumn()
  id: number;

  @Column("simple-array")
  names: string[];
}

const user = new User();
user.names = ["Alexander", "Alex", "Sasha", "Shurik"];

Will be stored in a single database column as Alexander,Alex,Sasha,Shurik value. When you'll load data from the database, the names will be returned as an array of names, just like you stored them.

Note you MUST NOT have any comma in values you write.

`simple-json` column type

There is a special column type called simple-json which can store any values which can be stored in database via JSON.stringify. Very useful when you do not have json type in your database and you want to store and load object without any hassle. For example:

@Model
export class User {
  @PrimaryColumn()
  id: number;

  @Column("simple-json")
  profile: { name: string; nickname: string };
}

const user = new User();
user.profile = { name: "John", nickname: "Malkovich" };

Will be stored in a single database column as {"name":"John","nickname":"Malkovich"} value. When you'll load data from the database, you will have your object/array/primitive back via JSON.parse

Columns with generated values

You can create column with generated value using @Generated decorator. For example:

@Model
export class User {
  @PrimaryColumn()
  id: number;

  @Column()
  @Generated("uuid")
  uuid: string;
}

uuid value will be automatically generated and stored into the database.

Besides "uuid" there is also "increment", "identity" (Postgres 10+ only) and "rowid" (CockroachDB only) generated types, however there are some limitations on some database platforms with this type of generation (for example some databases can only have one increment column, or some of them require increment to be a primary key).

Column options

Column options defines additional options for your entity columns. You can specify column options on @Column:

@Column({
    type: "varchar",
    length: 150,
    unique: true,
    // ...
})
name: string;

List of available options in ColumnOptions:

type: ColumnType - Column type. One of the type listed above.
name: string - Column name in the database table. By default the column name is generated from the name of the property. You can change it by specifying your own name.
length: number - Column type's length. For example if you want to create varchar(150) type you specify column type and length options.
width: number - column type's display width. Used only for MySQL integer types
onUpdate: string - ON UPDATE trigger. Used only in MySQL.
nullable: boolean - Makes column NULL or NOT NULL in the database. By default column is nullable: false.
update: boolean - Indicates if column value is updated by "save" operation. If false, you'll be able to write this value only when you first time insert the object. Default value is true.
insert: boolean - Indicates if column value is set the first time you insert the object. Default value is true.
select: boolean - Defines whether or not to hide this column by default when making queries. When set to false, the column data will not show with a standard query. By default column is select: true
default: string - Adds database-level column's DEFAULT value.
primary: boolean - Marks column as primary. Same if you use @PrimaryColumn.
unique: boolean - Marks column as unique column (creates unique constraint).
comment: string - Database's column comment. Not supported by all database types.
precision: number - The precision for a decimal (exact numeric) column (applies only for decimal column), which is the maximum number of digits that are stored for the values. Used in some column types.
scale: number - The scale for a decimal (exact numeric) column (applies only for decimal column), which represents the number of digits to the right of the decimal point and must not be greater than precision. Used in some column types.
zerofill: boolean - Puts ZEROFILL attribute on to a numeric column. Used only in MySQL. If true, MySQL automatically adds the UNSIGNED attribute to this column.
unsigned: boolean - Puts UNSIGNED attribute on to a numeric column. Used only in MySQL.
charset: string - Defines a column character set. Not supported by all database types.
collation: string - Defines a column collation.
enum: string[]|AnyEnum - Used in enum column type to specify list of allowed enum values. You can specify array of values or specify a enum class.
enumName: string - Defines the name for the used enum.
asExpression: string - Generated column expression. Used only in MySQL.
generatedType: "VIRTUAL"|"STORED" - Generated column type. Used only in MySQL.
hstoreType: "object"|"string" - Return type of HSTORE column. Returns value as string or as object. Used only in Postgres.
array: boolean - Used for postgres and cockroachdb column types which can be array (for example int[])
transformer: { from(value: DatabaseType): EntityType, to(value: EntityType): DatabaseType } - Used to marshal properties of arbitrary type EntityType into a type DatabaseType supported by the database. Array of transformers are also supported and will be applied in natural order when writing, and in reverse order when reading. e.g. [lowercase, encrypt] will first lowercase the string then encrypt it when writing, and will decrypt then do nothing when reading.

Note: most of those column options are RDBMS-specific and aren't available in MongoDB.

Entity inheritance

You can reduce duplication in your code by using entity inheritance.

For example, you have Photo, Question, Post entities:

@Model
export class Photo {
  @PrimaryColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  description: string;

  @Column()
  size: string;
}

@Model
export class Question {
  @PrimaryColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  description: string;

  @Column()
  answersCount: number;
}

@TableModel()
export class Poste extends Model {
  @PrimaryColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  description: string;

  @Column()
  viewCount: number;
}

As you can see all those entities have common columns: id, title, description. To reduce duplication and produce a better abstraction we can create a base class called Content for them:

export abstract class Content extends Model {
  @PrimaryColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  description: string;
}

@TableModel()
export class Photo extends Content {
  @Column()
  size: string;
}

@TableModel()
export class Question extends Content {
  @Column()
  answersCount: number;
}

@TableModel()
export class Post extends Content {
  @Column()
  viewCount: number;
}

All columns (relations, embeds, etc.) from parent entities (parent can extend other entity as well) will be inherited and created in final entities.

Tree entities

alapa supports the Adjacency list and Closure table patterns of storing tree structures.

Adjacency list

Adjacency list is a simple model with self-referencing. Benefit of this approach is simplicity, drawback is you can't load a big tree at once because of join limitations. Example:

import { Entity, Column, PrimaryColumn, ManyToOne, OneToMany } from "alapa";

@TableModel()
export class Category extends Model {
  @PrimaryColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  description: string;

  @ManyToOne((type) => Category, (category) => category.children)
  parent: Category;

  @OneToMany((type) => Category, (category) => category.parent)
  children: Category[];
}

Closure table

A closure table stores relations between parent and child in a separate table in a special way. Its efficient in both reads and writes. To learn more about closure table take a look at this awesome presentation by Bill Karwin. Example:

import {
  Model,
  Tree,
  Column,
  PrimaryColumn,
  TreeChildren,
  TreeParent,
  TreeLevelColumn,
} from "alapa";

@TableModel()
@Tree("closure-table")
export class Category {
  @PrimaryColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  description: string;

  @TreeChildren()
  children: Category[];

  @TreeParent()
  parent: Category;

  @TreeLevelColumn()
  level: number;
}

What is a Model?​

Model columns​

Primary columns​

Special columns​

Spatial columns​

set column type​

simple-array column type​

simple-json column type​

Columns with generated values​

Column options​

Entity inheritance​

Tree entities​

Adjacency list​

Closure table​