Developers often build high-performing, scalable applications using GraphQL and PostgreSQL to define data structure and achieve reliability, scalability, and high performance. First, however, selecting the appropriate framework to simplify and streamline the development process is crucial while building a GraphQL API with PostgreSQL.
This blog will explore the top tools for building GraphQL APIs with PostgreSQL, including Hasura, Postgraphile, Prisma, and GraphQL Nexus. Each tool comes with its unique features, benefits, and limitations. We'll delve into these features, advantages, and disadvantages to assist you in making an informed decision about which tool to choose for your project.
Whether you're an experienced developer or new to GraphQL and PostgreSQL, this blog will help you find the appropriate tool to meet your requirements. By the end of this blog, you'll better understand each tool and which would be the ideal fit for your project.
Choosing the appropriate tool is critical to the success of your project, whether you're developing a new API or enhancing an existing one. By selecting the right tool to leverage the power of GraphQL and PostgreSQL, you can build fast, scalable, and dependable APIs that can support your application's growth and evolution.
Let's look at the following:-
- What is GraphQL?
- Why do you add GraphQL API to the Postgres database?
- Best Tools for GraphQL APIs with PostgreSQL
- Factors for Choosing the Right GraphQL Tool
What is GraphQL?
With GraphQL, developers may create requests that combine data from several sources into a single API query as an alternative to REST. It can even be used with the GraphQL integrated development environment (IDE).
Additionally, API maintainers can add or remove fields without affecting already-running queries, thanks to GraphQL. The GraphQL standard guarantees that APIs behave in predictable ways to clients regardless of the methods developers use to design them.
Why GraphQL?
- Improved flexibility: Clients can specify precisely what data they need and how it should be formatted, simplifying development and reducing the number of round-trips required to retrieve data.
- Strongly typed schema: The schema provides a strongly typed contract between the client and server, which can help catch errors early and provide better documentation for APIs.
- Improved performance: GraphQL can reduce over-fetching and under-fetching of data, which can improve performance by reducing the amount of data that needs to be transferred over the network.
- Reduced network requests: Because the client can specify what data it needs, GraphQL can reduce the number of network requests required to retrieve all the necessary data.
- Simplified development: GraphQL allows developers to work with a single API endpoint rather than multiple endpoints for different types of data, reducing complexity and improving the codebase's maintainability.
- Better client experiences: Because GraphQL allows clients to specify precisely what data they need, they can receive only the data they need and nothing more, which can improve the user experience by reducing unnecessary data and increasing the speed of the application.
- Easier versioning: Because the schema provides a strongly typed contract between the client and server, it can be easier to version APIs without breaking existing clients.
- More robust security: Because clients can only access the data they specify in their queries, GraphQL can provide more robust security than traditional REST APIs, which can be vulnerable to over fetching and other security issues.
Why do you add GraphQL API to the Postgres database?
Adding a GraphQL API to a Postgres database can offer several advantages. Postgres provides support for keys, functions, and user-defined data types of SQL and is commonly used for data storage and warehousing of web and analytical applications.
Meanwhile, GraphQL is a query language for APIs that allows clients to request only the required information and nothing more. Furthermore, with runtime for GraphQL, API requests can be answered using the current data in the Postgres database. This simplifies updating APIs over time and supports robust developer tools.
Additionally, when a GraphQL API is added to the Postgres database, it becomes possible to create the finest possible query out of each GraphQL query. This is because it's possible to specify to which table each GraphQL type belongs.
This can be helpful for those who want to use something other than SQL directly but to construct the majority of the API server themselves. Adding a GraphQL API to a Postgres database can improve querying efficiency and flexibility, simplify development, and provide a strongly typed schema.
Best Tools for GraphQL APIs with PostgreSQL
There are two ways of adding GraphQL APIs to the PostgreSQL database
1. Database Focussed Approach
Database-focused solutions aim to do away with most configuration and schema setups. Instead, they will examine the structure of our database and offer us types and endpoints. As a result, they can produce efficient resolvers for us since they know the database's structure, preventing us from encountering the N+1 query problem.
i.) Hasura
Hasura is an open-source, fully managed GraphQL API engine that provides real-time data synchronization and automatic CRUD APIs for PostgreSQL databases. It is one of the most popular and mature GraphQL engines with many features and integrations.
# hasura.yaml
version: '2'
metadata:
databases:
- name: mydatabase
... # Database configuration
actions:
- name: get_user
definition:
kind: sql
sql: |-
SELECT * FROM public.users WHERE id = $1
input_arguments:
- name: id
type: uuid
# hasura metadata applySome of its key features include:
- Real-time GraphQL APIs: Hasura automatically generates GraphQL APIs that update in real time when there are changes in the underlying database.
- Role-based access control: Hasura provides granular access control at the field and row level, allowing you to restrict access to sensitive data.
- Event triggers: Hasura allows you to trigger serverless functions or webhooks in response to database events, such as inserts, updates, and deletes.
- Custom business logic: Hasura provides a JavaScript-based serverless platform for writing custom business logic and authentication.
Advantages:
- Easy to set up and use.
- Provides real-time data synchronization.
- Automatic generation of CRUD APIs for PostgreSQL databases.
- Support for event triggers and custom business logic.
Disadvantages:
- Limited support for other databases.
- Some features, such as role-based access control, require a paid subscription.
ii.) Postgraphile
Postgraphile is an open-source tool that generates a GraphQL API from a PostgreSQL database schema. It is highly customizable and can be extended using plugins.
# Install postgraphile globally
npm install -g postgraphile
# Generate GraphQL API
postgraphile --connection postgres://user:password@localhost:5432/mydatabase --schema public --watchSome of its key features include:
- Automatic GraphQL schema generation: Postgraphile generates a GraphQL schema based on the structure of your PostgreSQL database.
- Plugin architecture: Postgraphile can be extended using a wide range of plugins, allowing you to add custom functionality.
- Dynamic filtering and sorting: Postgraphile allows you to dynamically apply filters and sorting to your GraphQL queries.
Advantages:
- Generates GraphQL APIs automatically.
- Highly customizable through plugins.
- Support for dynamic filtering and sorting.
Disadvantages:
- Steep learning curve.
- Limited support for real-time updates.
iii.) Prisma
Prisma is a modern data access layer for building GraphQL APIs. It supports PostgreSQL, MySQL, and MongoDB databases and provides a powerful CLI for generating a GraphQL schema and client.
// Prisma Schema (schema.prisma)
model User {
id Int @id @default(autoincrement())
name String
email String @unique
}
// Query in GraphQL
query {
users {
id
name
email
}
}Some of its key features include:
- Type-safe database access: Prisma generates a type-safe client for accessing your database, ensuring that your queries are always correct at compile time.
- Automatic GraphQL schema generation: Prisma generates a GraphQL schema based on your database schema.
- Powerful CLI: Prisma's CLI provides a wide range of tools for managing your database schema and generating a GraphQL client.
Advantages:
- Type-safe database access.
- Automatic generation of GraphQL schema.
- Powerful CLI for managing database schema and generating a GraphQL client.
Disadvantages:
- Limited support for other databases.
- Steep learning curve.
iv.) GraphQL Nexus
GraphQL Nexus is a code-first approach to building GraphQL APIs in TypeScript. It provides a DSL for defining your GraphQL schema and automatically generates resolvers based on your types.
// Using Nexus with Prisma
const { makeSchema } = require('nexus');
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
const schema = makeSchema({
types: [
objectType({
name: 'User',
definition(t) {
t.model.id();
t.model.name();
t.model.email();
},
}),
],
});
// Use schema with Apollo Server or other GraphQL serverSome of its key features include:
- Code-first approach: GraphQL Nexus allows you to define your GraphQL schema using a TypeScript DSL.
- Automatic resolver generation: GraphQL Nexus generates resolvers for your GraphQL API based on your types.
- Strongly typed: GraphQL Nexus ensures your API is always type-safe at compile time.
Advantages:
- Code-first approach.
- Automatic resolver generation.
- Strongly typed.
Disadvantages:
- Limited support for other databases.
- Less mature than other GraphQL engines.
2. Schema Focussed Approach
The following three solutions address frequent issues but take a slightly more traditional approach, require manual schema construction, and don't include many extras. They need Node.js as well because they are standard Node.js libraries.
i.) Node.js API starter kit
Node.js API starter kit is a comprehensive set of tools and libraries for building robust and scalable Node.js APIs. It includes pre-built templates, boilerplate code, and other resources that can be used to quickly create RESTful or GraphQL APIs.
// Sample Node.js GraphQL API with Express
const express = require('express');
const { ApolloServer, gql } = require('apollo-server-express');
const typeDefs = gql`
type Query {
hello: String
}
`;
const resolvers = {
Query: {
hello: () => 'Hello, GraphQL!',
},
};
const server = new ApolloServer({ typeDefs, resolvers });
const app = express();
server.applyMiddleware({ app });
app.listen({ port: 4000 }, () =>
console.log(`Server ready at http://localhost:4000${server.graphqlPath}`)
);Features:
- Pre-built templates and boilerplate code for building Node.js APIs
- Tools for user authentication and authorization
- API testing tools
- Error handling and logging
- Database integration
Advantages:
- Faster development time
- Improved code quality
- Easier maintenance
Disadvantages:
- May require a learning curve for new users
- Limited customization options
ii.) Sequelize
With Sequelize, working with databases like MySQL, MariaDB, SQLite, PostgreSQL, and more is simple. Sequelize is a Node. An object-relational mapper may carry out tasks like processing database records by displaying the data as objects.
A promise-based Node.js ORM for Postgres and other databases is called Sequelize. It will assist us in connecting to and setting up the database. Some people dislike ORMs because of the extra work and complexity that their queries may need.
However, they perform a better job of serving as an abstraction for database functionality, enabling me to swiftly migrate from one database to another, a trend with modern applications where requirements might change at any time.
// Sequelize Model
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('postgres://user:password@localhost:5432/mydatabase');
const User = sequelize.define('User', {
name: {
type: DataTypes.STRING,
allowNull: false,
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
},
});
// Query using Sequelize
const users = await User.findAll();Features:
- Node.js ORM (Object-Relational Mapping) library
- Provides an easy-to-use interface for interacting with SQL databases, including Postgres
- Supports features such as associations, transactions, and migrations
Advantages:
- Simplifies working with SQL databases
- Provides a clean API for performing database operations
- Supports many different databases
Disadvantages:
- May not be suitable for complex queries or large datasets
- Can lead to performance issues if not used correctly
iii.) Join Monster
By combining the data from several GraphQL sources, GraphQL Joins lets you build a uniform GraphQL API. You may federate your queries and changes across several GraphQL sources as though they were a single GraphQL schema with GraphQL Joins. No additional code or modifications to the underlying APIs are required.
In keeping with the fundamental tenets of GraphQL, Join Monster only retrieves the information you require. It examines your schema definition, reads the parsed GraphQL query, and then automatically creates the SQL that only retrieves what is necessary to complete the request.
A single batch request is made for all resources' data retrieval. There is no need to manually build several SQL queries to obtain the appropriate quantity of data for all the different sorts of GraphQL queries.
// GraphQL Query using Join Monster
const { GraphQLObjectType, GraphQLList, GraphQLInt } = require('graphql');
const joinMonster = require('join-monster');
const UserType = new GraphQLObjectType({
name: 'User',
fields: {
id: { type: GraphQLInt },
name: { type: GraphQLString },
email: { type: GraphQLString },
},
});
const QueryType = new GraphQLObjectType({
name: 'Query',
fields: {
users: {
type: new GraphQLList(UserType),
resolve: (parent, args, context, resolveInfo) => {
return joinMonster(resolveInfo, context, sql => {
return context.db.query(sql);
});
},
},
},
});Features:
- GraphQL library that optimizes SQL queries
- Generates optimized SQL queries for GraphQL requests
- Supports batching and caching of queries
Advantages:
- Can significantly improve GraphQL query performance
- Allows for efficient data fetching from a SQL database
- Works well with existing SQL database schemas
Disadvantages:
- May require additional setup and configuration compared to other GraphQL libraries
- Has a steeper learning curve for new users
Factors for Choosing the Right GraphQL Tool
Choosing the right tool to build your GraphQL API with PostgreSQL can be daunting. With so many options available, it's essential to consider several critical factors before making a decision.
- Project Requirements: The first step in choosing the right tool is determining your project's requirements. Consider factors such as the project size, the number of users, the complexity of the data model, and the level of customization needed. Each tool has its strengths and weaknesses; some may be better suited for specific projects than others.
- Learning Curve: Another essential factor is the learning curve associated with each tool. Some frameworks and libraries may require more time and effort to learn and implement, while others may have a shallower learning curve. Choosing a tool your development team is comfortable with and can quickly work with is essential.
- Community Support: When choosing a tool, it's essential to consider its community support level. A strong and active community can provide valuable resources, documentation, and support, making overcoming challenges and finding solutions to problems more accessible.
- Performance: Performance is a critical factor to consider when building any API. Each tool has its performance characteristics; some may be better suited for handling high volumes of data and user traffic than others. Test each tool thoroughly to ensure it can handle your project's specific performance requirements.
- Integration with other tools: It's also important to consider how each tool integrates with other tools and technologies you may use in your project. For example, if you're using a specific front-end framework, you'll want to ensure that the GraphQL framework you choose has strong integration capabilities.
Conclusion
This article has shown various methods of adding graphQL to the Postgres database. Under database focused approach, we have Hasura, postgraphile, prisma, and graphQL Nexus. Under schema focused approach, we have node js, sequelize and join Monster.
The GraphQL API can assist us in handling communication between several microservices when we need to transition from a monolithic backend application to a microservice architecture by combining them into one GraphQL schema.
Every method has its advantages and disadvantages. However, even with its other benefits, GraphQL's most significant advantage is its developer experience. You can design, create, and launch features more quickly.
Database Monitoring with Atatus
Atatus provides you an in-depth perspective of your database performance by uncovering slow database queries that occur within your requests, and transaction traces to give you actionable insights. With normalized queries, you can see a list of all slow SQL calls to see which tables and operations have the most impact, know exactly which function was used and when it was performed, and see if your modifications improve performance over time.
Atatus benefit your business, providing a comprehensive view of your application, including how it works, where performance bottlenecks exist, which users are most impacted, and which errors break your code for your frontend, backend, and infrastructure.
