Writing a Data Mapper in Go without an ORM
Working with a database in go is quite simple thanks to the database/sql package, but after writing a couple of queries duplication can really start to build up. The Data Mapper Pattern presented by Martin Fowler in Patterns of Enterprise Application Architecture is the perfect first abstraction layer between the database an domain objects. In this post, we’ll look at how to implement a Data Mapper in Go.
Our Domain: Users
First, let’s start by defining our domain and the functionality we’re going to build. Since the domain isn’t the focus of this post, I will use the most common database table found in web applications: the User. Here’s the SQL to define the users
table we’ll be working with today:
nick=# CREATE DATABASE data_mapper_post;
CREATE DATABASE
nick=# \c data_mapper_post;
You are now connected to database "data_mapper_post" as user "nick".
nick=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
nick=# CREATE TABLE users (
id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
password text NOT NULL,
email text NOT NULL,
name text NOT NULL,
created_at timestamp without time zone
NOT NULL DEFAULT (now() at time zone 'utc')
);
Our users
table has an id primary key that is a uuid automatically generated from pgcrypto. If you’d like to read more about pgcrypto, check out Hashed Passwords with PostgreSQL’s pgcrypto. Our password will also be hashed with pgcrypto, so don’t worry that it’s just text right now. We also have a name and email text column, to be provided by the user. Finally, we have created_at
which keeps track of when the row was created.
In Go, our User
struct will closely match the row:
type User struct {
ID string
Name string
Email string
CreatedAt time.Time
}
We don’t include the password field, because we never need it in our application (there’s never a need to show a user their hashed password, all password code will be done by PostgreSQL).
OK, that’s the domain we’ll be working with: users. Let’s look at how we’d perform simple operations with database/sql
.
Basic SQL Functions in Go
To illustrate working with Users, we’ll work on three core functions throughout this blog post:
- Create a User
- Find a User by ID
- Find a User by Email and Password
Creating a user would be used during registration, finding by id is useful when their profile page is looked at or when we’re loading them as a foreign key, and finding by email and password is useful for logging in.
From Hashed Passwords with PostgreSQL’s pgcrypto we can grab the SQL code for both creating a user with a hashed password and for looking them up by email and password. Combine that with the database/sql
package, and we can implement those functions pretty simply in Go:
// CreateUser inserts a user record into the database and returns a User object
func CreateUser(db *sql.DB, name, email, password string) (*User, error) {
u := &User{}
err := db.QueryRow(`
INSERT INTO users(name, email, password)
VALUES($1, $2, crypt($3, gen_salt('bf', 8)))
RETURNING id, name, email, created_at
`, name, email, password).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
if err != nil {
return nil, err
}
return u, nil
}
CreateUser
is our creation function, and this is following in the Data Mapper pattern, because it encapsulates SQL within a function that only works with domain data (except for the db
parameter, but we’ll work on that in a bit).
We use QueryRow
because that is the database/sql
function that is designed for working with only a single row. Since we know we’re inserting one row, QueryRow
lets us both insert and Scan
with one line of code returning one error object.
We can write a very similar function for finding the user by their email and password:
// FindUserByEmailPassword finds a user with the given email and password
func FindUserByEmailPassword(db *sql.DB, email, pw string) (*User, error) {
u := &User{}
err := db.QueryRow(`
SELECT id, name, email, created_at
FROM users
WHERE email = $1 AND password = crypt($2, password)
`, email, pw).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
if err != nil {
if err == sql.ErrNoRows {
return nil, nil
}
return nil, err
}
return u, nil
}
It’s almost the same as CreateUser
except we’re doing a SELECT
for the query, and when we get the result, we check to see if err
is a sql.ErrNoRows
. We’re going to wrap up this error for the caller as a nil user being returned, since this could be a valid use of the function.
Finding a User by ID is just a simple tweak to finding them by name and email:
// FindUser finds a user with the given id or returns nil
func FindUser(db *sql.DB, id string) (*User, error) {
u := &User{}
err := db.QueryRow(`
SELECT id, name, email, created_at
FROM users
WHERE id = $1
`, id).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
if err != nil {
if err == sql.ErrNoRows {
return nil, nil
}
return nil, err
}
return u, nil
}
That’s the basics of working with the database/sql
package in Go, and we’ve also followed the Data Mapper pattern since we’re creating an abstraction layer between the SQL and User. However, we have a lot of duplication!
Data Mapper in Go, Refactored
In many languages, including Go, there are ORMs or Object Relational Mappers. Their job is to provide a generalized solution for mapping domain objects to sql databases. Since Go is statically typed, Go ORMs make heavy use of reflection, which can potentially be unsafe, and is certainly quite complicated.
Instead of explaining ORMs or building a generalized solution, we’re going to solve our problem the Go way: with a simple and specific solution.
Let’s start by identifying our duplication sources and other areas needing improvement:
- The
db
has to be provided to each function, which violates our Data Mapper’s abstraction over SQL - The columns we SELECT or RETURNING are the same in all queries
- The
Scan
is the same in all queries - Checking for no rows and returning nil is repeated in our Finders
1. UserMapper
First off, let’s make a simple UserMapper
that encapsulates our database connection:
type UserMapper struct {
DB *sql.DB
}
// CreateUser inserts a user record into the database and returns a User object
func (um *UserMapper) CreateUser(name, email, password string) (*User, error) {
u := &User{}
err := um.DB.QueryRow(`
INSERT INTO users(name, email, password)
VALUES($1, $2, crypt($3, gen_salt('bf', 8)))
RETURNING id, name, email, created_at
`, name, email, password).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
if err != nil {
return nil, err
}
return u, nil
}
// FindUserByEmailPassword finds a user with the given email and password
func (um *UserMapper) FindUserByEmailPassword(email, pw string) (*User, error) {
// ...
}
// FindUser finds a user with the given id or returns nil
func (um *UserMapper) FindUser(id string) (*User, error) {
// ...
}
Now, in our application we can connect to the database when we boot up and a create a UserMapper on our server object so that we can access it from our application code (check out Using Object-Oriented Web Servers in Go for a walkthrough of this technique). This means that when we call these methods in our application code, we don’t have to be passing around a database connection.
2. SELECT, RETURNING, and Scan
Now that we have a UserMapper object, we can make some helper methods to help out with SELECT and RETURNING’s columns as well as Scan’s fields.
There are a couple ways to do this, and so I will start by listing my goals for the refactoring:
- Repeat as few concepts as possible
- Keep the code that changes together close together
- Increase readability through abstraction
- Minimize the number of abstraction layers
What this means is that we don’t want to over-refactor our code beyond our current needs, and we want to make it more clear at the levels we will often be working. So, what I decided to do is to extract the following:
- Columns to SELECT and RETURN should be extracted along with Fields to scan to
- Columns and Fields should be near each other to reduce errors when they are changed
- Writing a SELECT to find a single user should be extracted to a common method
- Leave the Create mostly as-is, because we only have one use right now.
Here is my refactoring:
func (um *UserMapper) Columns() string {
return "id, name, email, created_at"
}
func (um *UserMapper) Fields(u *User) []interface{} {
return []interface{}{&u.ID, &u.Name, &u.Email, &u.CreatedAt}
}
// CreateUser inserts a user record into the database and returns a User object
func (um *UserMapper) CreateUser(name, email, pw string) (*User, error) {
u := &User{}
err := um.DB.QueryRow(fmt.Sprintf(`
INSERT INTO users(name, email, password)
VALUES($1, $2, crypt($3, gen_salt('bf', 8)))
RETURNING %s
`, um.Columns()), name, email, pw).Scan(um.Fields(u)...)
if err != nil {
return nil, err
}
return u, nil
}
// FindUserByEmailPassword finds a user with the given email and password
func (um *UserMapper) FindUserByEmailPassword(email, pw string) (*User, error) {
return um.SelectUser(
"lower(email) = lower($1) AND password = crypt($2, password)",
email,
w,
)
}
// FindUser finds a user with the given id or returns nil
func (um *UserMapper) FindUser(id string) (*User, error) {
return um.SelectUser("id = $1", id)
}
func (um *UserMapper) SelectUser(c string, v ...interface{}) (*User, error) {
u := &User{}
err := um.DB.QueryRow(
fmt.Sprintf(`SELECT %s FROM users WHERE %s`, um.Columns(), c),
v...,
).Scan(um.Fields(u)...)
if err != nil {
if err == sql.ErrNoRows {
return nil, nil
}
return nil, err
}
return u, nil
}
I made methods for Columns
and Fields
and put them next to each other to reduce errors when changing. I purposefully decided not to make the columns a constant, because I wanted to keep those concepts really close! I can’t tell you how many times I broke queries when I changed one but not the other.
Now in CreateUser
I can use Sprintf
with Columns
and Fields
to keep the query in sync with the schema. Otherwise, this method is mostly the same. I don’t have any more INSERT
queries right now, so I’m not going to extract. I’m avoiding a premature refactoring.
For both of my Finders, I’ve created a helper called SelectUser
which takes only the conditions for finding a single user (along with the values used by the conditions). This lets me write one kind of SELECT
query for finding users, and reduces the duplication of handling ErrNoRows
.
The SelectUser
method is a bit complicated, but since we can write it once and test it, it should be fairly stable. This greatly simplifies writing Finder methods, and they are really easy to read. This is nice because we may write and change Finders quite often, so it’s nice to shift the simplicity to areas of high churn.
Wrap-up
This is just one step towards creating a Data Mapper for Users. The goal of the pattern approach is not to provide generalized or perfect solutions, but simply to illustrate a technique for solving problems. I encourage you to make your own improvements to the pattern and simplify it for your specific solutions.
I hope this helped you figure out how to abstract code in Go when working with SQL databases!