Basic Example

Let's Start

We've learned how to set up TypeORM, define entities, and perform CRUD operations. Now let's build a complete working project that ties it all together — a Books API with PostgreSQL.

This mirrors the structure of the MongoDB example so you can compare the two approaches side by side.

Project Setup

Create the project folder and initialize it:

mkdir express-with-postgres
cd express-with-postgres
npm init -y

Install dependencies:

npm
Yarn
pnpm
Bun

npm install express typeorm pg reflect-metadata
npm install -D typescript ts-node @types/node @types/express nodemon

yarn add express typeorm pg reflect-metadata
yarn add --dev typescript ts-node @types/node @types/express nodemon

pnpm add express typeorm pg reflect-metadata
pnpm add -D typescript ts-node @types/node @types/express nodemon

bun add express typeorm pg reflect-metadata
bun add --dev typescript ts-node @types/node @types/express nodemon

Initialize TypeScript:

npx tsc --init

Then update your tsconfig.json with the options TypeORM requires:

tsconfig.json
{
  "compilerOptions": {
    "target": "ES2020",
    "module": "commonjs",
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true,
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true
  }
}

Update package.json scripts:

package.json
{
  "name": "express-with-postgres",
  "version": "1.0.0",
  "scripts": {
    "start": "node dist/index.js",
    "dev": "nodemon --exec ts-node src/index.ts"
  }
}

Folder Structure

📂 express-with-postgres
├── 📂 src
│   ├── 📂 entities
│   │   └── 📄 Book.ts
│   ├── 📂 routes
│   │   └── 📄 book.ts
│   ├── 📄 data-source.ts
│   └── 📄 index.ts
├── 📄 tsconfig.json
└── 📄 package.json

`src/data-source.ts`

Set up the TypeORM DataSource with your PostgreSQL connection details:

src/data-source.ts
import "reflect-metadata";
import { DataSource } from "typeorm";
import { Book } from "./entities/Book";

export const AppDataSource = new DataSource({
  type: "postgres",
  host: process.env.DB_HOST || "localhost",
  port: Number(process.env.DB_PORT) || 5432,
  username: process.env.DB_USER || "postgres",
  password: process.env.DB_PASSWORD || "your_password",
  database: process.env.DB_NAME || "books_db",
  synchronize: true,
  logging: false,
  entities: [Book],
});

tip

Create the database (books_db) first using pgAdmin or the psql CLI:

CREATE DATABASE books_db;

`src/entities/Book.ts`

Define the schema for a book:

name: String, required, unique
author: String, required
price: Decimal number, required, must be positive
stock: Integer, optional

src/entities/Book.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
} from "typeorm";

@Entity()
export class Book {
  @PrimaryGeneratedColumn()
  id: number;

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

  @Column()
  author: string;

  @Column({ type: "decimal", precision: 10, scale: 2 })
  price: number;

  @Column({ nullable: true })
  stock: number | null;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

`src/index.ts`

Set up the Express app and initialize the database connection before starting the server:

src/index.ts
import "reflect-metadata";
import express from "express";
import { AppDataSource } from "./data-source";
import bookRouter from "./routes/book";

const app = express();
app.use(express.json());

app.use("/book", bookRouter);

AppDataSource.initialize()
  .then(() => {
    console.log("Database Connected ~");
    app.listen(9999, () => console.log("Server running on port 9999"));
  })
  .catch((error) => console.error("Database Connection Failed:", error));

`src/routes/book.ts`

Create routes for the Book resource:

GET /book — get all books
GET /book/:id — get a single book by ID
POST /book — create a new book
PATCH /book/:id — update a book
DELETE /book/:id — delete a book
DELETE /book — delete all books

src/routes/book.ts
import { Router } from "express";
import { AppDataSource } from "../data-source";
import { Book } from "../entities/Book";

const router = Router();
const bookRepository = AppDataSource.getRepository(Book);

router.get("/", async (req, res) => {
  try {
    const books = await bookRepository.find();
    res.json(books);
  } catch (error: any) {
    res.status(500).json({ message: error.message });
  }
});

router.get("/:id", async (req, res) => {
  try {
    const book = await bookRepository.findOneBy({ id: Number(req.params.id) });
    if (!book) {
      return res.status(404).json({ message: "Book not found" });
    }
    res.json(book);
  } catch (error: any) {
    res.status(500).json({ message: error.message });
  }
});

router.post("/", async (req, res) => {
  try {
    const book = bookRepository.create(req.body);
    const result = await bookRepository.save(book);
    res.status(201).json(result);
  } catch (error: any) {
    res.status(500).json({ message: error.message });
  }
});

router.patch("/:id", async (req, res) => {
  try {
    const book = await bookRepository.findOneBy({ id: Number(req.params.id) });
    if (!book) {
      return res.status(404).json({ message: "Book not found" });
    }
    bookRepository.merge(book, req.body);
    const result = await bookRepository.save(book);
    res.json(result);
  } catch (error: any) {
    res.status(500).json({ message: error.message });
  }
});

router.delete("/:id", async (req, res) => {
  try {
    const result = await bookRepository.delete(Number(req.params.id));
    if (result.affected === 0) {
      return res.status(404).json({ message: "Book not found" });
    }
    res.json({ message: "Book deleted" });
  } catch (error: any) {
    res.status(500).json({ message: error.message });
  }
});

router.delete("/", async (req, res) => {
  try {
    await bookRepository.clear();
    res.json({ message: "All books deleted" });
  } catch (error: any) {
    res.status(500).json({ message: error.message });
  }
});

export default router;

A few things to notice in the routes:

404 handling: After findOneBy(), we check if book is null and return a proper 404. This is better practice than what you see in many examples.
merge(): bookRepository.merge(book, req.body) copies the fields from req.body into the existing book entity, then save() issues an UPDATE. This pattern preserves fields not included in the request.
result.affected: The delete() method returns a result with affected — the number of rows deleted. If it's 0, the ID didn't exist.

Running the App

Make sure PostgreSQL is running, then start the server:

npm
Yarn
pnpm
Bun

npm run dev

yarn dev

pnpm run dev

bun run dev

You should see:

Database Connected ~
Server running on port 9999

And because synchronize: true is enabled, TypeORM will have automatically created the book table in your books_db database.

Testing the API

You can test the API with Postman or any HTTP client.

Create a book:

POST http://localhost:9999/book
Content-Type: application/json

{
  "name": "Clean Code",
  "author": "Robert C. Martin",
  "price": 29.99,
  "stock": 50
}

Get all books:

GET http://localhost:9999/book

Get one book:

GET http://localhost:9999/book/1

Update a book:

PATCH http://localhost:9999/book/1
Content-Type: application/json

{
  "price": 24.99
}

Delete a book:

DELETE http://localhost:9999/book/1

Code

You can find the full code on GitHub.

Conclusion

In this article, we built a complete Books CRUD API using Express, TypeORM, and PostgreSQL. We covered project setup, entity definition, repository-based CRUD operations, and tested the API.

The pattern — DataSource → Entity → Repository → Route — is the standard TypeORM architecture that scales well as your application grows.

Let's Start​

Project Setup​

Folder Structure​

src/data-source.ts​

src/entities/Book.ts​

src/index.ts​

src/routes/book.ts​

Running the App​

Testing the API​

Code​

Conclusion​