Golang Database Library Orm Example - Create

December 14, 2021

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 a request to a custom ‘request’ struct, and if required, parses query parameter(s). We hash the password and together with UserRequest we try to insert to database. Then the struct is passed down the out data access layer.

type UserRequest 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 simple.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

This is part of a series of blog posts including:

The runnable code is available in the repository https://github.com/gmhafiz/golang-database-library-orm-example.

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.

const Insert = "INSERT INTO users (first_name, middle_name, last_name, email, password) VALUES ($1, $2, $3, $4, $5) RETURNING id, first_name, middle_name, last_name, email, password"
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,
	).Scan(
		&u.ID,
		&u.FirstName,
		&u.MiddleName,
		&u.LastName,
		&u.Email,
		&u.Password,
	)
	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. Thanks to using postgres' RETURNING clause, we do not have to make another query to retrieve the newly inserted record.

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. 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 uses reflection to know which database column maps to which struct field by looking at the struct tag.

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"`
}

Note that we are returning userDB type that has a password field. We should not expose this field to the client. So you will need to copy over the values to a new struct (UserResponse) that does not contain thatpassword 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)
VALUES ($1, $2, $3, $4, $5)
RETURNING *;

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

$ sqlc generate

And the generated code are created in query.sql.go. To use:

func (r *database) Create(ctx context.Context, request sqlx2.UserRequest, hash string) (*User, error) {
	user, err := r.db.CreateUser(ctx, CreateUserParams{
		FirstName: request.FirstName,
		MiddleName: sql.NullString{
			String: request.MiddleName,
			Valid:  true,
		},
		LastName: request.LastName,
		Email:    request.Email,
		Password: hash,
	})
	if err != nil {
		return nil, err
	}

	return &user, nil
}

Notice how the order no longer matters because we are filling in the parameters in a struct.

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 says that

database/sql/sql.go

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.

gorm

In gorm, no sql query need 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 *sqlx.UserRequest, hash string) (*User, error) {
	user := &User{
		FirstName:  u.FirstName,
		MiddleName: u.MiddleName,
		LastName:   u.LastName,
		Email:      u.Email,
		Password:   hash,
	}

	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 the Error is not compulsory, WithContext() too. So it is easy forget to call it.

The User struct is the same struct used for gorm’s auto migration.

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.

Password field is annotated with a - struct tag. Since Gorm tends to use a single model struct for everything, it is important to keep this password field from leaking to the client.

sqlboiler

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

	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.

Here the third parameter 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

Column ListBehavior
InferInfer the column list using “smart” rules
WhitelistInsert only the columns specified in this list
BlacklistInfer the column list, but ensure these columns are not inserted
GreylistInfer the column list, but ensure these columns are inserted

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 value, ent uses builder pattern.

func (r *database) Create(ctx context.Context, request sqlx.UserRequest, hash string) (*gen.User, error) {
	saved, err := r.db.User.Create().
		SetFirstName(request.FirstName).
		SetNillableMiddleName(&request.MiddleName).
		SetLastName(request.LastName).
		SetEmail(request.Email).
		SetPassword(hash).
		Save(ctx)
	if err != nil {
		return nil, fmt.Errorf("error saving user")
	}

	return saved, nil
}

The schema is generated by defining the field and relationships(edges) with a method

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(),
	}
}

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! There’s a bit of a learning curve especially to knowing how to define the relationships between the tables.

Like all ent output, there is an additional field called edges that will show if there are any related model at all.

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

All in all, except ent which is the easiest, there are not many differences. Some are a bit easier to use than others.

Next > Golang Database Library ORM Example List