Models

Model

---

Creating your first model

---

The boilerplate code for a model looks like this

import { Model, column, primaryKey } from "@lib/Model";
export default class Users extends Model {
    @column // Sets the id property to a column
    @primaryKey // Sets this column to a primary key
    public id: number;
    @column
    public name: string;

    tableName = "users"; // IMPORTANT: tablename must be set

    constructor(id?: number, name?: string) {
        super();
        this.id = id;
        this.name = name;
    }
}

When defining a model it's important to define the table columns with the decorator @column which is imported from the model file. You also need to set the primary key decorator which for at least one column but you can set it for multiple columns. The primary key decorator is imported from the model file and looks like this @primaryKey. The primary key is used when saving and updating the database.

CRUD

---

For all these examples I am gonna use the model shown in the example above

CREATE

If you wish to create a new model simply create a new object of the the class that extends the Model base class and assign properties and then run save. Example below

const user = new Users(); // User is an class that extends the base class Model
user.name = "A name"; // Setting a property
await user.save(); // .save() returns a promise

// You can assign the property in whatever way you want as long as they are assigned before running .save()
// Properties not assigned will not be included in the INSERT statement and will default to what is declared in the SQL database

// You can assign the properties in an constructor like this
const user = new User(undefined, "A name");
await user.save();

READ

When you wanna fetch a row from the database after a filter you do the following.

// Fetching many rows
const filter: Users = new Users();
filter.name = "Hugo";
const rows: Array<Users> = Users.getManyRowsByFilter<Users>(filter);

// You should generally only fetch many if you believe that the result from the database will otherwise use getSingleRowByFilter because it is faster.
const filter: Users = new Users();
filter.id = 1; // The id is the primary key so only one row will have that id
const user: Users = Users.getSingleRowByFilter<Users>(filter);

UPDATE

To update a row combine read capabilities and save capabilities.

Example:

const filter: Users = new Users();
filter.id = 1;
const user: Users = Users.getSingleRowByFilter<Users>(filter);
user.name = "John Doe";
await user.save();

Relationships

---

You have three types of relationships you can utilize, below I will explain them in detail and below that an example will be shown.

oneToMany()

One to many is used when the model is has relates to many other models, the other model will have a column that relates to this model called a foreign key.

oneToOne()

One to one is used when the model relates to one other model where two columns share the same value.

manyToMany()

Many to many is an relationships where many models relate to many models. For example a person have many cars but cars can have many owners (person). This kind of relationship is a bit more complicated and involve a junction table. MORE INFO COMING

---

The structure for relationships is that every model has an public get function that relates to the related model. So for an example I am going to demonstrate how you could use oneToMany relationships and oneToOne relationship when you have an User model that have an relationship with Messages. Messages has the forreign key userId that related to the User model primary key id.

The user model can then have the following get function to link them together, using oneToMany relationship

public get messages(): Promise<Array<Message>> {
    return this.oneToMany<Message>(Message, "id", "userId");
}

// This you can later get messages if you have an user object as follows
const messages = await user.messages;

The Message model can get their related user by using the oneToOne relationships in the following way

public get user(): Promise<User> {
    return this.oneToOne<User>(User, "userId", "id");
}

// This you can later get messages if you have an user object as follows
const messages = await user.messages;

Sync models with DB - BETA

PyramidJS comes with a script designed to sync the models you have created in PyrmidJS with your database. After creating some models you can run

ts-node SyncModelsWithDB.ts  

Note you need to have ts-node installed

You will be prompted with the querys needed to be run to sync your models with db. If you choose to execute the querys the script will execute the querys for you.

If you wish to use this feature you need to add the decorator @additionalProperties to all columns, here is an example

@additionalProperties({
    type: "INT(8)",
    notNull: true,
    autoIncrement: true,
})
public id: number;

You need to set the type property, here you could also add additional SQL column params. The notNull property and autoIncrement are optional.