Go REST API - Sort, Page, Filter

REST APIs are commonly used as a data source for graphical user interfaces like web applications. The data is often displayed within tables that allow sorting entries based on their attributes, paging between different entries if there are too many items to fit into one site, or filtering the data items. Here’s an example of such a table from the Angular Documentation:

In this article, we are going to extend the REST API we build in the previous post to support such features.

Additional features using query parameters
Populating the API with Data
Sorting
Pagination
Filter / Search

>>> Previous post of the series: REST API with SQL DB in Go
>>> Initial Code on Github

Additional features using query parameters

We’re going to add the additional features sorting, pagination, and filtering to our REST APIs getAllusers handler. A common way to implement this is using HTTP query parameters. You can add query parameters to your HTTP requests with a “?” for the first parameter and a “&” for each consecutive parameter. So for example a GET request, that would return the first 10 users sorted by the last name will look similar to this:

curl -XGET http://localhost:8080/users?sortBy={sortQuery}&limit=10

We’re not handling any query parameters in our getAllUser Method. Currently, it looks like this:

main.go

...

func (uh userHandler) getAllUsers(w http.ResponseWriter, r *http.Request) {
	users := []User{}
	if err := uh.db.Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(users); err != nil {
		fmt.Println(err)
		http.Error(w, "Error encoding response object", http.StatusInternalServerError)
	}
}

...

During the next sections of this post, we are going to extend this method to support all required query parameters for sorting, pagination, and counting.

Populating the API with Data

Before we start implementing the features, we have to populate our database, which holds the data for the API, with some more users. We’ll extend our main function to support two command-line flags “–init-data” and “–drop-table”. If the “–init-data” flag got set, the program will look for a file called “userdata.json” and insert all users in that file to the database. If the “–drop-table” flag gets, set the program will execute a raw SQL command to drop the users table and exit. If both flags get set, only “–drop-table” will be executed. Let’s see how we can implement this:

At first, we implement the logic for creating the users from a file in the function initUsers:

func initUsers(db *gorm.DB) error {
	data, err := ioutil.ReadFile("userdata.json")
	if err != nil {
		return err
	}

	var users []User
	if err := json.Unmarshal(data, &users); err != nil {
		return err
	}

	return db.Create(&users).Error
}

We read the file “userdata.json”, unmarshal it to a users slice and then call create with a reference to the populated users slice.

Calling gorms Create function with a slice will work and create a SQL command for batch insert into the database. For more detailed information see the GORM documentation for batch inserts.

Then we implement the function dropTable. It will execute a SQL command to drop the users table in our database:

func dropTable(db *gorm.DB) error {
	return db.Exec("DROP TABLE users;").Error
}

Now that we have the required functions in place, we can parse the command line flags in a function called handleFlags:

func handleFlags(db *gorm.DB) {
	initData := flag.Bool("init-data", false, "Set this flag if DB should be initialized with dummy data")
	drop := flag.Bool("drop-table", false, "Set this flag if you wan't to drop all user data in your DB")
	flag.Parse()

	if *drop {
		msg := ""
		if err := dropTable(db); err != nil {
			msg = fmt.Sprintf("Error dropping table: %v", err)
		} else {
			msg = "Dropped users table in DB"
		}

		fmt.Println(msg)
		os.Exit(0)
	}

	if *initData {
		msg := ""
		if err := initUsers(db); err != nil {
			msg = fmt.Sprintf("Error initializing data in DB: %v", err)
		} else {
			msg = "Initialized data in DB."
		}

		fmt.Println(msg)
		os.Exit(0)
	}
}

At first, we register the flags “initData” and “drop-table”. Then we call flag.Parse to populate the flag variables. Now we can test the values of the flags and execute corresponding operations. If one of the flags got set, we also exit the application to make sure our webservice won’t start.

Now we can call this function inside our main function, which will then look like this:

