Design a Database Wrapper Interface.

Part 2 of Make Go Interfaces work for you

https://unsplash.com/photos/jIBMSMs4_kA

In my opinion, two common traits of a 10x developer is having a good understanding of their favorite language and knowing when/how abstractions can be leveraged to save time.

Less is more; it’s about 10x output/impact and not 10x code.

In this post, I will design a database wrapper that can support all of the functionality expected from the CRUD application I want to build.

*CRUD — Create, Read, Update, Delete

Please note I do not plan on having a method that lists all the records within a database/table.

A database wrapper “provides an object-oriented interface … and hides the details of how to access the data maintained.” With Go, I can pass a database wrapper around my application as a struct or interface, anything is possible.

• With a struct, I’m limited to one database implementation at a time within the code base; unless I choose to write functions that support working with different struct types; or devise a sophisticate scheme that involves updating small sections of the code base to switch between database providers.
• With an interface, I can use it as a function parameter and thus reduce the code I need to update or maintain if the API server needs to change the database it connects to. For tests, I can also pass an implementation of the interface mocking an actual database.

I’ll start the implementation with an interface.

A Prototype

The ideal interface for the web server in mind should have a method to:

• Create a database record.
• Read a database record.
• Update a database record.
• Delete a database record.

Listing 1

type DatabaseWrapper interface {
 Create(any) error
 Read(int, any) error
 Update(int, any) error
 Delete(int) error
}

Listing 1 depicts an interface that has all the functionality required for this project. Here is brief summary about each method:

• Create(any) error— The role of this method is to add a new record to the database and return an error if the operation was not successful.
• Update(int, any) error — The role of this method is to update the record with the specified ID; this method should return an error if there was an issue while updating the record.
• Delete(int) error— The role of this method is to remove the specified record from the database.
• Read(int, any) error— The role of this method is to find a record with the specified ID and store it to the value pointed to by the method’s second argument; this approach enables the interface method to support returning various struct types and reduces the need to perform type-assertions after each invocation of the read method. The Go standard library also follows this pattern with it’s Unmarshal function. Since the first implementation will be working with “in-memory” data, I’ll define a utility function that will help me move data from one variable to the pointer of another.
• Listing 2:

package main

import "reflect"

// applyDataToPointer is inspired by the
// std library's JSON Unmarshal function,
// I cracked and had to find out how they
// did it for the Unmarshal function.
// This is the result.
func applyDataToPointer(src, dst any) {

 rv := reflect.ValueOf(src)
 ri := reflect.ValueOf(dst).Elem()
 ri.Set(rv)
}

• In listing 2, I’m defining a function that will populate the dst parameter with the value of parameter src.

Writing a function with the interface

A neat thing about Go is that I can begin writing functions that depend on this interface without an actual implementation; I usually take this route if I want to postpone writing any database code.

Listing 3

package main

import "time"

type memRecord struct {
 Text      string
 CreatedAt time.Time
}

func initDB(db DatabaseWrapper) error {
  return nil
}

Listing 3 depicts a function that accepts a parameter named db of type DatabaseWrapper; the magical thing about Go is that there’s no actual implementation of this interface and this code will still compile.

I’m also defining a struct type named memRecord that will act as a database record. The struct has a field (named CreatedAt) that’s there to test modifying a record prior to inserting it.

Listing 4

type memRecord struct {
 Text      string
 CreatedAt time.Time
}


func initDB(db DatabaseWrapper) error {
 
 // construct a memRecord instance
 record := memRecord{Text: "Hello World"}
 
 // construct a second instance
 // to add to the database.
 record2 := memRecord{Text: "Record 2"}

 // Add the first record
 if err := db.Create(record); err != nil {
  return err
 }
 
 // Add the second record
 if err := db.Create(record2); err != nil {
  return err
 }

 // Update a field within the second
 // record.
 record2.Text = "Hello World 2"
 
 // Push the updated record to the database.
 // I'm guessing the id of the second record is 1 because
 // I plan on having the first database id be 0.
 if err := db.Update(1, record2); err != nil {
  return err
 }
 
 // I'm declaring a variable that I will
 // store data to via the interface's Read
 // method.
 var queryResult memRecord
  
 // Load the data onto the variable queryResult
 // by passing it as a pointer.
 if err := db.Read(1, &queryResult); err != nil {
  return err
 }
 
 // Display the data fetched and display it to see
 // if the struct was correctly populated.
 fmt.Println("\n\nQuery Result\n\n", queryResult)
 
 // Remove the second record from the database.
 err := db.Delete(1)

 return err
}

The code in listing 4 is an updated version of function initDB; the code is calling the methods of the DatabaseWrapper interface to achieve the desired functionality.

First Implementation

As for my first implementation, I’ll define a struct that stores all database records in a map and prints a copy of the database after each change; this is to see the database operations performed by function initDB.

Listing 5

package main

type memRecord struct {
 Text      string
 CreatedAt time.Time
}

type InMemoryDb struct {
 // The int ID to make the simulation
 // of a PostGRE database feel more
 // accurate.
 storage map[int]memRecord
}

