Models & ORM

1import { initDatabase, closeDatabase } from "canxjs";
2
3// Initialize database connection
4await initDatabase({
5  driver: "mysql",  // or "postgresql"
6  host: "localhost",
7  port: 3306,
8  database: "myapp",
9  username: "root",
10  password: "password",
11  pool: { min: 2, max: 10 },
12  logging: true  // Log SQL queries
13});
14
15// Close connection on shutdown
16process.on("SIGTERM", async () => {
17  await closeDatabase();
18});

1import { Model } from "canxjs";
2
3// Define a User model
4export class User extends Model {
5  protected static tableName = "users";
6  protected static primaryKey = "id";
7  protected static timestamps = true;  // auto: created_at, updated_at
8  
9  // Type definition for the model
10  id!: number;
11  name!: string;
12  email!: string;
13  role!: string;
14  created_at!: Date;
15  updated_at!: Date;
16
17  // Relations
18  posts() {
19    return this.hasMany(Post, "user_id");
20  }
21
22  profile() {
23    return this.hasOne(Profile, "user_id");
24  }
25}
26
27export class Post extends Model {
28  // ...
29  user() {
30    return this.belongsTo(User, "user_id");
31  }
32  
33  tags() {
34    return this.belongsToMany(Tag, "post_tags", "post_id", "tag_id");
35  }
36}

1export class User extends Model {
2  // Allow only these fields to be mass-assigned
3  protected static fillable = ["name", "email", "password"];
4  
5  // OR block these fields (everything else is allowed)
6  protected static guarded = ["id", "is_admin", "balance"];
7}

1// Find by primary key
2const user = await User.find(1);
3
4// Get all records
5const users = await User.all();
6
7// Create a new record
8const newUser = await User.create({
9  name: "John Doe",
10  email: "john@example.com",
11  role: "user"
12});
13
14// Update by ID
15await User.updateById(1, { name: "Jane Doe" });
16
17// Delete by ID
18await User.deleteById(1);

1import { Model } from "canxjs";
2
3// Define a User model
4export class User extends Model {
5  protected static tableName = "users";
6  protected static primaryKey = "id";
7  protected static timestamps = true;  // auto: created_at, updated_at
8  
9  // Type definition for the model
10  id!: number;
11  name!: string;
12  email!: string;
13  role!: string;
14  created_at!: Date;
15  updated_at!: Date;
16
17  // Relations
18  posts() {
19    return this.hasMany(Post, "user_id");
20  }
21
22  profile() {
23    return this.hasOne(Profile, "user_id");
24  }
25}
26
27export class Post extends Model {
28  // ...
29  user() {
30    return this.belongsTo(User, "user_id");
31  }
32  
33  tags() {
34    return this.belongsToMany(Tag, "post_tags", "post_id", "tag_id");
35  }
36}

1import { Model } from "canxjs";
2
3export class User extends Model {
4  static tableName = "users";
5
6  // One-to-many
7  posts() { return this.hasMany(Post, "user_id"); }
8
9  // Polymorphic one-to-one: the related row stores
10  // imageable_id + imageable_type columns.
11  image() { return this.morphOne(Image, "imageable"); }
12
13  // Has-many-through: comments on all of this user's posts.
14  // (Comment -> Post -> User)
15  comments() {
16    return this.hasManyThrough(Comment, Post, "user_id", "post_id");
17  }
18}
19
20export class Post extends Model {
21  static tableName = "posts";
22
23  // Polymorphic one-to-many
24  tags() { return this.morphMany(Tag, "taggable"); }
25}
26
27export class Image extends Model {
28  static tableName = "images";
29  // Inverse of a polymorphic relation
30  imageable() { return this.morphTo(); }
31}
32
33// Eager-load exactly like normal relations
34const user = await User.with("image", "comments")
35  .where("id", "=", 1)
36  .first();
37
38console.log(user.relations.image);    // Image | null
39console.log(user.relations.comments); // Comment[]

1import { Model } from "canxjs";
2
3export class User extends Model {
4  static tableName = "users";
5
6  // Many-to-many via the "role_user" pivot table
7  roles() {
8    return this.belongsToMany(Role, "role_user", "user_id", "role_id");
9  }
10}
11
12const user = await User.find(1);
13
14// Attach one or many related ids (skips duplicate pivot rows)
15await user.roles().attach(5);
16await user.roles().attach([2, 3], { assigned_by: "admin" }); // extra pivot columns
17
18// Detach specific ids, or all when called without arguments
19await user.roles().detach(5);
20await user.roles().detach();          // remove every role
21
22// Replace the entire set in one call
23await user.roles().sync([1, 2, 3]);
24
25// Read the related records
26const roles = await user.roles().get();

