Introduction

As backend engineers, though, we rarely write raw SQL day-to-day. Instead, we usually work with an Object-Relational Mapper (ORM), and in the TypeScript/Node.js world, TypeORM is one of the most popular choices.

In this article, we’ll revisit the core SQL ideas you already learned—SELECT, filtering, joins, aggregates, transactions—but now through the lens of TypeORM. Think of this as the “ORM edition” of your SQL guide.

What is TypeORM

TypeORM is an ORM for TypeScript and JavaScript that runs on Node.js and supports multiple databases (PostgreSQL, MySQL, MariaDB, SQLite, MS SQL Server, Oracle, and more). It lets you work with database tables as TypeScript classes and records as objects, while still exposing powerful SQL-like tools when you need them.

Key ideas:

• Entities: TypeScript classes that map to database tables.
• Repositories / EntityManager: High-level APIs to query and persist entities.
• QueryBuilder: A fluent API to build more complex queries programmatically.
• Relations & Migrations: Declarative relations between entities and a way to version your schema.

TypeORM supports both Active Record and Data Mapper patterns, so you can choose the style you prefer.

Basic Queries with Repositories

In SQL, you SELECT data from tables. In TypeORM, you usually do this with repositories or the EntityManager.

Assuming you have an Employee entity:

// Select all employees
const allEmployees = await employeeRepository.find();

// Select specific columns (via select option)
const names = await employeeRepository.find({
  select: ["firstName", "lastName"],
});

// Using DISTINCT equivalent: find and use distinct on a column via QueryBuilder (see later)
const distinctDepartmentIds = await employeeRepository
  .createQueryBuilder("employee")
  .select("employee.departmentId", "departmentId")
  .distinct(true)
  .getMany();

// Using WHERE to filter results
const employeesInDept10 = await employeeRepository.find({
  where: { departmentId: 10 },
});

// Using LIMIT/OFFSET equivalents: take and skip
const firstFiveEmployees = await employeeRepository.find({
  take: 5,
});

const employeesAfterFive = await employeeRepository.find({
  skip: 5,
});

// Combined: skip and take
const pagedEmployees = await employeeRepository.find({
  skip: 5,
  take: 10,
});

Filtering Data

SQL filtering uses WHERE with comparison and logical operators. TypeORM’s find options and QueryBuilder map these to TypeScript-friendly patterns.

Using FindOptions:

// WHERE salary > 50000
const wellPaidEmployees = await employeeRepository.find({
  where: {
    salary: MoreThan(50000),
  },
});

// WHERE department_id = 10 AND salary > 50000
const employeesInDept10WithHighSalary = await employeeRepository.find({
  where: {
    departmentId: 10,
    salary: MoreThan(50000),
  },
});

For more complex conditions (OR, IN, BETWEEN, LIKE), QueryBuilder is often clearer:

// WHERE department_id IN (10, 20)
const employeesInDepts = await employeeRepository
  .createQueryBuilder("employee")
  .where("employee.departmentId IN (:...ids)", { ids: [10, 20] })
  .getMany();

// WHERE salary BETWEEN 40000 AND 60000
const employeesInRange = await employeeRepository
  .createQueryBuilder("employee")
  .where("employee.salary BETWEEN :min AND :max", { min: 40000, max: 60000 })
  .getMany();

// WHERE last_name LIKE 'S%' (starts with 'S')
const employeesWithS = await employeeRepository
  .createQueryBuilder("employee")
  .where("employee.lastName LIKE :pattern", { pattern: "S%" })
  .getMany();

Sorting Data

SQL uses ORDER BY. TypeORM uses the order option in find, or orderBy in QueryBuilder.

Using find:

// ORDER BY salary ASC (default)
const employeesBySalary = await employeeRepository.find({
  order: {
    salary: "ASC",
  },
});

// ORDER BY salary DESC
const employeesBySalaryDesc = await employeeRepository.find({
  order: {
    salary: "DESC",
  },
});

// ORDER BY department_id ASC, salary DESC
const employeesByDeptAndSalary = await employeeRepository.find({
  order: {
    departmentId: "ASC",
    salary: "DESC",
  },
});

Aggregate Functions

SQL aggregates like COUNT, SUM, AVG, MIN, MAX are typically expressed in TypeORM via QueryBuilder’s select and getRawOne / getRawMany.

