Integrating a PostgreSQL Database with GraphQL Server to Persist Data in a To-Do App

11 min readMar 2, 2023

GraphQL is a query language and runtime for APIs that was created by Facebook. It allows clients to ask for exactly what they need and nothing more, making it a powerful tool for developing efficient and flexible APIs.

GraphQL has become a popular alternative to the RESTful API in recent years due to its flexibility, performance, and ease of use. When combined with a backend server written in Go, GraphQL can provide a powerful and scalable solution for modern web applications. In this article, I will explore how to create a GraphQL server with a backend in Go, using the Go library ‘gqlgen’ and following a schema-first approach.

What is gqlgen?

“Gqlgen” is a popular Go library that provides tools for building GraphQL servers. It enables developers to generate type-safe code from a GraphQL schema, reducing the amount of boilerplate code required and allowing for rapid development. Gqlgen also provides features like resolver generation, input validation, and support for various Go web frameworks.

To use GraphQL in a Go project, use a package called gqlgen. gqlgen generates type-safe code based on my schema, reducing the likelihood of errors and making it easier to work with GraphQL.

Schema First Approach

When developing a GraphQL server, there are two approaches that can be used: schema first and code first. In the schema-first approach, the GraphQL schema is defined first, and then the server is built around it. This approach is useful when designing a server for an existing GraphQL API or when working with a team that includes front-end developers who are familiar with the GraphQL schema.

Project structure

A good project should be well structured.

go-graphql-todo/
├── build
│   └── go-graphql-todo
├── cmd
│   └── server
│       └── main.go
├── go.mod
├── go.sum
├── gqlgen.yml
├── internal
│   ├── database
│   │   └── db.go
│   ├── graphql
│   │   ├── generated
│   │   │   ├── generated.go
│   │   │   └── models_gen.go
│   │   ├── resolver.go
│   │   └── schema.graphql
│   └── models
│       ├── todo_repository.go
│       └── user.go
└── Makefile

The cmd/server contains the main file for my server application. This file should import and use the graphql package to run my GraphQL server.

The internal/database contains a file for initializing and connecting to a database. This directory could also contain additional files for defining database models and methods for querying and manipulating data.

The internal/graphql contains the resolver and schema files for my GraphQL server. The resolver.go file contains the resolver methods for each field in my schema. The schema.graphql file contains the GraphQL schema for my application.

By organizing my code in this way, I can separate my application logic into distinct packages and files. This makes my code more modular, easier to read and maintain, and promotes good programming practices. Additionally, this structure can help me to follow the Single Responsibility Principle, where each package has a clear and distinct responsibility.

Creating a GraphQL Server with Gqlgen

To create a GraphQL server with gqlgen, I first need to define a GraphQL schema. In this example, I have created a simple schema for a to-do list application. In the schema, I defined a “Todo” type with fields for the ID, title, and status of the completed to-do item, as well as queries and mutations for creating, updating, and deleting to-dos.

type Todo {
    id: ID!
    title: String!
    description: String!
    isCompleted: Boolean!
}

type DeleteTodoPayload {
    message: String!
}

input NewTodo {
    title: String!
    description: String!
    isCompleted: Boolean!
}

type Query {
    todos: [Todo!]!
}

type Mutation {
    createTodo(input: NewTodo!): Todo!
    updateTodo(id: ID!, input: NewTodo!): Todo!
    deleteTodo(id: ID!): DeleteTodoPayload!
}

This schema defines a Todo type with fields for id, title, description, and isCompleted. It also defines an input the object for creating new todos, as well as queries and mutations for fetching, creating, updating, and deleting todos.

With the schema defined, I can use gqlgen to generate the necessary Go code. I can install gqlgen using the following command:

go get github.com/99designs/gqlgen

Makefile

Of course, in order to make the project much easier to manage and for developers to have easier access to frequently used commands, it is also worth creating a Makefile that contains built-in commands that are frequently used by developers.

# Variables
APP_NAME = go-graphql-todo
VERSION = v1.0.0
GOFLAGS ?= $(GOFLAGS:)
BUILD_DIR ?= ./build
GO_FILES := $(shell find . -type f -name '*.go' -not -path "./vendor/*")

.PHONY: build clean run format test lint gqlgen

build:
 @echo "Building $(APP_NAME) $(VERSION)"
 mkdir -p $(BUILD_DIR)
 go build -ldflags="-X 'main.version=$(VERSION)'" $(GOFLAGS) -o $(BUILD_DIR)/$(APP_NAME) ./cmd/server

clean:
 @echo "Cleaning up..."
 rm -rf $(BUILD_DIR)

run:
 @echo "Running $(APP_NAME) $(VERSION)"
 $(BUILD_DIR)/$(APP_NAME)

run-dev:
 @echo "Running $(APP_NAME) $(VERSION) in development mode"

 go run $(GOFLAGS) ./cmd/server -config ./config.dev.yml

format:
 @echo "Formatting source code..."
 go fmt $(GO_FILES)

test:
 @echo "Running tests..."
 go test $(GOFLAGS) ./...

lint:
 @echo "Running linter..."
 golangci-lint run