// NewInMemory is a test database
// that will log the updated database
// each time an operation is called.
func NewInMemoryDb() *InMemoryDb {
 return &InMemoryDb{
  map[int]memRecord{},
 }
}

func logDB(db InMemoryDb) {
 fmt.Println("\n\nCurrent database:")
 for key, data := range db.storage {
  fmt.Println("Row", key, "Data:", data)
 }
}

In listing 5, I’m defining:

• The struct type representing the records to be stored in the database.
• The struct type of an in-memory database — this may seem confusing because the wrapper is updating data within itself. I’m also defining a factory function that returns a pointer to an instance InMemoryDb; I’m doing this because all the methods have a pointer receiver to ensure any updates made, to the map, remain persistent.
• A function named logDB that will be called each time a database change has occurred; this is how I’ll observe the current state of the database.

Listing 6

func (db *InMemoryDb) Create(r any) error {
 
 nextId := len(db.storage)
 entry, ok := r.(memRecord)
 if !ok {
  return errors.New("Wrong concrete type passed")
 }
 entry.CreatedAt = time.Now()
 db.storage[nextId] = entry
 // Log contents of database
 logDB(*db)
 return nil
}

Listing 6 represents an implementation of the DatabaseWrapper create method. I’m determining the ID of the new record by counting the keys within the storage map and then setting it as the value of variable nextId. Once the ID is determined, I’ll declare a variable named entry and set it’s value to the underlying concrete type of function parameter r ; an error is returned if another type, besides memRecord, is passed. I’ll then set the CreatedAt field of variable entry’s time to now. As for adding the record to the database, I’ll set a value for key nextId; this value is variable entry.

Listing 7

func (db *InMemoryDb) Update(q int, r any) error {
 data, ok := db.storage[q]

 // return early if item not present.
 if !ok {
  return errors.New("Record not found")
 }

 data.Text = r.(memRecord).Text
 db.storage[q] = data
 logDB(*db)
 return nil
}

Listing 7 is an implementation of the interface’s update method. The function will attempt to read the record from the map and return an error if the map value of the id/key does not exist, this id/key is represented as function parameter q. If the record exists, the database will update the text field with the one passed with function parameter r and preserve the value for the CreatedAt field. The updated record is then stored on the map again.

Listing 8

func (db *InMemoryDb) Delete(q int) error {
 // This delete function will only work
 // if you're removing the last element.
 // This is due to the fact that I'm guessing
 // the next ID with this statement:
 // `nextId := len(db.storage)`
 delete(db.storage, q)
 logDB(*db)
 return nil
}

Listing 8 is an implementation of the interface’s delete method; it removes the record with the specified key by calling Go’s built-in delete function.

Please note that this code is not reliable due to the way this implementation determines the ID of a new element.

Listing 9

func (db *InMemoryDb) Read(q int, r any) error {

 data, ok := db.storage[q]

 // return early if item not present.
 if !ok {
  return errors.New("Record not found")
 }

 applyDataToPointer(data, r)
 return nil
}

Listing 9 is an implementation of the interface’s read function. The method will read the map value stored in the key passed as a method parameter q ; an error is returned if the key passed is not present within the map. I’ll call the applyDataToPointer function I defined in listing 2 to store variables data to the underlying concrete type of method parameter r.

With all the interface methods implemented, I can call the function initDB and pass a variable of type InMemoryDb .

Listing 10

func main() {

 db := NewInMemoryDb()

 if err := initDB(db); err != nil {
  panic(err)
 }

 // Database setup complete
}

Listing 10 depicts a program that constructs the InMemoryDb and passes it as a parameter of type DatabaseWrapper without causing any new compiler-related errors; this is a result of the struct type having all of the methods expected by the interface.

Listing 11

$ go run ./

Current database:
Row 0 Data: {Hello World 2023-08-07 17:59:38.515067526 +0000 GMT m=+0.000068293}


Current database:
Row 0 Data: {Hello World 2023-08-07 17:59:38.515067526 +0000 GMT m=+0.000068293}
Row 1 Data: {Record 2 2023-08-07 17:59:38.515323853 +0000 GMT m=+0.000324623}


Current database:
Row 0 Data: {Hello World 2023-08-07 17:59:38.515067526 +0000 GMT m=+0.000068293}
Row 1 Data: {Hello World 2 2023-08-07 17:59:38.515323853 +0000 GMT m=+0.000324623}


Query Result

{Hello World 2 2023-08-07 17:59:38.515323853 +0000 GMT m=+0.000324623}


Current database:
Row 0 Data: {Hello World 2023-08-07 17:59:38.515067526 +0000 GMT m=+0.000068293}

Listing 11 depicts the output of the code in Listing 4 and Listing 10; the output is verbose because each time a database change occurs, the content of the database is written to the terminal.

Second Implementation

The next implementation will interface with SQLite; I’m doing this to showcase how I won’t need to update the initDB function’s code to support a different database provider.

Listing 12

package main

import (
 "database/sql"
)

type SQLite struct {
 db *sql.DB
}

func NewSQLite(db *sql.DB) *SQLite {
 return &SQLite{
  db,
 }
}

