go-pg

https://pg.uptrace.dev
Admin
go-pg is a PostgreSQL ORM client library for Go that provides object-relational mapping, model...
Tokens:22,992
Snippets:212
Trust Score:9.4
Update:7 months ago
Show doc for...
Tokens:
Raw
### Install go-pg ORM

Source: https://pg.uptrace.dev/index

Installs the go-pg ORM library, version v10. Ensure you are using the correct version in your import statements.

```bash
go get github.com/go-pg/pg/v10

```

--------------------------------

### Full CRUD Example with go-pg

Source: https://pg.uptrace.dev/index

Demonstrates connecting to a database, creating schemas for User and Story models, inserting data, and performing select operations, including fetching related data. Uses temporary tables for schema creation.

```go
package pg_test

import (
    "fmt"

    "github.com/go-pg/pg/v10"
    "github.com/go-pg/pg/v10/orm"
)

type User struct {
    Id     int64
    Name   string
    Emails []string
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Emails)
}

type Story struct {
    Id       int64
    Title    string
    AuthorId int64
    Author   *User
}

func (s Story) String() string {
    return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}

func ExampleDB_Model() {
    db := pg.Connect(&pg.Options{
        User: "postgres",
    })
    defer db.Close()

    err := createSchema(db)
    if err != nil {
        panic(err)
    }

    user1 := &User{
        Name:   "admin",
        Emails: []string{"admin1@admin", "admin2@admin"},
    }
    _, err = db.Model(user1).Insert()
    if err != nil {
        panic(err)
    }

    _, err = db.Model(&User{
        Name:   "root",
        Emails: []string{"root1@root", "root2@root"},
    }).Insert()
    if err != nil {
        panic(err)
    }

    story1 := &Story{
        Title:    "Cool story",
        AuthorId: user1.Id,
    }
    _, err = db.Model(story1).Insert()
    if err != nil {
        panic(err)
    }

    // Select user by primary key.
    user := &User{Id: user1.Id}
    err = db.Model(user).WherePK().Select()
    if err != nil {
        panic(err)
    }

    // Select all users.
    var users []User
    err = db.Model(&users).Select()
    if err != nil {
        panic(err)
    }

    // Select story and associated author in one query.
    story := new(Story)
    err = db.Model(story).
        Relation("Author").
        Where("story.id = ?", story1.Id).
        Select()
    if err != nil {
        panic(err)
    }

    fmt.Println(user)
    fmt.Println(users)
    fmt.Println(story)
    // Output:
    // User<1 admin [admin1@admin admin2@admin]>
    // [User<1 admin [admin1@admin admin2@admin]> User<2 root [root1@root root2@root]>]
    // Story<1 Cool story User<1 admin [admin1@admin admin2@admin]>>
}

// createSchema creates database schema for User and Story models.
func createSchema(db *pg.DB) error {
    models := []interface{}{
        (*User)(nil),
        (*Story)(nil),
    }

    for _, model := range models {
        err := db.Model(model).CreateTable(&orm.CreateTableOptions{
            Temp: true,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

```

--------------------------------

### Initialize Go Module

Source: https://pg.uptrace.dev/index

Initializes a new Go module for your project. This is a prerequisite for using Go modules and managing dependencies.

```bash
go mod init github.com/my/repo

```

--------------------------------

### Connect to Database using Options

Source: https://pg.uptrace.dev/index

Establishes a connection to a PostgreSQL database using explicit connection options. Requires database address, user, password, and database name.

```go
db := pg.Connect(&pg.Options{
    Addr:     ":5432",
    User:     "user",
    Password: "pass",
    Database: "db_name",
})

```

--------------------------------

### Select PostgreSQL Version

Source: https://pg.uptrace.dev/index

Queries the database to retrieve the PostgreSQL version and prints it to the console. Handles potential errors during the query.

```go
var version string
_, err := db.QueryOneContext(ctx, pg.Scan(&version), "SELECT version()")
if err != nil {
    panic(err)
}
fmt.Println(version)

```

--------------------------------

### Execute Simple Query

Source: https://pg.uptrace.dev/index

Executes a simple SQL query (e.g., 'SELECT 1') to verify database connectivity. Panics if the execution fails.

```go
ctx := context.Background()

_, err := db.ExecContext(ctx, "SELECT 1")
if err != nil {
    panic(err)
}

```

--------------------------------

### Connect to Database using Connection String

Source: https://pg.uptrace.dev/index

Connects to a PostgreSQL database by parsing a connection URL string. Handles potential errors during URL parsing.

```go
opt, err := pg.ParseURL("postgres://user:pass@localhost:5432/db_name")
if err != nil {
   panic(err)
}

db := pg.Connect(opt)

```

--------------------------------

### Go-pg Many-to-Many Relation Setup and Query

Source: https://pg.uptrace.dev/orm/many-to-many-relation

This Go code snippet demonstrates setting up and querying a many-to-many relationship using the go-pg ORM. It defines Order, Item, and OrderToItem models, registers the intermediary table, inserts data, and then queries orders with their associated items, showcasing both default and sorted retrieval. Dependencies include the 'github.com/go-pg/pg/v10' package.

