# Creating a Project Template? Go With Database Storage

[Tomás Kocman](https://www.strv.com/blog/authors/tomas-kocman)  
Go Backend Engineer

---

I expect you’ve already read [the first part of this “STRV Go Template” series](https://www.strv.com/blog/creating-a-project-template-go-with-domain-driven-design), where we mentioned the database layer and noticed a directory called *postgres* in each of the domains. In this blog post, you will find out what’s inside — and how we work with a database, what design pattern we adopted and, most importantly, how it fits in with domain-driven design.

When talking about databases a little bit deeper, the database is our source of truth, isn't it? A lot of effort should be put into keeping data consistent to prevent application misbehaving. Our database layer must also be impervious to data races.

**One of the most important aspects is maintainability and extendability.** You definitely shouldn't be stressed out when modifying or implementing a new feature — that's the goal. Everyone wants to prevent situations like data inconsistency due to data races of transactions or internal server errors caused by wrongly handled database errors.

Let’s see **how we work with a database** and how it matches the DDD principles.

## Which Database to Choose?

From my experience, most projects — at least in the first phase — are suitable for relational databases. From time to time, we also have use cases for document databases, and it’s possible that in later phases, we might run into performance issues or that we just want to improve application latency. This is the right time to think about a caching system.

I mentioned three database types, **but what specific databases do we really use?**

In the case of relational databases, it’s PostgreSQL (Aurora Serverless V2); as a document database, it’s MongoDB (Amazon DocumentDB); and as a caching system, we have experience with Redis (Amazon ElastiCache) and Amazon DynamoDB. But I think the template should contain only the bare minimum of functionality, which will most probably be used within every project. **That means we’ve chosen PostgreSQL as a database in the template** — and if there’s a requirement for another database during project development, we add the support as we go.

Considering all that, the rest of this blog post focuses solely on PostgreSQL implementation and how we work with relational databases.

## Not Everything Is Solved by an ORM

When starting to implement a database layer, many people’s first question is, “What ORM should I use?” There is actually another option, which I really like: using plain SQL queries.

There are both **advantages and disadvantages to using an Object-Relational Mapper.** ORMs are great at abstracting database access, which is perfect when you’re starting a new project and don’t want to spend time fiddling around with SQL database-specifics early on. Maybe you’ve noticed that a lot of people in the Go community do things a little differently, and database access is a good example. As Rob Pike said, “Clear is better than clever.”

The use of ORM in Go is not typical among developers. I realize ORM has benefits like significant improvement in developer speed or helping with migrations. But on the other side, it's less performant and too invasive and "magical.” ORMs hide much of the SQL internals, which might be very tricky when it comes to complicated queries.

**We use plain SQL; it's the Go community’s standard and is good to know in general.** But to mention some drawbacks, it does represent a challenge to manage a lot of queries and increases maintenance efforts. Writing simple SQL statements to do basic CRUD operations also takes more time than with ORM.

ORM in Go comes with the danger of not having all types of support (like complex joins) so at some point later down the road, you might end up writing plain SQL anyway. It's not uncommon to solve complex problems just using a single optimized query in Postgres.

I also see another problem, less directly about ORM and more about the design nature. It can be so easy to think of models as regular objects that using them everywhere goes without saying. Sometimes, I hear from other departments: **“You guys copy-paste models so much, why don’t you just use a single model for business logic and at least a database but ideally for everything?”**  
Well, I don’t know much about mobile development but in the backend, before you know it, the entire application starts revolving around that same fat model — and other parts of the code suddenly depend on parts of the model on which they weren’t supposed to.

Regardless of whether you use an ORM or not, keep your database models succinct and tied to the database layer. Keep in mind that the domain is the core of your application and the database adapter is supposed to satisfy the domain’s output port. That’s one of the reasons we keep database models unexported. I’ll explain how conversion to domain models is made a little later, in the repository pattern section.

## SQL Driver

Another decision is in order. **Choosing the right underlying SQL driver** is the next critical mission in a project’s early stages. In the case of the template, a [pgx](https://github.com/jackc/pgx) driver with raw SQL queries is used. Of course, there are alternatives, such as [sqlx](https://github.com/jmoiron/sqlx) or [sqlc](https://github.com/kyleconroy/sqlc); we've chosen **pgx** because it's suited directly for Postgres, which we use for every project.

Since it's written purely for a single database, it aims to be **low-level, fast and performant** — enabling Postgres-specific features other packages don't allow for. You can even define serialization/deserialization for your custom type in relation to the Postgres type. All SQL files the app needs are embedded directly into the binary using the [embed](https://pkg.go.dev/embed) package.

## Querying & Scanning

Now that we’ve selected the SQL driver, the question is how much it offers in terms of querying and scanning.

Until recently, there was only one option for how to query and scan using **pgx**. If you use the default Go [database/sql](https://pkg.go.dev/database/sql) package for operating with databases, you’re probably familiar with positional arguments. Let's take a simple SQL query as an example:

```sql
SELECT
  u.id,
  u.name,
  u.email
FROM
  "user" AS u
WHERE
  u.email = $1
```

Each argument passed into the query function needs to match the dollar signs in the query definition. Similarly, when scanning the result into the **user** object, three arguments for scanning need to match the order in our case.

Things substantially changed with **pgx v5**. Arguments in our simple query could be slightly rewritten. Instead of the dollar sign, we can now use named arguments:

`u.email = @email`

This approach significantly eliminates the possibility of mistakes and it's much more readable. You explicitly provide the mapping between argument names and structure fields, so there shouldn't be any mistakes in argument ordering.

```go
pgx.NamedArgs{"email": user.Email}
```

There is an analogous approach regarding scanning. If you’ve ever worked with **sqlx** or some alternative, you’re probably already familiar with modifying the structure with **db** tags. We use the package [scany](https://github.com/georgysavva/scany) to enable this feature. In that case, we just provide tags for structure fields and **scany** ensures correct argument handling when reading from Postgres.

```go
type user struct {
  ID    uuid.UUID `db:"id"`
  Name  string    `db:"name"`
  Email string    `db:"email"`
}
```

After the changes, both the code and SQL queries are much more readable and maintainable.

## Migrations

Migrations are an integral part of working with a database. Every time a new feature comes in, migration files sit nearby.

Migration means converting a database schema to a new version requested by a new functionality. **This is the field where ORM probably wins**, with its out-of-the-box auto migration functionality. But don't worry, there’s a great tool for database migrations: [migrate](https://github.com/golang-migrate/migrate).

We use migrate in the form of CLI binary for local development or testing migration files, but it can be used as a Go package; since this database tooling is written purely in Go, **you can easily implement auto migrate for your application, too.** We did so in the template and I highly recommend you to do the same. The application simply tries to apply all migrations on each start so we don’t have to do it manually before deployment. It supports a wide range of databases, including⁠ MongoDB, Neo4j, Cassandra, and CockroachDB.

The most important part here is the migration files. **Each migration consists of two SQL files** (up and down script). My main recommendation is to name the files with number prefixes so **migrate** processes them in the correct order. In our case, it could be:

`0001_create_tables.up.sql`  
`0001_create_tables.down.sql`

The number at the beginning of the file name is the order number the migration file was created with. Migration files can be generated directly with the migrate tool:

`migrate create -ext sql -digits 4 -seq -dir ./migrations create_tables`

**Alternatively, you can name the start of migration files with the date — but we changed it to simple integers.**  
In an environment where a lot of developers in a single team create their pull requests, a migration with an earlier date could be merged after another migration with a later date. In this case, the migrate tool wouldn’t apply this migration. The worst-case scenario with sequential migration names is that two migrations will have the same sequence, in which case the error will be returned so a developer can notice and fix it.

The content of the files is the description of how the database schema should be built (in most cases, it's create/drop table). **migrate** allows you to do miracles with your database based on your migration files — just try it out yourself.

## Repository Pattern

With all chore parts set up, we can finally delve into the **relationship between the domain and the database** — starting with the most interesting part for those interested in DDD.

[The first part of this blog series](https://www.strv.com/blog/creating-a-project-template-go-with-domain-driven-design) depicts domain code structure. Inside each domain is the *repository.go* file. The thing is, the repository is the whole pattern; it cannot fit into a single file with code. Let’s deep dive into why this pattern is one of my favorites.

In my opinion, **one of the main reasons an application may have become very complex and hard to read is that some people tend to be too reluctant to a proper decoupling of the database logic and the application (business) logic.**  
Keeping the logic of your application along with your database logic makes your application much more complex and hard to both test and maintain. So if you encounter words like *connection*, *begin*, *commit*, *rollback*, and so on in your service layer, be aware that it might be a serious problem in the future.

The main purpose of the repository pattern is to separate these two logic spaces. It allows you to make your code simpler and easier to add new functionalities. As a bonus, you can defer the important decisions of choosing a database solution and schema. Another good side effect of this approach is out-of-the-box immunity for database vendor lock-in.

The idea of the repository pattern is to communicate with the database via the interface. **You should define the interface to support any database implementation**, which means that it should be free of any implementation details of any database. The interface in *repository.go* might look like this:

```go
type Repository interface {
  Create(ctx context.Context, user *User) error
  Read(ctx context.Context, userID uuid.UUID) (*User, error)
}
```

**Hidden within the** **Create** **and** **Read** **functions of real database implementation is the entire database boilerplate** — like initializing a connection, releasing, calling *Exec* or *Select* pgx functions and so on. This is answers what’s inside the *postgres* directory (a question posed in the previous blog post). As you can see, there’s no need for a service using this interface to call any database low-level operation, like for working with connections and (most importantly) transactions. As a result, another **SOLID** principle — dependency inversion — is ticked off.

You might be thinking that it’s an artificial example and things are much more complex in the real world. And you’re right, because **how do we do the transactions?**

Personally, my biggest challenge was how to manage transactions in a clean way that does not affect the rest of the application too much, is not dependent on the implementation and is both explicit and fast. Let’s take a look at the **Update** function I added to the repository interface.

```go
type UpdateFunc func(*User) (*User, error)

type Repository interface {
  Create(ctx context.Context, user *User) error
  Read(ctx context.Context, userID uuid.UUID) (*User, error)
  Update(ctx context.Context, userID uuid.UUID, fn UpdateFunc) error
}
```

Besides the **Update** function, you can see also **UpdateFunc**. This is the key to working with transactions in the repository pattern; it’s an approach based on closure passed to the update function. **The basic idea is that when we call** **Update**, **we need to provide** **UpdateFunc** **that can update the provided user.**  
In practice, the transaction flow in the database function is as follows:

- Open the transaction.
- Read the user from the database.
- Call the update method on the user object.
- Save the updated user back to the database.
- Execute commit/rollback.

It’s also nothing terrible to have multiple update-like functions created with extra data to save. Under the hood, the implementation should reuse the same code without a lot of duplication.

**Pro tip:** Don’t forget to use *SELECT … FOR UPDATE* in your queries to prevent data races where it might potentially happen. But still, you can ask how to do certain operations — like creating a user, a session, and some default settings in our database, all within one transaction. (I’ll elaborate on this in a minute.)

### Eventual Consistency

One of the most important aspects of software development in general is **keeping application data consistent, no matter what.** It means having a guarantee that changes made within a transaction are consistent with database constraints.  
Consistency models are a topic for a blog post of its own — but in general, in DDD, you should strive for **eventual consistency** beyond your bounded contexts, as we already know from the repository pattern that a domain cannot touch the data of another domain. Bounded contexts have to be completely separated, which, of course, also applies to database operations.

Eventual consistency is a design approach for improving scalability and performance. **Although transactional consistency seems more straightforward and easier to use, eventual consistency has an advantage in a lot of use cases.**  
In DDD, it often leads to better design compared to working with traditional transactions. I wouldn’t even be afraid to say that in real-world use cases of really big systems, transactional consistency workflows are rare. A business process often involves a series of use cases and a persisting group of changes represented by aggregates. Domain use cases are responsible for keeping aggregates consistent.

Getting back to the problem with the user, session and settings, **I’d recommend rethinking the aggregates and making an aggregate from the user and his settings.** The session could be a separate aggregate.  
To support this statement, my previous blog post states that DDD is a great design for rewriting your entire huge application into microservices, if you decide to do so. You can now see one of the reasons.

If you’d like to have the session in one transaction with the user and all other things that relate to the user, how would you then refactor this enormous transaction and the whole flow to make an authentication microservice? It would be very difficult and ultimately — because of the nature of microservices — you’d end up with eventual consistency anyway.  
So think about it from the very beginning and **try to design your entities and aggregates in the correct way.**

Even if the approach with repository and eventual consistency between bounded contexts adds a bit more code, it’s totally worth making that investment. It should benefit you very soon, when the project grows up.

## Database Adapter in Detail

We already know the relationship between the service and database layer, but **how is the database layer actually implemented internally to support all needed functionality to repositories inside the domains?**

Because all domains in our template use PostgreSQL, there’s no need to duplicate code in each domain. Instead, we’ve separated the database implementation into the sub-package of the project root.

There are also all migration files because it’s very convenient to have all migrations in one place. In the case of our template and most of our projects (where we use solely PostgreSQL), there’s no reason to not have all migration files in the database adapter — so domain repositories can expect the prepared database schema in place.

### Setting Up the Correct Interface

So that repositories can do their job, they require very simple database operations, specifically acquiring/releasing a connection and transaction-related operations like beginning, committing, and rolling back transactions. Since the repository embeds the whole database adapter, we can simply start with the interface describing how the database functions will be used and what an adapter needs to implement.

```go
type DataContext interface {
  Ctx() context.Context
}

type DataSource interface {
  AcquireConnCtx(ctx context.Context) (DataContext, error)
  ReleaseConnCtx(dctx DataContext) error
  Begin(ctx context.Context) (DataContext, error)
  Commit(dctx DataContext) error
  Rollback(dctx DataContext) error
}
```

**DataContext** holds internal information about a connection or transaction. The second interface, **DataSource**, is used for obtaining or using **DataContext** and is directly embedded in repositories.

That's how the contract between the database layer and other parts of the code base could look like. No one knows anything about the internal implementation of the database, they just use the interface.

As you can see from the function declarations in **DataSource**, **you can simply change the SQL database** — since the interface is pretty common for relational databases. It’s up to you how you implement concrete implementation of this interface based on the used database and driver, but there are no big differences.

### Useful Helpers

It’s boring to constantly copy-paste *AcquireConnCtx*/*ReleaseConnCtx* or similar functions in each repository function. Let’s try to simplify it and make generic helpers, which will make repositories more concise.

```go
func WithConnectionResult[T any](ctx context.Context, s DataSource, f func(DataContext) (T, error)) (result T, err error) {
  dctx, err := s.AcquireConnCtx(ctx)
  if err != nil {
    return result, fmt.Errorf("acquiring connection: %w", err)
  }
  defer func() {
    if releaseErr := s.ReleaseConnCtx(dctx); releaseErr != nil {
      err = errors.Join(fmt.Errorf("releasing connection: %w", releaseErr), err)
    }()
  }()
  return f(dctx)
}
```

This is one of our helper functions for working with databases in the repository. **WithConnectionResult** takes a function as a parameter, which contains the actual database logic. The function is just wrapped between connection handling.

Similarly, we have helpers for working with transactions. An example is refreshing a session. The helper first begins a transaction, then calls the provided function with expected logic like reading the session, checking whether the session isn’t expired, and generating and storing a new session. Then, the helper calls commit and rollback if needed.

But back to our simpler use case of just a connection. The practical use of this function might look like this:

```go
func (r *Repository) Read(ctx context.Context, userID uuid.UUID) (*domuser.User, error