func main() {

	db, err := initDB()
	if err != nil {
		fmt.Printf("Error initializing DB: %v\n", err)
	}

	handleFlags(db)

	uh := userHandler{db: db}

	r := mux.NewRouter()
	usersR := r.PathPrefix("/users").Subrouter()
	usersR.Path("").Methods(http.MethodGet).HandlerFunc(uh.getAllUsers)
	usersR.Path("").Methods(http.MethodPost).HandlerFunc(uh.createUser)
	usersR.Path("/{id}").Methods(http.MethodGet).HandlerFunc(uh.getUserByID)
	usersR.Path("/{id}").Methods(http.MethodPut).HandlerFunc(uh.updateUser)
	usersR.Path("/{id}").Methods(http.MethodDelete).HandlerFunc(uh.deleteUser)

	fmt.Println("Start listening")
	fmt.Println(http.ListenAndServe(":8080", r))
}

The next step is to download the userdata.json file and run the program with the “init-data” flag:

# if you don't have curl installed you can also download the file with your browser using the URL below

# execute this in the root path of your project to download the file
curl https://raw.githubusercontent.com/johannes94/blog/master/posts/crud-rest-api-with-gorilla-mux/userdata.json -o userdata.json

# run the program to initialize the user data
go run main.go --init-data

# run the same command with the --drop-table flag delete all data in your DB

Now that we have data to work on, we can finally start with implementing new API features.

Sorting

We’ll start with returning a sorted list of users for our GET endpoint. Sorting should be possible by all fields of the user struct, and an API user should be able to choose between ascending and descending order. I found an interesting post on how to design API query parameters. You can take a look if you are interested. I choose using the approach of separating field and sort order by a “.” in the query parameter. Here are some examples of valid URLs for our API after we implemented the sortBy query parameter:

https://localhost:8080/users?sortBy=lastname.asc
https://localhost:8080/users?sortBy=age.desc
https://localhost:8080/users?sortBy=age.asc

This is the code of our getAllUser handler and helper functions after implementing the sort query parameter:

func (uh userHandler) getAllUsers(w http.ResponseWriter, r *http.Request) {
	users := []User{}

	// sortBy is expected to look like field.orderdirection i. e. id.asc
	sortBy := r.URL.Query().Get("sortBy")
	if sortBy == "" {
		// id.asc is the default sort query
		sortBy = "id.asc"
	}

	sortQuery, err := validateAndReturnSort(sortBy)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := uh.db.Order(sortQuery).Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(users); err != nil {
		fmt.Println(err)
		http.Error(w, "Error encoding response object", http.StatusInternalServerError)
	}
}

var userFields = getUserFields()

func getUserFields() []string {
	var field []string

	v := reflect.ValueOf(User{})
	for i := 0; i < v.Type().NumField(); i++ {
		field = append(field, v.Type().Field(i).Tag.Get("json"))
	}

	return field
}

func stringInSlice(strSlice []string, s string) bool {
	for _, v := range strSlice {
		if v == s {
			return true
		}
	}

	return false
}

func validateAndReturnSortQuery(sortBy string) (string, error) {
	splits := strings.Split(sortBy, ".")
	if len(splits) != 2 {
		return "", errors.New("malformed sortBy query parameter, should be field.orderdirection")
	}

	field, order := splits[0], splits[1]

	if order != "desc" && order != "asc" {
		return "", errors.New("malformed orderdirection in sortBy query parameter, should be asc or desc")
	}

	if !stringInSlice(userFields, field) {
		return "", errors.New("unknown field in sortBy query parameter")
	}

	return fmt.Sprintf("%s %s", field, strings.ToUpper(order)), nil

}

At first, we get the sortBy query parameter from the request input object of our handler function.

	sortBy := r.URL.Query().Get("sortBy")
	if sortBy == "" {
		sortBy = "id.asc"
	}

Then we call another function to validate the input parameter and return the query string required to let the PostgreSQL database sort its response.

func validateAndReturnSortQuery(sortBy string) (string, error) {
	splits := strings.Split(sortBy, ".")
	if len(splits) != 2 {
		return "", errors.New("malformed sortBy query parameter, should be field.orderdirection")
	}

	field, order := splits[0], splits[1]

	if order != "desc" && order != "asc" {
		return "", errors.New("malformed orderdirection in sortBy query parameter, should be asc or desc")
	}

	if !stringInSlice(userFields, field) {
		return "", errors.New("unknown field in sortBy query parameter")
	}

	return fmt.Sprintf("%s %s", field, strings.ToUpper(order)), nil

}