```go
package pg_test

import (
    "fmt"

    "github.com/go-pg/pg/v10"
    "github.com/go-pg/pg/v10/orm"
)

func init() {
    // Register many to many model so ORM can better recognize m2m relation.
    // This should be done before dependant models are used.
    orm.RegisterTable((*OrderToItem)(nil))
}

type Order struct {
    Id    int
    Items []Item `pg:"many2many:order_to_items"`
}

type Item struct {
    Id int
}

type OrderToItem struct {
    OrderId int
    ItemId  int
}

func ExampleDB_Model_manyToMany() {
    db := connect()
    defer db.Close()

    if err := createManyToManyTables(db); err != nil {
        panic(err)
    }

    values := []interface{}{
        &Item{Id: 1},
        &Item{Id: 2},
        &Order{Id: 1},
        &OrderToItem{OrderId: 1, ItemId: 1},
        &OrderToItem{OrderId: 1, ItemId: 2},
    }
    for _, v := range values {
        _, err := db.Model(v).Insert()
        if err != nil {
            panic(err)
        }
    }

    // Select order and all items with following queries:
    //
    // SELECT "order"."id" FROM "orders" AS "order" ORDER BY "order"."id" LIMIT 1
    //
    // SELECT order_to_items.*, "item"."id" FROM "items" AS "item"
    // JOIN order_to_items AS order_to_items ON (order_to_items."order_id") IN (1)
    // WHERE ("item"."id" = order_to_items."item_id")

    order := new(Order)
    err := db.Model(order).Relation("Items").First()
    if err != nil {
        panic(err)
    }
    fmt.Println("Order", order.Id, "Items", order.Items[0].Id, order.Items[1].Id)

    // Select order and all items sorted by id with following queries:
    //
    // SELECT "order"."id" FROM "orders" AS "order" ORDER BY "order"."id" LIMIT 1
    //
    // SELECT order_to_items.*, "item"."id" FROM "items" AS "item"
    // JOIN order_to_items AS order_to_items ON (order_to_items."order_id") IN (1)
    // WHERE ("item"."id" = order_to_items."item_id")
    // ORDER BY item.id DESC

    order = new(Order)
    err = db.Model(order).
        Relation("Items", func(q *pg.Query) (*pg.Query, error) {
            q = q.OrderExpr("item.id DESC")
            return q, nil
        }).
        First()
    if err != nil {
        panic(err)
    }
    fmt.Println("Order", order.Id, "Items", order.Items[0].Id, order.Items[1].Id)

    // Output: Order 1 Items 1 2
    // Order 1 Items 2 1
}

func createManyToManyTables(db *pg.DB) error {
    models := []interface{}{
        (*Order)(nil),
        (*Item)(nil),
        (*OrderToItem)(nil),
    }
    for _, model := range models {
        err := db.Model(model).CreateTable(&orm.CreateTableOptions{
            Temp: true,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

```

--------------------------------

### Full Example: Insert Data into Partitioned Table

Source: https://pg.uptrace.dev/table-partitioning

A complete Go program demonstrating how to connect to a PostgreSQL database, create a partitioned table, and insert data. It includes a function to dynamically create child partitions before inserting records.

```go
package main

import (
    "fmt"
    "log"
    "time"

    "github.com/go-pg/pg/v10"
    "github.com/go-pg/pg/v10/orm"
)

// Log is a data structure to save log string partitioned by time range.
type Log struct {
    tableName struct{} `pg:"logs,partition_by:RANGE(log_time)"`

    Id        int       `pg:"id,pk"`
    LogString string    `pg:"log_string"`
    LogTime   time.Time `pg:"log_time,pk"`
}

func main() {
    db := pg.Connect(&pg.Options{
        Addr:     "localhost:5432",
        User:     "postgres",
        Password: "postgres",
        Database: "db_name",
    })

    defer func() {
        if err := db.Close(); err != nil {
            log.Println(err.Error())
        }
    }()

    // creates database schema for Log models.
    err := db.Model(&Log{}).CreateTable(&orm.CreateTableOptions{
        IfNotExists: true,
    })
    if err != nil {
        log.Fatal(err)
    }

    logStartTime := time.Date(2020, 1, 1, 0, 0, 0, 0, time.UTC)
    logEndTime := logStartTime.AddDate(0, 0, 3)
    for logStartTime.Unix() <= logEndTime.Unix() {
        logData := &Log{
            LogString: fmt.Sprintf("Log at %s", logStartTime.String()),
            LogTime:   logStartTime,
        }

        // Before insert, always try create partition
        err = createNewPartition(db, logStartTime)
        if err != nil {
            log.Fatal(err)
        }

        _, err = db.Model(logData).Insert()
        if err != nil {
            log.Fatal(err)
        }

        logStartTime = logStartTime.AddDate(0, 0, 1)
    }
}

// createNewPartition will create new partition of logs table if not exist.
// Partition created by in one month range start from first date of the month to last date of the month.
func createNewPartition(db *pg.DB, currentTime time.Time) error {
    firstOfMonth := time.Date(currentTime.Year(), currentTime.Month(), 1, 0, 0, 0, 0, time.UTC)
    firstOfNextMonth := firstOfMonth.AddDate(0, 1, 0)

    year := firstOfMonth.Format("2006")
    month := firstOfMonth.Format("01")
    sql := fmt.Sprintf(
        `CREATE TABLE IF NOT EXISTS logs_y%s_m%s PARTITION OF logs FOR VALUES FROM ('%s') TO ('%s');`,
        year, month,
        firstOfMonth.Format(time.RFC3339Nano),
        firstOfNextMonth.Format(time.RFC3339Nano),
    )

    _, err := db.Exec(sql)
    return err
}

```

--------------------------------

### Enable Query Debugging in go-pg (v10+)

Source: https://pg.uptrace.dev/faq

Shows how to enable verbose query logging in go-pg starting from v10 using `pgdebug.DebugHook`. This is useful for understanding the SQL queries being generated by the library.

```go
import "github.com/go-pg/pg/extra/pgdebug"

db.AddQueryHook(pgdebug.DebugHook{
    // Print all queries.
    Verbose: true,
})

```

--------------------------------

### Install go-pg OpenTelemetry Instrumentation

Source: https://pg.uptrace.dev/tracing

This command installs the pgotel module, which provides OpenTelemetry instrumentation for go-pg. This is the first step to enable distributed tracing for your database operations.