1import { Model } from "canxjs";
2
3export class Post extends Model {
4  static tableName = "posts";
5  static softDeletes = true;          // enables the deleted_at column
6}
7
8const post = await Post.find(1);
9
10// Soft delete: sets deleted_at instead of removing the row
11await post.delete();
12
13// Default queries automatically exclude soft-deleted rows
14await Post.find(1);                    // => null
15
16// Include trashed rows explicitly
17const trashed = await Post.query()
18  .withTrashedResults()
19  .where("id", "=", 1)
20  .first();
21
22// Restore a soft-deleted model
23await trashed.restore();
24
25// Permanently remove (ignores soft-delete scope)
26await Post.query().where("id", "=", 1).forceDelete();

1import { Model } from "canxjs";
2
3export class User extends Model {
4  static tableName = "users";
5
6  // Values are cast on read and serialized on write
7  protected casts = {
8    is_admin: "boolean",   // 1/0  <-> true/false
9    meta: "json",          // TEXT column <-> object/array
10    settings: "array",
11    last_login: "datetime" // string <-> Date
12  } as any;
13}
14
15const user = await User.create({
16  name: "Ada",
17  is_admin: true,
18  meta: { theme: "dark" }
19});
20
21typeof user.is_admin; // "boolean"
22typeof user.meta;     // "object"

1// Using the query builder for complex queries
2const activeAdmins = await User.query()
3  .select("id", "name", "email")
4  .where("role", "=", "admin")
5  .where("status", "=", "active")
6  .orderBy("created_at", "desc")
7  .limit(10)
8  .get();
9
10// With pagination
11const page = 1;
12const perPage = 20;
13const users = await User.query()
14  .orderBy("id", "asc")
15  .limit(perPage)
16  .offset((page - 1) * perPage)
17  .get();
18
19// First record matching condition
20const admin = await User.query()
21  .where("role", "=", "admin")
22  .first();

1// Get paginated results (page 1, 15 items per page)
2const result = await User.query()
3  .where("status", "=", "active")
4  .paginate(1, 15);
5
6console.log(result);
7/*
8{
9  data: [...],
10  total: 45,
11  perPage: 15,
12  currentPage: 1,
13  lastPage: 3
14}
15*/

1// Basic where
2const users = await User.query()
3  .where("status", "=", "active")
4  .get();
5
6// Multiple conditions (AND)
7const results = await User.query()
8  .where("role", "=", "admin")
9  .where("status", "=", "active")
10  .get();
11
12// OR condition
13const results = await User.query()
14  .where("role", "=", "admin")
15  .orWhere("role", "=", "moderator")
16  .get();
17
18// WHERE IN
19const users = await User.query()
20  .whereIn("id", [1, 2, 3, 4, 5])
21  .get();
22
23// NULL checks
24const unverified = await User.query()
25  .whereNull("email_verified_at")
26  .get();
27
28const verified = await User.query()
29  .whereNotNull("email_verified_at")
30  .get();

1// Inner join
2const postsWithUsers = await Post.query()
3  .select("posts.*", "users.name as author")
4  .join("users", "posts.user_id", "=", "users.id")
5  .get();
6
7// Left join
8const results = await User.query()
9  .select("users.*")
10  .leftJoin("posts", "users.id", "=", "posts.user_id")
11  .get();

1// Count records
2const totalUsers = await User.query().count();
3
4// Sum
5const totalSales = await Order.query()
6  .where("status", "=", "completed")
7  .sum("amount");
8
9// Average
10const avgRating = await Review.query().avg("rating");
11
12// Group by with aggregates
13const salesByCategory = await Product.query()
14  .select("category")
15  .groupBy("category")
16  .get();

1// Execute raw SQL
2const results = await User.query().raw(
3  "SELECT * FROM users WHERE created_at > ?",
4  ["2024-01-01"]
5);

1// Eager load 'posts' relationship
2const users = await User.query()
3  .with("posts")
4  .get();
5
6// Eager load multiple relationships
7const posts = await Post.query()
8  .with("author", "comments")
9  .get();
10  
11// Lazy Eager Loading (on existing instance)
12const user = await User.find(1);
13await user.load("posts");