Associations

import { Prorm, DataTypes } from 'ts-prorm-orm';

const prorm = new Prorm({ dialect: 'sqlite', storage: ':memory:' });

const User = prorm.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  name: { type: DataTypes.STRING() },
});

const Post = prorm.define('Post', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  title: { type: DataTypes.STRING() },
  userId: { type: DataTypes.INTEGER() }, // foreign key column
});

User.hasMany(Post);   // a user has many posts
Post.belongsTo(User); // a post belongs to a user

await prorm.connect();
await prorm.sync();

Post.belongsTo(User, { foreignKey: 'userId', as: 'author' });

const article = await Post.findByPk(1);

const author = await article.getAuthor();            // SELECT * FROM "users" WHERE "id" = 1 LIMIT 1
await article.setAuthor(user);                       // sets article.userId, saves
await article.unsetAuthor();                         // clears the foreign key
const created = await article.createAuthor({ name: 'Ada' }); // create + link
const has = await article.hasAuthor();               // boolean
const n = await article.countAuthor();               // 0 or 1

const Profile = prorm.define('Profile', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  bio: DataTypes.STRING,
  userId: DataTypes.INTEGER,
});

User.hasOne(Profile, { foreignKey: 'userId' });

const ada = await User.findByPk(1);

const profile = await ada.getProfile();          // findOne on Profile
await ada.setProfile(profile);                    // updates Profile.userId
const created = await ada.createProfile({ bio: 'hi' });
const has = await ada.hasProfile();               // boolean
const n = await ada.countProfile();               // 0 or 1

User.hasMany(Post, { foreignKey: 'userId', as: 'posts' });

const ada = await User.findByPk(1);

const posts = await ada.getPosts();                 // findAll on Post
await ada.setPosts([postA, postB]);                 // replaces the whole set
await ada.addPosts([postC, postD]);                 // links additional rows
await ada.addPost(postE);                           // singular form
const post = await ada.createPost({ title: 'Hi' }); // create + link
const count = await ada.countPosts();
const any = await ada.hasPosts();                   // any linked?
const both = await ada.hasPosts([postC, postD]);    // all linked?
const one = await ada.hasPost(postE);               // singular form

await Post.update({ userId: null }, { where: { id: postA.id, userId: ada.id } });

const Role = prorm.define('Role', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  name: DataTypes.STRING,
});

User.belongsToMany(Role, {
  through: 'UserRoles',   // junction table name (or a model)
  foreignKey: 'userId',   // column on the junction pointing at User
  otherKey: 'roleId',     // column on the junction pointing at Role
});

const ada = await User.findByPk(1);

const roles = await ada.getRoles();               // two queries, via the junction
await ada.setRoles([roleA, roleB]);               // replace all links
await ada.addRoles([roleC]);                      // insert junction rows
await ada.addRole(roleD);                         // singular form
await ada.removeRole(roleB);                      // delete one junction row
const count = await ada.countRoles();             // counts junction rows
const one = await ada.hasRole(roleC);

export async function grantRole(user: any, role: any) {
  if (await ada.hasRole(role)) return false; // idempotent: already granted
  await ada.addRole(role);
  return true;
}

// 1. A table name — a junction model is generated for you.
User.belongsToMany(Role, { through: 'UserRoles' });

// 2. A model you defined yourself (extra columns allowed).
const UserRole = prorm.define('UserRole', {
  userId: DataTypes.INTEGER,
  roleId: DataTypes.INTEGER,
  grantedAt: DataTypes.DATE,
});
User.belongsToMany(Role, { through: UserRole });

// 3. A ThroughOptions object.
User.belongsToMany(Role, {
  through: { model: UserRole, otherKey: 'roleId' },
});

const users = await User.findAll({
  include: [{ model: Post }],
});

for (const user of users) {
  console.log(user.name, user.posts); // Post[] under the `posts` alias
}

{"id":1,"title":"One","published":true,"userId":1,"author":{"id":1,"name":"Ada"}}

const users = await User.findAll({
  include: [
    {
      model: Post,
      as: 'posts',
      where: { published: true },        // filter associated rows
      attributes: ['id', 'title', 'userId'], // select a subset of columns
      order: [['createdAt', 'DESC']],    // order the associated rows
    },
  ],
});

const users = await User.findAll({
  include: [
    {
      model: Post,
      include: [{ model: Comment }],   // user -> posts -> comments
    },
  ],
});

const users = await User.findAll({
  include: [
    {
      model: Role,
      where: { name: 'admin' },
    },
  ],
});

Post.belongsTo(User, {
  foreignKey: 'userId',
  targetKey: 'id',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
});

OrderItem.belongsTo(Order, {
  foreignKey: ['orderId', 'customerId'],
  targetKey: ['id', 'customerId'],
});

Post.belongsTo(User, {
  foreignKey: { name: 'userId', allowNull: false },
});

const Product = prorm.define('Product', {
  sku: { type: DataTypes.STRING, primaryKey: true },
  name: DataTypes.STRING,
});

Product.hasMany(Review, { foreignKey: 'productSku' });