// COUNT(*)
const countResult = await employeeRepository
  .createQueryBuilder("employee")
  .select("COUNT(*)", "count")
  .getRawOne(); // { count: "123" }

// SUM(salary)
const totalSalaryResult = await employeeRepository
  .createQueryBuilder("employee")
  .select("SUM(employee.salary)", "total")
  .getRawOne(); // { total: "1234567.89" }

// AVG(salary)
const avgSalaryResult = await employeeRepository
  .createQueryBuilder("employee")
  .select("AVG(employee.salary)", "avg")
  .getRawOne();

// MIN(salary)
const minSalaryResult = await employeeRepository
  .createQueryBuilder("employee")
  .select("MIN(employee.salary)", "min")
  .getRawOne();

// MAX(salary)
const maxSalaryResult = await employeeRepository
  .createQueryBuilder("employee")
  .select("MAX(employee.salary)", "max")
  .getRawOne();

Grouping Data

SQL’s GROUP BY and HAVING map directly to QueryBuilder in TypeORM.

// GROUP BY department_id, COUNT(*)
const departmentCounts = await employeeRepository
  .createQueryBuilder("employee")
  .select("employee.departmentId", "departmentId")
  .addSelect("COUNT(*)", "count")
  .groupBy("employee.departmentId")
  .getRawMany(); // [{ departmentId: 10, count: "25" }, ...]

// GROUP BY department_id HAVING COUNT(*) > 5
const largeDepartments = await employeeRepository
  .createQueryBuilder("employee")
  .select("employee.departmentId", "departmentId")
  .addSelect("COUNT(*)", "count")
  .groupBy("employee.departmentId")
  .having("COUNT(*) > :min", { min: 5 })
  .getRawMany();

Joins

In SQL, you join tables to combine related rows. In TypeORM, you define relations between entities and then use join options in find or QueryBuilder.

Assume Employee and Department entities with a ManyToOne relation:

// Inner Join using QueryBuilder
const employeesWithDepartments = await employeeRepository
  .createQueryBuilder("employee")
  .innerJoin("employee.department", "department")
  .getMany();

// Left Join (most common in ORMs)
const employeesWithDepartmentsLeft = await employeeRepository
  .createQueryBuilder("employee")
  .leftJoin("employee.department", "department")
  .getMany();

// Join with selected columns
const employeesWithDepartmentNames = await employeeRepository
  .createQueryBuilder("employee")
  .leftJoin("employee.department", "department")
  .addSelect(["department.name"])
  .getMany();

Using find with relations:

// Eagerly load a relation (left join behind the scenes)
const employeesWithDept = await employeeRepository.find({
  relations: {
    department: true,
  },
});

Full outer joins are less common in ORMs and depend on the underlying database; you can execute them via raw SQL or QueryBuilder’s specialized methods when needed.

Subqueries

SQL subqueries are nested queries. TypeORM supports subqueries via QueryBuilder and using SubqueryExpression factories.

// WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700)
const employeesAtLocation1700 = await employeeRepository
  .createQueryBuilder("employee")
  .where(
    "employee.departmentId IN " +
      employeeRepository
        .createQueryBuilder()
        .subQuery()
        .select("department.id")
        .from(Department, "department")
        .where("department.locationId = :locationId", { locationId: 1700 })
        .getQuery()
  )
  .setParameter("locationId", 1700)
  .getMany();

// Correlated subquery: salary > AVG(salary) for same department
const betterPaidThanDeptAvg = await employeeRepository
  .createQueryBuilder("employee")
  .where(
    "employee.salary > " +
      employeeRepository
        .createQueryBuilder()
        .subQuery()
        .select("AVG(e2.salary)")
        .from(Employee, "e2")
        .where("e2.departmentId = employee.departmentId")
        .getQuery()
  )
  .getMany();

Views (Views via QueryBuilder / Raw SQL)

TypeORM doesn’t treat database views as entities by default, but you can:

• Create views using raw SQL in migrations or custom queries.
• Map an entity to an existing view using @Entity({ view: true }) (if supported by your DB) or simply by treating the view like a read-only table.

Example: creating and using a view via raw SQL in a migration or custom query:

-- Migration SQL to create a view
CREATE VIEW high_paid_employees AS
SELECT * FROM employees WHERE salary > 80000;