```bash
go get github.com/go-pg/pg/extra/pgotel/v10
```

--------------------------------

### Connect to Google Cloud SQL from App Engine (Standard)

Source: https://pg.uptrace.dev/faq

Provides a Go code example for connecting to Google Cloud SQL from the App Engine standard environment. It includes logic for both development (local instance) and production (Cloud SQL) environments, using environment variables for configuration.

```go
// +build appengine

package myapp

import (
    "net"
    "os"

    "github.com/go-pg/pg"
    "google.golang.org/appengine"
    "google.golang.org/appengine/cloudsql"
)

func NewDB() *pg.DB {
    // Connect to local instance in development
    // Environment variables can be set via app.yaml
    if appengine.IsDevAppServer() {
        return pg.Connect(&pg.Options{
            Addr:     os.Getenv("LOCALSQL_HOST")+ ":" +os.Getenv("LOCALSQL_PORT"),
            User:     os.Getenv("LOCALSQL_USER"),
            Password: os.Getenv("LOCALSQL_PASS"),
            Database: os.Getenv("LOCALSQL_NAME"),
        })
    }

    // Connect to Cloud SQL in production
    // Environment variables can be set via app.yaml
    return pg.Connect(&pg.Options{
        Dialer: func(network, addr string) (net.Conn, error) {
            return cloudsql.Dial(os.Getenv("CLOUDSQL_HOST")) // project-name:region:instance-name
        },
        User:     os.Getenv("CLOUDSQL_USER"),
        Password: os.Getenv("CLOUDSQL_PASS"),
        Database: os.Getenv("CLOUDSQL_NAME"),
    })
}

```

--------------------------------

### SQL for Child Table Partition

Source: https://pg.uptrace.dev/table-partitioning

An example SQL statement for creating a child partition of a parent table. This statement defines the partition name, the parent table, and the value range for the partition.

```sql
CREATE TABLE IF NOT EXISTS logs_y2020_m01
PARTITION OF logs
FOR VALUES FROM ('2020-01-01T00:00:00Z') TO ('2020-02-01T00:00:00Z')

```

--------------------------------

### Install Google UUID Package for Go

Source: https://pg.uptrace.dev/uuid

Installs the google/uuid package, a popular Go library for generating and working with UUIDs. This is a prerequisite for using UUIDs in Go applications.

```go
go get github.com/google/uuid
```

--------------------------------

### Manual PostgreSQL Transactions in Go

Source: https://pg.uptrace.dev/transactions

Demonstrates how to manually control PostgreSQL transactions using go-pg. It includes starting a transaction with `db.Begin()`, deferring `tx.Close()`, handling errors with `tx.Rollback()`, and committing on success with `tx.Commit()`. Ensure proper error handling for rollback and commit operations.

```go
tx, err := db.Begin()
// Make sure to close transaction if something goes wrong.
default tx.Close()

if err := doSomething(ctx, tx); err != nil {
    // Rollback on error.
    _ = tx.Rollback()
    return
}

// Commit on success.
if err := tx.Commit(); err != nil {
    panic(err)
}

```

--------------------------------

### Select 20 Books and Count Estimated using go-pg

Source: https://pg.uptrace.dev/queries

Illustrates selecting a limited number of books and getting an estimated count of all books using `SelectAndCountEstimate`. This can be faster than a full count for very large tables.

```go
var books []Book
count, err := db.Model(&books).Limit(20).SelectAndCountEstimate(100000)
```

--------------------------------

### Ping Database Connection

Source: https://pg.uptrace.dev/index

Checks if the database connection is active and the database is reachable. Panics if the ping operation fails.

```go
ctx := context.Background()

if err := db.Ping(ctx); err != nil {
    panic(err)
}

```

--------------------------------

### Select 20 Books and Count All using go-pg

Source: https://pg.uptrace.dev/queries

Demonstrates how to perform two operations in one go: select the first 20 books and also get the total count of all books. The `SelectAndCount` method is used for this purpose.

```go
var books []Book
count, err := db.Model(&books).Limit(20).SelectAndCount()
```

--------------------------------

### Install PostgreSQL uuid-ossp Extension

Source: https://pg.uptrace.dev/uuid

Installs the uuid-ossp extension for PostgreSQL, which provides functions for generating UUIDs. This is typically done via a system package manager.

```bash
sudo apt install postgresql-contrib-14
```

--------------------------------

### Check PostgreSQL Connection Health (Older Versions)

Source: https://pg.uptrace.dev/faq

Provides a method to check PostgreSQL connection health for versions older than v10 by executing a simple `SELECT 1` query. This approach is suitable for legacy systems or older installations.

```go
_, err := db.Exec("SELECT 1")
if err != nil {
    fmt.Println("PostgreSQL is down")
}

```

--------------------------------

### Define and Query Has-Many Relation in Go

Source: https://pg.uptrace.dev/orm/has-many-relation

Defines `User` and `Profile` models with a 'has-many' relationship and demonstrates querying a user with their active profiles. Requires the `pg` ORM library. The query selects a user and then filters their profiles based on the 'active' status.

