Golang Database Library Orm Example - Create

In this post, we compare and contrast how these libraries and ORMs handle a record insertion.

As a standard approach to all example in these blog series, our controller accepts and parses client request to a custom ‘request’ struct, and if required, parses query parameter(s). We hash the password and together with CreateUserRequest we try to insert to database. Then the struct is passed down to our data access layer.

type CreateUserRequest struct {
	ID         uint   `json:"id"`
	FirstName  string `json:"first_name"`
	MiddleName string `json:"middle_name"`
	LastName   string `json:"last_name"`
	Email      string `json:"email"`
	Password   string `json:"password"`
}

All CRUD operations are done in crud.go files of each of the libraries and ORMs directories.

You should perform validation in production. In our case, we skip this part to keep this blog series focused.

Table of Contents

sqlx

In sqlx, we call ExecContext() to perform an insertion. It requires a context, an SQL query and its arguments. In this example, we use QueryRowContext() which not only performs the insertion, but we can obtain the newly inserted data thanks to using postgres’ RETURNING clause.

const Insert = "INSERT INTO users (first_name, middle_name, last_name, email, password, favourite_colour)
VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, first_name, middle_name, last_name, email, favourite_colour"

func (r *database) Create(ctx context.Context, request *UserRequest, hash string) (*userDB, error) {
	var u userDB
	err := r.db.QueryRowContext(ctx, Insert,
		request.FirstName,
		request.MiddleName,
		request.LastName,
		request.Email,
		hash,
		request.FavouriteColour
	).Scan(
		&u.ID,
		&u.FirstName,
		&u.MiddleName,
		&u.LastName,
		&u.Email,
		&u.FavouriteColour,
	)
	if err != nil {
		return nil, fmt.Errorf("error creating user record: %w", err)
	}

	return &u, nil
}

Care needs to be taken that the order of your arguments must match with your sql query. Any order mismatch will cause an error. That means if column order has been changed, you will have to check your Go code manually since this library or compiler does not warn you.

Similar story in the scanning database values into Go code. Scanning is tedious because not only you need to get the order right, but you also need to know what the required fields are required to be scanned by looking at the sql query. For example, middle_name is nullable, which means the value can be either null or a string. For that reason, we need to scan the value into something like sql.NullString.

type userDB struct {
	ID              uint           `db:"id"`
	FirstName       string         `db:"first_name"`
	MiddleName      sql.NullString `db:"middle_name"`
	LastName        string         `db:"last_name"`
	Email           string         `db:"email"`
	Password        string         `db:"password"`
}

Also, the userDB struct is a new custom struct created specifically for scanning the values into a Go struct. We use a db struct tag for each field. It can potentially use reflection to know which database column maps to which struct field by looking at the struct tag. The value you give to the db struct tag must match with the field name you have in the database.

Note that we are returning userDB type that has a password field. We should not expose this field to the client. We have already omitted scanning password field into userDB struct. But a good discipline is to have a struct for each db scanning and client response. In this example, we make a data transform object (DTO) from a db struct by copying into a UserResponse struct that does not have a password field.

respond.Json(w, http.StatusOK, &UserResponse{
		ID:         u.ID,
		FirstName:  u.FirstName,
		MiddleName: u.MiddleName.String,
		LastName:   u.LastName,
		Email:      u.Email,
	})

sqlc

To use sqlc, we put all of sql queries in an .sql file and tell sqlc to generate from that file. You only need to give each of the query a name and how many records it returns. For example:

-- name: CreateUser :one
INSERT INTO users (first_name, middle_name, last_name, email, password, favourite_colour)
VALUES ($1, $2, $3, $4, $5)
RETURNING *;

The name that we want our method is CreateUser and it only returns one record. Once sqlc.yaml is properly configured, we run

$ sqlc generate

