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}

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);

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// 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");