```go
package main

import (
	"fmt"
	"github.com/go-pg/pg/v10"
)

type Profile struct {
	Id     int
	Lang   string
	Active bool
	UserId int
}

// User has many profiles.
type User struct {
	Id       int
	Name     string
	Profiles []*Profile `pg:"rel:has-many"`
}

func connect() *pg.DB {
	// Replace with your actual database connection details
	db := pg.Connect(&pg.Options{
		User:     "user",
		Password: "password",
		Database: "database",
		Addr:     "localhost:5432",
	})

	// Optional: Ping to check connection
	_, _, err := db.Exec("SELECT 1")
	if err != nil {
		panic(err)
	}
	fmt.Println("Connected to PostgreSQL!")
	return db
}

func main() {
	db := connect()
	defer db.Close()

	qs := []string{
		"DROP TABLE IF EXISTS users, profiles CASCADE", // Clean up previous runs
		"CREATE TEMP TABLE users (id int, name text)",
		"CREATE TEMP TABLE profiles (id int, lang text, active bool, user_id int)",
		"INSERT INTO users VALUES (1, 'user 1')",
		"INSERT INTO profiles VALUES (1, 'en', TRUE, 1), (2, 'ru', TRUE, 1), (3, 'md', FALSE, 1)",
	}
	for _, q := range qs {
		_, err := db.Exec(q)
		if err != nil {
			panic(err)
		}
	}

	// Select user and all his active profiles with following queries:
	//
	// SELECT "user".* FROM "users" AS "user" ORDER BY "user"."id" LIMIT 1
	//
	// SELECT "profile".* FROM "profiles" AS "profile"
	// WHERE (active IS TRUE) AND (("profile"."user_id") IN ((1)))

	var user User
	err := db.Model(&user).
		Column("user.*", "Profiles"). // Explicitly select Profiles column
		Relation("Profiles", func(q *pg.Query) (*pg.Query, error) {
			return q.Where("active IS TRUE"), nil
		}).
		First()
	if err != nil {
		panic(err)
	}

	fmt.Printf("User ID: %d, Name: %s\n", user.Id, user.Name)
	if len(user.Profiles) > 0 {
		fmt.Printf("First Active Profile: %+v\n", user.Profiles[0])
	}
	if len(user.Profiles) > 1 {
		fmt.Printf("Second Active Profile: %+v\n", user.Profiles[1])
	}
	// Expected Output (order of profiles might vary if not explicitly ordered):
	// User ID: 1, Name: user 1
	// First Active Profile: &{Id:1 Lang:en Active:true UserId:1}
	// Second Active Profile: &{Id:2 Lang:ru Active:true UserId:1}
}
```

--------------------------------

### Executing Raw SQL and Model Operations within Transactions in Go

Source: https://pg.uptrace.dev/transactions

Illustrates how to perform both raw SQL executions and ORM model operations within an active go-pg transaction. The examples show selecting an item using `tx.Model()` and creating a savepoint using `tx.Exec()`. Note that go-pg transactions do not support automatic retries.

```go
err := tx.Model(&item).Where("id = ?", 1).Select()

_, err := tx.Exec("SAVEPOINT my_savepoint")

```

--------------------------------

### Go 1.22+ Enhanced Routing with http.ServeMux

Source: https://pg.uptrace.dev/rest-api

Demonstrates how to use Go 1.22+'s http.ServeMux for method-based routing and path parameters. This approach reduces the need for third-party routing libraries by allowing direct registration of handlers for specific HTTP methods and URL patterns.

```go
mux := http.NewServeMux()
mux.HandleFunc("GET /users/{id}", showUser)
mux.HandleFunc("GET /users", listUsers)
mux.HandleFunc("DELETE /users/{id}", deleteUser)
```

```go
func showUser(w http.ResponseWriter, req *http.Request) {
    id := req.PathValue("id")
    // ...
}
```

--------------------------------

### Create Parent Partitioned Table in Go

Source: https://pg.uptrace.dev/table-partitioning

Demonstrates how to create a parent partitioned table in PostgreSQL using `go-pg`. It utilizes the `CreateTable` method with `orm.CreateTableOptions` to ensure the table is created only if it doesn't exist.

```go
// creates database schema for Log models.
err := db.Model(&Log{}).CreateTable(&orm.CreateTableOptions{
    IfNotExists: true,
})

```

--------------------------------

### Get Last Inserted ID in PostgreSQL (go-pg)

Source: https://pg.uptrace.dev/faq

Explains that PostgreSQL does not directly support `LastInsertId` like MySQL. Instead, it recommends using the `RETURNING` clause in SQL or the ORM's `Returning` method to get the ID of the last inserted or updated record.

```go
", err := tx.QueryOne(record, "INSERT INTO test_table (x) VALUES ('x') RETURNING id", record)

```

```go
", err := db.Model(&record).Returning("id").Update()

```

--------------------------------

### PostgreSQL: Update rows in consistent order (Bad Example)

Source: https://pg.uptrace.dev/migrations-zero-downtime

Demonstrates a scenario where concurrent transactions updating rows in different orders can lead to deadlocks in PostgreSQL. This approach should be avoided.

```sql
-- transaction 1
UPDATE test WHERE id IN (1, 2);

-- transaction 2
UPDATE test WHERE id IN (2, 1);

```

--------------------------------

### Select First 20 Books with go-pg

Source: https://pg.uptrace.dev/queries

Demonstrates how to select a limited number of records (the first 20) from the 'books' table, ordered by 'id' in ascending order. The `Order` and `Limit` methods are used.

```go
var books []Book
err := db.Model(&books).Order("id ASC").Limit(20).Select()
```

--------------------------------

### Select User with Profile using Explicit SQL Join

Source: https://pg.uptrace.dev/orm/intro

Demonstrates how to select a 'User' along with their 'Profile' data by manually constructing a SQL query with a LEFT JOIN. This method explicitly maps SQL columns to Go struct fields using `ColumnExpr` and aliasing.

```go
db.Model(&user).
    ColumnExpr("user.* ").
    ColumnExpr("profile.id AS profile__id").
    ColumnExpr("profile.lang AS profile__lang").
    Join("LEFT JOIN profiles AS profile ON profile.user_id = user.id").
    Where("user.id = ?", 123).
    Select()
```

--------------------------------

### Select User with Profile using Relation Method

Source: https://pg.uptrace.dev/orm/intro

