Build TypeScript Express App with PostgreSQL/pg and NodeJS Example

This tutorial teaches you how to connect to PostgreSQL using the pg and Express Node.js library. TypeScript elevates the way you write JavaScript code. Still, you want TypeScript to connect to databases, for example, PostgreSQL. Therefore. Node.js should be able to use Express and Serve your API while still using TypeScript advantages to connect to Postgres.

Learn how to leverage Node.js with TypeScript and create an Express API example to connect to PostgreSQL using the pg library.

In summary, you will learn:

• How to set up Node.js TypeScript Express server with PostgreSQL and pg
• Creating a TypeScript PostgreSQL database connection.
• How to run PostgreSQL (pg) Type checks for TypeScript within Node.js
• The best way to run TypeScript code with Node.js, Express, and PostgreSQL

Related: Sequelize Models with Typescript using MySQL and PostgreSQL

What you Need to Use Node.js with TypeScript AND PostgreSQL

Before using Node.js typescript Express and PostgreSQL on your apps, make sure you have the following:

• Some basic knowledge of working with TypeScript
• Have Node.js runtime installed
• Ensure PostgreSQL is installed and ready to accept connections using Node.js and TypeScript pg.

Creating Node.js (Express) with TypeScript and PostgreSQL

Now, you need Node.js ready. If you have already, run npm init -y in your working directory to get it ready. TypeScript must be available on your computer. Use the following command to install it globally:

npm i typescript -g

At the same time, libraries such as Express and pg must be ready on your Node.js TypeScript app. Use the following commands:

Install the core packages:

pg express concurrently

Install these packages as dev dependencies to extend to TypeScript types for each:

ts-node typescript @types/pg @types/express nodemon @types/node --dev

Running TypeScript with Node.js Express and PostgreSQL(pg)

You will need TypeScript configurations ready on your app to run your code. Use the following command to make it ready. But make sure you run the command inside your working directory:

tsc --init
# Or
npx tsc --init

By now, you should have tsconfig.json ready. You will have two options to run this app: ts-node or tsc.

With ts-node, you don’t need to build typescript code to TypeScript. ts-node jut must point to your Express PostgreSQL API’s main (entry) file. In this example, you will use the scr/index.ts file. Therefore, edit the package.json file as follows:

"dev": "ts-node src/index.ts"

Option Two uses tsc. This means your tsconfig.json needs rootDir and outDir set. Add them in tsconfig.json:

   "rootDir": "./src", 
    "outDir": "./dist"

Now edit the package.json file with tsc as follows:

"dev": "npx tsc && node dist/index.js",

If you want to use Nodemon, use the following command with concurrently

"dev": "concurrently \"tsc --watch\" \"nodemon dist/index.js\"",

Creating TypeScript PostgreSQL Connection with Node.js and pg

A TypeScript PostgreSQL connection should be simple. The only thing that changes is how you import pg to your file. In this case, create an src folder and add a database.js file.

In your database.js, create a Pool connection to PostgreSQL using pg library as follows:

import { Pool } from 'pg';

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

In this simple example:

• Make sure the user matches your PostgreSQL user.
• Add your specific password for this user.
• Ensure you have the database Task created within PostgreSQL.

How to add Data to PostgreSQL with Express and TypeScript

Once the connection is ready, the next step is adding data to PostgreSQL. At this point, you must have the database table prepared with the correct attributes.

Go to PostgreSQL, make sure you are on the Task database, and execute the following query:

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  completed BOOLEAN DEFAULT false
);

Your table should be ready as follows:

In your src folder, create a controllers directly and add an index.controllers.ts file.

Proceed and add TypeScript PostgreSQL connections in your database.ts file, Express and pg as follows:

// Import necessary types from Express
import { Request, Response } from 'express';
import { QueryResult } from 'pg';
// Import the PostgreSQL connection pool from database.ts
import { pool } from '../database';

Create a method to Insert new items to your database table using PostgreSQL and Express as follows:

// Controller for a new task
export const createTask = async (req: Request, res: Response): Promise<Response> => {
  // Extract task details from the request body
  //(title, description, completed)
  const { title, description, completed } = req.body;
  // Execute a SQL INSERT statement
  await pool.query('INSERT INTO tasks (title, description, completed) VALUES ($1, $2, $3)', [title, description, completed]);
  // Send a JSON response to the client
  return res.status(201).json({
    // Task Created successfully
    message: 'Task created successfully',
    task: {
      title,
      description,
      completed,
    }
  });
};

Getting Data with TypeScript and PostgreSQL

Like adding data, you need a method that executes a SELECT command to fetch data from the database to your Express.

In the same index.controllers.ts file, you will create the following:

• A SELECT query to execute a PostgreSQL query to select all tasks
• A SELECT with condition query. This will extract the task ID from the request parameters and return the selected Task.

