Dal

Summary

The Dal (Data Access Layer) is designed to decouple the hard dependency on gorm in v0.12. The advantages of introducing this isolation are:

• Unit Test: Mocking an Interface is easier and more reliable than Patching a Pointer.
• Clean Code: DBS operations are more consistence than using gorm directly.
• Replaceable: It would be easier to replace gorm in the future if needed.

The Dal Interface

type Dal interface {
    AutoMigrate(entity interface{}, clauses ...Clause) error
    Exec(query string, params ...interface{}) error
    RawCursor(query string, params ...interface{}) (*sql.Rows, error)
    Cursor(clauses ...Clause) (*sql.Rows, error)
    Fetch(cursor *sql.Rows, dst interface{}) error
    All(dst interface{}, clauses ...Clause) error
    First(dst interface{}, clauses ...Clause) error
    Count(clauses ...Clause) (int64, error)
    Pluck(column string, dest interface{}, clauses ...Clause) error
    Create(entity interface{}, clauses ...Clause) error
    Update(entity interface{}, clauses ...Clause) error
    CreateOrUpdate(entity interface{}, clauses ...Clause) error
    CreateIfNotExist(entity interface{}, clauses ...Clause) error
    Delete(entity interface{}, clauses ...Clause) error
    AllTables() ([]string, error)
}

How to use

Query

// Get a database cursor
user := &models.User{}
cursor, err := db.Cursor(
  dal.From(user),
  dal.Where("department = ?", "R&D"),
  dal.Orderby("id DESC"),
)
if err != nil {
  return err
}
for cursor.Next() {
  err = dal.Fetch(cursor, user)  // fetch one record at a time
  ...
}

// Get a database cursor by raw sql query
cursor, err := db.Raw("SELECT * FROM users")

// USE WITH CAUTIOUS: loading a big table at once is slow and dangerous
// Load all records from database at once. 
users := make([]models.Users, 0)
err := db.All(&users, dal.Where("department = ?", "R&D"))

// Load a column as Scalar or Slice
var email string
err := db.Pluck("email", &username, dal.Where("id = ?", 1))
var emails []string
err := db.Pluck("email", &emails)

// Execute query
err := db.Exec("UPDATE users SET department = ? WHERE department = ?", "Research & Development", "R&D")

Insert

err := db.Create(&models.User{
  Email: "hello@example.com", // assuming this the Primarykey
  Name: "hello",
  Department: "R&D",
})

Update

err := db.Create(&models.User{
  Email: "hello@example.com", // assuming this the Primarykey
  Name: "hello",
  Department: "R&D",
})

Insert or Update

err := db.CreateOrUpdate(&models.User{
  Email: "hello@example.com",  // assuming this is the Primarykey
  Name: "hello",
  Department: "R&D",
})

Insert if record(by PrimaryKey) didn't exist

err := db.CreateIfNotExist(&models.User{
  Email: "hello@example.com",  // assuming this is the Primarykey
  Name: "hello",
  Department: "R&D",
})

Delete

err := db.CreateIfNotExist(&models.User{
  Email: "hello@example.com",  // assuming this is the Primary key
})

DDL and others

// Returns all table names
allTables, err := db.AllTables()

// Automigrate: create/add missing table/columns
// Note: it won't delete any existing columns, nor does it update the column definition
err := db.AutoMigrate(&models.User{})

How to do Unit Test

First, run the command make mock to generate the Mocking Stubs, the generated source files should appear in mocks folder.

mocks
├── ApiResourceHandler.go
├── AsyncResponseHandler.go
├── BasicRes.go
├── CloseablePluginTask.go
├── ConfigGetter.go
├── Dal.go
├── DataConvertHandler.go
├── ExecContext.go
├── InjectConfigGetter.go
├── InjectLogger.go
├── Iterator.go
├── Logger.go
├── Migratable.go
├── PluginApi.go
├── PluginBlueprintV100.go
├── PluginInit.go
├── PluginMeta.go
├── PluginTask.go
├── RateLimitedApiClient.go
├── SubTaskContext.go
├── SubTaskEntryPoint.go
├── SubTask.go
└── TaskContext.go

With these Mocking stubs, you may start writing your TestCases using the mocks.Dal.

import "github.com/apache/incubator-devlake/mocks"

func TestCreateUser(t *testing.T) {
    mockDal := new(mocks.Dal)
    mockDal.On("Create", mock.Anything, mock.Anything).Return(nil).Once()
    userService := &services.UserService{
        Dal: mockDal,
    }
    userService.Post(map[string]interface{}{
        "email": "helle@example.com",
        "name": "hello",
        "department": "R&D",
    })
    mockDal.AssertExpectations(t)