Go To SQL

Gabe Schmidt Databases, Go, Programming, Tutorial Leave a Comment

In between projects here at Keyhole, I’ve been tasked with applying a relational database access and mapping framework in the Go language.

In this post, I go step by step to create a Postgres relational database, then perform CRUD operations against it in the Go language. You can find the repo here.

I won’t get into the specifics of configuring Go in this blog, but you can check it out yourself here. Additionally, Keyhole’s very own David Pitt wrote an excellent primer on the subject here.

Our Example

My task is to create a new project and connect to a relational database.

First, I will connect via a database driver for Postgres (similar to ADO.Net). Then I will try the same thing with an ORM. Technically, this should be called an SRM (Struct relational mapper) since Go isn’t really object-oriented, but I digress.

I’ll be using Visual Studio Code for this project. I have Go installed and configured locally with my GOPATH set to C:\development\keyhole.

So with that, our first step: install Postgres.

Second, I’ll create a Customer database in Postgres.

  • This is really easy in pgAdmin -> right-click on Databases → Create → Database
  • Give it a name. Click “Save”

Now we have a Customer database. Let’s add a Customer table, which is just as easy.

  • Under the Customer database that was just created, navigate to Schemas –> public –>Tables
  • Right-click on Tables
  • Select Create → Table from the context menu
  • Name the table Customer

Click the Columns tab. We’ll keep it simple; the table will have just two columns: ID and name.

Click “Save.”

Now, let’s try to connect to it from our Go application.

We’ll need a driver. Here’s a list: https://github.com/golang/go/wiki/SQLDrivers. There are three pg drivers on the list. Which do I choose?

  • Hmmm…what’s the difference between pure Go and uses Cgo?
  • According to the docs, Cgo enables the creation of Go packages that call C code.
  • I think I’ll stick with pure Go. This is a new project. Let’s keep it native.

Two left.

  • Shall I choose [*] or [**]?
  • Checking the footnotes: Drivers marked with [*] are both included in and pass the compatibility test suite at https://github.com/bradfitz/go-sql-test.
  • Drivers marked with [**] pass the compatibility test suite but are not currently included in it.
  • Single asterisk it is! It appears to be the most vetted.

Alrighty then. How do I reference this thing in my project? Clicking on the link: https://github.com/lib/pq

So I’ll type that in the Terminal in VS Code

No errors! I guess we’re good.

Let’s start coding!

I’ve created and application folder under C:\development\keyhole\src called go-rdms. In the root of that folder, I’ve created a file named main.go.

The entry point for a Go application is the main function, taking no parameters and returning no value.

My main method simply calls another function, doSqlDriverStuff().

I’ll start by simply making a connection:

func main() {
    doSqlDriverStuff()
}
 
func doSqlDriverStuff() {
    connStr := "user=postgres dbname=Customer password=viXLbzKq3seyOROsmirW sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
}

I’ll run this by typing go run main.go at the command line. This will compile and run the application.

So far, so good. Now let’s seed some data. I’ll check for existing customer and insert 10 if none exist.

Checking for existing customers…

func doCustomersExist(db *sql.DB) bool {
    var customerCount int
    err := db.QueryRow(`select count(*) from "Customer" as customerCount`).Scan(&customerCount)
    if err != nil {
        log.Fatal(err)
    }
    return customerCount > 0
}

Do note that Customer needs to be quoted because pg names are case-sensitive. Otherwise, we get this:

Message:"relation "customer" does not exist"

Now, let’s add a single Customer.

func addCustomer(name string, db *sql.DB) string {
    var customerId string
    id := uuid.New().String()
    err := db.QueryRow(`INSERT INTO "Customer"(id, name)
    VALUES($1,$2) RETURNING id`, id, name).Scan(&customerId)
    if err != nil {
        log.Fatal(err)
    }
    return customerId
}

Here we see the uuid.New(), which generates the unique values for our IDs. This is installed from an external package and added to our imports, just like the SQL driver above was.

The ID generated from uuid.New() could be returned here, but I went with the customerId returned from Postgres here in order to confirm that the record did, in fact, get inserted.

You can add multiple Customers by iterating an array and calling the previous addCustomer function for each.

func addCustomers(db *sql.DB) {
    var customers = [10]string{"James", "John", "Jimmy", "Does Not Start with J", "Hermoine", "Narcissus", "Hank", "Heather", "Bocephus", "Bob"}
    for _, customer := range customers {
        _ = addCustomer(customer, db)
    }
}

This executed successfully and Customers have been inserted into our Customer table in postgres. Next step will be to select each row into a struct.

A struct (short for “structure”) is a collection of data fields with declared data types.

