How to Connect GraphQL API & Express JS backend with MySQL/PgSQL database?
Published: 2024-06-04 05:31:54

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**:
   ```bash
   mkdir project-name
   cd project-name
   npm init -y
   ```
2. **Install Required Packages**:
   ```bash
   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:
   ```json
   {
     "type": "mysql",
     "host": "localhost",
     "port": 3306,
     "username": "root",
     "password": "password",
     "database": "test",
     "synchronize": true,
     "logging": false,
     "entities": [
       "src/entity/**/*.ts"
     ]
   }
   ```
   For PostgreSQL:
   ```json
   {
     "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**:
   ```bash
   mkdir -p src/entity src/resolvers
   ```

2. **Create the User Entity**:
   Create `src/entity/User.ts`:
   ```typescript
   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`:
   ```typescript
   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`:
   ```typescript
   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**:
   ```bash
   npm install typescript ts-node @types/node @types/express
   ```

2. **Add TypeScript Configuration**:
   Create `tsconfig.json`:
   ```json
   {
     "compilerOptions": {
       "target": "ES6",
       "module": "commonjs",
       "strict": true,
       "esModuleInterop": true,
       "skipLibCheck": true,
       "outDir": "./dist"
     },
     "include": ["src"]
   }
   ```

3. **Compile and Run the Server**:
   ```bash
   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**:
  ```graphql
  {
    users {
      id
      name
      email
    }
  }
  ```

- **Create a New User**:
  ```graphql
  mutation {
    createUser(name: "John Doe", email: "john.doe@example.com") {
      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.