This function splits the query parameter into field and sort order. Then it makes sure the field matches one of our user structs fields and the sort order is either “desc” or “asc”. If the function yields an error, a bad request status code is returned. If the query parameter is valid, it returns a query string that will be used as input for the GORM database query.

When using user input to build database queries, it is crucial to check that the values provided match the values expected. That protects our API from SQL Injection Attacks.

	sortQuery, err := validateAndReturnSortQuery(sortBy)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

The sort query string returned by the validateAndReturnSortQuery function is then used as input for the Order method when calling the gorm.DB reference:

	if err := uh.db.Order(sortQuery).Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

We also implemented two helper methods: getUserFields uses the reflect package to initialize a global string slice that holds all JSON fields of our user object. stringInSlice returns a boolean to indicate if a given string matches one of the strings in the given slice.

We use a global variable to store the user field values cause they don’t change during program execution, and recalculating the slice on each request would lead to bad performance for our API.

Pagination

For pagination, the choice on which query parameter design we should take is a more complex one. It is not only that we have to decide which approach we expect to be more convenient for our API users. Also, the performance and consistency of the API have to get considered. Again the post linked in the sorting section is a good read on which approach to choose when. I don’t want to couple our filter function with the pagination function, so I choose the Offset Pagination approach for this API. That means calls to our URL using the paging feature will look like this:

http://localhost:8080/users?limit=10 # get first 10 entries
http://localhost:8080/users?limit=10&offset=20 # get entries 21 to 30
http://localhost:8080/users # get all users
http://localhost:8080/users?offset=10 # get all entries beginning with the entry 11

Let’s see how we can implement this in our getAllUsers method:

func (uh userHandler) getAllUsers(w http.ResponseWriter, r *http.Request) {
	users := []User{}

	// sortBy is expected to look like field.orderdirection i. e. id.asc
	sortBy := r.URL.Query().Get("sortBy")
	if sortBy == "" {
		// id.asc is the default sort query
		sortBy = "id.asc"
	}

	sortQuery, err := validateAndReturnSortQuery(sortBy)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	strLimit := r.URL.Query().Get("limit")
	// with a value as -1 for gorms Limit method, we'll get a request without limit as default
	limit := -1
	if strLimit != "" {
		limit, err = strconv.Atoi(strLimit)
		if err != nil || limit < -1 {
			http.Error(w, "limit query parameter is no valid number", http.StatusBadRequest)
			return
		}
	}

	strOffset := r.URL.Query().Get("offset")
	offset := -1
	if strOffset != "" {
		offset, err = strconv.Atoi(strOffset)
		if err != nil || offset < -1 {
			http.Error(w, "offset query parameter is no valid number", http.StatusBadRequest)
			return
		}
	}

	if err := uh.db.Limit(limit).Offset(offset).Order(sortQuery).Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(users); err != nil {
		fmt.Println(err)
		http.Error(w, "Error encoding response object", http.StatusInternalServerError)
	}
}

At first, we added a call to the request object to get and validate the limit query parameter. If it was not set we set it to a value of “-1” this value will lead to a SQL query without a limit when calling gorms “Limit” method later on.

	strLimit := r.URL.Query().Get("limit")
	// with a value as -1 for gorms Limit method, we'll get a request without limit as default
	limit := -1
	if strLimit != "" {
		limit, err = strconv.Atoi(strLimit)
		if err != nil || limit < -1 {
			http.Error(w, "limit query parameter is no valid number", http.StatusBadRequest)
			return
		}
	}

Then we do the same for the offset query parameter:

	strOffset := r.URL.Query().Get("offset")
	offset := -1
	if strOffset != "" {
		offset, err = strconv.Atoi(strOffset)
		if err != nil || offset < -1 {
			http.Error(w, "offset query parameter is no valid number", http.StatusBadRequest)
			return
		}
	}

At last, the gorm query got changed to call the “Limit” and “Offset” method with the corresponding values.

	if err := uh.db.Limit(limit).Offset(offset).Order(sortQuery).Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