And the generated codes are created in query.sql.go.

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
	row := q.db.QueryRowContext(ctx, createUser,
		arg.FirstName,
		arg.MiddleName,
		arg.LastName,
		arg.Email,
		arg.Password,
		arg.FavouriteColour,
	)
	var i User
	err := row.Scan(
		&i.ID,
		&i.FirstName,
		&i.MiddleName,
		&i.LastName,
		&i.Email,
		&i.Password,
		&i.FavouriteColour,
	)
	return i, err
}

sqlc automatically generates Go code containing the arguments to QueryRowContext() as well as the scanning. To use, we call this CreateUser() method with the provided struct to fill in the parameters CreateUserParams.

func (r *database) Create(ctx context.Context, request db.CreateUserRequest, hash string) (*User, error) {
	u, _ := r.db.CreateUser(ctx, CreateUserParams{
		FirstName: request.FirstName,
		MiddleName: sql.NullString{
			String: request.MiddleName,
			Valid: request.MiddleName != "",
		},
		LastName:        request.LastName,
		Email:           request.Email,
		FavouriteColour: ValidColours(request.FavouriteColour),
		Password:        hash,
	})
}

Notice how the order no longer matters because we are filling in the parameters in a struct. But with any schema change (column ordering, creation, etc), you will have to re-run sqlc generate.

Note that CreateUserParams generated by sqlc requires the optional field middle_name to be declared in sql.NullString and we have to tell that it is valid! The Go documentation in database/sql package says that

NullString implements the Scanner interface so
it can be used as a scan destination:

 var s NullString
 err := db.QueryRow("SELECT name FROM foo WHERE id=?", id).Scan(&s)
 ...
 if s.Valid {
    // use s.String
 } else {
    // NULL value
 }

What I often do is I use my IDE to automatically set all required fields I need to fill in:

Overall, sqlc does look less error-prone than sqlx. You still need to copy over the fields to UserResponse like sqlx to prevent leakage of password field as usual.

squirrel

Writing squirrel feels like writing a hand-rolled query but uses Go methods. The methods reflect their sql counterpart. However, you still need to write magic strings for columns and tables names. Scanning order is also important. There are no generated helper struct to insert the parameters.

func (r repository) Create(ctx context.Context, request *db.CreateUserRequest, hash string) (*userDB, error) {
	var u userDB

	query := r.db.Insert("users").
		Columns("first_name", "middle_name", "last_name", "email", "password", "favourite_colour").
		Values(request.FirstName, request.MiddleName, request.LastName, request.Email, hash, request.FavouriteColour).
		Suffix(`RETURNING "id", "first_name", "middle_name", "last_name", "email", "favourite_colour"`)

	err := query.
		QueryRowContext(ctx).
		Scan(&u.ID, &u.FirstName, &u.MiddleName, &u.LastName, &u.Email, &u.FavouriteColour)
	if err != nil {
		return nil, err
	}

	return &u, nil
}

gorm

In gorm, no sql query needs to be written for a simple operation like this. Filling in the details are the same as sqlc; by using a struct.

func (r *repo) Create(ctx context.Context, u *db.CreateUserRequest, hash string) (*User, error) {
	user := &User{
		FirstName:       u.FirstName,
		MiddleName:      u.MiddleName,
		LastName:        u.LastName,
		Email:           u.Email,
		Password:        hash,
		FavouriteColour: u.FavouriteColour,
	}

	err := r.db.WithContext(ctx).Create(user).Error
	if err != nil {
		return nil, err
	}

	return user, nil
}

Creating using gorm looks similar to sqlx. But chaining the Error is not compulsory, WithContext() too. So it is easy forget to call it.

In Gorm, we need to declare a struct for Gorm to scan into. No special struct tag is needed. Here, only json struct tag is used to do two things; 1. decide the json key for each field and; 2. Do not serialize Password field ( annotated with a - struct tag) and return to user.

This User struct is also the same struct used for gorm’s auto migration. Since Gorm tends to use a single model struct for everything, it is important to keep this password field from leaking to the client. Of course, a good practice is to have separate struct for database and client response so that we can control what the client sees.

