TypeScript is a popular programming language that provides static type checking, making it easier to catch errors early on during development. PostgreSQL is an open-source relational database management system that is widely used for its reliability and scalability. In this tutorial, we will explore how to use TypeScript with PostgreSQL to build robust and scalable applications.


Prerequisites:

  • Basic knowledge of TypeScript and Node.js
  • PostgreSQL installed and running on your system
  • A code editor of your choice


Step 1: Set up a TypeScript project

First, create a new directory for your project and navigate to it in your terminal. Then, run the following commands:

npm init -y
npm install --save-dev typescript ts-node @types/node

The npm init command will initialize a new npm project in your directory, while the npm install command installs TypeScript and the necessary dependencies for our project. The ts-node package will allow us to run TypeScript files directly without having to compile them first, and the @types/node package provides TypeScript definitions for Node.js.

Next, create a tsconfig.json file in the root of your project directory with the following contents:

{
  "compilerOptions": {
    "target": "es6",
    "module": "commonjs",
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true
  },
  "include": [
    "./src/**/*"
  ]
}

This configuration tells TypeScript to target ES6, output compiled files to the dist directory, and include all files under the src directory for compilation.


Step 2: Install PostgreSQL and set up a database

If you haven't already, install PostgreSQL on your system and create a new database for your project. You can do this using the createdb command: 

createdb my_database_name

Be sure to replace my_database_name with the name of your desired database.


Step 3: Connect to PostgreSQL using pg package

To interact with PostgreSQL from our TypeScript code, we will use the pg package. Install it by running the following command: 

npm install pg

Next, create a new file src/db.ts with the following contents:

import { Pool } from 'pg';

export const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'my_database_name',
  password: 'my_password',
  port: 5432,
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

This file exports a Pool instance from the pg package, which we will use to create connections to our PostgreSQL database. Be sure to replace the user, database, password, and host fields with your own values.


Step 4: Create a table and query data

Let's create a table in our database and query some data from it. Create a new file src/main.ts with the following contents:

import { pool } from './db';

async function createTable() {
  const client = await pool.connect();
  try {
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL
      )
    `);
  } finally {
    client.release();
  }
}

async function insertUser(name: string, email: string) {
  const client = await pool.connect();
  try {
    await client.query(
      'INSERT INTO users (name, email) VALUES ($1, $2)',
[name, email]
);
} finally {
client.release();
}
}

async function getUsers() {
const client = await pool.connect();
try {
const { rows } = await client.query('SELECT * FROM users');
return rows;
} finally {
client.release();
}
}

async function main() {
await createTable();

await insertUser('John Doe', 'john@example.com');
await insertUser('Jane Doe', 'jane@example.com');

const users = await getUsers();
console.log(users);
}

main().catch(console.error);

This file defines three functions:

  • createTable: creates a `users` table if it doesn't already exist
  • insertUser: inserts a new user into the `users` table
  • getUsers: retrieves all users from the `users` table

The `main` function calls `createTable` to ensure that the table exists, inserts two sample users using `insertUser`, and retrieves all users using `getUsers`. The retrieved users are then logged to the console.


Step 5: Run the application

To run the application, use the `ts-node` command to execute the `main.ts` file: 

npx ts-node src/main.ts

If everything is set up correctly, you should see the following output in your terminal: 

[ { id: 1, name: 'John Doe', email: 'john@example.com' },
{ id: 2, name: 'Jane Doe', email: 'jane@example.com' } ]

Congratulations! You have successfully built a TypeScript application that interacts with a PostgreSQL database.


Conclusion

In this tutorial, we learned how to use TypeScript with PostgreSQL to build robust and scalable applications. We set up a TypeScript project, connected to a PostgreSQL database using the `pg` package, created a table, and queried data from it. With these concepts in hand, you can now start building your own TypeScript and PostgreSQL applications.