TypeORM Basics: Entities, Repositories & Queries Explained

// ─── 1. Install dependencies first ───────────────────────────────────────────
// npm install typeorm reflect-metadata pg
// npm install -D @types/node typescript
// Also add to tsconfig.json:
//   "experimentalDecorators": true,
//   "emitDecoratorMetadata": true

import "reflect-metadata"; // MUST be imported once, before anything else
import {
  DataSource,
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
} from "typeorm";

// ─── 2. Define the Entity (maps to a 'users' table) ──────────────────────────
@Entity("users") // explicit table name — avoids surprises if class is renamed
export class User {
  @PrimaryGeneratedColumn()
  id: number; // TypeORM handles auto-increment — never set this manually

  @Column({ length: 100 })
  fullName: string; // maps to column 'full_name' by default (snake_case conversion)

  @Column({ unique: true })
  email: string; // unique constraint applied at the DB level

  @Column({ default: true })
  isActive: boolean; // new users are active by default

  @CreateDateColumn()
  createdAt: Date; // TypeORM sets this automatically on INSERT — don't touch it
}

// ─── 3. Create and initialise the DataSource ─────────────────────────────────
export const AppDataSource = new DataSource({
  type: "postgres",
  host: "localhost",
  port: 5432,
  username: "forge_user",
  password: "forge_pass",
  database: "forge_db",
  synchronize: true,   // DEV ONLY: auto-creates tables. Use migrations in prod.
  logging: true,       // prints generated SQL — great for learning what TypeORM does
  entities: [User],    // every entity the DataSource manages must be listed here
});

// ─── 4. Bootstrap — initialise the connection before doing anything else ─────
async function bootstrap() {
  await AppDataSource.initialize(); // opens the connection pool
  console.log("DataSource initialised — connected to PostgreSQL");

  // Get the repository for User — this is your gateway to the 'users' table
  const userRepository = AppDataSource.getRepository(User);

  // Create a new user entity instance
  const newUser = userRepository.create({
    fullName: "Ada Lovelace",
    email: "ada@codeforge.io",
    isActive: true,
  });

  // .save() performs an INSERT (or UPDATE if the entity has an id)
  const savedUser = await userRepository.save(newUser);
  console.log("User saved:", savedUser);

  // Find all active users
  const activeUsers = await userRepository.find({
    where: { isActive: true },
    order: { createdAt: "DESC" }, // most recently created first
  });
  console.log(`Found ${activeUsers.length} active user(s):", activeUsers);

  await AppDataSource.destroy(); // clean up — close the connection pool
}

bootstrap().catch(console.error);

import "reflect-metadata";
import {
  DataSource,
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  JoinColumn,
  CreateDateColumn,
  Relation,
} from "typeorm";

// ─── User Entity (updated to include the OneToMany side) ──────────────────────
@Entity("users")
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 100 })
  fullName: string;

  @Column({ unique: true })
  email: string;

  // OneToMany: one user can have many orders.
  // The string 'order.user' is the inverse side property name.
  // Relation<T[]> is preferred over T[] to avoid circular reference issues.
  @OneToMany(() => Order, (order) => order.user)
  orders: Relation<Order[]>; // no foreign key here — this is purely navigational
}

// ─── Order Entity ─────────────────────────────────────────────────────────────
@Entity("orders")
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column("decimal", { precision: 10, scale: 2 })
  totalAmount: number; // e.g. 99.99 — use decimal, never float for money

  @Column({ length: 50, default: "pending" })
  status: string; // 'pending' | 'shipped' | 'delivered' | 'cancelled'

  @CreateDateColumn()
  placedAt: Date;

  // ManyToOne: many orders belong to one user.
  // This side owns the foreign key (user_id column will be created in 'orders' table).
  @ManyToOne(() => User, (user) => user.orders, {
    onDelete: "CASCADE", // if the user is deleted, their orders are deleted too
    nullable: false,     // every order MUST have a user
  })
  @JoinColumn({ name: "user_id" }) // explicit column name — don't let TypeORM guess
  user: Relation<User>;
}

// ─── DataSource with both entities ───────────────────────────────────────────
export const AppDataSource = new DataSource({
  type: "postgres",
  host: "localhost",
  port: 5432,
  username: "forge_user",
  password: "forge_pass",
  database: "forge_db",
  synchronize: true,
  logging: false, // turned off here for cleaner output — turn on when debugging
  entities: [User, Order], // both entities registered
});

async function demonstrateRelations() {
  await AppDataSource.initialize();

  const userRepo = AppDataSource.getRepository(User);
  const orderRepo = AppDataSource.getRepository(Order);

  // Step 1: Create a user
  const user = await userRepo.save(
    userRepo.create({ fullName: "Grace Hopper", email: "grace@codeforge.io" })
  );

  // Step 2: Create two orders belonging to that user
  // Pass the full user object — TypeORM extracts the foreign key automatically
  const order1 = await orderRepo.save(
    orderRepo.create({ totalAmount: 149.99, status: "shipped", user })
  );
  const order2 = await orderRepo.save(
    orderRepo.create({ totalAmount: 29.99, status: "pending", user })
  );

  console.log("Created orders:", order1.id, order2.id);

  // Step 3: Fetch the user WITH their orders — relations are opt-in!
  const userWithOrders = await userRepo.findOne({
    where: { id: user.id },
    relations: { orders: true }, // explicitly request the relation — no magic loading
  });

  console.log(
    `${userWithOrders?.fullName} has ${userWithOrders?.orders.length} orders:`
  );
  userWithOrders?.orders.forEach((order) => {
    console.log(`  Order #${order.id} — $${order.totalAmount} (${order.status})`);
  });

  await AppDataSource.destroy();
}