Illustrates selecting a 'User' and their associated 'Profile' using go-pg's `Relation` method. This approach leverages the defined `rel:has-one` tag in the 'User' struct for a more concise query.

```go
db.Model(&user).Relation("Profile").Where("user.id = ?", 123).Select()
```

--------------------------------

### PostgreSQL: Update rows in consistent order (Good Example)

Source: https://pg.uptrace.dev/migrations-zero-downtime

Illustrates the correct way to update rows in concurrent transactions by ensuring a consistent order, which prevents deadlocks. This is the recommended approach.

```sql
-- transaction 1
UPDATE test WHERE id IN (1, 2);

-- transaction 2
UPDATE test WHERE id IN (1, 2);

```

--------------------------------

### Create ZFS Datasets for PostgreSQL Data and WAL

Source: https://pg.uptrace.dev/zfs

Sets up two ZFS datasets, 'pg/data' for PostgreSQL data and 'pg/wal-14' for the write-ahead log, mounting them to their respective PostgreSQL directories. It includes steps for moving existing data, creating datasets, copying data back, and fixing permissions.

```bash
# Move PostgreSQL files to a temp location.
mv /var/lib/postgresql/14/main/pg_wal /tmp/pg_wal
mv /var/lib/postgresql /tmp/postgresql

# Create datasets.
zfs create pg/data -o mountpoint=/var/lib/postgresql
zfs create pg/wal-14 -o mountpoint=/var/lib/postgresql/14/main/pg_wal

# Move PostgreSQL files back.
cp -r /tmp/postgresql/* /var/lib/postgresql
cp -r /tmp/pg_wal/* /var/lib/postgresql/14/main/pg_wal

# Fix permissions.
chmod 0750 /var/lib/postgresql
chmod 0750 /var/lib/postgresql/14/main/pg_wal
```

--------------------------------

### Override Default Table Name Pluralization in go-pg

Source: https://pg.uptrace.dev/models

Provides an example of how to override the default table name pluralization behavior in go-pg by setting a custom `TableNameInflector`. This allows for custom prefixes or naming schemes for tables.

```go
import "github.com/go-pg/pg/v10/orm"

func init() {
    orm.SetTableNameInflector(func(s string) string {
        return "myprefix_" + s
    })
}

```

--------------------------------

### Connect to Google Cloud SQL using Proxy (go-pg)

Source: https://pg.uptrace.dev/faq

Illustrates how to establish a database connection to Google Cloud SQL using the cloud SQL proxy with go-pg. It utilizes a custom `Dialer` to route connections through the proxy.

```go
import "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/proxy"

db := pg.Connect(&pg.Options{
    Dialer: func(ctx context.Context, network, addr string) (net.Conn, error) {
        return proxy.DialContext(ctx, "project-name:region:instance-name")
    },
})

```

--------------------------------

### Optimize PostgreSQL for Testing

Source: https://pg.uptrace.dev/faq

Suggests specific PostgreSQL configuration settings to improve test execution speed. These settings disable certain durability features, making writes faster but potentially less safe for production environments.

```sql
fsync = off
synchronous_commit = off
archive_mode = off
wal_level = minimal
shared_buffers = 512MB

```

--------------------------------

### Insert map

Source: https://pg.uptrace.dev/queries

Demonstrates inserting data provided as a map[string]interface{} into the 'books' table.

```go
values := map[string]interface{}{
    "title": "title1",
    "text":  "text1",
}
_, err := db.Model(&values).TableExpr("books").Insert()
```

--------------------------------

### PostgreSQL: Adding a NOT NULL column (Error Example)

Source: https://pg.uptrace.dev/migrations-zero-downtime

Shows the error encountered when attempting to add a 'NOT NULL' column to a PostgreSQL table that already contains rows. This operation fails because existing rows would have null values for the new column.

```sql
> ALTER TABLE test ADD COLUMN foo text NOT NULL;

ERROR:  column "foo" of relation "test" contains null values

```

--------------------------------

### Go: Build and Execute a SQL Query with go-pg

Source: https://pg.uptrace.dev/queries

Demonstrates how to use the go-pg query builder in Go to construct and execute a SQL query. It utilizes `ColumnExpr` for specific column selection and `Where` for filtering, with `pg.Ident` for proper identifier quoting. The `Select` method executes the query.

```Go
err := db.Model(book).
    ColumnExpr("lower(name)").
    Where("? = ?", pg.Ident("id"), "some-id").
    Select()

```

--------------------------------

### Select Book using WHERE ... AND ... with go-pg

Source: https://pg.uptrace.dev/queries

Demonstrates how to combine multiple conditions in a WHERE clause using the `Where` method twice, which implicitly creates an AND condition. It also shows how to apply a `Limit`.

```go
err := db.Model(book).
    Where("id > ?", 100).
    Where("title LIKE ?", "my%").
    Limit(1).
    Select()
```

--------------------------------

### Insert Zero Values in go-pg

Source: https://pg.uptrace.dev/faq

Explains how to insert zero values (like `0`, `false`, empty string, `nil`) in go-pg. By default, these are marshaled as SQL `DEFAULT` or `NULL`. Using the `pg:",use_zero"` tag ensures they are inserted as provided.

```go
type Item struct {
    Available bool `pg:",use_zero"`
}

```

--------------------------------

### Replace encoding/json with go-json for Faster JSON Operations

Source: https://pg.uptrace.dev/rest-api

Shows how to replace the standard Go 'encoding/json' package with 'github.com/goccy/go-json' for improved JSON encoding and decoding performance. This change is a drop-in replacement and offers 2-3x speedup, with support for zero-allocation encoding using []byte.

```diff
-import "encoding/json"
+import "github.com/goccy/go-json"
```

```go
func Marshal(v any) ([]byte, error)
func Unmarshal(data []byte, v any) error
```

--------------------------------