Filter / Search

When designing a filter feature for an API, there are many questions to ask. Should a filter only support exact matches, or do you want to allow greater or lesser than semantics? Should the API work with URL query parameters, or might a JSON body with a query language be the better solution. Should your API provide fuzzy search capabilities? Should the API support filter for multiple fields of the user struct at once? And many more.

To keep it simple for this tutorial, I decided to only provide a filter feature for exact matches of a single user field and to use query parameters as filter input to the GET endpoint for users. The query parameter we will provide is called “filter” and the value should be in the form of “field.value”. Here are some examples of how filter queries to our API should look like:

http://localhost:8080/users?filter=firstname.sergey
http://localhost:8080/users?filter=age.27
http://localhost:8080/users?filter=email.test@test

Now that we have a plan, let’s look at the code for filtering our user entries:

func (uh userHandler) getAllUsers(w http.ResponseWriter, r *http.Request) {
	users := []User{}

	// sortBy is expected to look like field.orderdirection i. e. id.asc
	sortBy := r.URL.Query().Get("sortBy")
	if sortBy == "" {
		// id.asc is the default sort query
		sortBy = "id.asc"
	}

	sortQuery, err := validateAndReturnSortQuery(sortBy)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	strLimit := r.URL.Query().Get("limit")
	// with a value as -1 for gorms Limit method, we'll get a request without limit as default
	limit := -1
	if strLimit != "" {
		limit, err = strconv.Atoi(strLimit)
		if err != nil || limit < -1 {
			http.Error(w, "limit query parameter is no valid number", http.StatusBadRequest)
			return
		}
	}

	strOffset := r.URL.Query().Get("offset")
	offset := -1
	if strOffset != "" {
		offset, err = strconv.Atoi(strOffset)
		if err != nil || offset < -1 {
			http.Error(w, "offset query parameter is no valid number", http.StatusBadRequest)
			return
		}
	}

	filter := r.URL.Query().Get("filter")
	filterMap := map[string]string{}
	if filter != "" {
		filterMap, err = validateAndReturnFilterMap(filter)
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	}

	if err := uh.db.Where(filterMap).Limit(limit).Offset(offset).Order(sortQuery).Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

	w.Header().Add("Content-Type", "application/json")
	if err := json.NewEncoder(w).Encode(users); err != nil {
		fmt.Println(err)
		http.Error(w, "Error encoding response object", http.StatusInternalServerError)
	}
}

func validateAndReturnFilterMap(filter string) (map[string]string, error) {
	splits := strings.Split(filter, ".")
	if len(splits) != 2 {
		return nil, errors.New("malformed sortBy query parameter, should be field.orderdirection")
	}

	field, value := splits[0], splits[1]

	if !stringInSlice(userFields, field) {
		return nil, errors.New("unknown field in filter query parameter")
	}

	return map[string]string{field: value}, nil
}

We get the value of the filter query parameter from the request object.

	filter := r.URL.Query().Get("filter")
	filterMap := map[string]string{}
	if filter != "" {
		filterMap, err = validateAndReturnFilterMap(filter)
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
	}

If the value is not an empty string, the query parameter gets passed to the “validateAndReturnFilterMap” function. This function returns a map from the field to the value specified in the query. If there is a validation error, the handler responds with an http.StatusBadRequest and an appropriate error message.

Next, the gorm.DB query was changed to also call the “Where” method with the filterMap as an argument. The filterMap is an empty map by default. If an empty map gets passed to the “Where” method, there is no SQL where clause added to the query.

	if err := uh.db.Where(filterMap).Limit(limit).Offset(offset).Order(sortQuery).Find(&users).Error; err != nil {
		fmt.Println(err)
		http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
		return
	}

Conclusion

In this post, we added Sorting, Pagination, and Filtering to our REST API. Although we handled each of these features separately, you can combine all of them in your GET request to the API. Also, I hope this post is helping you as a starting point for more complex query features like sorting or filtering for multiple columns at once.

>>> Previous post of the series: REST API with SQL DB in Go
>>> Full Code on Github

Go REST API – Sort, Page, Filter