Go Web Application Structure - Part 3 - The Database

Go Web Application Structure - Part 3 - The Database
  • Part 1: Web Application Structure
  • Part 2: Routing/Serving
  • Part 3: The Database (you're looking at it!)
  • Part 4: Database Migrations & Business Logic (soon...)


The database is a fundamental part of any application, whether you're using a simple database like sqlite or a graph database like neo4j, the data for our application must be persisted somewhere. Choosing the right database is worth its own blog post (at least) so we won't cover that here.

For our application, we will use PostgresSQL which is an open source relational database, under development for over 30 years.

Selecting a database (the short version)

When starting to build a web application, it makes sense to start with designing your data model. A data model can be a formal UML diagram or just something you draw on a piece of paper. In either case, you should have a firm understanding of what the models you will need are and how they are related. Once you have this, you can start to assess different databases in a more educated way.

Data models

Designing data models can be quite fun; when done properly, making modifications to your application are easy and pain-free. When done improperly, it can feel like every new addition you make is like removing a brick from Jenga. Luckily, our data model for storing TODOs will be quite simple.


We only have two models, a "Todo" and a "User". However, there is a relation between them. We see that a Todo has a user id field. In database terminology, this is called a foreign key. We can talk about this in two ways:

  1. A Todo belongs to a User
  2. A User has many Todos


An ORM (Object Relational Mapper) is very common in dynamic languages like Python and Ruby. Depending on the web application framework you're using, it may be built in as is the case with Django. In Go, we have a couple options: we can build our own using database/sql or use something like GORM. For our application, we will be using GORM because our database interactions are very simple and GORM will greatly reduce the amount of code we need to write.

Why use an ORM?

ORMs sometimes get a bad rap for being slow and bloated, however, I believe that this is a symptom of bad data modeling or trying to use the ORM for something more than it was designed to do. A prebuilt ORM, like GORM, will likely be open source and receive contributions from hundreds or thousands of people. You can feel more confident about interacting with your database and not worry about implementing new features. A better discussion on ORMs would deserve its own blog post as well.

Connecting to the database

Now that we have our database schema, it's time to set up our application to connect to a database. The first thing to do is create a db folder in our man directory mkdir -p $(go env GOPATH)/src/github.com/<username>/todos/db. After that, we will create the config.go file.

// ... snip ...

type Config struct {
	DatabaseURI string

func InitConfig() (*Config, error) {
	config := &Config{
		DatabaseURI: viper.GetString("DatabaseURI"),
	if config.DatabaseURI == "" {
		return nil, fmt.Errorf("DatabaseURI must be set")
	return config, nil

Now we can create our Database struct in a db.go file.

// ... snip ...

type Database struct {

func New(config *Config) (*Database, error) {
	db, err := gorm.Open("postgres", config.DatabaseURI)
	if err != nil {
		return nil, errors.Wrap(err, "unable to connect to database")
	return &Database{db}, nil

Notice, we are wrapping the gorm.DB struct so we can add new methods to our custom Database struct. This will make interacting with it simpler because we can do db.First(&todo) instead of db.DB.First(&todo). It may not seem like much, but as our application grows those three extra characters add up.

Database connections

Before we go further, it's a good time to talk about database connections. When working with a database, usually there is a maximum limit of concurrent connections to it. This means that if we get one million api requests, we can't create one million database connections. Luckily for us, the database/sql library is threadsafe and can automatically handle connection pooling. This means we can connect to our database once when our application starts and use the same instance with more than one request. There was even a Github issue related to this topic.

Modifying App to connect to the database

Now that we understand how database connections work, we can modify our App struct to point to a Database.

// ... snip ...

type App struct {
	Config   *Config
	Database *db.Database

// ... snip ...

func New() (app *App, err error) {
	// ... snip ...

	dbConfig, err := db.InitConfig()
	if err != nil {
		return nil, err

	app.Database, err = db.New(dbConfig)
	if err != nil {
		return nil, err

	return app, err

func (a *App) Close() error {
	return a.Database.Close()

Notice the new Close method for the App? This makes sure that any connections our App has are closed when the App is. In our serve.go file we will make a small update that simply is defer a.Close(). Lastly, when we instantiate a new Context we will have a pointer to the App's Database connection.

Declaring Data Models

In our existing models folder, we will create two new files: user.go and todo.go.

// ... snip ...

type User struct {

	Email          string
	HashedPassword []byte
// ... snip ...

type Todo struct {

	Name string
	Done bool

	User   User
	UserID int

By using gorm.Model we will get some fields by default. You can read more about it here.

Database methods

There are a few database methods we will want to create to help us interact with our data models.

  • GetUserByEmail
  • CreateUser
  • GetTodoById
  • GetTodosByUserId
  • CreateTodo
  • UpdateTodo
  • DeleteTodoById

In our db directory, we can create two new files user.go and todo.go. It's good practice to separate your code into different files depending on the use or model in this case.

// ... snip ...

func (db *Database) GetUserByEmail(email string) (*model.User, error) {
	var user model.User
	return &user, errors.Wrap(db.First(&user, &model.User{Email: email}).Error, "unable to get user")

func (db *Database) CreateUser(user *model.User) error {
	return db.Create(user).Error

func (db *Database) GetTodoById(id uint) (*model.Todo, error) {
	var todo model.Todo
	return &todo, errors.Wrap(db.First(&todo, id).Error, "unable to get todo")

func (db *Database) GetTodosByUserId(userId uint) ([]*model.Todo, error) {
	var todos []*model.Todo
	return todos, errors.Wrap(db.Find(&todos, model.Todo{UserID: userId}).Error, "unable to get todos")

func (db *Database) CreateTodo(todo *model.Todo) error {
	return errors.Wrap(db.Create(todo).Error, "unable to create todo")

func (db *Database) UpdateTodo(todo *model.Todo) error {
	return errors.Wrap(db.Save(todo).Error, "unable to update todo")

func (db *Database) DeleteTodoById(id uint) error {
	return errors.Wrap(db.Delete(&model.Todo{}, id).Error, "unable to create todo")

Our methods are short and concise; this is one of the benefits of using an ORM. Notice how we don't do any validation here? Our database should be "dumb" in the sense that all it needs to worry about doing is interacting with the database. All this business logic of validating user emails, checking that only the user who created the TODO can view it, or anything else like this should be handled in the app directory.

Part 4 will cover how to create methods for handling business logic as well as database migrations. For now, you can update serve.go with this little snippet if you want to play around with your database:

// ... snip ...

var serveCmd = &cobra.Command{
	Use:   "serve",
	Short: "serves the api",
	RunE: func(cmd *cobra.Command, args []string) error {
        // ... snip ...
		var wg sync.WaitGroup

		go func() {
			defer wg.Done()
			defer cancel()
			serveAPI(ctx, api)
        // start new
        a.Database.AutoMigrate(&model.User{}, &model.Todo{})
        a.Database.CreateUser(&model.User{Email: "foo@example.com"})
        // end new

		return nil

Then you can use the psql command to look at the tables. In the next part we will go over proper migrations and how to interact with these database methods we created from our App.

View all the code for this part here.

Photo by panumas nikhomkhai from Pexels

Go Web Application Structure - Part 3 - The Database
Share this