In this article I would like to write about how can the model layer interact with the database. In this case I will use a PostgreSQL database.

There are a couple things we need to do to connect to a database in our application.

Driver

The first thing we will need is a sort of a driver. The driver contains the logic for how to actually communicate with that back-end SQL database. Once we have the driver loaded, then we are going to call the open() function from the SQL package and that is what actually going to open up the DB object that represents the database in our Go application. Once we got it ready, we are able to start sending commands to the SQL database and get results back.

In code it looks like this

package main

import _ "github.com/lib/pq" /* SQL Driver */
import "database/sql"

func main() {
    /* Logic */
    db, err := sql.Open("postgres", {connection string})
}

Notice that the import statement contains an underscore. It is because we actually import this package for it's side effects. The side effect for a database driver are to register the driver and all of its internal logic with the SQL package.
Once we have that we can go ahead and import the database/sql package.

Then in the main function we will do some initial logic and then we will establish the DB object with the code. The postgres word is determined by the driver. In this case we are loading the PQ driver. Then the second argument in the function is a connection string that gets sent to the database. This is where we can say which database we want to connect, any credentials that we need to pass are going to be passed here as well. If the command is successful then the first returned object is going to be a pointer to a DB object, which is going to be what we are going to interact with your database through, if anything goes wrong you will have that error object and you can manage that error.

func connectToDatabase() *sql.DB {
    db,err := sql.Open("postgres", "postgres://lss:lss@localhost/lss?sslmode=disable")

    if err != nil {
        log.Fatalln(fmt.Errorf("Unable to connect to DB %v,err))
    }

    model.SetDatabase(db)
    return db
}

After a succesful modify in the DB you should close your DB connection with deferring.

defer db.Close()

the SetDabase() method should be handled in the model layer

package model

import "database/sql"

var db *sql.DB

func SetDatabase(database *sql.DB){
    db = database
}

Now that we have a connection to our database established it is time to query that database and send some information. There are a couple of methods on the DB object those are avilable to help us do that

Querying

• Query() - general select statement off to your database and it is designed to pull one or more rows back
• QueryRow() - especially only one row, this is optimised to get one record back
• QueryContext()
• QueryRowContext()

QueryContext() and QueryRowContext() are similiar for the two above them however they take in optional context (set up cancel functions ang register a timeout) with the QueryContext() and QueryRowContext() you can actually pass that request context into the query and so if something cancels the request then you can inform the query that it should stop execution and free up those resources from the database.

Now the return types can be two. The Query() and the QueryContext() method return an object of type Rows

type Rows struct {}

and that Row object actually has quite a few methods associated with it.

func (*Rows) Close () error
func (*Rows) ColumnTypes() ([]*ColumnType, error)
func (*Rows) Columns() ([]string, error)
func (*Rows) Err() error
func (*Rows) Next() bool
func (*Rows) NextResultSet() bool
func (*Rows) Scan (dest ...interface{}) error

• Close() method is important for closing the query once we are done working with it
• ColumnTypes() gives us information about all the columns on the data that is returned in that row set
• Columns() returns column names
• Error() is going to be populated if something went wrong with the query
• Next() is allowing us to step throught the results of the query one record at a time and then
• NextResultSet() - if your query contains multiple results sets we have the NextResultSet() method which is going to allow you to move on to the next result set in your query Notice that Next... functions return boolean. It is important because if the method returns false then you know that was the last record and it is time to exit out of the loop.
• Scan() method is used to pull data from the current row into variables in our Go application

QueryContext() and QueryRowContext() methods returns also a Row object

type Row struct {}

and this object has one and only one method on it and that is a scan method

func (*Row) Scan (dest ...interface{}) error

So now we know how to query our database there is still a little bit of functionality that we need to know about in order to really understand how to work with a SQL database. For example we know how to create records, how to update and how to delete records. Well in Go there are really two methods that you need to know for all those operations.

Exec() and ExecContext()

Now that Exec() method is going to take that command that you want to send, so if you want to send an insert command or an update command or a delete command you can send that with the exec method. The ExecContext() is exactly the same but it, just like the QueryContext() method that we talked about takes a request context and so the statement is going to be aware if that request needs to be canceled so it can free up the database resources. Now when these statements run they return what is called a result object and that result object contains a little bit of information about how the query went. For example if you are inserting a record you are going to get the ID of the record that was created and if you sent a statement that is going to affect multiple records you will also get information about how many records were affected by that statement.

Lets make an example where we could update the value in the database that signals when somebody had logged in for the last time

t := time.Now()
    _, err := db.Exec(`
        UPDATE public.user
        SET last_login = $1
        WHERE id = $2`, t, userID)
    if err!= nil {
        log.Printf("Failed to update login time %v",err)
    }

So this way all CRUD operates are possible to do in Go, using the Exec method.

Additional activities

• Ping()/PingContext() - to see if the database is available to do work
• Prepare()/PrepareContext() - create prepared statements that can be used by your Go application when it is going to be calling the same statement multiple times it -> it allows the database to calculate an execution plan one time and reuse that execution plan for multiple queries. So if you are going to be inserting a lot of records, you can use these methods to make those queries run a little bit more efficiently.
• Transactions: Begin() and BeginTx() methods -> these will return a transaction object which contains a lot of the same functions that w ehave on the DB object but they are isolated to the scope of the transaction. You can use a rollback method on that transaction object in case the entire transaction needs to be canceled.

Later in an article I am planning to show all these features in a demo project. Keep up reading the blog 😁