GO-PG Golang Postgre ORM
Tired of writing manual queries on PostgreSQL? Let’s make it simpler with go-pg
In Golang, there are many reasons to use ORM for communicating with the database. The most important is the development speed. We want to speed up our development speed but in fact, ORM might slow down your performance.
Are you willing to spend more on development time and use straight data access or trade relatively slower performance with less development time?
Why must sacrifice If we can get both
I’ll introduce you to go-pg. A Golang ORM with a focus on PostgreSQL features and performance.
Here are some advantages if you use go-pg :
1. No rows.Close to manually manage connections — In go-pg, you don’t have to do rows.Close for every single connection that you open.
2. go-pg is 2x-10x faster than GORM — Because of its focus, go-pg has a better performance than the GORM that doesn’t focus on one database engine.
3. go-pg automatically maps rows on Go structs and slice — In go-pg you don’t have to scan the query result because it automatically maps rows to go structs (model)
4. go-pg generates much more efficient queries for joins — You can make efficient queries for joins than other ORMs, even the database package.
5. Simplify your code — Go-pg using a function to write queries so your code can be simplified
6. Faster development time — Of course this is our top reason why we used ORM in our project, in go-pg you don’t have to write your queries manually so it can make your development time faster.
Simplicity is the soul of efficiency — Austin Freeman
CONS
There are cons using go-pg
- Time.Time UTC Converted — the time like created_at, updated_at will convert to UTC() so you have to add your time zone if you want to use time zone.
- Only Postgre — isn’t like GORM, go-pg can only be used in Postgre
BENCHMARK
As you can see, go-pg has fewer times/operation than the GORM, even 1.3x faster than the standard library PQ in Golang.
MODELS
Models are defined using Go structs which are mapped to PostgreSQL tables. Exported struct fields are mapped to table columns, unexported fields are ignored. The tag used on models is pg or sql tags
type Test struct {
tableName struct{} `pg:"test_table"`
ID string `json:"id" pg:"id,pk"`
Name string `json:"name,omitempty" pg:"name"`
HP string `json:"hp,omitempty" pg:"hp"`
Status string `json:"status,omitempty" pg:"status"`
}Here’s the tag meaning on go-pg tag :
CONNECTION
Here’s a sample code to connect with go-pg. You can modify your options according to your needs
func NewDBConn() (con *pg.DB) {
address := fmt.Sprintf("%s:%s", "localhost", "5432")
options := &pg.Options{
User: "postgres",
Password: "12345678",
Addr: address,
Database: "postgres",
PoolSize: 50,
}con = pg.Connect(options)
if con == nil {
log.Error("cannot connect to postgres")
}return
}
QUERY
Go-pg query simplifies SQL queries to function that can make your development process easier. You have to make the models using pg or sql tags first before making a query.
INSERT
func InsertDB(pg *pg.DB, post Post) error {
_, err := pg.Model(&post).Insert()
return err
}You can return your last inserted id with returning function.
func InsertDB(pg *pg.DB, post Post) error {
_, err := pg.Model(&post).Returning("id").Insert()
return err
}You can also insert many rows. Just by changing your model into an array then you can insert many rows in one transaction.
func InsertDBManyRows(pg *pg.DB, tests []Test) error {
_, err := pg.Model(&tests).Insert()
return err
}Or you can make it easier if you only want to insert one row.
func Save (pg *pg.DB, test Test) error {
_, err := pg.Insert(&test)
return err
}SELECT
func SelectDBPost(pg *pg.DB, post Post) ([]Post, error) {
err := pg.Model(&post).Where("id = 1").Select()
return post, err
}You can also select many rows. Just by changing your model into an array then you can select many rows in one transaction.
func SelectDBPost(pg *pg.DB, posts []Post) ([]Post, error) {
err := pg.Model(&posts).Where("city = jakarta").Select()
return posts, err
}Many rows with column limit and skip method.
func SelectDBPost(pg *pg.DB, post []Post) ([]Post, error) {
err := pg.Model(&post).Where("city = jakarta").Limit(2).Select()
return post, err
}Or you can simplify it by selecting it with the primary key.
func SelectDBTest(pg *pg.DB, test Test) (Test, error) {
err := pg.Select(&test)
return test, err
}For select exists you can use Exists() function in go-pg
func SelectExists(pg *pg.DB, post Post) (bool, error) {
exists, err := pg.Model(&post).Where("city = jakarta")..Exists()
return exists, err
}UPDATE
func UpdateDB(pg *pg.DB, test Test) error {
_, err := pg.Model(&test).Where("id = ?0", test.ID).Update()
return err
}You can also update just the column that you want on with column function.
func UpdateSomeColumnDB(pg *pg.DB, test Test) error {
_, err := pg.Model(&test).Column("name", "hp").Where("id = ?0", test.ID).Update()
return err
}DELETE
func DeleteDB(pg *pg.DB, test Test) error {
_, err := pg.Model(&test).Where("id = ?0", test.ID).Delete()
return err
}You can also simplify it if you use the primary key as your condition.
func DeleteDB(pg *pg.DB, test Test) error {
err := pg.Delete(&test)
return err
}JOIN
If you want to use join on the query, you must add the column that you want to add to your main model.
type Test struct {
tableName struct{} `sql:"notification.test"`
ID string `json:"id" sql:"id,pk"`
Name string `json:"name,omitempty" sql:"name"`
HP string `json:"hp,omitempty" sql:"hp"`
Status string `json:"status,omitempty" sql:"status"`
Vacancies string `json:"vacancies,omitempty" sql:"vacancies"` }Then you can use columnExpr function to write join query on it.
func JoinDBTestWithPost(pg *pg.DB, test Test) (Test, error) {
err := pg.Model(&test).
ColumnExpr("test.*").
ColumnExpr("p.vacancies AS vacancies").
Join("JOIN notification.post AS p ON p.id = test.id").
Select()
return test, err
}SUBQUERY
You can use two queries to make a subquery on the go-pg query.
subq := db.Model(&post{}).Column("author").Where("admin")
q := db.Model(&test).Where("author IN (?)", subq)The query above will generate :
SELECT * FROM test
WHERE author IN (
SELECT author FROM post WHERE admin
)RAW QUERY
Not only with function, but you can also write a raw advance query. you can use a function like Query, QueryOne, or ColumnExpr you want to write a query that affects the column.
Query function
db.Query(&result, `SELECT id FROM bar WHERE code_id = ANY (?)`, pg.Array(params.Codes))With QueryOne function
db.Model(&GroupType{}).QueryOne(pg.Scan(&exists), `
SELECT EXISTS(select r.name from (select array_agg(name) as name from admin) r where r.name @> ?)`, pg.Array(userType))ColumnExpr
db.Model(&user).ColumnExpr("CASE WHEN u.nohp = '' THEN '0' ELSE u.nohp END")TRANSACTION
You can use Begin() function to make a transaction on go-pg.
func InsertDBWithTx(pg *pg.DB, tests []Test) error {
tx, err := pg.Begin()
if err != nil {
return err
}_, err = tx.Model(&tests).Insert()
if err != nil {
tx.Rollback()
return err
}return tx.Commit()
}
VIEW GENERATED QUERIES
You can make a query logger view queries from your function.
type dbLogger struct { }func (d dbLogger) BeforeQuery(c context.Context, q *pg.QueryEvent) (context.Context, error) {
return c, nil
}func (d dbLogger) AfterQuery(c context.Context, q *pg.QueryEvent) error {
fmt.Println(q.FormattedQuery())
return nil
}db := pg.Connect(&pg.Options{...})
db.AddQueryHook(dbLogger{})
Then just tail the log file.
tail -f /var/log/postgresql/postgresql-9.5-main.logYou can combine all query functions above according to your needs and creativity.
Make it work, make it right, make it fast — Kent Beck
Go-pg has many advantages that can make our development process faster and easier on a PostgreSQL query. You just need to be creative to find your easiest way to write SQL queries on Postgre with go-pg.