See Also:  Go "On The Fly"

First, I’ll create the struct.

type Customer struct {
    Id   string
    Name string
}

And now a new getCustomers function.

func getCustomers(db *sql.DB) []Customer {
    rows, err := db.Query(`SELECT id, name FROM "Customer"`)
    if err != nil {
        log.Fatal(err)
    }
    var customerArray []Customer
    for rows.Next() {
        var customer Customer
        var id string
        var name string
        err = rows.Scan(&id, &name)
        customer = Customer{Id: id, Name: name}
        customerArray = append(customerArray, customer)
    }
    defer db.Close()
    return customerArray
}


Here, we are retrieving all customers and mapping them to the Customer struct created above. Ideally, we’d allocate the memory for the customerArray based on the row count, but there’s apparently no way to get the row count without iterating first.

For more information, see this link: https://stackoverflow.com/questions/37629357/how-to-get-count-of-sql-rows-without-using-next

The updated doSqlDriverStuff function:

func doSqlDriverStuff() {
    connStr := "user=postgres dbname=Customer password=viXLbzKq3seyOROsmirW sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    if !doCustomersExist(db) {
        addCustomers(db)
    }
    customers := getCustomers(db)
    for _, customer := range customers {
        fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
    }
}

And the output:

Now, how about a single customer?

func getCustomer(id string, db *sql.DB) Customer {
    var customerId string
    var name string
    err := db.QueryRow(`SELECT id, name FROM "Customer" where "id"=$1`, id).Scan(&customerId, &name)
    if err != nil {
        log.Fatal(err)
    }
    return Customer{Id: customerId, Name: name}
}

For demonstrative purposes, I’m going to hard code the ID from one of the customers in the output above. However, in practice, this method would typically be called via other means such as an API.

func doSqlDriverStuff() {
    connStr := "user=postgres dbname=Customer password=viXLbzKq3seyOROsmirW sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    if !doCustomersExist(db) {
        addCustomers(db)
    }
    customer := getCustomer("f71d62ed-58b5-438b-8faa-3764499484fd", db)
    fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
}
 

And our output:

The full main.go file with all CRUD operations for the SQL driver:

package main
 
import (
    "database/sql"
    "fmt"
    "log"
 
    _ "github.com/lib/pq"
 
    "github.com/google/uuid"
)
 
type Customer struct {
    Id   string
    Name string
}
 
func main() {
    doSqlDriverStuff()
}
 
func doSqlDriverStuff() {
    connStr := "user=postgres dbname=Customer password=viXLbzKq3seyOROsmirW sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    if !doCustomersExist(db) {
        addCustomers(db)
    }
    customers := getCustomers(db)
    for _, customer := range customers {
        fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
    }
    customer := getCustomer("f71d62ed-58b5-438b-8faa-3764499484fd", db)
    fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
}
 
func doCustomersExist(db *sql.DB) bool {
    var customerCount int
    err := db.QueryRow(`select count(*) from "Customer" as customerCount`).Scan(&customerCount)
    if err != nil {
        log.Fatal(err)
    }
    return customerCount > 0
}
 
func addCustomer(name string, db *sql.DB) string {
    var customerId string
    id := uuid.New().String()
    err := db.QueryRow(`INSERT INTO "Customer"(id, name)
    VALUES($1,$2) RETURNING id`, id, name).Scan(&customerId)
    if err != nil {
        log.Fatal(err)
    }
    return customerId
}
 
func addCustomers(db *sql.DB) {
    var customers = [10]string{"James", "John", "Jimmy", "Does Not Start with J", "Hermoine", "Narcissus", "Hank", "Heather", "Bocephus", "Bob"}
    for _, customer := range customers {
        _ = addCustomer(customer, db)
    }
}
 
func getCustomers(db *sql.DB) []Customer {
    rows, err := db.Query(`SELECT id, name FROM "Customer"`)
    if err != nil {
        log.Fatal(err)
    }
    var customerArray []Customer
    for rows.Next() {
        var customer Customer
        var id string
        var name string
        err = rows.Scan(&id, &name)
        customer = Customer{Id: id, Name: name}
        customerArray = append(customerArray, customer)
    }
    defer db.Close()
    return customerArray
}
 
func getCustomer(id string, db *sql.DB) Customer {
    var customerId string
    var name string
    err := db.QueryRow(`SELECT id, name FROM "Customer" where "id"=$1`, id).Scan(&customerId, &name)
    if err != nil {
        log.Fatal(err)
    }
    return Customer{Id: customerId, Name: name}
}
 

Next, we’ll see if we can use an ORM to accomplish the same thing

Go-pg seems to be the most popular. Let’s give it a go (no pun intended).