Now add these two logics as follows:

// Get all tasks
export const getTasks = async (req: Request, res: Response): Promise<Response> => {
  try {
    // Execute a PostgreSQL query to select all tasks
    const response: QueryResult = await pool.query('SELECT * FROM tasks');

    // Return a JSON response with the retrieved tasks
    return res.status(200).json(response.rows);
  } catch (error) {
    // Handle errors, log them, and return an internal server error response
    console.error(error);
    return res.status(500).json('Internal Server error');
  }
}

// Get a task by ID
export const getTaskById = async (req: Request, res: Response): Promise<Response> => {
  // Extract the task ID from the request parameters
  const id = parseInt(req.params.id);

  try {
    // Execute a PostgreSQL query to select a task by ID
    const response: QueryResult = await pool.query('SELECT * FROM tasks WHERE id = $1', [id]);

    // Return a JSON response with the retrieved task
    return res.json(response.rows);
  } catch (error) {
    // Handle errors, log them, and return an internal server error response
    console.error(error);
    return res.status(500).json('Internal Server error');
  }
}

Updating and Deleting Tasks from PostgreSQL

The final step is to be able to delete or update a task available on your table.

// Update a task by ID
export const updateTask = async (req: Request, res: Response): Promise<Response> => {
  // Extract task ID from request parameters
  const id = parseInt(req.params.id);

  // Extract updated task details from request body
  const { title, description, completed } = req.body;

  try {
    // Execute a PostgreSQL query to update the task by ID
    await pool.query('UPDATE tasks SET title = $1, description = $2, completed = $3 WHERE id = $4', [title, description, completed, id]);

    // Return a JSON response with the updated task details
    return res.json({
      message: 'Task updated successfully',
      task: {
        id,
        title,
        description,
        completed,
      },
    });
  } catch (error) {
 
    console.error(error);
    return res.status(500).json('Internal Server error');
  }
}

// Delete a task by ID
export const deleteTask = async (req: Request, res: Response): Promise<Response> => {
  // Extract task ID from request parameters
  const id = parseInt(req.params.id);

  try {
    // Execute a PostgreSQL query to delete the task by ID
    await pool.query('DELETE FROM tasks WHERE id = $1', [id]);

    // Return a JSON response indicating successful deletion
    return res.status(200).json(`Task ${id} deleted successfully`);
  } catch (error) {
    console.error(error);
    return res.status(500).json('Internal Server error');
  }
}

Serving TypeScript and PostgreSQL with Node.js and Express

All CRUD methods are ready. It’s time to create routes that TypeScript and Node.js will use to execute the CRUD with Express.

In your src directory, create an index.ts file and use Express to execute the PostgreSQL pg connection and the CRUD methods as we have created above:

// Import necessary modules and types
import express, { Router } from 'express';
import { getTasks, getTaskById, createTask, deleteTask, updateTask } from './controllers/index.controllers';

// Create an Express application
const app = express();

// Create a Router instance for handling task-related routes
const taskRouter = Router();

// Configure middleware for parsing JSON and URL-encoded data
app.use(express.json());
app.use(express.urlencoded({ extended: false }));

// Define routes for task operations using the taskRouter
taskRouter.get('/tasks', getTasks);
taskRouter.get('/tasks/:id', getTaskById);
taskRouter.post('/tasks', createTask);
taskRouter.put('/tasks/:id', deleteTask);
taskRouter.delete('/tasks/:id', updateTask);

// Use the taskRouter for paths starting with '/api'
app.use(taskRouter);

// Set up the Express application to listen on port 3000
const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Testing This Node.js TypeScript Express and PostgreSQL Example

Now run your server using the following command (This should be based on the command selected to use):

npm run dev

I will test this API using Postman as follows:

• POST Request (Create Task):

Set the request type to POST with URL http://localhost:3000/tasks. Go to the “Body” tab, select “raw,” and enter JSON data to create a task.

{
  "title": "TypeScript and PostgreSQL",
  "description": "Build a TypeScript Express PostgreSQL/pg NodeJS API example",
  "completed": false
}

Click Send and receive a success message and details of the created task.

These changes should be available in the Task database (table tasks) as well:

Go ahead and add more data. Then send GET to http://localhost:3000/tasks. to check the added tasks.

Note that the same GET Request can be Get Task by ID. For example, http://localhost:3000/tasks/1, But replace 1 with the task you want to get. The same applies to Update and Delete. Only that for delete, you will send a Delete request, and for update, you will send a PUT request with the update payload, such that:

{
  "title": "Updated Task",
  "description": "Updated description",
  "completed": true
}

Conclusion

This tutorial taught you how to connect to PostgreSQL using the pg library alongside Node.js, Express, and TypeScript. I hope you can now run PostgreSQL (pg) Type checks for TypeScript within Node.js and express your server with Express. Check the whole code setup on GitHub