type User struct {
	ID         int    `json:"id"`
	FirstName  string `json:"first_name"`
	MiddleName string `json:"middle_name"`
	LastName   string `json:"last_name"`
	Email      string `json:"email"`
	Password   string `json:"-"`
}

Gorm has a feature of adding a gorm.Model field that automates creation of ID,created_at, and updated_at timestamps.

sqlboiler

func (r *database) Create(ctx context.Context, request *db.CreateUserRequest, hash string) (*models.User, error) {
	user := &models.User{
		FirstName: request.FirstName,
		MiddleName: null.String{
			String: request.MiddleName,
			Valid:  request.MiddleName != "",
		},
		LastName: request.LastName,
		Email:    request.Email,
		Password: hash,
		FavouriteColour: null.String{
			String: request.FavouriteColour,
			Valid:  request.FavouriteColour != "",
		},
	}

	return user, user.Insert(ctx, r.db, boil.Infer())
}

Inserting a record also looks similar to sqlc and gorm. Like sqlx, you need to use the user struct (models.User) generated by the library. Like sqlc, assigning middle_name is awkward.

Here the third parameter of Insert() is something you can choose whether you want sqlboiler to infer which column to insert, or manually set them:

https://github.com/volatiletech/sqlboiler#insert

Then, you use Insert() method of the User struct to perform an insertion.

You do not have to worry if sqlboiler does a RETURNING clause or not because it will fill in the ID to the User struct for you.

Since Password field is not annotated with - struct tag, you will need to copy over the fields to UserResponse struct like sqlx above.

ent

Instead of using a struct to set the values, ent uses a builder pattern. The choice to give an empty string or null to middle_name depends on which method you choose.

func (r *database) Create(ctx context.Context, request db.CreateUserRequest, hash string) (*gen.User, error) {
	saved, err := r.db.User.Create().
		SetFirstName(request.FirstName).
		SetNillableMiddleName(&request.MiddleName). // Does not insert anything to this column
		//SetMiddleName(request.MiddleName).        // Inserts empty string
		SetLastName(request.LastName).
		SetEmail(request.Email).
		SetPassword(hash).
		Save(ctx)
	if err != nil {
		return nil, fmt.Errorf("error saving user")
	}

	return saved, nil
}

Ent uses a code-first approach (defines models and relationships using code as opposed to using database as a single source of truth), so before using ent, we need to define the fields and relationships(edges) with a methods.

func (User) Fields() []ent.Field {
	return []ent.Field{
		field.Uint("id"),
		field.String("first_name"),
		field.String("middle_name").Nillable().Optional(),
		field.String("last_name"),
		field.String("email").Unique(),
		field.String("password").Sensitive(),
		field.Enum("favourite_colour").
			Values("red", "green", "blue").
			Default("green"),
		field.Time("updated_at").Default(time.Now()),
	}
}

Notice that password field is made sensitive, which means, the json output will not contain the password. This means, you no longer have to worry about forgetting to copy the values to a new struct just to hide password value.

Like all ent output, there is an additional field called edges added to client response. There isn’t a way to turn off edges key from ent’s output, so you will need to transform to a new response struct before returning to the client.

{
  "id": 6,
  "first_name": "John",
  "middle_name": "",
  "last_name": "Doe",
  "email": "john-doe@example.com",
  "edges": {}
}

If table columns order keeps changing, then both sqlx and squirrel will be a nightmare because scanning order needs to keep track with your database schema. Others do not suffer this issue.

All of them return an updated model with its ID after insertion which is great especially when we want to return that record back to client.

There is an awkward setting value to a nullable column in both sqlc and sqlboiler request.MiddleName) != "" sqlx, squirrel and gorm transparently handles this nil value.

Ent gives an option to set middle_name column to be nullable (SetNillableMiddleName(&request.MiddleName)) or empty string (SetMiddleName(request.MiddleName)). There’s a bit of a learning curve especially to knowing how to define the relationships between the tables.

Next > Golang Database Library ORM Example List