GraphQL

Connecting to a Database

20 min Lesson 8 of 35

Connecting GraphQL to a Database

Real-world GraphQL servers need to fetch data from databases. In this lesson, we'll learn how to connect GraphQL to a database using Prisma ORM and implement CRUD operations.

Why Use an ORM?

ORMs (Object-Relational Mapping) tools like Prisma and Mongoose simplify database operations by:

Providing type-safe database queries
Handling database migrations
Generating TypeScript types from your database schema
Preventing SQL injection attacks
Supporting multiple databases (PostgreSQL, MySQL, SQLite, MongoDB)

Setting Up Prisma

Install Prisma and its dependencies:

npm install @prisma/client
npm install -D prisma

# Initialize Prisma
npx prisma init

This creates a prisma folder with a schema.prisma file:

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

Set your DATABASE_URL in the .env file created by Prisma. Example: DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Defining Data Models

Define your database schema in prisma/schema.prisma:

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
}

Running Migrations

Generate and apply database migrations:

# Create a migration
npx prisma migrate dev --name init

# Generate Prisma Client
npx prisma generate

Run npx prisma studio to open a visual database browser in your web browser. It's great for viewing and editing data during development.

Connecting Prisma to GraphQL

Create a Prisma client instance and add it to your GraphQL context:

// db.js
const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient();

module.exports = prisma;

// server.js
const { ApolloServer } = require('apollo-server-express');
const prisma = require('./db');

const server = new ApolloServer({
  typeDefs,
  resolvers,
  context: ({ req }) => {
    return {
      prisma, // Now available in all resolvers
      req
    };
  }
});

CRUD Resolvers with Prisma

Implement Create, Read, Update, Delete operations:

const typeDefs = `
  type User {
    id: Int!
    email: String!
    name: String
    posts: [Post!]!
    createdAt: String!
  }

  type Post {
    id: Int!
    title: String!
    content: String
    published: Boolean!
    author: User!
    createdAt: String!
  }

  type Query {
    users: [User!]!
    user(id: Int!): User
    posts: [Post!]!
    post(id: Int!): Post
  }

  type Mutation {
    createUser(email: String!, name: String): User!
    updateUser(id: Int!, email: String, name: String): User!
    deleteUser(id: Int!): User!
    createPost(title: String!, content: String, authorId: Int!): Post!
    updatePost(id: Int!, title: String, content: String, published: Boolean): Post!
    deletePost(id: Int!): Post!
  }
`;

const resolvers = {
  Query: {
    users: async (parent, args, { prisma }) => {
      return await prisma.user.findMany();
    },
    user: async (parent, { id }, { prisma }) => {
      return await prisma.user.findUnique({ where: { id } });
    },
    posts: async (parent, args, { prisma }) => {
      return await prisma.post.findMany();
    },
    post: async (parent, { id }, { prisma }) => {
      return await prisma.post.findUnique({ where: { id } });
    }
  },

  Mutation: {
    createUser: async (parent, { email, name }, { prisma }) => {
      return await prisma.user.create({
        data: { email, name }
      });
    },
    updateUser: async (parent, { id, email, name }, { prisma }) => {
      return await prisma.user.update({
        where: { id },
        data: { email, name }
      });
    },
    deleteUser: async (parent, { id }, { prisma }) => {
      return await prisma.user.delete({ where: { id } });
    },
    createPost: async (parent, { title, content, authorId }, { prisma }) => {
      return await prisma.post.create({
        data: { title, content, authorId }
      });
    },
    updatePost: async (parent, { id, title, content, published }, { prisma }) => {
      return await prisma.post.update({
        where: { id },
        data: { title, content, published }
      });
    },
    deletePost: async (parent, { id }, { prisma }) => {
      return await prisma.post.delete({ where: { id } });
    }
  },

  User: {
    posts: async (parent, args, { prisma }) => {
      return await prisma.post.findMany({
        where: { authorId: parent.id }
      });
    }
  },

  Post: {
    author: async (parent, args, { prisma }) => {
      return await prisma.user.findUnique({
        where: { id: parent.authorId }
      });
    }
  }
};

Security Warning: Always validate input data before passing it to database queries. Add authorization checks to ensure users can only modify their own data.

Advanced Prisma Queries

Prisma supports complex filtering, sorting, and pagination:

const resolvers = {
  Query: {
    // Filtering
    publishedPosts: async (parent, args, { prisma }) => {
      return await prisma.post.findMany({
        where: { published: true }
      });
    },

    // Sorting
    recentPosts: async (parent, args, { prisma }) => {
      return await prisma.post.findMany({
        orderBy: { createdAt: 'desc' },
        take: 10
      });
    },

    // Pagination
    paginatedPosts: async (parent, { skip, take }, { prisma }) => {
      return await prisma.post.findMany({
        skip: skip || 0,
        take: take || 10,
        orderBy: { createdAt: 'desc' }
      });
    },

    // Search
    searchPosts: async (parent, { query }, { prisma }) => {
      return await prisma.post.findMany({
        where: {
          OR: [
            { title: { contains: query } },
            { content: { contains: query } }
          ]
        }
      });
    }
  }
};

Using Mongoose (Alternative for MongoDB)

If you're using MongoDB, Mongoose is a popular choice:

const mongoose = require('mongoose');

// Connect to MongoDB
mongoose.connect(process.env.MONGODB_URI);

// Define schema
const userSchema = new mongoose.Schema({
  email: { type: String, required: true, unique: true },
  name: String,
  createdAt: { type: Date, default: Date.now }
});

const User = mongoose.model('User', userSchema);

// Use in resolvers
const resolvers = {
  Query: {
    users: async () => await User.find(),
    user: async (parent, { id }) => await User.findById(id)
  },
  Mutation: {
    createUser: async (parent, { email, name }) => {
      const user = new User({ email, name });
      return await user.save();
    }
  }
};

Prisma vs Mongoose:

Prisma: Type-safe, supports SQL & NoSQL, auto-generates TypeScript types, modern syntax
Mongoose: MongoDB-specific, flexible schema design, rich plugin ecosystem, mature

Practice Exercise:

Set up Prisma with a SQLite database
Define models for Category and Product (one-to-many relationship)
Create migrations and generate the Prisma Client
Implement GraphQL queries to fetch all products and products by category
Implement mutations to create, update, and delete products
Add a search query that finds products by name