### Global DB Placeholders in go-pg

Source: https://pg.uptrace.dev/placeholders

Shows how to use global DB placeholders in go-pg ORM by creating database instances with specific parameters using `WithParam`. This feature is useful for managing different configurations, like PostgreSQL schemas, across multiple database connections.

```go
db1 := db.WithParam("SCHEMA", "foo")
db2 := db.WithParam("SCHEMA", "bar")

db1.TableExpr("?SCHEMA.table")
// FROM foo.table

db2.TableExpr("?SCHEMA.table")
// FROM bar.table
```

--------------------------------

### Create Index with Exec in Go

Source: https://pg.uptrace.dev/queries

Executes a custom SQL command to create an index concurrently on a table. It uses the Exec() method with a raw SQL string, utilizing placeholder syntax for table names. This is useful for database schema management.

```go
"_, err := db.Model((*Book)(nil)).Exec(`
    CREATE INDEX CONCURRENTLY books_author_id_idx
    ON ?TableName (author_id)
`)
```

--------------------------------

### Select Book using WHERE ... AND (... OR ...) with go-pg

Source: https://pg.uptrace.dev/queries

Illustrates a more complex WHERE clause construction using `WhereGroup` to group OR conditions within an AND clause. This allows for flexible and readable complex query building.

```go
err := db.Model(book).
    Where("title LIKE ?", "my%").
    WhereGroup(func(q *pg.Query) (*pg.Query, error) {
        q = q.WhereOr("id = 1").
            WhereOr("id = 2")
        return q, nil
    }).
    Limit(1).
    Select()
```

--------------------------------

### Configure ZFS Compression and Other Settings

Source: https://pg.uptrace.dev/zfs

Applies various ZFS settings to optimize for PostgreSQL, including setting recordsize, enabling LZ4 or ZSTD compression, disabling atime updates, enabling extended attributes, setting logbias, and reducing redundant metadata. These settings aim to improve compression ratios and performance.

```bash
# same as default
zfs set recordsize=128k pg

# enable lz4 compression
zfs set compression=lz4 pg
# or zstd compression
#zfs set compression=zstd-3 pg

# disable access time updates
zfs set atime=off pg

# enable improved extended attributes
zfs set xattr=sa pg

# same as default
zfs set logbias=latency pg

# reduce amount of metadata (may improve random writes)
zfs set redundant_metadata=most pg
```

--------------------------------

### Implementing Model Hooks in Go

Source: https://pg.uptrace.dev/hooks

This Go code demonstrates how to implement various hooks for a 'Book' model in the Uptrace ORM. It shows BeforeScan, AfterScan, AfterSelect, BeforeInsert, AfterInsert, BeforeUpdate, AfterUpdate, BeforeDelete, and AfterDelete hooks, along with compile-time checks to ensure correct implementation.

```go
type Book struct{}

var _ pg.BeforeScanHook = (*Book)(nil)

func (*Book) BeforeScan(ctx context.Context) error {
    return nil
}

var _ pg.AfterScanHook = (*Book)(nil)

func (*Book) AfterScan(ctx context.Context) error {
    return nil
}

var _ pg.AfterSelectHook = (*Book)(nil)

func (b *Book) AfterSelect(ctx context.Context) error {
    return nil
}

var _ pg.BeforeInsertHook = (*Book)(nil)

func (b *Book) BeforeInsert(ctx context.Context) (context.Context, error) {
    return ctx, nil
}

var _ pg.AfterInsertHook = (*Book)(nil)

func (b *Book) AfterInsert(ctx context.Context) error {
    return updateBookCache(b)
}

var _ pg.BeforeUpdateHook = (*Book)(nil)

func (b *Book) BeforeUpdate(ctx context.Context) (context.Context, error) {
   return ctx, nil
}

var _ pg.AfterUpdateHook = (*Book)(nil)

func (b *Book) AfterUpdate(ctx context.Context) error {
   return nil
}

var _ pg.BeforeDeleteHook = (*Book)(nil)

func (b *Book) BeforeDelete(ctx context.Context) (context.Context, error) {
   return ctx, nil
}

var _ pg.AfterDeleteHook = (*Book)(nil)

func (b *Book) AfterDelete(ctx context.Context) error {
   return nil
}
```

--------------------------------

### Select Author ID and Book Count using go-pg

Source: https://pg.uptrace.dev/queries

Shows how to select aggregated data, specifically the author ID and the count of books per author. It uses `ColumnExpr` for the count and `Group` and `Order` for aggregation and sorting.

```go
var res []struct {
    AuthorId  int
    BookCount int
}
err := db.Model((*Book)(nil)).
    Column("author_id").
    ColumnExpr("count(*) AS book_count").
    Group("author_id").
    Order("book_count DESC").
    Select(&res)
```

--------------------------------

### Select Book with Manual Author Join in Go

Source: https://pg.uptrace.dev/queries

Selects a book record and manually joins it with the authors table. It uses ColumnExpr() to select specific columns from both the book and author tables, aliasing author columns for ORM mapping. The Join() method specifies the join type and condition.

```go
book := new(Book)
err := db.Model(book).
    ColumnExpr("book.*").
    ColumnExpr("a.id AS author__id, a.name AS author__name").
    Join("JOIN authors AS a ON a.id = book.author_id").
    First()
```

--------------------------------

### Select Book IDs as PostgreSQL Array using go-pg

Source: https://pg.uptrace.dev/queries

Demonstrates how to fetch all book IDs and aggregate them into a PostgreSQL array using `array_agg`. The result is then scanned into a Go integer slice using `pg.Array`.

```go
var ids []int
err := db.Model((*Book)(nil)).ColumnExpr("array_agg(id)").Select(pg.Array(&ids))
```

--------------------------------

### Join Conditions with JoinOn in Go