demonstateRelations().catch(console.error);

import { AppDataSource } from "./DataSource"; // your configured DataSource
import { Order } from "./OrderEntity";
import { User } from "./UserEntity";

async function runAdvancedQueries() {
  await AppDataSource.initialize();

  const orderRepo = AppDataSource.getRepository(Order);

  // ─── Example 1: Paginated list of shipped orders with user info ─────────────
  const pageNumber = 1;
  const pageSize = 10;

  const [shippedOrders, totalCount] = await orderRepo
    .createQueryBuilder("order") // 'order' is the alias for the Order entity
    .leftJoinAndSelect("order.user", "customer") // JOIN users table, alias it 'customer'
    .where("order.status = :status", { status: "shipped" }) // parameterised — safe
    .andWhere("customer.isActive = :isActive", { isActive: true })
    .orderBy("order.placedAt", "DESC")
    .skip((pageNumber - 1) * pageSize) // offset — skip records before this page
    .take(pageSize)                    // limit — return at most this many records
    .getManyAndCount();               // executes ONE query, returns [results, total]

  console.log(`Page ${pageNumber} of shipped orders (${totalCount} total):");
  shippedOrders.forEach((order) => {
    console.log(
      `  Order #${order.id} by ${order.user.fullName} — $${order.totalAmount}`
    );
  });

  // ─── Example 2: Aggregate — total revenue per user ─────────────────────────
  const revenueByUser = await AppDataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoin("user.orders", "order")           // JOIN without SELECT (no .AndSelect)
    .addSelect("SUM(order.totalAmount)", "totalRevenue") // raw aggregate
    .addSelect("COUNT(order.id)", "orderCount")
    .groupBy("user.id")
    .having("SUM(order.totalAmount) > :minRevenue", { minRevenue: 100 })
    .orderBy("totalRevenue", "DESC")
    .getRawMany(); // use getRawMany() for aggregates — getMany() won't include them

  console.log("\nTop customers by revenue:");
  revenueByUser.forEach((row) => {
    // getRawMany returns plain objects — column names are alias_column format
    console.log(
      `  ${row.user_fullName}: $${parseFloat(row.totalRevenue).toFixed(2)} across ${row.orderCount} orders`
    );
  });

  // ─── Example 3: Conditional query building (common in filter APIs) ──────────
  const statusFilter: string | null = "pending"; // imagine this comes from a request
  const userIdFilter: number | null = null;

  const dynamicQuery = orderRepo
    .createQueryBuilder("order")
    .leftJoinAndSelect("order.user", "customer");

  // Only add the WHERE clause if the filter is actually provided
  if (statusFilter) {
    dynamicQuery.andWhere("order.status = :status", { status: statusFilter });
  }
  if (userIdFilter) {
    dynamicQuery.andWhere("customer.id = :userId", { userId: userIdFilter });
  }

  const filteredOrders = await dynamicQuery.getMany();
  console.log(`\nDynamic filter returned ${filteredOrders.length} order(s)`);

  await AppDataSource.destroy();
}

runAdvancedQueries().catch(console.error);

// ─── Step 1: Configure DataSource for CLI use ─────────────────────────────────
// Create a dedicated file: src/data-source.ts
// The TypeORM CLI reads this file to know where your entities and migrations live.

import "reflect-metadata";
import { DataSource } from "typeorm";
import { User } from "./entities/UserEntity";
import { Order } from "./entities/OrderEntity";

export const AppDataSource = new DataSource({
  type: "postgres",
  host: process.env.DB_HOST ?? "localhost",
  port: parseInt(process.env.DB_PORT ?? "5432"),
  username: process.env.DB_USER ?? "forge_user",
  password: process.env.DB_PASS ?? "forge_pass",
  database: process.env.DB_NAME ?? "forge_db",

  synchronize: false,     // ALWAYS false once you're using migrations
  logging: ["migration"], // only log migration-related queries

  entities: [User, Order],

  migrations: ["src/migrations/*.ts"], // glob pattern — picks up all migration files
  migrationsTableName: "schema_migrations", // the table TypeORM uses to track runs
});

// ─── Step 2: Add CLI scripts to package.json ──────────────────────────────────
// "scripts": {
//   "migration:generate": "typeorm-ts-node-commonjs migration:generate src/migrations/migration -d src/data-source.ts",
//   "migration:run":      "typeorm-ts-node-commonjs migration:run -d src/data-source.ts",
//   "migration:revert":   "typeorm-ts-node-commonjs migration:revert -d src/data-source.ts"
// }

// ─── Step 3: Example of a generated migration file ───────────────────────────
// After running: npm run migration:generate AddPhoneToUsers
// TypeORM creates: src/migrations/1710500000000-AddPhoneToUsers.ts

import { MigrationInterface, QueryRunner } from "typeorm";

export class AddPhoneToUsers1710500000000 implements MigrationInterface {
  name = "AddPhoneToUsers1710500000000"; // timestamp in name ensures ordering

  // up(): what to do when this migration runs forward
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      // Nullable so existing rows aren't broken by the new column
      `ALTER TABLE "users" ADD "phone_number" character varying(20)`
    );
  }

  // down(): how to completely reverse the up() change — this is your rollback
  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "users" DROP COLUMN "phone_number"`
    );
  }
}

// ─── Step 4: Run the migration ───────────────────────────────────────────────
// npm run migration:run
// TypeORM checks the schema_migrations table, sees this hasn't run, and executes up()

// ─── Step 5: Rollback if something went wrong ─────────────────────────────────
// npm run migration:revert
// TypeORM calls down() on the last applied migration — column is dropped safely