While the application code can certainly interact with the database using SQL statements, you needs to be careful to ensure that the DB interactions are not strewn across the application layers. The Data Access Layer (DAL) is a layer that should be responsible for handling entities and their interactions with the database. The rest of the application is abstracted from the DB interaction details.

Objects Relational Mappers (ORMs) are a special form of DAL, which translate DB entities into objects. Generally this is done seamlessly behind the scenes via generic glue code.

GORM is the most popular Go ORM out there. To install it, we run the following command:

go get "github.com/jinzhu/gorm"

To use GORM, you must also specify the database to be used along with the drivers and dialect. Thus, the GORM import inside your application would be as follows:

import (
     _ "github.com/go-sql-driver/mysql" 
    "github.com/jinzhu/gorm" 
     _ "github.com/jinzhu/gorm/dialects/mysql"  
 ) 

To connect to the database, the code for mysql would be something similar to the following:

db, err:= gorm.Open("mysql", "root:@tcp(127.0.0.1:3306)/users?charset=utf8&parseTime=True") 
if err != nil { 
     panic("failed to connect database") 
} 

Before looking at the operations, let's see what an entity that is managed by GORM looks like. It's a normal Go struct, which embedded the GORM gorm.Model struct. This base struct provides some standard fields, such as timestamps, as shown in the following example. Let's define a User entity/model:

type User struct { 
    gorm.Model 
    Name string 
    Age  uint 
} 

GORM has a migration feature that allows the DB structure to be in sync with the entity definition. While this is cool in dev, generally it is not advisable to use this in production for fear of unchecked/unwanted DB changes, which can cause data loss. But since this is a dev example, we can initiate schema migration via the following code:

// Migrate the schema 
db.AutoMigrate(&User{}) 

This will create a table called users, as follows:

+------------+------------------+------+-----+---------+----------------+ 
| Field      | Type             | NULL | Key | Default | Extra          | 
+------------+------------------+------+-----+---------+----------------+ 
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| created_at | timestamp        | YES  |     | NULL    |                | 
| updated_at | timestamp        | YES  |     | NULL    |                | 
| deleted_at | timestamp        | YES  | MUL | NULL    |                | 
| name       | varchar(255)     | YES  |     | NULL    |                | 
| age        | int(10) unsigned | YES  |     | NULL    |                | 
+------------+------------------+------+-----+---------+----------------+ 

Now that we have our database, we can create the following user:

   // Create 
   db.Create(&User{Name: "James Bond", Age: 40}) 

This will insert a record in the database through a SQL query:

INSERT INTO users (name,age) VALUES ('James Bond',40); 

We can query the database using various fields:

   // Read 
   var user User 
   db.First(&user, 1) // find user with id 1 
   fmt.Println(user) 
 
   db.First(&user, "Name = ?", "James Bond") // find James Bond 
   fmt.Println(user) 

This will translate to SQL queries:

SELECT * FROM users WHERE name='James Bond'  limit 1; 

Entities can be updated as follows:

   // Update - update Bond's age 
   db.Model(&user).Update("Age", 41) 

This will update the entity as well as the database.

Deletion is somewhat quirky in GORM. The main API is straightforward:

   // Delete - delete user 
   db.Delete(&user) 

However, if the entity has the deleted_at field, then rather than deleting the entry, GORM will just set the deleted_at value to the current time. These records will be skipped for reads done via GORM. So, the preceding select query is really as follows:

SELECT * FROM users WHERE name='James Bond'  AND deleted_at IS NULL limit 1; 

To actually delete from the database, you can use the Unscoped API:

db.Unscoped().Delete(&user) 

The whole hello world program that writes, reads, and deletes is described as follows:

package main 
import (
   "fmt" 
   _ "github.com/go-sql-driver/mysql" 
   "github.com/jinzhu/gorm" 
   _ "github.com/jinzhu/gorm/dialects/mysql" 
) 
type User struct { 
   gorm.Model 
   Name string 
   Age  uint 
} 
func main() { 
   db, err:= gorm.Open("mysql", "root:@tcp(127.0.0.1:3306)/users?charset=utf8&parseTime=True") 
   if err != nil { 
         panic("failed to connect database") 
   } 
   defer db.Close() 
   // Migrate the schema 
   db.AutoMigrate(&User{}) 
   // Create 
   db.Create(&User{Name: "James Bond", Age: 40}) 
   // Read 
   var user User 
   db.First(&user, 1) // find user with id 1 
   fmt.Println(user) 
   db.First(&user, "Name = ?", "James Bond") // find James Bond 
   fmt.Println(user) 
   // Update - update Bond's age 
   db.Model(&user).Update("Age", 41) 
   fmt.Println(user) 
   // Delete - delete user 
   db.Delete(&user) 
} 

This program will not delete the James Bond entry, since Delete() is just a soft delete. After the program is run, the DB will have the following entry:

mysql>  SELECT * FROM users WHERE name='James Bond'   ; 
+----+---------------------+---------------------+---------------------+------------+------+ 
| id | created_at          | updated_at          | deleted_at          | name       | age  | 
+----+---------------------+---------------------+---------------------+------------+------+ 
|  5 | 2018-05-06 08:44:22 | 2018-05-06 08:44:22 | 2018-05-06 08:44:22 | James Bond |   41 | 
+----+---------------------+---------------------+---------------------+------------+------+ 
1 row in set (0.01 sec) 

GORM has support for transactions. For example, the following code will either create both userA and userB, or will not create either user:

func createTwoUsers(db *gorm.DB) { 
   userA:= User{Name: "UserA", Age: 20} 
   userB:= User{Name: "UserB", Age: 20} 
 
   tx:= db.Begin() 
   if err:= tx.Create(&userA).Error; err != nil { 
         tx.Rollback() 
   } 
   if err:= tx.Create(&userB).Error; err != nil { 
         tx.Rollback() 
   } 
 
   //commit! 
   tx.Commit() 
} 

GORM also has support for relationships, which translates object relationships onto the DB structure. Relationships can be belongs-to, one-to-one, one-to-many, and many-to-many. For example, the following program defines a belongs-to relationship between a hotel and a hotel chain:

package main 
 
import (
   _ "fmt" 
 _ "github.com/go-sql-driver/mysql" 
   "github.com/jinzhu/gorm" 
   _ "github.com/jinzhu/gorm/dialects/mysql" 
) 
 
type HotelChain struct { 
   gorm.Model 
   Name string 
} 
 
type Hotel struct { 
   gorm.Model 
   Name    string 
   NoRooms uint 
   Chain   HotelChain `gorm:"foreignkey:ChainId"` // use ChainId as foreign key 
   ChainId uint 
} 
 
func main() { 
   db, err:= gorm.Open("mysql", "root:@tcp(127.0.0.1:3306)/users?charset=utf8&parseTime=True") 
   if err != nil { 
         panic("failed to connect database") 
   } 
   defer db.Close() 
 
   // Migrate the schema 
   db.AutoMigrate(&HotelChain{}) 
   db.AutoMigrate(&Hotel{}) 
   db.Model(&Hotel{}).AddForeignKey("chain_id", "hotel_chains(id)", "CASCADE", "CASCADE") 
   // Create some entities and save 
   taj:= HotelChain{Name: "Taj"} 
   db.Save(&taj) 
   vivanta:= Hotel{Name: "Vivanta by the sea", NoRooms: 400, Chain: taj} 
   db.Save(&vivanta) 
}