Example of pagination using PostgreSQL, Golang and SQLx

README.md

Configure environment variable

export DATABASE_URL=postgres://postgres@localhost/dbname?sslmode=disable 

Run in CLI

go run main.go -page 1

example.sql

CREATE TABLE clientes
(
    id serial NOT NULL,
    nome character varying(255) NOT NULL,
    PRIMARY KEY (id)
);


INSERT INTO clientes(nome) VALUES ('cliente 1');
INSERT INTO clientes(nome) VALUES ('cliente 2');
INSERT INTO clientes(nome) VALUES ('cliente 3');
INSERT INTO clientes(nome) VALUES ('cliente 4');
INSERT INTO clientes(nome) VALUES ('cliente 5');
INSERT INTO clientes(nome) VALUES ('cliente 6');
INSERT INTO clientes(nome) VALUES ('cliente 7');
INSERT INTO clientes(nome) VALUES ('cliente 8');
INSERT INTO clientes(nome) VALUES ('cliente 9');
INSERT INTO clientes(nome) VALUES ('cliente 10');
INSERT INTO clientes(nome) VALUES ('cliente 11');
INSERT INTO clientes(nome) VALUES ('cliente 12');
INSERT INTO clientes(nome) VALUES ('cliente 13');
INSERT INTO clientes(nome) VALUES ('cliente 14');
INSERT INTO clientes(nome) VALUES ('cliente 15');
INSERT INTO clientes(nome) VALUES ('cliente 16');
INSERT INTO clientes(nome) VALUES ('cliente 17');
INSERT INTO clientes(nome) VALUES ('cliente 18');
INSERT INTO clientes(nome) VALUES ('cliente 19');
INSERT INTO clientes(nome) VALUES ('cliente 20');
INSERT INTO clientes(nome) VALUES ('cliente 21');
INSERT INTO clientes(nome) VALUES ('cliente 22');
INSERT INTO clientes(nome) VALUES ('cliente 23');
INSERT INTO clientes(nome) VALUES ('cliente 24');

main.go

package main

import (
	"fmt"
	"log"

	"github.com/crgimenes/goconfig"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
)

type config struct {
	DatabaseURL string `cfg:"database_url" cfgRequired:"true"`
	Page        int    `cfg:"page" cfgRequired:"true"`
}

var db *sqlx.DB

func listRecords(page int) {

	limit := 10
	offset := limit * (page - 1)

	SQL := `SELECT "id","nome" FROM "clientes" ORDER BY "id" OFFSET $1 LIMIT $2`
	rows, err := db.Queryx(SQL, offset, limit)
	if err != nil {
		log.Println(err)
		return
	}
	for rows.Next() {
		p := struct {
			ID   int    `db:"id"`
			Nome string `db:"nome"`
		}{}
		err = rows.StructScan(&p)
		if err != nil {
			log.Println(err)
			return
		}
		fmt.Printf("ID: %v\tNome: %v\n", p.ID, p.Nome)
	}
}

func main() {
	cfg := config{}
	err := goconfig.Parse(&cfg)
	if err != nil {
		fmt.Println(err)
		return
	}
	db, err = sqlx.Connect("postgres", cfg.DatabaseURL)
	if err != nil {
		fmt.Printf("error open db: %v\n", err)
		return
	}

	listRecords(cfg.Page)
}