In this tutorial, we will explore how to query PostgreSQL with TypeScript. PostgreSQL is a powerful relational database management system that is widely used in modern web development. TypeScript is a statically typed superset of JavaScript that provides a great deal of type safety and helps developers write more reliable and maintainable code. By combining the two technologies, we can create robust and efficient web applications that leverage the power of a well-designed database.


To get started, we need to install the necessary dependencies. We will be using the following packages:

  • pg - A PostgreSQL client library for Node.js
  • @types/pg - TypeScript type definitions for pg

We can install these packages using npm:

npm install pg @types/pg

Once we have installed the necessary packages, we can start writing our code. Let's create a new TypeScript file called index.ts. In this file, we will import the pg package and connect to our PostgreSQL database:

import { Client } from 'pg';

const client = new Client({
  user: 'postgres',
  host: 'localhost',
  database: 'mydatabase',
  password: 'mypassword',
  port: 5432,
});

async function connect() {
  try {
    await client.connect();
    console.log('Connected to PostgreSQL database');
  } catch (error) {
    console.error('Error connecting to PostgreSQL database:', error);
  }
}

connect();

In this code, we are creating a new instance of the Client class and passing in the connection details for our PostgreSQL database. We are then defining an async function called connect that attempts to connect to the database using the await keyword. If the connection is successful, we log a message to the console. If there is an error, we log the error to the console.


Now that we have established a connection to our database, we can start querying it. Let's write a simple query that selects all the rows from a table called users:

async function getUsers() {
  try {
    const result = await client.query('SELECT * FROM users');
    console.log(result.rows);
  } catch (error) {
    console.error('Error querying PostgreSQL database:', error);
  }
}

getUsers();

In this code, we are defining another async function called getUsers that uses the query method of the Client class to execute a SQL query that selects all the rows from the users table. If the query is successful, we log the resulting rows to the console. If there is an error, we log the error to the console.


We can run this code by executing the following command:

npx ts-node index.ts

This will execute our TypeScript code using the ts-node package. If everything is set up correctly, we should see the rows from the users table logged to the console.

Of course, this is just the beginning. There are many more advanced features that we can use to interact with our PostgreSQL database, such as prepared statements, transactions, and more. But by starting with these basic examples, we can build a solid foundation for more complex database interactions in our TypeScript applications.