In listing 12, I’m defining a struct type named SQLite with a field named db; this field is a pointer to a value with type sql.DB and will be used to interface (execute queries) against the SQLite database.

The factory function NewSQLite requires a pre-initialized database connection. I chose this option because it enables the caller to pick how they want to initialize and manage a connection to the database.

Listing 13

func (db *SQLite) Create(r any) error {

 now := time.Now()
 entry, ok := r.(memRecord)

 if !ok {
  return errors.New("wrong concrete type passed")
 }

 _, err := db.db.Exec(
  fmt.Sprintf(
   `INSERT INTO entries(text, created_at) VALUES('%s',%d);`,
   entry.Text,
   now.Unix(),
  ),
 )

 return err
}

Listing 13 is an implementation of the DatabaseWrapper interface’s create method. I’m leveraging the fmt package as a makeshift query builder since I do not fully understand the database driver I’m using. I’m also performing a type assertion to retrieve the value passed with the method parameter r and storing it in the database.

Listing 14

func (db *SQLite) Update(q int, r any) error {
 record := r.(memRecord)

 _, err := db.db.Exec(
  "UPDATE entries SET text = '?' WHERE id = ?;",
  record.Text,
  q,
 )

 return err
}

Listing 14 is an implementation of the DatabaseWrapper interface’s update method. I’m performing a type assertion to retrieve the new value and update the record with said new value.

Listing 15

func (db *SQLite) Delete(q int) error {

 _, err := db.db.Exec("DELETE FROM entries WHERE id = ?;", q)

 return err
}

Listing 15 is an implementation of the interface’s delete method. To remove a record, I’m running a query with the DELETE statement and passing the ID of the record.

Listing 16

func (db *SQLite) Read(q int, r any) error {

 var text string
 var timestamp int

 row := db.db.QueryRow("SELECT text,created_at FROM entries WHERE id = ?", q)
 err := row.Scan(&text, &timestamp)

 if err != nil {
  return err
 }
 result := memRecord{
  CreatedAt: time.Unix(
   int64(timestamp),
   0,
  ),
  Text: text,
 }

 applyDataToPointer(result, r)

 return nil
}

Listing 16 is an implementation of the interface’s read method. To read a record, I run a SQL query and store the row’s column values in separate variables; I then use these variables to construct an instance of the memRecord struct. Once constructed, I call the applyDataToPointer utility function to store the struct instance to the underlying concrete value of the pointer passed as a parameter r.

Listing 17

package main

import (
 "database/sql"
 "fmt"

 _ "github.com/mattn/go-sqlite3"
)


func main() {

 // Database setup complete
 // sqlite test. init database in memory
 sqldb, err := sql.Open("sqlite3", "./test.db")

 if err != nil {
   panic(err)
 }

 // close connection
 // after function returns.
 defer sqldb.Close()

 if _, err := sqldb.Exec(`
 DROP TABLE IF EXISTS entries;
 CREATE TABLE entries(id INTEGER PRIMARY KEY, text TEXT, created_at INT);`); err != nil {
  panic(err)
 }

 sqlWrapper := NewSQLite(sqldb)

 if err := initDB(sqlWrapper); err != nil {
  panic(err)
 }

}

Listing 17 represents a program that :

• Opens a local SQLite database file.
• Clear data from the previous test and add a new table with columns named: id, text, and created_at.
• Construct the SQLite database wrapper and pass it as a parameter while calling the function initDB.

Notice the import path with the blank identifier (_) import; this is to ensure the package’s init function executes to register the database driver.

Listing 18

$ go run ./

Query Result

{Hello World 2023-08-07 21:29:51 +0000 GMT}

Listing 18 is the output of the code shown in Listing 17. Only one output message is shown because this implementation of the DatabaseWrapper the interface does not display the state of the database after each change.

If you noticed, the behavior of the function initDB has changed although no changes were made to it; the change of behavior can be attributed to the different interface implementations passed while calling the function.

Conclusion

As seen in this post, database wrapper interfaces give you the ability to quickly switch your database provider without updating the code reliant on it. Although a tedious first step, it’ll make maintaining your code in the future easier and sane.

A critique of the solution outlined in this post is that the functions need to perform type assertions to update and create a record. I need to look at the performance impact this pattern has and what are the potential security risks it entails. Usually, the Go database libraries I use ask for an interface value to store and provide an option to specify the ID of a document. This pattern can be observed within the following package:

couchdb

Another way to update the fields of an interface’s underlying concrete type is with the reflect package; again, I have yet to understand the impact this pattern will have on a program’s performance.

I plan on writing the web server handlers in the next post; these handlers will leverage the DatabaseWrapper interface to access data stored on a SQLite file.

You can find the source code used in this post here, with both implementations:

Thank you for reading until the end. Please consider following the writer and this publication. Visit Stackademic to find out more about how we are democratizing free programming education around the world.

miwfy/p1 at main · cheikh2shift/miwfy

Sources:

Figure 12 — uploaded by Joseph O Dada https://www.researchgate.net/figure/Example-of-database-wrapper-class-right-design-model-that-provides-interface-for_fig12_272486452

json