Source: https://pg.uptrace.dev/queries

Demonstrates splitting join conditions using the JoinOn() method for more complex joins. This allows for multiple ON clauses, including conditions with parameters. It's an alternative to specifying the entire join condition in a single Join() call.

```go
q.Join("LEFT JOIN authors AS a").
    JoinOn("a.id = book.author_id").
    JoinOn("a.active = ?", true)
```

--------------------------------

### Select Book by Primary Key using go-pg

Source: https://pg.uptrace.dev/queries

Demonstrates how to select a single book record by its primary key (ID) using the go-pg ORM. It utilizes the `Where` and `Select` methods to construct and execute the query. The result is mapped to a Book struct.

```go
book := new(Book)
err := db.Model(book).Where("id = ?", 1).Select()
```

--------------------------------

### SQL for Parent Partitioned Table

Source: https://pg.uptrace.dev/table-partitioning

The SQL query generated by `go-pg` for creating a partitioned table named 'logs'. It defines columns, a primary key including the partition key, and specifies the partitioning method as RANGE on 'log_time'.

```sql
CREATE TABLE IF NOT EXISTS "logs" (
    "id" bigserial,
    "log_string" text,
    "log_time" timestamptz,
    PRIMARY KEY ("id", "log_time")
) PARTITION BY RANGE(log_time)

```

--------------------------------

### Select existing or insert new book

Source: https://pg.uptrace.dev/queries

Attempts to select a book by its title. If no book is found, it proceeds to insert a new book record. The OnConflict option is commented out but can be used for specific conflict handling.

```go
_, err := db.Model(book).
    Where("title = ?title").
    OnConflict("DO NOTHING"). // optional
    SelectOrInsert()
```

--------------------------------

### Updating Database Columns with Key-Value Pairs in Go

Source: https://pg.uptrace.dev/sql-null-go-zero-values

Shows how to update specific columns in a database table using a map of key-value pairs. This method is useful for partial updates where not all fields of a struct are being modified.

```go
kv := map[string]interface{}{
    "col1": "val1",
    "col2": 123,
}
db.Model(&kv).TableExpr("items").Update()
```

--------------------------------

### SQL: Generated Query from go-pg

Source: https://pg.uptrace.dev/queries

Presents the SQL query generated by the preceding Go code snippet using the go-pg query builder. This showcases how go-pg translates Go model operations into a safe and executable SQL statement, including function application and placeholder substitution.

```sql
SELECT lower(name)
FROM "books"
WHERE "id" = 'some-id'

```

--------------------------------

### Check PostgreSQL Connection Health (go-pg v10+)

Source: https://pg.uptrace.dev/faq

Demonstrates how to check the health of a PostgreSQL database connection using the `db.Ping()` method available from v10 onwards. This is a straightforward way to verify connectivity.

```go
if err := db.Ping(ctx); err != nil {
    fmt.Println("PostgreSQL is down", err)
}

```

--------------------------------

### Configure PostgreSQL to Log All Queries

Source: https://pg.uptrace.dev/faq

Details how to configure a PostgreSQL server to log all executed queries by modifying `postgresql.conf`. This method is independent of the go-pg library and provides server-level logging.

```sql
log_statement = 'all'
log_min_duration_statement = 0

```

--------------------------------

### Count Books using go-pg

Source: https://pg.uptrace.dev/queries

Shows how to efficiently count the total number of records in the 'books' table using the `Count` method. This is a common operation for pagination or statistics.

```go
count, err := db.Model((*Book)(nil)).Count()
```

--------------------------------

### Create CTE using WrapWith

Source: https://pg.uptrace.dev/queries

An alternative method to create CTEs using the WrapWith function. This approach allows defining the CTE inline with the main query construction.

```go
err := db.Model(&books).
    Where("author_id = ?", 1).
    WrapWith("author_books").
    Table("author_books").
    Select(&books)
```

--------------------------------

### Insert from Select query

Source: https://pg.uptrace.dev/queries

Inserts data into the 'books' table by selecting from another query (defined as a CTE named 'sel').

```go
selq := db.Model((*Book)(nil)).
    Where("1 = 1")

_, err := db.Model((*Book)(nil)).
    With("sel", selq).
    TableExpr("sel").
    Insert()
```

--------------------------------

### Customize JSON Output with MarshalJSON in Go

Source: https://pg.uptrace.dev/rest-api

Demonstrates the correct way to implement the `MarshalJSON` method in Go to customize JSON output, avoiding stack overflow errors caused by infinite recursion. It involves defining a new type based on the original to break the recursion when marshaling.

```go
type User struct{
    Name string
}

func (u *User) MarshalJSON() ([]byte, error) {
    if u.Name == "" {
        u.Name = "anonymous"
    }
    // This call causes infinite recursion.
    return json.Marshal(u)
}
```

```go
type jsonUser User

func (u *User) MarshalJSON() ([]byte, error) {
    if u.Name == "" {
        u.Name = "anonymous"
    }
    return json.Marshal((*jsonUser)(u))
}
```

--------------------------------

### Create CTE using WITH statement

Source: https://pg.uptrace.dev/queries

Demonstrates how to define and use Common Table Expressions (CTEs) with the Uptrace library. It involves creating a CTE named 'author_books' from a model query and then selecting from this CTE.

```go
authorBooks := db.Model((*Book)(nil)).Where("author_id = ?", 1)

err := db.Model().
    With("author_books", authorBooks).
    Table("author_books").
    Select(&books)
```

--------------------------------

### Representing SQL NULL with Go Pointers or sql.Null* Types

Source: https://pg.uptrace.dev/sql-null-go-zero-values

Demonstrates how to use Go pointers or sql.Null* types to accurately represent SQL NULL values. This is crucial for distinguishing between a zero value and an actual NULL in the database.

