This file contains an example of how to perform basic SQL operations in Go using the pgx
library. The example code connects to a PostgreSQL database and performs the following operations:
- Fetch all albums from the
album
table - Fetch a single album from the
album
table - Insert new albums into the
album
table
The first step in performing SQL operations is to connect to the database. This is done using the pgxpool.New
function, which takes a context.Context
and a connection string as arguments. The connection string should be in the format postgresql://user:password@host:port/database
.
DATABASE_URL := "postgresql://user:password@host:port/database"
conn, err := pgxpool.New(context.Background(), DATABASE_URL)
if err != nil {
// Handle error
}
defer conn.Close()
To fetch data from the database, you can use the conn.Query
function. This function takes a context.Context
and a SQL query string as arguments, and returns a pgx.Rows
object that can be used to iterate over the results.
rows, err := conn.Query(context.Background(), "SELECT * FROM album")
if err != nil {
// Handle error
}
defer rows.Close()
for rows.Next() {
var album Album
err := rows.Scan(&album.ID, &album.Title, &album.Artist, &album.Price)
if err != nil {
// Handle error
}
// Do something with album
}
To fetch a single row from the database, you can use the conn.QueryRow
function. This function takes a context.Context
and a SQL query string as arguments, and returns a pgx.Row
object that can be used to scan the result.
var album Album
err := conn.QueryRow(context.Background(), "SELECT * FROM album WHERE id = $1", id).Scan(&album.ID, &album.Title, &album.Artist, &album.Price)
if err != nil {
// Handle error
}
// Do something with album
To insert data into the database, you can use the conn.Exec
function. This function takes a context.Context
and a SQL query string as arguments, and returns a pgx.CommandTag
object that can be used to check the number of rows affected.
result, err := conn.Exec(context.Background(), "INSERT INTO album (title, artist, price) VALUES ($1, $2, $3)", album.Title, album.Artist, album.Price)
if err != nil {
// Handle error
}
if result.RowsAffected() != 1 {
// Handle error
}
That's it! This should give you a basic understanding of how to perform SQL operations in Go using the pgx
library.