How to Write a Persistence Layer

A persistence layer is the layer within an application that interacts with the database. The purpose of this post is to explore the common problems that occur while designing a persistence layer, and how those problems can be solved.

Not at all the problems explored here will be relevant to all applications or all database types. Some problems only start to occur once the application reaches a critically large complexity level. To this end, we will explore the problems in the order they are likely to be incurred as a function of increasing application complexity.

Problem Class 1: Running the Queries Incorrectly

In order for application code to use a database, a connection needs to be opened and read/write queries need to be executed using some driver. While code like this tends to be simple to write, writing it is also error prone.

There are two places to make coding mistakes in the application when accessing a database — in the usage of the driver to access the database or in the construction of the query.

Problem 1.1: Driver Usage Bugs

Database drivers are the pieces of software that enable an application to access a database. Driver usage bugs are easy to slip into code because they are often hard to spot in code reviews, hard to unit test and do not result in compile time issues. Common examples of driver usage bugs are failing to handle null correctly or forgetting to close some resource. For a more complete list of common driver usage bugs check out this post. So while driver usage code does tend to be simple to write, it is also easy to write a bug in this code.

Problem 1.2: Query Construction Bugs

Ultimately in order to interact with a database a query needs to be constructed. There are two ingredients to constructing a query — the query template and the input. The query template will take a form that looks like SELECT * FROM People WHERE first_name=? AND last_name=? AND age < ?

Query inputs are supplied in order to produce a fully hydrated query. For example, the previous query may be executed as follows —

queryStr := SELECT * FROM People WHERE first_name=? AND last_name=? AND age < ?
rows, err := dbDriver.Scan(queryStr, "andrew", "dawson", 28)
...

This process of query construction is error prone for several reasons

• The library used to produce a fully hydrated query that the driver can run will accept inputs of type any (or whatever the language equivalent of any is). This is because the library needs to be general enough to construct any type of query. This makes it very easy to pass the wrong type or invert the order of arguments.
• The query is likely defined separately from the schema definition. This makes it easy for column name mismatch bugs to slip in. This would not be caught by most types of unit tests, it would not result in a compile time issue and it would be hard to catch in code review.

The bottom line is that correctly executing queries against a database from application code is error prone and these errors are hard to detect.

Next we will talk about three ways in which developers can build persistence layers to help avoid these common pitfalls.

Solution 1.1: Introduce Persistence Interface

An application which scatters the execution of queries throughout the code wherever they are needed is more likely to write bugs than an application that directs all queries through a single persistence interface. There are two reasons for this:

• Reduced Risk of Duplication: If all queries are run by means of calling a single persistence interface then there is a much smaller change of introducing duplicate queries. The queries just need to be defined once in this single interface and all points in the application which need to run a given query will use that single interface method. Reduced duplication means the total surface area to introduce bugs is reduced.
• Sample of Best Practices: When code to access persistence layer is centralized in a single module, the patterns for accessing the persistence layer are made more clear to future code readers/authors than if access is scattered throughout the application. If the first few persistence interface methods are written correctly, it sets a strong pattern going forward how to write these methods. In contrast this pattern is less immediately obvious is access is scattered throughout the application.

So do not access the database wherever it is needed in the application, instead introduce an interface that is dedicated to persistence interactions and be sure to establish best patterns for driver use and query construction in that interface’s initial implementation.

Solution 1.2: Generate Persistence Wrappers

Despite code authors’ best attempts to define a correct persistence access layer interface, it is still possible for bugs to get introduced in this interface. A good way to protect against this is to autogenerate this persistence access layer. There are tools like sqlc that let the developer define queries in a templated SQL DSLs and the tool autogenerates query access wrappers that can be used by the application. This is super cool because it further reduces the probability of introducing a bug at this layer and it reduces the amount of boilerplate code developers need to write in order to add a new query.

I strongly recommend not writing persistence access interface by hand, but instead using a tool to autogenerate it.

Solution 1.3: Used Named Parameters

The addition of a persistence access interface on top of a driver enables the application to deal with more specific input/output types. The driver interface has to be general enough to support any type of inputs, so the query hydration function signature is going to look as follows —

ConstructQuery(queryTemplate string, inputs ...any)

This is an annoying interface to use because there is zero type safety. It becomes very easy to accidentally pass arguments in the wrong order or use the wrong types. By contrast the persistence access interface defines methods for specific queries so the interface can define more specific types, for example —