Initially I thought that I was doing Go modules all wrong when I got this error
package github.com/go-pg/pg/v9: cannot find package github.com/go-pg/pg/v9 in any of:

  • c:\go\src\github.com\go-pg\pg\v9 (from $GOROOT)
  • C:\development\keyhole\src\github.com\go-pg\pg\v9 (from $GOPATH)

But if you remove the v9, it’s all good.

PS C:\development\keyhole\src\go-rdms> go get github.com/go-pg/pg
PS C:\development\keyhole\src\go-rdms>

I created a getCustomersOrm function.

func getCustomersOrm() []Customer {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "Customer",
        Password: "viXLbzKq3seyOROsmirW",
    })
 
    var customers []Customer
    err := db.Model(&customers).Select()
    if err != nil {
        log.Fatal(err)
    }
    return customers
}

Then updated my main function to call it.

func main() {
    customers := getCustomersOrm()
    for _, customer := range customers {
        fmt.Println(customer)
    }
}

But then I got this error: 2019/10/14 14:26:45 ERROR #42P01 relation "customer" does not exist.

I tried changing the case and plurality of about every object and variable related to this functionality to no avail…then I came across this.

Okay, I’ll just alias the table name.

type Customer struct {
    tableName struct{} `pg:"Customer,alias:customer"`
    Id        string
    Name      string
}

No luck there either.

2019/10/14 14:30:50 ERROR #42P01 relation "customer" does not exist

Convention seems to be lower-case table names. So what if I model this based on the straight-outta-the-box example https://github.com/go-pg/pg.

The setup:

func main() {
    doOrmStuff()
}
 
func doOrmStuff() {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "Customer",
        Password: "viXLbzKq3seyOROsmirW",
    })
    createSchema(db)
}

Modifying the createSchema function from the example:

func createSchema(db *pg.DB) error {
    for _, model := range []interface{}{(*Customer)(nil)} {
        err := db.CreateTable(model, &orm.CreateTableOptions{})
        if err != nil {
            return err
        }
    }
    return nil
}

The ORM created a customers (plural) table in Postgres.

Okay, that’s something. But running the same code again produces an error:

77:"relation "customers" already exists"

Makes sense. Definitely want to do some defensive coding around that in a real-world situation, but it doesn’t hinder us moving forward with this exercise. I’m going to ignore for now.

See Also:  [Video] DevOps Orchestration: Kubernetes, OpenShift & Cloud Foundry

Let’s seed some customers. I’ll take the same approach here as I did with the SQL driver:

Check for existing:

func doCustomersExist(db *pg.DB) bool {
    var customers []Customer
    err := db.Model(&customers).Select()
    if err != nil {
        log.Fatal(err)
    }
    return len(customers) > 0
}

I created an addCustomer function to insert a single customer and return an ID:

func addCustomer(name string, db *pg.DB) string {
    id := uuid.New().String()
    customer := &Customer{
        Id:   id,
        Name: name,
    }
    err := db.Insert(customer)
    if err != nil {
        log.Fatal(err)
    }
    return customer.Id
}

Then I create an addCustomers function to insert multiple customers (which looks eerily similar to the SQL driver function 😊)

func addCustomers(db *pg.DB) {
    var customers = [10]string{"James", "John", "Jimmy", "Does Not Start with J", "Hermoine", "Narcissus", "Hank", "Heather", "Bocephus", "Bob"}
    for _, customer := range customers {
        _ = addCustomer(customer, db)
    }
}

And now we’ll update the doOrmStuff function and run it.

func doOrmStuff() {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "Customer",
        Password: "viXLbzKq3seyOROsmirW",
    })
    createSchema(db)
    if !doCustomersExist(db) {
        addCustomers(db)
    }
}

Selecting all rows from the customers table, we see that we were successful.

Okay, great. Now let’s retrieve that data in our code.

First, getCustomers.

func getCustomers(db *pg.DB) []Customer {
    var customers []Customer
    err := db.Model(&customers).Select()
    if err != nil {
        log.Fatal(err)
    }
    return customers
}

And we’ll update goOrmStuff and run it.

func doOrmStuff() {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "Customer",
        Password: "viXLbzKq3seyOROsmirW",
    })
    createSchema(db)
    if !doCustomersExist(db) {
        addCustomers(db)
    }
    customers := getCustomers(db)
    for _, customer := range customers {
        fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
    }
}

Let’s run it.

Beautiful!

You may have noticed the getCustomers function looks exactly the same as doCustomersExist sans the return statement. Time for a quick refactor.

func doCustomersExist(db *pg.DB) bool {
    customers := getCustomers(db)
    return len(customers) > 0
}

