Pricing Blog Privacy Sign in

Writing a Data Mapper in Go without an ORM

by on

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:

  1. Create a User
  2. Find a User by ID
  3. 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:

  1. The db has to be provided to each function, which violates our Data Mapper’s abstraction over SQL
  2. The columns we SELECT or RETURNING are the same in all queries
  3. The Scan is the same in all queries
  4. 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:

  1. Repeat as few concepts as possible
  2. Keep the code that changes together close together
  3. Increase readability through abstraction
  4. 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:

  1. Columns to SELECT and RETURN should be extracted along with Fields to scan to
  2. Columns and Fields should be near each other to reduce errors when they are changed
  3. Writing a SELECT to find a single user should be extracted to a common method
  4. 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!

— Nick Gauthier

Frustrated with video conferencing?

MeetSpace has the best audio quality and reliability around, and is built for distributed teams.

Start Your Free Trial