```go
type Item struct {
    Active *bool
    // or
    Active sql.NullBool
}

// Examples:
// (*bool)(nil) and sql.NullBool{} represent NULL.
// (*bool)(false) and sql.NullBool{Valid: true} represent FALSE.
// (*bool)(true) and sql.NullBool{Valid: true, Value: true} represent TRUE.
```

--------------------------------

### Select book and associated author

Source: https://pg.uptrace.dev/queries

Demonstrates selecting a book along with its associated author details by using the Relation method. This performs a JOIN operation.

```go
err := db.Model(book).Relation("Author").Select()
```

--------------------------------

### Handle Errors with Middlewares in Go

Source: https://pg.uptrace.dev/rest-api

Presents a pattern for handling errors in Go HTTP handlers using middlewares. Instead of repeating error-checking logic in each handler, a middleware can centralize error management, returning errors from handlers and processing them uniformly.

```go
func handler(w http.ResponseWriter, req *http.Request) {
    if err := processRequest(req); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }
    if err := json.NewEncoder(w).Encode(H{}); err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
}
```

```go
func handler(w http.ResponseWriter, req *http.Request) error {
    if err := processRequest(req); err != nil {
        return err
    }
    if err := json.NewEncoder(w).Encode(H{}); err != nil {
        return err
    }
    return nil
}

func errorHandler(next func(w http.ResponseWriter, req *http.Request) error) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, req *http.Request) {
        if err := next(w, req); err != nil {
            // You should change status code depending on the error.
            http.Error(w, err.Error(), http.StatusBadRequest)
        }
    })
}
```

--------------------------------

### Enable Faster JSON Encoding with Segmentio

Source: https://pg.uptrace.dev/extra-modules

This snippet demonstrates how to integrate the segmentio/encoding/json package for faster JSON encoding within the pg_uptrace_dev project. It requires importing the necessary packages and setting the JSON provider during initialization. This can improve performance for applications heavily reliant on JSON serialization.

```go
import (
    "github.com/go-pg/pg/v10/pgjson"
    "github.com/go-pg/pg/extra/pgsegment/v10"
)

func init() {
    pgjson.SetProvider(pgsegment.NewJSONProvider())
}
```

--------------------------------

### Select Book using WHERE ... OR ... with go-pg

Source: https://pg.uptrace.dev/queries

Shows how to use the `WhereOr` method to create an OR condition within the WHERE clause. This allows selecting records that match either the first condition or the second condition.

```go
err := db.Model(book).
    Where("id > ?", 100).
    WhereOr("title LIKE ?", "my%").
    Limit(1).
    Select()
```

--------------------------------

### Simplified Transactions with RunInTransaction in Go

Source: https://pg.uptrace.dev/transactions

Shows how to use the `RunInTransaction` helper function in go-pg for simplified transaction management. This function automatically handles rollback on error and commit on success, reducing boilerplate code. The provided function is executed within the transaction context.

```go
if err := db.RunInTransaction(ctx, func(tx *Tx) error {
    return doSomething(ctx, tx)
}); err != nil {
    panic(err)
}

```

--------------------------------

### Select book ID and associated author ID

Source: https://pg.uptrace.dev/queries

Shows how to select specific columns from a model and its relation, including the author's ID aliased as 'author__id'.

```go
err := db.Model(book).Column("book.id").Relation("Author.id").Select()
```

--------------------------------

### Select Specific Columns into Variables using go-pg

Source: https://pg.uptrace.dev/queries

Illustrates selecting specific columns ('title', 'text') and directly mapping them into Go string variables. This is useful when you only need a few fields and don't want to map them to a full struct.

```go
var title, text string
err := db.Model((*Book)(nil)).
    Column("title", "text").
    Where("id = ?", 1).
    Select(&title, &text)
```

--------------------------------

### Create ZFS Pool for PostgreSQL

Source: https://pg.uptrace.dev/zfs

Creates a ZFS pool named 'pg' on the specified device, enabling auto-expansion. This pool will host PostgreSQL data.

```bash
zpool create -o autoexpand=on pg /dev/nvme1n1
```

--------------------------------

### Insert struct and return all columns

Source: https://pg.uptrace.dev/queries

Inserts a new book record from a struct and returns all columns of the newly inserted row.

```go
err := db.Model(book).Returning("*").Insert()
```

--------------------------------

### Execute Custom Query with QueryOne in Go

Source: https://pg.uptrace.dev/queries

Executes a custom SQL query and scans the result into a variable using QueryOne(). This method is suitable for queries that return a single row or aggregate values. It uses placeholder syntax for table names and aliases.

```go
var count int
_, err := db.Model((*Book)(nil)).QueryOne(pg.Scan(&count), `
    SELECT count(*)
    FROM ?TableName AS ?TableAlias
`)
```

--------------------------------

### Tail PostgreSQL Log File

Source: https://pg.uptrace.dev/faq

Provides a command to continuously monitor the PostgreSQL log file for query activity. This is used in conjunction with server-level query logging to observe database operations.

```bash
tail -f /var/log/postgresql/postgresql-9.5-main.log

```

--------------------------------

### Create ZFS Pool with Specific Ashift Value

Source: https://pg.uptrace.dev/zfs

Creates a ZFS pool named 'pg' with a specific ashift value (12, corresponding to 4KB sectors) and enables auto-expansion. This is recommended when the underlying drive's sector size is known, particularly for cloud storage like Amazon EBS.

```bash
zpool create -o ashift=12 -o autoexpand=on pg /dev/nvme1n1
```

--------------------------------

### Insert struct and return primary keys

Source: https://pg.uptrace.dev/queries

Inserts a new book record from a struct and returns the generated primary key(s).

```go
err := db.Model(book).Insert()
```
Tokens:
Raw