And re-running the code yields the same results:

Now for a single customer:

func getCustomer(id string, db *pg.DB) Customer {
    customer := &Customer{Id: id}
    err := db.Select(customer)
    if err != nil {
        log.Fatal(err)
    }
    return *customer
}
func doOrmStuff() {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "Customer",
        Password: "viXLbzKq3seyOROsmirW",
    })
    createSchema(db)
    if !doCustomersExist(db) {
        addCustomers(db)
    }
    customer := getCustomer("1bbe4c12-b25a-4142-a4c5-3695e4905786", db)
    fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
}

And there you have it!

The full main.go file with all CRUD operations for the ORM.

package main
 
import (
    "fmt"
    "log"
 
    "github.com/go-pg/pg"
    "github.com/go-pg/pg/orm"
    "github.com/google/uuid"
)
 
type Customer struct {
    Id   string
    Name string
}
 
func main() {
    doOrmStuff()
}
 
func doOrmStuff() {
    db := pg.Connect(&pg.Options{
        User:     "postgres",
        Database: "Customer",
        Password: "viXLbzKq3seyOROsmirW",
    })
    createSchema(db)
    if !doCustomersExist(db) {
        addCustomers(db)
    }
    customers := getCustomers(db)
    for _, customer := range customers {
        fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
    }
    customer := getCustomer("1bbe4c12-b25a-4142-a4c5-3695e4905786", db)
    fmt.Printf("id=%s name=%s\n", customer.Id, customer.Name)
}
 
func createSchema(db *pg.DB) error {
    for _, model := range []interface{}{(*Customer)(nil)} {
        err := db.CreateTable(model, &orm.CreateTableOptions{})
        if err != nil {
            return err
        }
    }
    return nil
}
 
func doCustomersExist(db *pg.DB) bool {
    customers := getCustomers(db)
    return len(customers) > 0
}
 
func addCustomer(name string, db *pg.DB) string {
    id := uuid.New().String()
    customer := &Customer{
        Id:   id,
        Name: name,
    }
    err := db.Insert(customer)
    if err != nil {
        log.Fatal(err)
    }
    return customer.Id
}
 
func addCustomers(db *pg.DB) {
    var customers = [10]string{"James", "John", "Jimmy", "Does Not Start with J", "Hermoine", "Narcissus", "Hank", "Heather", "Bocephus", "Bob"}
    for _, customer := range customers {
        _ = addCustomer(customer, db)
    }
}
 
func getCustomers(db *pg.DB) []Customer {
    var customers []Customer
    err := db.Model(&customers).Select()
    if err != nil {
        log.Fatal(err)
    }
    return customers
}
 
func getCustomer(id string, db *pg.DB) Customer {
    customer := &Customer{Id: id}
    err := db.Select(customer)
    if err != nil {
        log.Fatal(err)
    }
    return *customer
}
 

Wrap Up

So we’ve done quite a bit here and learned a few things along the way, including:

  • We created a Go project.
  • We created a PostgreSQL database and a Customer table.
  • We seeded some data via the SQL provider.
  • We queried some data via the SQL provider.
  • We inserted some data via an ORM.
  • We queried said data via the ORM.

That covers the basics. It bothers me that I wasn’t able to query data from the original Customer table via the ORM. This seems like a common use case that should be easy to accomplish with an ORM. Feel free to leave a comment if you have any ideas.

While the examples above connect to a Postgres database, I encourage you to try connecting to a different relational database, such as Microsoft SQL Server, MySQL, Oracle, or Aurora. Or, if you’re feeling super adventurous, you could even try DynamoDb! (see https://medium.com/@spiritualcoder/step-by-step-guide-to-use-dynamodb-with-golang-cd374f159a64) Or even expand upon what’s been done here. You could include a delete. Create some relationships (Customer->Order) and map it to a nested Go Structure. Create an API in Go to manage it all.

You get the idea. Check out the repo here. Happy coding!

Further Reading

https://golang.org/
https://keyholesoftware.com/2019/09/26/go-on-the-fly/
https://www.enterprisedb.com/downloads/postgresql
https://github.com/golang/go/wiki/SQLDrivers
https://golang.org/cmd/cgo/
https://github.com/bradfitz/go-sql-test
https://github.com/lib/pq
https://www.golangprograms.com/go-language/struct.html
https://stackoverflow.com/questions/37629357/how-to-get-count-of-sql-rows-without-using-next

Additional Resources:

https://flaviocopes.com/golang-sql-database/#select-multiple-rows
https://mindbowser.com/golang-go-database-sql/
https://golang.org/pkg/database/sql/

What Do You Think?