Working with Databases in TypeScript: PostgreSQL and MongoDB

Working with Databases in TypeScript: PostgreSQL and MongoDB

TypeScript is a powerful programming language that adds static typing to JavaScript. When developing modern web applications, working with databases is an essential part. In this article, we will look at how to effectively work with two popular databases — relational PostgreSQL and document-oriented MongoDB — using TypeScript.

1. Environment Setup and ORM Selection

Before starting, you need to set up the project. Create a new Node.js project with TypeScript support:

mkdir ts-database-democd ts-database-demonpm init -ynpm install typescript @types/node ts-node --save-devnpx tsc --init

For working with databases, we will use popular libraries:

• Prisma — a modern ORM for PostgreSQL (and other SQL databases)
• Mongoose — an ODM for MongoDB with excellent TypeScript support

Install the necessary packages:

npm install prisma @prisma/client mongoosenpm install @types/mongoose --save-dev

2. Working with PostgreSQL via Prisma

Prisma is an ORM that automatically generates TypeScript types based on your database schema. This ensures complete type safety.

2.1. Prisma Setup

Initialize Prisma in the project:

npx prisma init

In the prisma/schema.prisma file, define the data model:

generator client {  provider = "prisma-client-js"}

datasource db {  provider = "postgresql"  url      = env("DATABASE_URL")}

model User {  id        Int      @id @default(autoincrement())  email     String   @unique  name      String?  posts     Post[]  createdAt DateTime @default(now())  updatedAt DateTime @updatedAt}

model Post {  id        Int      @id @default(autoincrement())  title     String  content   String?  published Boolean  @default(false)  author    User     @relation(fields: [authorId], references: [id])  authorId  Int  createdAt DateTime @default(now())  updatedAt DateTime @updatedAt}

Create a .env file with the connection string:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Run the migration:

npx prisma migrate dev --name init

2.2. CRUD Operations with TypeScript

Now let's write typed code for working with the database. Create a file src/postgres.ts:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

interface CreateUserInput {  email: string;  name?: string;}

interface CreatePostInput {  title: string;  content?: string;  authorId: number;}

// Create a userasync function createUser(data: CreateUserInput) {  return await prisma.user.create({    data: {      email: data.email,      name: data.name,    },  });}

// Get all users with postsasync function getUsersWithPosts() {  return await prisma.user.findMany({    include: {      posts: true,    },  });}

// Create a postasync function createPost(data: CreatePostInput) {  return await prisma.post.create({    data: {      title: data.title,      content: data.content,      authorId: data.authorId,    },  });}

// Update a userasync function updateUser(id: number, data: Partial<CreateUserInput>) {  return await prisma.user.update({    where: { id },    data,  });}

// Delete a userasync function deleteUser(id: number) {  return await prisma.user.delete({    where: { id },  });}

// Example usage(async () => {  try {    const user = await createUser({      email: 'ivan@example.com',      n