PostgreSQL is a popular open-source relational database management system, widely used in enterprise and web applications. TypeScript, on the other hand, is a superset of JavaScript that adds optional static typing and other features to the language. In this tutorial, we'll explore how to create models in PostgreSQL using TypeScript.

Before we dive into creating models in PostgreSQL with TypeScript, make sure you have the following tools and technologies installed on your system:

  • PostgreSQL database server
  • Node.js runtime environment
  • TypeScript compiler


Step 1: Setting up the project

Create a new directory for your project and initialize a new Node.js project using the following command:

mkdir postgres-ts
cd postgres-ts
npm init -y

Next, install the required dependencies for the project using the following command:

npm install pg typescript @types/node --save

The pg package is the official PostgreSQL client for Node.js, while typescript and @types/node are required to compile TypeScript code.


Step 2: Configuring PostgreSQL

Create a new PostgreSQL database and a user with permissions to access the database. You can use the following commands to create a new database named mydb and a user named myuser:

createdb mydb
createuser myuser

Grant the necessary permissions to the user by running the following commands:

psql mydb
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
\q


Step 3: Creating the model

Create a new file named user.model.ts in the project directory and add the following code:

import { Pool, PoolClient } from 'pg';

export interface User {
  id: number;
  name: string;
  email: string;
}

export class UserModel {
  private pool: Pool;

  constructor() {
    this.pool = new Pool({
      user: 'myuser',
      host: 'localhost',
      database: 'mydb',
      password: 'mypassword',
      port: 5432,
    });
  }

  async getUsers(): Promise<User[]> {
    const client: PoolClient = await this.pool.connect();
    try {
      const result = await client.query('SELECT * FROM users');
      return result.rows as User[];
    } finally {
      client.release();
    }
  }

  async createUser(user: User): Promise<User> {
    const client: PoolClient = await this.pool.connect();
    try {
      const result = await client.query(
        `INSERT INTO users(name, email) VALUES($1, $2) RETURNING *`,
        [user.name, user.email]
      );
      return result.rows[0] as User;
    } finally {
      client.release();
    }
  }
}

This code defines an interface User that describes the structure of a user object, and a class UserModel that provides methods to interact with the PostgreSQL database.

The UserModel class uses the pg package to create a new connection pool to the database, and provides two methods:

  • getUsers(): retrieves all users from the users table and returns them as an array of User objects.
  • createUser(user: User): inserts a new user into the users table and returns the newly created User object.

Note that the getUsers() and createUser(user: User) methods use async/await syntax to perform asynchronous database operations.


Step 4: Compiling the code

To compile the TypeScript code into JavaScript, create a new file named tsconfig.json in the project directory and add the following code:

{
  "compilerOptions": {
    "target": "es6",
"module": "commonjs",
"outDir": "dist",
"rootDir": "src",
"sourceMap": true
},
"include": ["src/**/*.ts"],
"exclude": ["node_modules"]
}


This configuration file specifies the target version of JavaScript, the output directory for the compiled code, and the directories to include and exclude from the compilation process.

Next, create a new directory named `src` in the project directory and move the `user.model.ts` file into it. Then, create a new file named `index.ts` in the `src` directory and add the following code:

import { UserModel } from './user.model';

async function main() {
const userModel = new UserModel();
const users = await userModel.getUsers();
console.log(users);
}

main().catch((err) => console.error(err));

This code imports the `UserModel` class from the `user.model.ts` file and uses it to retrieve all users from the database and log them to the console.

Finally, compile the TypeScript code into JavaScript using the following command: 

npx tsc


This will generate a new `dist` directory in the project directory, containing the compiled JavaScript code.

Step 5: Running the code

To run the code, use the following command: 

node dist/index.js

This will execute the `main()` function and log the users retrieved from the database to the console.

Congratulations! You've just created a model in PostgreSQL using TypeScript. You can extend this example by adding more methods to the `UserModel` class, such as updating and deleting users, or by creating models for other database tables.