How to Connect GraphQL API & Express JS backend with MySQL/PgSQL database?

Connecting a GraphQL API in an Express.js backend with a MySQL or PostgreSQL database involves several steps. Here’s a step-by-step guide for both databases:

Step 1: Set Up a New Express.js Project

1. Initialize a New Project:

   mkdir project-name
   cd project-name
   npm init -y

1. Install Required Packages:

   npm install express express-graphql graphql
   npm install typeorm reflect-metadata mysql2 pg
   npm install type-graphql class-validator

Step 2: Configure TypeORM

1. Create a ormconfig.json File: For MySQL:

   {
     "type": "mysql",
     "host": "localhost",
     "port": 3306,
     "username": "root",
     "password": "password",
     "database": "test",
     "synchronize": true,
     "logging": false,
     "entities": [
       "src/entity/**/*.ts"
     ]
   }

For PostgreSQL:

   {
     "type": "postgres",
     "host": "localhost",
     "port": 5432,
     "username": "user",
     "password": "password",
     "database": "test",
     "synchronize": true,
     "logging": false,
     "entities": [
       "src/entity/**/*.ts"
     ]
   }

Step 3: Define the User Entity

1. Create a Directory Structure:

   mkdir -p src/entity src/resolvers

1. Create the User Entity: Create src/entity/User.ts:

   import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
   import { ObjectType, Field, ID } from 'type-graphql';

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

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

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

Step 4: Create the User Resolver

1. Create the User Resolver: Create src/resolvers/UserResolver.ts:

   import { Resolver, Query, Mutation, Arg } from 'type-graphql';
   import { User } from '../entity/User';
   import { getRepository } from 'typeorm';

   @Resolver()
   export class UserResolver {
     private userRepository = getRepository(User);

     @Query(() => [User])
     async users() {
       return this.userRepository.find();
     }

     @Mutation(() => User)
     async createUser(@Arg('name') name: string, @Arg('email') email: string) {
       const user = this.userRepository.create({ name, email });
       return this.userRepository.save(user);
     }
   }

Step 5: Set Up the Express.js Server

1. Create the Server: Create src/index.ts:

   import 'reflect-metadata';
   import { createConnection } from 'typeorm';
   import express from 'express';
   import { graphqlHTTP } from 'express-graphql';
   import { buildSchema } from 'type-graphql';
   import { UserResolver } from './resolvers/UserResolver';

   async function bootstrap() {
     await createConnection();

     const schema = await buildSchema({
       resolvers: [UserResolver],
     });

     const app = express();

     app.use(
       '/graphql',
       graphqlHTTP({
         schema,
         graphiql: true,
       }),
     );

     app.listen(4000, () => {
       console.log('Server is running on http://localhost:4000/graphql');
     });
   }

   bootstrap();

Step 6: Compile TypeScript and Run the Server

1. Install TypeScript and ts-node:

   npm install typescript ts-node @types/node @types/express

1. Add TypeScript Configuration: Create tsconfig.json:

   {
     "compilerOptions": {
       "target": "ES6",
       "module": "commonjs",
       "strict": true,
       "esModuleInterop": true,
       "skipLibCheck": true,
       "outDir": "./dist"
     },
     "include": ["src"]
   }

1. Compile and Run the Server:

   npx ts-node src/index.ts

Step 7: Test the GraphQL API

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

Example GraphQL Queries and Mutations

• Query All Users:

  {
    users {
      id
      name
      email
    }
  }

• Create a New User:

  mutation {
    createUser(name: "John Doe", email: "[e-mail]") {
      id
      name
      email
    }
  }

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

Disclaimer: This content is generated by AI.