From TypeORM, you can then query this view like a table:

const highPaidEmployees = await dataSource
  .createQueryBuilder()
  .select("*")
  .from("high_paid_employees", "hpe")
  .getRawMany();

For more complex, view-heavy use cases, you might still manage views directly in SQL and use TypeORM mainly for querying them.

Indexing

SQL indexes make queries faster. In TypeORM, you can declare indexes on entities using decorators, which will then be generated via migrations.

import { Entity, PrimaryGeneratedColumn, Column, Index } from "typeorm";

@Entity()
@Index("idx_lastname", ["lastName"]) // single-column index
export class Employee {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  salary: number;

  @Column()
  departmentId: number;
}

Composite indexes:

@Entity()
@Index("idx_dept_salary", ["departmentId", "salary"])
export class Employee {
  // ...
}

The index definitions are picked up by TypeORM’s migration tools to generate the appropriate CREATE INDEX statements.

Transactions

SQL transactions ensure atomicity: all statements succeed or none do. TypeORM provides multiple ways to manage transactions, such as using DataSource.transaction or QueryRunner.

Using a high-level transaction helper:

await dataSource.transaction(async (manager) => {
  const employee = manager.create(Employee, {
    firstName: "John",
    lastName: "Doe",
    salary: 60000,
    departmentId: 10,
  });

  await manager.save(employee);

  // any error thrown here will cause rollback
});

For more control (e.g., manual commits/rollbacks), use QueryRunner:

const queryRunner = dataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  await queryRunner.manager.save(Employee, {
    firstName: "Jane",
    lastName: "Smith",
    salary: 70000,
    departmentId: 10,
  });

  // Other operations...

  await queryRunner.commitTransaction();
} catch (err) {
  await queryRunner.rollbackTransaction();
  throw err;
} finally {
  await queryRunner.release();
}

Stored Procedures (Calling via TypeORM)

Stored procedures are database-level routines. TypeORM doesn’t define stored procedures directly, but you can call them using QueryRunner’s raw SQL capabilities.

For example, if you have a stored procedure get_employee(employee_id):

const result = await dataSource.query(
  "CALL get_employee(?)",
  [100]
);

You can wrap such calls in repository methods or services to keep your codebase clean.

Backup and Recovery (Database-Level, Not TypeORM-Specific)

Backup and recovery are typically handled by your database tooling (pg_dump, mysqldump, cloud provider backup, etc.), not by the ORM. TypeORM focuses on schema and data manipulation in your application code.

Common patterns:

• Use your database’s native backup tools (or managed service features).
• Use migrations to version your schema so that restoring/replicating environments is repeatable.
• Optionally, write custom scripts that use TypeORM to export/import critical data, but this is application-level logic, not a replacement for DB backups.

In short: treat backup and recovery as infrastructure/DBA concerns; keep TypeORM focused on schema and app-level queries.

SQL Dialects vs TypeORM Abstractions

Just as SQL dialects differ across databases, TypeORM smooths over many of those differences in TypeScript, but still lets you tap into database-specific features when needed.

For example:

• PostgreSQL-specific JSONB or array columns can be used via TypeORM column types.
• MySQL-specific functions or spatial features can be used via QueryBuilder or .query().
• SQL Server’s T-SQL can be called through raw SQL when necessary.

TypeORM’s strength is that it lets you:

• Start with database-agnostic entities and repositories.
• Drop down to raw SQL or driver-specific features when you really need them.

How This All Fits Together

To round out your backend journey with TypeORM, a practical path looks like this:

1. Relational database design: entities, keys, relationships (as you covered in SQL).
2. SQL fundamentals: SELECT, WHERE, JOIN, aggregates, transactions.
3. TypeORM fundamentals: entities, repositories, relations, migrations, QueryBuilder, transactions.
4. Pick an RDBMS: e.g., PostgreSQL or MySQL, then use TypeORM with its specific dialect while still relying mostly on TypeScript abstractions.

Conclusion

SQL is vast, and so is TypeORM. We’ve touched on the core areas—entities, repositories, filtering, sorting, aggregates, grouping, joins, subqueries, views, indexes, transactions, and more—that give you a solid ORM-focused foundation. For backend engineers, this combination of SQL knowledge and TypeORM skills is more than enough to start designing robust, type-safe data layers in TypeScript.