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/gosidekick/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" LIMIT $2 OFFSET $1`
	
	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)
}

I think the order should be,

`ORDER BY "id" LIMIT $2 OFFSET $1`` instead of writing OFFSET than LIMIT. I encountered a SQL syntax error with that. Please refer here stackoverflow

Hello @kabaluyot,

I will do some tests, I believe that oderm is irrelevant for PostgreSQL.

If it works both ways in PostgreSQL I can update the order without any problems.

just improve to add total rows in the tables with this query, maybe somebody needed it.

select count(*) over() as total, column1, column2, .. from tables order by id limit $1 offset $2

just improve to add total rows in the tables with this query, maybe somebody needed it.

select count(*) over() as total, column1, column2, .. from tables order by id limit $1 offset $2

this doesn't work for mysql

just improve to add total rows in the tables with this query, maybe somebody needed it.

select count(*) over() as total, column1, column2, .. from tables order by id limit $1 offset $2

this doesn't work for MySQL

absolutely, 'cuz this one is for PGSQL. in MySQL you need no over() just count and combine with limit offset. for example, you can read this official documentation