install-gqlgen:
 @echo "Installing gqlgen..."
 go get -u github.com/99designs/gqlgen

gqlgen:
 @echo "Generating GraphQL code..."
 go run github.com/99designs/gqlgen generate

docker-build:
 @echo "Building $(APP_NAME) $(VERSION) in Docker"
 docker-compose build

docker-run:
 @echo "Running $(APP_NAME) $(VERSION) in Docker"
 docker-compose up

docker-stop:
 @echo "Stopping $(APP_NAME) $(VERSION) in Docker"
 docker-compose down

.PHONY: help
help:
 @echo "Available targets:"
 @echo "  build       Build the server application"
 @echo "  clean       Remove build artifacts"
 @echo "  run         Run the server application"
 @echo "  format      Format the source code"
 @echo "  test        Run tests"
 @echo "  lint        Run linter"
 @echo "  gqlgen      Generate GraphQL code"
 @echo "  help        Show this help message"

This Makefile includes targets for building, cleaning, running, formatting, testing, linting, and showing a help message. The build target creates a build directory and compiles the server application using the go build command. The run target runs the compiled server application. The format target formats the source code using go fmt. The test target runs all tests using go test. The lint target runs the linter using golangci-lint. The help target shows a help message with a description of each target.

This adds a new target called gqlgen which runs the gqlgen generate command using go run. The go run runs a Go program in a temporary directory and the gqlgen generate command generates the GraphQL code based on the schema file and resolver methods. The @echo statements are used to print informative messages when each target is run.

I have also attached below the YAML configuration for gqlgen, in the context of my project.

schema:
  - internal/graphql/schema.graphql
exec:
  filename: internal/graphql/generated/generated.go
  package: generated
model:
  filename: internal/graphql/generated/models_gen.go
  package: generated

schema: specifies the path to the GraphQL schema file.
exec: specifies the file name and package for the file in which the code elements for the resolvers and API structure will be generated.
model: specifies the file name and package for the file in which the models for the GraphQL types will be generated.

After setting up both the YAML configuration well, creating directories, and writing a correct Makefile, I am currently able to generate code with the following command without any problems:

make gqlgen

This command will generate a new “generated” directory containing the generated Go code for our GraphQL server. I can now write my server code using this generated code.

Writing the Server Code

I will start by creating a new Go file called “server.go”. In this file, I will define my main function, which will create a new GraphQL server using the “github.com/graphql-go/graphql” package and serve it on port 8080.

package main

import (
 "log"
 "net/http"

 "github.com/53jk1/go-graphql-todo/internal/graphql"
 "github.com/53jk1/go-graphql-todo/internal/graphql/generated"

 "github.com/99designs/gqlgen/graphql/handler"
 "github.com/99designs/gqlgen/graphql/playground"
 "github.com/go-chi/chi"
 "github.com/go-chi/chi/middleware"
)

func main() {
 router := chi.NewRouter()
 router.Use(middleware.Logger)

 srv := handler.NewDefaultServer(generated.NewExecutableSchema(generated.Config{Resolvers: &graphql.Resolver{}}))

 router.Handle("/", playground.Handler("GraphQL playground", "/query"))
 router.Handle("/query", srv)

 log.Println("listening on :8080")
 err := http.ListenAndServe(":8080", router)
 if err != nil {
  return
 }
}

In this code, I imported the necessary packages, including the code I generated, and created a new chi router with middleware for logging. I then created a new GraphQL server using the generated code and added it to my router. I also added a playground route to test my queries and mutations.

Now that I have configured the server code, I can start implementing resolvers for my schema. Resolvers are functions that provide data for each field in my GraphQL schema. I will be implementing my resolvers in a “resolver.go” file.

package graphql

import (
 "context"

 "github.com/53jk1/go-graphql-todo/internal/graphql/generated"
 "github.com/53jk1/go-graphql-todo/internal/models"
 _ "github.com/99designs/gqlgen/graphql"
)

type Resolver struct {
 todoRepo *models.TodoRepository
}

func NewResolver(todoRepo *models.TodoRepository) *Resolver {
 return &Resolver{todoRepo}
}

func (r *Resolver) Mutation() generated.MutationResolver {
 return &mutationResolver{r}
}

func (r *Resolver) Query() generated.QueryResolver {
 return &queryResolver{r}
}

type mutationResolver struct{ *Resolver }

func NewTodoFromModel(todo *models.Todo) *generated.Todo {
 return &generated.Todo{
  ID:          todo.ID,
  Title:       todo.Title,
  Description: todo.Description,
  IsCompleted: todo.IsCompleted,
 }
}

func NewTodoListFromModel(todos []*models.Todo) []*generated.Todo {
 var todoList []*generated.Todo
 for _, todo := range todos {
  todoList = append(todoList, NewTodoFromModel(todo))
 }
 return todoList
}

func (r *mutationResolver) CreateTodo(ctx context.Context, input generated.NewTodo) (*generated.Todo, error) {
 todo := &models.Todo{
  Title:       input.Title,
  Description: input.Description,
  IsCompleted: false,
 }
 if err := r.todoRepo.Create(todo); err != nil {
  return nil, err
 }
 return NewTodoFromModel(todo), nil
}

