Dependency
Make sure you have created a project with go mod init mssql-go in the mssql-go folder and the dependency that we will use is using
import _ "github.com/microsoft/go-mssqldb"
If it isn’t there, we can try downloading the dependency above first using the command below.
go get github.com/microsoft/go-mssqldb
Install MS SQL Server and create a database with the name recordings and also the table album for the needs of this project. If you don’t have SQL Server Database then we need to install it first. You can also use Docker for the installation.
If we use a Mackbook M1 we can also use the Docker below
docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=MyPass@word" -e "MSSQL_PID=Developer" -e "MSSQL_USER=SA" -p 1433:1433 -d --name=sql mcr.microsoft.com/azure-sql-edge
Then, to make it easier to access the MS SQL database, you can install the [VS Code MS SQL Connection] plugin (https://marketplace.visualstudio.com/items?itemName=ms-mssql.mssql)
Database Schema
After the MSSQL installation is complete and we can connect to SQL Server, it’s time for us to continue by preparing the database and tables, following the schema that we will need.
CREATE schema dbo
GO
CREATE TABLE album(
id INT IDENTITY(1,1) PRIMARY KEY,
title NVARCHAR(255),
artist NVARCHAR(255),
price INT
)
GO
Create 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.
func openDB(connString string, setLimits bool) (*sql.DB, error) {
var err error
db, err := sql.Open("sqlserver", connString)
if err != nil {
log.Fatal("Error creating connection pool: ", err.Error())
}
if setLimits {
fmt.Println("setting limits")
db.SetMaxIdleConns(5)
db.SetMaxOpenConns(5)
}
ctx := context.Background()
err = db.PingContext(ctx)
if err != nil {
log.Fatal(err.Error())
}
fmt.Printf("Connected!\n")
return db, nil
}
In this connection function we use sql.Open to make a database connection where we also receive parameters from the main function dsn` which contains the location server, user, password and the destination database.
db, err := sql.Open("sqlserver", 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.
db.SetMaxOpenConns(5)
db.SetMaxIdleConns(5)
Next, we also make sure to set a context timeout limit, which is useful here to ensure that the connection to this database doesn’t take too long. In this case, we set the maximum timeout limit to 5 seconds.
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
And finally, we do PingContext to ensure that the connection to this database is not hampered by anything, for example delay or network.
err = 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.
package services
type Album struct {
ID int64
Title string
Artist string
Price float32
}
type AlbumService interface {
Get(id int64) (*Album, error)
Create(album *Album) error
GetAllAlbum() ([]Album, error)
BatchCreate(albums []Album) error
Update(Album Album) error
Delete(id int64) error
}
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.
type AlbumService interface {
Get(id int64) (*Album, error)
Create(album *Album) error
GetAllAlbum() ([]Album, error)
BatchCreate(albums []Album) error
Update(Album Album) error
Delete(id int64) error
}
Retrieve Album Data by Id
Let’s create a function that can later retrieve data from a SQL Server 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.
type MSSQLService struct {
db *sql.DB
}
func NewMSSQLService(db *sql.DB) *MSSQLService {
return &MSSQLService{db: db}
}
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.
func (p *MSSQLService) Get(id int64) (*Album, error) {
query := `
SELECT id, title, artist, price
FROM dbo.album
WHERE id = @id`
var album Album
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
err := p.db.QueryRowContext(ctx, query, sql.Named("id", id)).Scan(
&album.ID,
&album.Title,
&album.Artist,
&album.Price,
)
if err != nil {
return nil, err
}
return &album, nil
}
In the function above we see creating a query into the database as below.
query := `
SELECT id, title, artist, price
FROM dbo.album
WHERE id = @id`
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.
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
Next, we also use the QueryRowContext command to retrieve the data after we initialize the connection to the database.
err := p.db.QueryRowContext(ctx, query, sql.Named("id", id)).Scan(
&album.ID,
&album.Title,
&album.Artist,
&album.Price,
)
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.
func (p *MSSQLService) Create(album *Album) error {
query := `
INSERT INTO dbo.album (title, artist, price)
VALUES (@title, @artist, @price)`
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
_, err := p.db.ExecContext(ctx, query,
sql.Named("title", album.Title),
sql.Named("artist", album.Artist),
sql.Named("price", album.Price))
if err != nil {
return err
}
return nil
}
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.
query := `
INSERT INTO dbo.album (title, artist, price)
VALUES (@title, @artist, @price)`
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.
_, err := p.db.ExecContext(ctx, query,
sql.Named("title", album.Title),
sql.Named("artist", album.Artist),
sql.Named("price", album.Price))
Retrieve All Data in Database
Let’s create the next function, which is to retrieve all data from the database as follows.
func (p *MSSQLService) GetAllAlbum() ([]Album, error) {
query := `
SELECT id, title, artist, price
FROM dbo.album`
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
defer cancel()
var albums []Album
rows, err := p.db.QueryContext(ctx, query)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var album Album
err := rows.Scan(
&album.ID,
&album.Title,
&album.Artist,
&album.Price,
)
if err != nil {
return nil, err
}
albums = append(albums, album)
}
return albums, nil
}
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 .
rows, err := p.db.QueryContext(ctx, query)
if err != nil {
return nil, err
}
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.
defer rows.Close()
Next we will take data from rows to move it into a struct array.
for rows.Next() {
var album Album
err := rows.Scan(
&album.ID,
&album.Title,
&album.Artist,
&album.Price,
)
if err != nil {
return nil, err
}
albums = append(albums, album)
}
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.
func (p *MSSQLService) BatchCreate(albums []Album) error {
tx, err := p.db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
query := `INSERT INTO dbo.album (title, artist, price) VALUES (@title, @artist, @price)`
for _, album := range albums {
_, err := tx.ExecContext(ctx, query,
sql.Named("title", album.Title),
sql.Named("artist", album.Artist),
sql.Named("price", album.Price))
if err != nil {
log.Printf("error execute insert err: %v", err)
continue
}
}
err = tx.Commit()
if err != nil {
return err
}
return nil
}
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.
tx, err := p.db.Begin()
if err != nil {
return err
}
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.
for _, album := range albums {
_, err := tx.ExecContext(ctx, query,
sql.Named("title", album.Title),
sql.Named("artist", album.Artist),
sql.Named("price", album.Price))
if err != nil {
log.Printf("error execute insert err: %v", err)
continue
}
}
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.
err = tx.Commit()
if err != nil {
return err
}
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.
func (p *MSSQLService) Update(album Album) error {
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
query := `UPDATE dbo.album set title=@title, artist=@artist, price=@price WHERE id=@id`
result, err := p.db.ExecContext(ctx, query,
sql.Named("title", album.Title),
sql.Named("artist", album.Artist),
sql.Named("price", album.Price),
sql.Named("id", album.ID))
if err != nil {
return err
}
rows, err := result.RowsAffected()
if err != nil {
return err
}
fmt.Printf("Affected update : %d", rows)
return nil
}
The 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.
query := `UPDATE dbo.album set title=@title, artist=@artist, price=@price WHERE id=@id`
result, err := p.db.ExecContext(ctx, query,
sql.Named("title", album.Title),
sql.Named("artist", album.Artist),
sql.Named("price", album.Price),
sql.Named("id", album.ID))
if err != nil {
return err
}
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.
rows, err := result.RowsAffected()
if err != nil {
return err
}
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.
func (p *MSSQLService) Delete(id int64) error {
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
query := `DELETE from dbo.album WHERE id=@id`
result, err := p.db.ExecContext(ctx, query, sql.Named("id", id))
if err != nil {
return err
}
rows, err := result.RowsAffected()
if err != nil {
return err
}
fmt.Printf("Affected delete : %d", rows)
return nil
}
In this function we call ExecContext to delete data into the database. This is used the same as update and insert.
query := `DELETE from dbo.album WHERE id=@id`
result, err := p.db.ExecContext(ctx, query, sql.Named("id", id))
if err != nil {
return err
}
Followed by the RowsAffected() command to ensure that the data we want to delete is successful or not found in the database.
rows, err := result.RowsAffected()
if err != nil {
return err
}
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.
type app struct {
AlbumService services.AlbumService
}
func main() {
err := godotenv.Load(".env")
if err != nil {
log.Fatalf("Error loading .env file")
}
db, err := openDB(os.Getenv("MSSQL_URL"), true)
if err != nil {
log.Fatalln(err)
}
defer func(db *sql.DB) {
err := db.Close()
if err != nil {
log.Fatalln(err)
}
}(db)
application := app{AlbumService: services.NewMSSQLService(db)}
err = application.AlbumService.BatchCreate([]services.Album{
{Title: "Hari Yang Cerah", Artist: "Peterpan", Price: 50000},
{Title: "Sebuah Nama Sebuah Cerita", Artist: "Peterpan", Price: 50000},
{Title: "Bintang Di surga", Artist: "Peterpan", Price: 60000},
})
if err != nil {
log.Fatal(err)
}
albums, err := application.AlbumService.GetAllAlbum()
if err != nil {
log.Fatal(err)
}
fmt.Printf("all album : %v\n", albums)
albumNo1, err := application.AlbumService.Get(albums[0].ID)
if err != nil {
log.Fatal(err)
}
fmt.Printf("album number 1 : %v\n", albumNo1)
err = application.AlbumService.Create(&services.Album{
Title: "Mungkin Nanti", Artist: "Peterpan", Price: 70000,
})
if err != nil {
log.Fatal(err)
}
log.Println("Success Create Album!")
albumNo1.Price = 70000
err = application.AlbumService.Update(*albumNo1)
if err != nil {
log.Fatal(err)
}
log.Printf("Success update album %v\n", albumNo1)
err = application.AlbumService.Delete(albumNo1.ID)
if err != nil {
log.Fatal(err)
}
log.Printf("Success delete id: %d!\n", albumNo1.ID)
for _, alb := range albums {
err = application.AlbumService.Delete(alb.ID)
log.Printf("error : %v", err)
}
log.Println("Success delete all data from table album")
}
func openDB(connString string, setLimits bool) (*sql.DB, error) {
var err error
db, err := sql.Open("sqlserver", connString)
if err != nil {
log.Fatal("Error creating connection pool: ", err.Error())
}
if setLimits {
fmt.Println("setting limits")
db.SetMaxIdleConns(5)
db.SetMaxOpenConns(5)
}
ctx := context.Background()
err = db.PingContext(ctx)
if err != nil {
log.Fatal(err.Error())
}
fmt.Printf("Connected!\n")
return db, nil
}
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.
err := godotenv.Load(".env")
if err != nil {
log.Fatalf("Error loading .env file")
}
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.
os.Getenv("<name-configuration>")
In this case we save the URL connection in a database that is stored in the environment so we need to retrieve the configuration data using the command os.Getenv("MSSQL_URL").
Next, we initialize the connection to the database.
db, err := openDB(os.Getenv("MSSQL_URL"), true)
if err != nil {
log.Fatalln(err)
}
So now we also need to make sure this database connection needs to end with close. We can also use defer.
defer func(db *sql.DB) {
err := db.Close()
if err != nil {
log.Fatalln(err)
}
}(db)
In order for our service to access the main program, we need to initialize the service module.
application := app{AlbumService: services.NewMSSQLService(db)}
The rest is calling the service functions that we have defined in the function.
How To Communication Golang with MongoDB
Hot Articles
11 Add Unit Tests Using Mockery
04 Apr 202410 Adding Simple Authentication
03 Mar 2024
Understanding Kubernetes Services
03 Mar 2024Understanding Kubernetes Networking
03 Mar 2024
11 Add Unit Tests Using Mockery
10 Adding Simple Authentication
Understanding Kubernetes Services