How to connect GraphQL API & Nest JS backend with MySQL/PgSQL database?

Connecting a GraphQL NestJS backend with MySQL and PostgreSQL databases involves several steps. Here’s a step-by-step guide for both databases:

Step 1: Setup a New NestJS Project

1. Install Nest CLI:

   npm install -g @nestjs/cli

1. Create a New Project:

   nest new project-name

1. Navigate to the Project Directory:

   cd project-name

Step 2: Install Required Packages

1. Install TypeORM and GraphQL Packages:

   npm install @nestjs/typeorm typeorm @nestjs/graphql graphql apollo-server-express

1. Install Database Drivers: For MySQL:

   npm install mysql2

For PostgreSQL:

   npm install pg

Step 3: Configure TypeORM and GraphQL

1. Create a GraphQL Module Configuration: Open src/app.module.ts and configure the GraphQL module:

   import { Module } from '@nestjs/common';
   import { TypeOrmModule } from '@nestjs/typeorm';
   import { GraphQLModule } from '@nestjs/graphql';
   import { join } from 'path';
   import { UserModule } from './user/user.module';

   @Module({
     imports: [
       TypeOrmModule.forRoot({
         type: 'mysql', // or 'postgres'
         host: 'localhost',
         port: 3306, // or 5432 for PostgreSQL
         username: 'root', // or your PostgreSQL username
         password: 'password', // or your PostgreSQL password
         database: 'test',
         entities: [__dirname + '/**/*.entity{.ts,.js}'],
         synchronize: true,
       }),
       GraphQLModule.forRoot({
         autoSchemaFile: join(process.cwd(), 'src/schema.gql'),
       }),
       UserModule,
     ],
   })
   export class AppModule {}

Step 4: Define the User Entity

1. Create a User Entity: Create the src/user/user.entity.ts file:

   import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';
   import { ObjectType, Field, Int } from '@nestjs/graphql';

   @ObjectType()
   @Entity()
   export class User {
     @Field(type => Int)
     @PrimaryGeneratedColumn()
     id: number;

     @Field()
     @Column()
     name: string;

     @Field()
     @Column()
     email: string;
   }

Step 5: Create the User Service

1. Implement Service Logic: Open src/user/user.service.ts and implement the service methods:

   import { Injectable } from '@nestjs/common';
   import { InjectRepository } from '@nestjs/typeorm';
   import { Repository } from 'typeorm';
   import { User } from './user.entity';

   @Injectable()
   export class UserService {
     constructor(
       @InjectRepository(User)
       private usersRepository: Repository<User>,
     ) {}

     findAll(): Promise<User[]> {
       return this.usersRepository.find();
     }

     findOne(id: number): Promise<User> {
       return this.usersRepository.findOneBy({ id });
     }

     create(user: Partial<User>): Promise<User> {
       return this.usersRepository.save(user);
     }

     async update(id: number, user: Partial<User>): Promise<User> {
       await this.usersRepository.update(id, user);
       return this.usersRepository.findOneBy({ id });
     }

     async delete(id: number): Promise<void> {
       await this.usersRepository.delete(id);
     }
   }

Step 6: Create the User Resolver

1. Generate a Resolver: Use the Nest CLI to generate a resolver:

   nest g resolver user

1. Define Resolver Logic: Open src/user/user.resolver.ts and define the resolver logic:

   import { Resolver, Query, Mutation, Args, Int } from '@nestjs/graphql';
   import { UserService } from './user.service';
   import { User } from './user.entity';
   import { CreateUserInput } from './dto/create-user.input';

   @Resolver(of => User)
   export class UserResolver {
     constructor(private readonly userService: UserService) {}

     @Query(returns => [User])
     users() {
       return this.userService.findAll();
     }

     @Query(returns => User)
     user(@Args('id', { type: () => Int }) id: number) {
       return this.userService.findOne(id);
     }

     @Mutation(returns => User)
     createUser(@Args('createUserInput') createUserInput: CreateUserInput) {
       return this.userService.create(createUserInput);
     }

     @Mutation(returns => User)
     updateUser(
       @Args('id', { type: () => Int }) id: number,
       @Args('updateUserInput') updateUserInput: CreateUserInput,
     ) {
       return this.userService.update(id, updateUserInput);
     }

     @Mutation(returns => Boolean)
     async deleteUser(@Args('id', { type: () => Int }) id: number) {
       await this.userService.delete(id);
       return true;
     }
   }

Step 7: Create DTOs

1. Create DTOs for User Input: Create the src/user/dto/create-user.input.ts:

   import { InputType, Field } from '@nestjs/graphql';

   @InputType()
   export class CreateUserInput {
     @Field()
     name: string;

     @Field()
     email: string;
   }

Step 8: Update the User Module

1. Update the User Module: Open src/user/user.module.ts and update it to include the controller and service:

   import { Module } from '@nestjs/common';
   import { TypeOrmModule } from '@nestjs/typeorm';
   import { User } from './user.entity';
   import { UserService } from './user.service';
   import { UserResolver } from './user.resolver';

   @Module({
     imports: [TypeOrmModule.forFeature([User])],
     providers: [UserService, UserResolver],
   })
   export class UserModule {}

Step 9: Run the Application

1. Start the NestJS Application:

   npm run start:dev

Step 10: Test the GraphQL API

1. Access the GraphQL Playground: Navigate to http://localhost:3000/graphql to access the GraphQL playground and test your API by running queries and mutations.

This guide provides a foundational approach to creating a GraphQL API in NestJS connected to MySQL or PostgreSQL databases. You can further expand and customize it based on your application's requirements.

Support My Work ❤️

If you enjoy my content and find it valuable, consider supporting me by buying me a coffee. Your support helps me continue creating and sharing useful resources. Thank you!

Connect with Me 🌍

Let’s stay connected! You can follow me or reach out on these platforms:

🔹 YouTube – Tutorials, insights & tech content

🔹 LinkedIn – Professional updates & networking

🔹 GitHub – My open-source projects & contributions

🔹 Instagram – Behind-the-scenes & personal updates

🔹 X (formerly Twitter) – Quick thoughts & tech discussions

I’d love to hear from you—whether it’s feedback, collaboration ideas, or just a friendly hello!

Disclaimer

This content has been generated with the assistance of AI. While I strive for accuracy and quality, please verify critical information independently.