How To Communication Golang with Postgres Database
Dependency
Make sure you have created a project with go mod init postgres-go in the postgres-go folder and the dependency that we will use is using
1import _ "github.com/lib/pq"If it isn’t there, we can try downloading the dependency above first using the command below.
1go get github.com/lib/pgCreate Database Connection Function
In creating this database connection function we will use a function from SQL which has been prepared by the go library. Let’s create this database connection function by returning return db connection.
1func openDB(dsn string, setLimits bool) (*sql.DB, error) {
2 db, err := sql.Open("postgres", dsn)
3 if err != nil {
4 return nil, err
5 }
6
7 if setLimits {
8 fmt.Println("setting limits")
9 db.SetMaxOpenConns(5)
10 db.SetMaxIdleConns(5)
11 }
12
13 ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
14 defer cancel()
15
16 err = db.PingContext(ctx)
17 if err != nil {
18 return nil, err
19 }
20
21 return db, nil
22}In this connection function we use sql.Open to connect to the Postgres database where we also receive parameters from the main function dsn which contains the location server, user, password and the destination database.
1db, err := sql.Open("postgres", dsn)Then we also provide some configuration to ensure this connection has a SetMaxOpenConns Connection when we run this service and we also set SetMaxIdleConns to ensure our connection is not misconfigured and runs normally.
1db.SetMaxOpenConns(5)
2db.SetMaxIdleConns(5)Next, we also make sure to create a context set timeout limit, which is useful here to ensure the connection to this database doesn’t take too long. In this case, we set the maximum timeout limit to 5 seconds.
1ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
2defer cancel()And finally, we do PingContext to ensure that the connection to this database is not hampered by anything, for example delay or network.
1err = db.PingContext(ctx)Create Services and Queries to the Database
At this stage, we will create several functions to access data from the database. We also create functions with interface encapsulation. Let’s just run it and practice. Create a services folder then we also create a file in that folder with the file name contract.go.
1package services
2
3type Album struct {
4 ID int64
5 Title string
6 Artist string
7 Price float32
8}
9
10type AlbumService interface {
11 Get(id int64) (*Album, error)
12 Create(album *Album) error
13 GetAllAlbum() ([]Album, error)
14 BatchCreate(albums []Album) error
15 Update(Album Album) error
16 Delete(id int64) error
17}We can see that the contract file contains struct data which will be used as the basis for the album data structure.
Meanwhile, the functions that we will use are the requirements for adding, changing, deleting. Below we create an interface which we will later implement these functions too.
1type AlbumService interface {
2 Get(id int64) (*Album, error)
3 Create(album *Album) error
4 GetAllAlbum() ([]Album, error)
5 BatchCreate(albums []Album) error
6 Update(Album Album) error
7 Delete(id int64) error
8}Retrieve Album Data by Id
Let’s create a function that can later retrieve data from the Postgres database. Before going to the related functions, we need to first define the existing functions by including the database connection that we defined at the beginning.
1type PostgresService struct {
2 db *sql.DB
3}
4
5func NewPostgresService(db *sql.DB) *PostgresService {
6 return &PostgresService{db: db}
7}We use this initialization so that we can create a function that retrieves data into the database with an existing connection. Then next, let’s create a function to retrieve album data using the id parameter.
1func (p *PostgresService) Get(id int64) (*Album, error) {
2 query := `
3 SELECT id, title, artist, price
4 FROM public.album
5 WHERE id = $1`
6
7 var album Album
8
9 ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
10 defer cancel()
11
12 err := p.db.QueryRowContext(ctx, query, id).Scan(
13 &album.ID,
14 &album.Title,
15 &album.Artist,
16 &album.Price,
17 )
18
19 if err != nil {
20 return nil, err
21 }
22
23 return &album, nil
24}In the function above we see creating a query into the database as below.
1query := `
2 SELECT id, title, artist, price
3 FROM public.album
4 WHERE id = $1`Then we also make sure that the query needs to be timed out so that it doesn’t take too long to retrieve data into the database. In this program we use a timeout of 15 seconds.
1ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
2defer cancel()Next, we also use the QueryRowContext command to retrieve the data after we initialize the connection to the database.
1err := p.db.QueryRowContext(ctx, query, id).Scan(
2 &album.ID,
3 &album.Title,
4 &album.Artist,
5 &album.Price,
6 )The QueryRowContext command is used to retrieve data from the database by returning only one row of data. Next, if the data exists, we will save the data into the album variable.
Save Album Data to Database
Apart from retrieving data from the database, we also have to be able to send data from the system to the database. Below we will create a create function.
1func (p *PostgresService) Create(album *Album) error {
2 query := `
3 INSERT INTO public.album (id, title, artist, price)
4 VALUES ($1, $2, $3, $4)
5 RETURNING id`
6
7 ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
8 defer cancel()
9
10 return p.db.QueryRowContext(ctx, query, album.Title, album.Artist, album.Price).Scan(&album.ID)
11}As usual, because this is a native query where we define our own query in the code, we need to define the query first. The following is a query to insert data into the database.
1query := `
2 INSERT INTO public.album (id, title, artist, price)
3 VALUES ($1, $2, $3, $4)
4 RETURNING id`Set a timeout to be more secure and then we execute create. Here we use the same query as retrieving data because we will retrieve the ID after it is added.
1return p.db.QueryRowContext(ctx, query, album.Title, album.Artist, album.Price).Scan(&album.ID)Retrieve All Data in Database
Let’s create the next function, which is to retrieve all data from the database as follows.
1func (p *PostgresService) GetAllAlbum() ([]Album, error) {
2 query := `
3 SELECT id, title, artist, price
4 FROM album`
5
6 ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
7 defer cancel()
8
9 var albums []Album
10
11 rows, err := p.db.QueryContext(ctx, query)
12 if err != nil {
13 return nil, err
14 }
15 defer rows.Close()
16
17 for rows.Next() {
18 var album Album
19 err := rows.Scan(
20 &album.ID,
21 &album.Title,
22 &album.Artist,
23 &album.Price,
24 )
25 if err != nil {
26 return nil, err
27 }
28
29 albums = append(albums, album)
30 }
31
32 return albums, nil
33}In this function we will retrieve all the data and as we can see the query function used is different from the previous query, namely we use the query QueryContext where the results of this function return have multi rows so we need to loop to store the data in a struct array .
1 rows, err := p.db.QueryContext(ctx, query)
2 if err != nil {
3 return nil, err
4 }
5 defer rows.Close()The following QueryContext is used to retrieve data and don’t forget that because this return has multiple rows, we also need to close the connection for each row at the end of the function. Here we use defer so that it is executed after returning to the main function.
1defer rows.Close()Next we will take data from rows to move it into a struct array.
1for rows.Next() {
2 var album Album
3 err := rows.Scan(
4 &album.ID,
5 &album.Title,
6 &album.Artist,
7 &album.Price,
8 )
9 if err != nil {
10 return nil, err
11 }
12
13 albums = append(albums, album)
14}And ends with the command return albums, nil in the function.
Create Batch Add Album
In this function we will create data batching where we send data in more than one quantity. We can use this for data that we send at once. Let’s take a look at the function we are going to create.
1func (p *PostgresService) BatchCreate(albums []Album) error {
2 tx, err := p.db.Begin()
3 if err != nil {
4 return err
5 }
6 defer tx.Rollback()
7
8 ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
9 defer cancel()
10
11 query := `INSERT INTO album (title, artist, price) VALUES ($1, $2, $3)`
12
13 for _, album := range albums {
14 _, err := tx.ExecContext(ctx, query, album.Title, album.Artist, album.Price)
15 if err != nil {
16 log.Printf("error execute insert err: %v", err)
17 continue
18 }
19 }
20
21 err = tx.Commit()
22 if err != nil {
23 return err
24 }
25
26 return nil
27}In this batch function we use several transaction batching commands into the database. In the initial stage we will open a transaction to the DB with the command below.
1tx, err := p.db.Begin()
2 if err != nil {
3 return err
4 }
5 defer tx.Rollback()The Begin() function is used to open a transaction and ends with defer tx.Rollback() if something happens in the middle that results in an error.
Next, we repeat to insert data into the database.
1for _, album := range albums {
2 _, err := tx.ExecContext(ctx, query, album.Title, album.Artist, album.Price)
3 if err != nil {
4 log.Printf("error execute insert err: %v", err)
5 continue
6 }
7}When creating data into the database, we use the ExecContext function which is useful for storing it temporarily in the database. And ends with Commit() to save permanently to the database.
1err = tx.Commit()
2if err != nil {
3 return err
4}Change album data
To make changes to one data we need to create a function that sends the id parameter to select the data to be changed. Below are the complete functions for changing albums.
1func (p *PostgresService) Update(album Album) error {
2 ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
3 defer cancel()
4
5 query := `UPDATE album set title=$1, artist=$2, price=$3 WHERE id=$4`
6 result, err := p.db.ExecContext(ctx, query, album.Title, album.Artist, album.Price, album.ID)
7 if err != nil {
8 return err
9 }
10
11 rows, err := result.RowsAffected()
12 if err != nil {
13 return err
14 }
15
16 fmt.Printf("Affected update : %d", rows)
17 return nil
18}ExecContext command is used the same as add because we can use this operation as long as the query needs to be updated, in this case we use it to update data.1query := `UPDATE album set title=$1, artist=$2, price=$3 WHERE id=$4`
2result, err := p.db.ExecContext(ctx, query, album.Title, album.Artist, album.Price, album.ID)
3if err != nil {
4 return err
5}The next command is to confirm whether the data has been successfully changed or not. Here we use the RowsAffected() function which ensures whether there are rows of data that have been updated after this operation or not.
1rows, err := result.RowsAffected()
2if err != nil {
3 return err
4}Delete Album data
The operation or function to delete data in this program we delete one by one based on the input parameters of the function. Here are the complete functions.
1func (p *PostgresService) Delete(id int64) error {
2 ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
3 defer cancel()
4
5 query := `DELETE from album WHERE id=$1`
6 result, err := p.db.ExecContext(ctx, query, id)
7 if err != nil {
8 return err
9 }
10
11 rows, err := result.RowsAffected()
12 if err != nil {
13 return err
14 }
15 fmt.Printf("Affected delete : %d", rows)
16 return nil
17}In this function we call ExecContext to delete data into the database. This is used the same as update and insert.
1query := `DELETE from album WHERE id=$1`
2result, err := p.db.ExecContext(ctx, query, id)
3if err != nil {
4 return err
5}Followed by the RowsAffected() command to ensure that the data we want to delete is successful or not found in the database.
1rows, err := result.RowsAffected()
2if err != nil {
3 return err
4}Initialize the Service module on Main
After we create a service program in which we create each function for operations in the database, it’s time for us to integrate all of this into the main program. Below is the complete function play.
1type app struct {
2 AlbumService services.AlbumService
3}
4
5func main() {
6 err := godotenv.Load(".env")
7 if err != nil {
8 log.Fatalf("Error loading .env file")
9 }
10
11 db, err := openDB(os.Getenv("POSTGRES_URL"), true)
12 if err != nil {
13 log.Fatalln(err)
14 }
15 defer func(db *sql.DB) {
16 err := db.Close()
17 if err != nil {
18 log.Fatalln(err)
19 }
20 }(db)
21
22 application := app{AlbumService: services.NewPostgresService(db)}
23
24 albums, err := application.AlbumService.GetAllAlbum()
25 if err != nil {
26 log.Fatal(err)
27 }
28 fmt.Printf("all album : %v\n", albums)
29
30 albumNo1, err := application.AlbumService.Get(1)
31 if err != nil {
32 log.Fatal(err)
33 }
34 fmt.Printf("album number 1 : %v\n", albumNo1)
35
36 err = application.AlbumService.BatchCreate([]services.Album{
37 {Title: "Hari Yang Cerah", Artist: "Peterpan", Price: 50000},
38 {Title: "Sebuah Nama Sebuah Cerita", Artist: "Peterpan", Price: 50000},
39 {Title: "Bintang Di surga", Artist: "Peterpan", Price: 60000},
40 })
41 if err != nil {
42 log.Fatal(err)
43 }
44
45 albumNo1.Price = 70000
46 err = application.AlbumService.Update(*albumNo1)
47 if err != nil {
48 log.Fatal(err)
49 }
50
51 err = application.AlbumService.Delete(albumNo1.ID)
52 if err != nil {
53 log.Fatal(err)
54 }
55}Let’s discuss one by one what commands are executed in the main function.
Retrieves configuration from .env file
In this main program, we save all connection configurations in the database, stored in separate files, for example we save them in the .env file.
1err := godotenv.Load(".env")
2if err != nil {
3 log.Fatalf("Error loading .env file")
4}The command above is used to load configuration data in the .env file. then saved to the env of the server or computer it is running on. Usually you can retrieve data from the environment with a command like this.
1os.Getenv("<nama-konfigurasi>")In this case we save the URL connection in a database that is saved in the environment so we need to retrieve the configuration data using the command os.Getenv("POSTGRES_URL").
Next, we initialize the connection to the database.
1db, err := openDB(os.Getenv("POSTGRES_URL"), true)
2if err != nil {
3 log.Fatalln(err)
4}So now we also need to make sure this database connection needs to end with close. We can also use defer.
1defer func(db *sql.DB) {
2 err := db.Close()
3 if err != nil {
4 log.Fatalln(err)
5 }
6}(db)In order for our service to access the main program, we need to initialize the service module.
1application := app{AlbumService: services.NewPostgresService(db)}The rest is calling the service functions that we have defined in the function.