InsertPerson(firstName string, lastName string, age int)

This is better because now at compile time the client is forced to pass exactly three arguments and the types must match. But its still not ideal…

Suppose the client wanted to use this method to insert a person with name “Bob Smith” who is 49 years old. The following would compile, and would be an easy mistake to make

firstName := "Bob"
lastName := "Smith"
age := 49
dbAccess.InsertPerson(lastName, firstName, age)

All the types match just fine, the arguments were just passed incorrectly (lastName and firstName are swapped). This is a serious bummer because its easy to miss at code review time, unit tests likely won’t catch this, it won’t even be caught at run time. It won’t really be caught until some developer realizes a bunch of bad data has been inserted or until some application invariant is broken and things blow up.

A solution to help with this is to use named parameters. Different languages will support this differently. But as an example in golang this would be supported by taking a struct instead of a list of arguments. The new caller would look as follows

firstName := "Bob"
lastName := "Smith"
age := 49
dbAccess.InsertPerson(&InsertPersonRequest{
    FirstName: lastName, // more clearly wrong
    LastName: firstName,
    Age: age,
}

It is still possible to make this mistake but the mistake is much more obvious and will be harder to get through code review by mistake.

In summary for part 1, writing code to access a database in the application is error prone. In order to increase your changes of writing correct database access code — define a database access interface, autogenerate the access layer and use named parameters.

Problem Class 2: Ensuring Input / Output Correctness

In the last section we talked about the problems associated with actually running queries, but there is more to interacting with a database then running queries.

This section will focus on two new problem areas — serialization and validation.

Problem 2.1: Serialization

Many application types can be written directly to the database. For example if your application is dealing with an integer, string or boolean those types can just be directly passed to the database driver. But sometimes the application needs to write a blob to the database which represents a complex serialized application type. For these types there needs to be a method for serializing the application type to something that can be written to the database, and there needs to be a method for deserializing the database type into an application type on reads.

Without making some intentional design decisions about how/where (de)serialization is done, these can quickly turn into a mess in code.

Problem 2.2: Validation

Writing corrupted data to a database is very bad. It can result in broken application invariants causing the application to explode or silently result in a build up of corrupted data. Therefore it is important that the application is sure all types that have been written to the database obey application level validation / invariant rules.

It is easy to scatter these validations thoughtless throughout the application leading to high likelihood of messing up the validation in some places.

Validation and serialization are not hard problems but they are easy to not give attention to, and instead just get bolted on wherever necessary. In order to avoid making mistakes in these areas, careful thought should be paid towards getting these right. The next part will talk about a design pattern I have had success with to address serialization and validation.

Solution 2: Application Types

The lowest level of the persistence layer should be an autogenerated interface which is used to execute read/write queries as we talked about in part 1, we will call this part of the persistence layer the query wrappers. The query wrappers should not handle, serialization/validation. Serialization and validation are business logic level concerns, not strictly database query execution concerns so they cannot easily be handled by an auto generation tool.

The solution is to introduce a layer above the query wrappers which operates in terms of application level types rather than database level types. Application types should be non-autogenerated types which have serialization/deserialization and validation logic built in.

The best way to demonstrate this pattern is to look at an example. Suppose we wanted to have the application insert a Person object into a database and suppose a Person was defined by the following schema

CREATE TABLE Person (
  id varchar(256) PRIMARY KEY,
  first_name varchar(256), 
  last_name varchar(256),
  age int,
  contact_info blob
)

Suppose that the application has a baked in assumption that age is a number between 0 and 200, and suppose that contact_info is a complicated application level type.

Building a persistence layer to support this involves the following four pieces: application code which uses the persistence layer, the query wrapper SQL DSLs, the auto generated query wrappers and the layer above the query wrappers which uses the application level types. Lets take a look at what each of these parts could look like from the lowest point on the stack to the highest point on the stack.

Layer 1: SQL DSL Templates

Using a tool like sqlc a SQL DSL template will be defined that looks as follows

get: expect 1
// MethodName: SelectByID
SELECT * FROM Person WHERE id = ?;

exec
// MethodName: InsertPerson
INSERT INTO Person (id, first_name, last_name, age, contact_info) (?, ?, ?, ?, ?)

This templated DSL can be understand by a wrapper auto generation tool in order to generate strongly typed query wrappers. Lets take a look at those.

Layer 2: Query Wrappers

Running a tool like sqlc will then generate query wrappers that look roughly like the following

type (
  Person struct {
    Id string
    FirstName string
    LastName string
    Age int32
    ContactInfo []byte
  }

  SelectByIDParams struct {
    Id string
  }

  InsertPersonParams struct {
    Id string
    FirstName string
    LastName string
    Age int32
    ContactInfo []byte
  }

  PersonWrapper struct {
    dbDriver *mysql.Driver
  }
)

func (pw *PersonWrapper) SelectByID(ctx context.Context, params *SelectByIDParams) (*Person, error) {
  // auto generated code to correctly use driver to do the following 
  // run select query 
  // construct Person struct 
  // handle errors correctly 
  // close any open resources 
  // return result to caller
}

func (pw *PersonWrapper) InsertPerson(ctx context.Context, params *InsertPersonParams) error {
  // auto generated code to correctly use driver to do the following 
  // run insert query 
  // return any errors encountered to caller
}

This code is fully auto-generated boiler plate code. As long as a good query wrapper generation library is selected the odds that there is a bug in this code are small. For the most part developers should not even need to look at this code, they think in terms of the templated SQL DSL and take these query wrappers for granted.

But these query wrappers do not address the problems of validation or (de)serialization. In fact they cannot address those problems because those are applications concerns which cannot be inferred by reading a SQL DSL template. So we need to introduce another layer that deals with application types.

Layer 3: Application Types

In our application we have one validation rule that is not enforced at the database level and one bit of serialization that needs to be done at the application layer

• Application Invariant: Age must be a number between 0 and 200.
• Serialization: ContactInfo must be serialized on write and deserialized on read.

In order to do that we are going to introduce a non-autogenerated application type for Person — it will look as follows

type (
  Person struct {
    Id string
    FirstName string
    LastName string
    Age Age
    ContactInfo ContactInfo
  }

  ContactInfo struct {
    PhoneNumber string
    Email string
  }

  Age interface {
    RawAge() int
  }
)

There are several things we should note about this application type.

• Age on person is an interface. This is a golang language specific mechanism for ensuring that Age conforms to certain invariants. Other languages will have different means of enforcing that fields are constructed with certain restrictions. But in golang, by using an interface you can actaully ensure that clients can only construct an Age interface if it is constructed to meet certain invariants. This means the Person object ultimately cannot be constructed with an invalid age — it is impossible to do that.
• ContactInfo is not an array of bytes like it is at the query wrapper level, instead its a nicely deserialized application type. The application does not need to think about dealing with an array of bytes it simply calls ContactInfo.PhoneNumber to get the phone number.

In order to complete this picture lets look at the glue that connects this application type to the query wrappers in the previous section.

type (
  Person struct {
    Id string
    FirstName string
    LastName string
    Age Age
    ContactInfo ContactInfo
  }

  ContactInfo struct {
    PhoneNumber string
    Email string
  }

  Age interface {
    RawAge() int
  }

  age int
)

func NewAge(rawAge int) (Age, error) {
  if rawAge < 0 || rawAge > 200 {
    // this enforces at runtime before the query could possibly insert a 
    // record that the age for a person is valid
    return nil, errors.New("invalid age given")
  }

  return age(rawAge), nil
}

func (a *age) RawAge() int {
  return int(*a)
}

func InsertPerson(ctx context.Context, person *Person) error {
  // note that here its impossible for person to violate any application
  // level invariants, age must be valid here

  // note that callers of this function do not directly need to consider
  // validation or serialization that is baked into the cake here.
  
  rawContactInfoBytes, err := json.Marshal(person.ContactInfo)
  if err != nil {
    return err
  }

  if err := queryWrappers.InsertPerson(ctx, &InsertPersonParams{
    Id: person.Id,
    FirstName: person.FirstName,
    LastName: person.LastName,
    Age: person.Age.RawAge(), // here we are converting to the persistence level type
    ContactInfo: rawContactInfoBytes, // here we convert to persistence types
  }); err != nil {
    return err
  }

  return nil
}

func SelectByID(ctx context.Context, id string) (*Person, error) {
  rawPerson, err := queryWrappers.SelectByID(ctx, id) 
  if err != nil {
    return nil, err
  }

  var applicationContactInfo ContactInfo{}
  if err := json.Unmarshal(rawPerson.ContactInfo, &applicationContactInfo); err != nil {
    return nil, err
  }

  return &Person{
    Id: rawPerson.Id,
    FirstName: rawPerson.FirstName,
    LastName: rawPerson.LastName,
    Age: NewAge(rawPerson.Age),
    ContactInfo: applicationContactInfo,
  }, nil
}

Lets make a few more notes about this code

• Any application code that uses this InsertPerson method can only construct a Person which conforms to application invariants. If a non-valid person is a constructed the query will just fail at runtime before any insert happens.
• Any application code that uses this InsertPerson method does not need to think about serialization at all because its handled within the InsertPerson method. Symmetrically on the read path callers of SelectByID method do not need to worry about deserialization of the ContactInfo object.
• Lastly notice that the usage of named types everywhere rather than flat list of input arguments makes it very clear that the right arguments are being passed in the right order.

Now in order to close this out lets look at what the application code that needs to interact with persistence would look like.

Layer 4: Application Code

func AddPersonRPCHandler(ctx context.Context, req *AddPersonRequest) error {
  age, err := NewAge(req.GetAge()) 
  if err != nil {
     log.Error("failed to add person, age must be between 0 and 200")
     return err
  }

  p := &Person{
    Id: uuid.NewUUID()
    FirstName: req.GetFirstName(),
    LastName: req.GetLastName(),
    Age: age, // use the already validated type
    ContactInfo: ContactInfo{
      PhoneNumber: req.GetContactInfo().GetPhoneNumber(),
      EmailAddress: req.GetContactInfo().GetEmailAddress(),
    }
  }
  return InsertPerson(ctx, p) 
}

This application code has all the nice properties we want because of the layers we have built below it.

• Types are validated
• Serialization and deserialization have a clear and single owning component
• Query and driver usage is very likely to be correct
• The surface area used to run queries is as small as possible. If this same query needs to be run somewhere else it will depend on the same functions.

In order to understand just how good this is, contrast it with it running a query inline in this RPC as follows —

func AddPersonRPCHandler(ctx context.Context, req *AddPersonRequest) error {
  sqlDriver, err := mysql.NewDriver(connStr)
  if err != nil {
    return err
  }

  // possible mistake 1: easy to forget to close 
  defer sqlDriver.Close() 

  // possible mistake 2: easy to skip validation logic or do it differently in different places throughout codebase 
  if req.Age < 0 || req.Age > 200 {
    log.Error("failed to add person, age must be between 0 and 200")
    return errors.New("age is invalid")
  }

  // possible mistake 3: easy to do serialization differently throughout codebase
  serializedContactInfo, err := json.Marshal(req.GetContactInfo())
  if err != nil {
    return err
  }

  // possible mistake 4: easy to define query incorrectly 
  queryTemplate := `INSERT INTO Person (id, first_name, last_name, age, contact_info) VALUES (?, ?, ?, ?, ?)`
  
  // possible mistake 5: easy to pass arguments in the wrong order
  return sqlDriver.Exec(
    queryTemplate, 
    uuid.NewUUID(), 
    req.GetFirstName(), 
    req.GetLastName(), 
    req.GetAge(), 
    serializedContactInfo,
   )
}

By contrast this code is much harder to spot errors during code review and clutters business logic with (de)serialization, validation and driver usage logic. Multiple this by every place queries need to be run and the application will become a nightmare.

• Use application types that have validation and (de)seralization built in
• Writer a persistence layer on top of auto generated query wrappers that deals in terms of application types and converts to wrapper types.
• Use named parameters everywhere
• Use whatever your language offers to enforce at runtime that application types cannot be constructed unless they are valid.

Problem Class 3: Orchestrating Queries Together

The last bucket of problems is rarely encountered so I will be only touch on it briefly. Sometimes application code gets so complex that the same set of queries need to be run as part of a transaction representing a unit of business logic in multiple places throughout the codebase.

For example suppose suppose there are multiple places that a new user needs to be registered. The registration of a user may require running multiple queries as part of a transaction in a given order. The process of registering a user can be seen as a logical chunk of business logic that can be factored out. Factoring out these logical business logic units into a component that sits on top of the existing persistence layer can simplify application code. Doing this ensures that there is just a single component responsible for the complexity of user registration.

Often this final level of abstraction is not needed. I would recommend skipping it until you find you need this added layer.

Good luck writing persistence layers, I hoped this helped.