func (r *mutationResolver) UpdateTodo(ctx context.Context, id string, title *string, description *string, isCompleted *bool) (*generated.Todo, error) {
 todo, err := r.todoRepo.FindByID(id)
 if err != nil {
  return nil, err
 }
 if title != nil {
  todo.Title = *title
 }
 if description != nil {
  todo.Description = *description
 }
 if isCompleted != nil {
  todo.IsCompleted = *isCompleted
 }
 if err := r.todoRepo.Update(todo); err != nil {
  return nil, err
 }
 return NewTodoFromModel(todo), nil
}

func (r *mutationResolver) DeleteTodo(ctx context.Context, id string) (bool, error) {
 err := r.todoRepo.Delete(id)
 if err != nil {
  return false, err
 }
 return true, nil
}

type queryResolver struct{ *Resolver }

func (r *queryResolver) Todos(ctx context.Context) ([]*generated.Todo, error) {
 todos, err := r.todoRepo.FindAll()
 if err != nil {
  return nil, err
 }
 return NewTodoListFromModel(todos), nil
}

func (r *queryResolver) TodoByID(ctx context.Context, id string) (*generated.Todo, error) {
 todo, err := r.todoRepo.FindByID(id)
 if err != nil {
  return nil, err
 }
 return NewTodoFromModel(todo), nil
}

type QueryResolver interface {
 Hello(ctx context.Context, name string) (string, error)
}

In this code, I have defined a ‘Resolver’ struct and implemented the resolvers for my schema. Each resolver takes the context and necessary arguments for the query or mutation and returns the data for the field or error.

For example, my ‘Todos’ resolver will retrieve a list of todos from the database and return them as an array of ‘Todo’ objects. Similarly, my ‘CreateTodo’ resolver will create a new todo in the database with the given title and return a new todo object.

Note that the logic for retrieving and manipulating data in the database is not implemented in this code. This will depend on the specific database you are using and is beyond the scope of this article.

With the resolvers implemented, I can now test my GraphQL server using the playground route I defined earlier. I can open a browser and go to “http://localhost:8080/" to access the playground. Here I can enter queries and mutations for my schema and see the results in real-time.

For example, I can use the following query to retrieve all todos:

query {
  todos {
    id
    title
    description
    isCompleted
  }
}

And I can use the following mutation to create a new todo:

mutation {
  createTodo(title: "My new todo") {
    id
    title
    completed
  }
}

Of course, I don’t actually endorse attempting to conquer the world! But it’s a funny and playful example of using GraphQL to create a new todo.

And this is what the query I wrote looks like, after doing a little shopping.

mutation {
  updateTodo(
    id: "55cb28a2-f782-42cc-a085-f134a5079ec4"
    isCompleted: true
  ) {
    id
    title
    description
    isCompleted
  }
}

I may not have succeeded in conquering the world, but I did shop at a neighborhood shop.

To get all the todos, that we created, we can simply use this query:

An awful lot to do, fit something to get rid of.

This will retrieve all the todos in the system and return their id, title, description, and isCompleted fields.

If I have accidentally created a todo, I can also delete it, this will delete the todo with the specified ID and return a boolean value indicating whether the deletion was successful.

mutation {
  deleteTodo(id: "86a4d560-49c8-455f-a3b7-40d83d156355")
}

These queries and mutations should return the expected data based on my resolver implementations.

Database integration

To ensure that the data in my todo app persisted even after shutting it down, I needed to integrate it with a database. To achieve this, I opted to use PostgreSQL, a popular SQL database. I then established a connection to the database from my GraphQL server using the “github.com/lib/pq” driver and libraries to execute queries. With the connection in place, I created models and repositories that were responsible for handling the necessary CRUD operations. However, I had to modify the Todo model to match the schema of the database table. This required making changes to the entire codebase. By integrating my GraphQL server with a database, I was able to ensure that the data persisted and was readily available even after shutting down the app.

Data from PostgreSQL database integrated with GraphQL

Conclusion

In this article, I have explored how to create a GraphQL server with a backend in Go using the gqlgen library and following the schema-first approach. I have defined a simple GraphQL schema for a to-do list application and used gqlgen to generate the necessary Go code. I have then implemented resolvers for my schema to retrieve and manipulate data in a database. Finally, I tested my GraphQL server using the playground route and demonstrated how to use queries and mutations to interact with my schema.

GraphQL and Go are both powerful technologies that can provide scalable and efficient solutions for modern web applications. By following the schema-first approach and using the gqlgen library, I can quickly and easily create a GraphQL server with a backend in Go that meets the needs of my application. It took me approximately 2.5 hours to create all of this.

While this tutorial covered the basics of creating a GraphQL API with Go, there is much more to explore in both GraphQL and Go. With the knowledge gained here, you can start building more complex APIs with additional models, custom queries, and mutations.

To learn more about GraphQL, check out the official GraphQL documentation at graphql.org. And for more information about Go, visit the official Go documentation at golang.org.

Source code: https://github.com/53jk1/go-graphql-todo