TypeScript and PostgreSQL: A Complete Guide to Building a Reliable Backend

Introduction: Why TypeScript and PostgreSQL Are the Perfect Pair for Your Project

Have you ever wondered why developers are increasingly choosing the TypeScript and PostgreSQL combination? This is not just a trendy fad — it is a powerful tandem that is changing the rules of the game in modern web development. Imagine a Swiss Army knife and a reliable safe in one package: TypeScript brings rigor and predictability to the code, while PostgreSQL brings reliability and flexibility in data storage.

In this guide, we won't just skim through the theory. We will dive into real practice: set up a connection, write queries, create an API, and figure out how to avoid common mistakes. Ready? Let's go!

Environment Setup: Where to Start with TypeScript and PostgreSQL

Before writing code, let's prepare the ground. Installing dependencies is like the foundation of a house: the stronger it is, the longer the project will last.

Installing Necessary Packages

We will need two main tools: pg (the PostgreSQL client) and typescript with its types. Run the following in your terminal:

npm init -y\nnpm install pg\nnpm install -D typescript @types/node @types/pg\nnpx tsc --init\n

Note: we install the types for pg separately. This is a mandatory requirement for working with a TypeScript database — otherwise, the compiler will complain about missing types.

Basic Project Structure

Create a file called database.ts — this will be the heart of our connection. Here is a minimal but working template:

import { Pool } from 'pg';\n\nconst pool = new Pool({\n  user: 'your_username',\n  host: 'localhost',\n  database: 'your_database',\n  password: 'your_password',\n  port: 5432,\n});\n\nexport default pool;\n

Why Pool instead of a regular client? Pool manages a pool of connections, which is critical for production. Imagine each request is a café visitor. Without a pool, you would open a new café for each customer. With a pool, you already have several tables, and you simply seat the guests.

Basic Operations: CRUD with TypeScript and PostgreSQL

Now for the interesting part — writing queries. We will create a simple users table and implement all four operations: Create, Read, Update, Delete.

Creating a Table

Before working with data, you need to create the structure. Here is how to do it with TypeScript:

import pool from './database';\n\nasync function createTable() {\n  const query = `\n    CREATE TABLE IF NOT EXISTS users (\n      id SERIAL PRIMARY KEY,\n      name VARCHAR(100) NOT NULL,\n      email VARCHAR(255) UNIQUE NOT NULL,\n      age INTEGER CHECK (age > 0),\n      created_at TIMESTAMP DEFAULT NOW()\n    );\n  `;\n  \n  try {\n    await pool.query(query);\n    console.log('Table users created');\n  } catch (error) {\n    console.error('Error:', error);\n  }\n}\n\ncreateTable();\n

Notice SERIAL — this is an auto-increment that PostgreSQL generates itself. And CHECK ensures that the age will be positive. This is an example of how PostgreSQL TypeScript together ensure data integrity at two levels.

Adding Data (Create)

Parameterized insertion is a safe way to avoid SQL injection:

interface User {\n  name: string;\n  email: string;\n  age: number;\n}\n\nasync function addUser(user: User) {\n  const query = `\n    INSERT INTO users (name, email, age)\n    VALUES ($1, $2, $3)\n    RETURNING *;\n  `;\n  \n  const values = [user.name, user.email, user.age];\n  \n  try {\n    const result = await pool.query(query, values);\n    console.log('User added:', result.rows[0]);\n    return result.rows[0];\n  } catch (error) {\n    console.error('Error adding user:', error);\n    throw error