109 Connecting Resolvers to Database Models: A Practical Case Study

In modern software development, integration between the application layer and the database is the foundation of every scalable and robust application. One architecture that has been gaining popularity is GraphQL, which relies on a resolver structure to fetch or modify data from various sources—particularly databases. This article explores how to connect resolvers to database models using concrete examples, code, tables, and flow diagrams built with mermaid.

Warm-Up: What Is a Resolver?

Before we dive deeper, let’s get on the same page about resolvers. In the context of GraphQL, a resolver is a function responsible for handling requests (queries or mutations) from the user, then fetching, manipulating, and returning the appropriate data. Resolvers typically communicate with database models to retrieve up-to-date data.

Case Study: A Book Management System

Suppose we want to build a simple application to manage a collection of books. We’ll use a stack consisting of Node.js, Express, Sequelize (an ORM for PostgreSQL), and Apollo Server (a GraphQL implementation).

Database Model Structure

We’ll define the Book model using Sequelize as follows:

 1// models/book.js
 2const { DataTypes } = require('sequelize');
 3const sequelize = require('../config/database');
 4
 5const Book = sequelize.define('Book', {
 6  id: {
 7    type: DataTypes.INTEGER,
 8    primaryKey: true,
 9    autoIncrement: true
10  },
11  title: {
12    type: DataTypes.STRING,
13    allowNull: false
14  },
15  author: DataTypes.STRING,
16  published_year: DataTypes.INTEGER
17});
18
19module.exports = Book;

Book Table:

Flow Diagram of Resolver and Database Interaction

Let’s visualize what happens when a client requests data using a GraphQL query, with the following mermaid diagram:

sequenceDiagram
  participant Client
  participant Server
  participant Resolver
  participant Database

  Client->>Server: Send GraphQL query
  Server->>Resolver: Call the relevant resolver (e.g., books)
  Resolver->>Database: Query the list of books
  Database-->>Resolver: Return book data
  Resolver-->>Server: Send the resulting data
  Server-->>Client: Send JSON response

sequenceDiagram
  participant Client
  participant Server
  participant Resolver
  participant Database

  Client->>Server: Send GraphQL query
  Server->>Resolver: Call the relevant resolver (e.g., books)
  Resolver->>Database: Query the list of books
  Database-->>Resolver: Return book data
  Resolver-->>Server: Send the resulting data
  Server-->>Client: Send JSON response

Building the Resolver

In the resolvers folder, let’s create a simple resolver that connects to the Book model.

 1// resolvers/bookResolver.js
 2const Book = require('../models/book');
 3
 4const bookResolver = {
 5  Query: {
 6    books: async () => {
 7      try {
 8        const allBooks = await Book.findAll();
 9        return allBooks;
10      } catch (error) {
11        throw new Error(`Failed to fetch book data: ${error.message}`);
12      }
13    },
14    book: async (_, { id }) => {
15      try {
16        const book = await Book.findByPk(id);
17        if (!book) throw new Error('Book not found');
18        return book;
19      } catch (error) {
20        throw new Error(`Failed to fetch book data: ${error.message}`);
21      }
22    }
23  },
24  Mutation: {
25    addBook: async (_, { title, author, published_year }) => {
26      try {
27        const newBook = await Book.create({ title, author, published_year });
28        return newBook;
29      } catch (error) {
30        throw new Error(`Failed to add book: ${error.message}`);
31      }
32    }
33  }
34};
35
36module.exports = bookResolver;

Example GraphQL Schema

Now let’s connect this resolver to our GraphQL schema:

 1// schema/typeDefs.js
 2const { gql } = require('apollo-server-express');
 3
 4const typeDefs = gql`
 5  type Book {
 6    id: ID!
 7    title: String!
 8    author: String
 9    published_year: Int
10  }
11
12  type Query {
13    books: [Book]
14    book(id: ID!): Book
15  }
16
17  type Mutation {
18    addBook(title: String!, author: String, published_year: Int): Book
19  }
20`;
21
22module.exports = typeDefs;

Integrating the Resolver and the Database Layer

In essence, the technique for connecting a resolver to a database model works as follows:

Code Flow:

1. The query is received by the GraphQL server.
2. The resolver is called according to the query or mutation request.
3. The resolver fetches data using ORM model methods (e.g., findAll, findByPk, create in Sequelize).
4. The result is returned to the client in JSON format.

Simulation: End-to-End

1. Querying the List of Books

Request from the client:

1query {
2  books {
3    id
4    title
5    author
6    published_year
7  }
8}

Response from the server:

 1{
 2  "data": {
 3    "books": [
 4      {
 5        "id": "1",
 6        "title": "Clean Code",
 7        "author": "Robert Martin",
 8        "published_year": 2008
 9      },
10      {
11        "id": "2",
12        "title": "The Pragmatic Programmer",
13        "author": "Andy Hunt",
14        "published_year": 1999
15      }
16    ]
17  }
18}

2. Adding a New Book

Mutation:

1mutation {
2  addBook(title: "Refactoring", author: "Martin Fowler", published_year: 1999) {
3    id
4    title
5  }
6}

Best Practices and Notes

Connecting resolvers to database models should be done with attention to the following best practices:

1. Error Handling: Always handle errors from the ORM layer so that problem information is easy to debug.
2. Asynchronous Handling: Use async/await in resolvers to avoid callback hell and make debugging easier.
3. Separation of Concerns: Place database logic in the model or service layer, and keep resolvers lean.
4. Query Optimization: Avoid the N+1 problem by using batching techniques or a data loader, especially for many-to-many relationships.

Conclusion

Connecting resolvers to database models is a vital step in building applications with GraphQL. With the right practices—as shown in the case study above—we can build APIs that are scalable, robust, and easy to extend. Hopefully the flow diagrams, code, and simulations above give a concrete picture of how this process works in the real world.

If you’re interested in taking things further—from authentication and table relationships to more complex use cases—don’t hesitate to experiment and read up on the documentation for whichever ORM and GraphQL framework you’re using.

Happy coding! 🚀