Write a CRUD SQL app

A common REST API structure provides methods for users to create, read, update, and delete (CRUD) resources. In this tutorial, you'll:

Create an HTTP server that provides these CRUD methods
Implement logic that interfaces with a MySQL backend

Specifically, users of your service will be able create, read, update, and delete todos, such as you might use for a "To do list".

Before you begin

While this tutorial requires no prior code, it is an intermediate gosoline tutorial and, therefore, skips some explanations. To understand the logic in this tutorial, you should have already completed at least one of the following tutorials first:

This tutorial also assumes you have some basic knowledge about relational databases, such as:

Data models
Primary keys
Migrations

Before you begin, make sure you have the following installed on your machine:

Set up your file structure

First, you need to set up the following file structure:

crud/
├── migrations
│   └── 1_initialize_schema.up.sql
├── docker-compose.yml
├── handler.go
├── config.dist.yml
└── main.go

For example, in Unix, run:

mkdir crud; cd crud
mkdir migrations
touch migrations/1_initialize_schema.up.sql
touch docker-compose.yml
touch handler.go
touch config.dist.yml
touch main.go

Those are all the files you need to build your CRUD service with gosoline! Next, you'll implement each of these files, starting with handler.go.

Implement handler.go

In handler.go, add the following code:

handler.go

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/justtrackio/gosoline/pkg/cfg"
	"github.com/justtrackio/gosoline/pkg/db-repo"
	"github.com/justtrackio/gosoline/pkg/httpserver/crud"
	"github.com/justtrackio/gosoline/pkg/log"
	"github.com/justtrackio/gosoline/pkg/mdl"
)


var settings = db_repo.Settings{
	Metadata: db_repo.Metadata{
		ModelId: mdl.ModelId{
			Name: "todo",
		},
		TableName:  "todos",
		PrimaryKey: "todos.id",
	},
}


type Todo struct {
	db_repo.Model
	Text    string
	DueDate time.Time
}


type CreateInput struct {
	Text    string    `form:"text"`
	DueDate time.Time `form:"dueDate"`
}

type UpdateInput struct {
	Text string `form:"text"`
}


type TodoCrudHandlerV0 struct {
	repo db_repo.Repository
}


func NewTodoCrudHandler(ctx context.Context, config cfg.Config, logger log.Logger) (*TodoCrudHandlerV0, error) {
	// Declare `error` and `repo` variables.
	var err error
	var repo db_repo.Repository

	// Try to create a new `Repository` given a configuration, a logger, and settings. If there is an error, you return it.
	if repo, err = db_repo.New(ctx, config, logger, settings); err != nil {
		return nil, fmt.Errorf("can not create db_repo.Repositorys: %w", err)
	}

	// Create a new `TodoCrudHandlerV0` with that repo.
	handler := &TodoCrudHandlerV0{
		repo: repo,
	}

	// Return the handler.
	return handler, nil
}


func (h TodoCrudHandlerV0) GetRepository() crud.Repository {
	return h.repo
}


func (h TodoCrudHandlerV0) GetModel() db_repo.ModelBased {
	return &Todo{}
}


func (h TodoCrudHandlerV0) GetCreateInput() interface{} {
	return &CreateInput{}
}


func (h TodoCrudHandlerV0) TransformCreate(ctx context.Context, input interface{}, model db_repo.ModelBased) error {
	in := input.(*CreateInput)
	m := model.(*Todo)

	m.Text = in.Text
	m.DueDate = in.DueDate

	return nil
}


func (h TodoCrudHandlerV0) GetUpdateInput() interface{} {
	return &UpdateInput{}
}


func (h TodoCrudHandlerV0) TransformUpdate(ctx context.Context, input interface{}, model db_repo.ModelBased) error {
	in := input.(*UpdateInput)
	m := model.(*Todo)

	m.Text = in.Text

	return nil
}


func (h TodoCrudHandlerV0) TransformOutput(ctx context.Context, model db_repo.ModelBased, apiView string) (interface{}, error) {
	return model, nil
}


func (h TodoCrudHandlerV0) List(ctx context.Context, qb *db_repo.QueryBuilder, apiView string) (interface{}, error) {
	var err error

	// Instatiate a list of Todo objects, called result.
	result := make([]*Todo, 0)

	// Query the database using a Context and a QueryBuilder. If it finds the results, it sets them on result. Otherwise, it returns an error.
	if err = h.repo.Query(ctx, qb, &result); err != nil {
		return nil, fmt.Errorf("can not query todo items: %w", err)
	}

	// Transform each result with TransformOutput().
	out := make([]interface{}, len(result))
	for i, res := range result {
		if out[i], err = h.TransformOutput(ctx, res, apiView); err != nil {
			return nil, err
		}
	}

	// Return the transformed results.
	return out, nil
}

Now, you'll walkthrough this file in detail to learn how it works.

Import dependencies

At the top of handler.go, you declared the package and imported some dependencies:

handler.go
package main

import (
	"context"
	"fmt"
	"time"

	"github.com/justtrackio/gosoline/pkg/cfg"
	"github.com/justtrackio/gosoline/pkg/db-repo"
	"github.com/justtrackio/gosoline/pkg/httpserver/crud"
	"github.com/justtrackio/gosoline/pkg/log"
	"github.com/justtrackio/gosoline/pkg/mdl"
)

Here, you declared the package as main. Then, you imported the context, fmt, and time modules along with several gosoline dependencies.

Define your database settings

In this tutorial, you use a MySQL database to manage todo records. So, you first defined the settings for the data:

handler.go
var settings = db_repo.Settings{
	Metadata: db_repo.Metadata{
		ModelId: mdl.ModelId{
			Name: "todo",
		},
		TableName:  "todos",
		PrimaryKey: "todos.id",
	},
}

These settings include a model name, table name, and primary key.

Define your Todo struct

Next, you defined a structure to model your todos:

handler.go
type Todo struct {
	db_repo.Model
	Text    string
	DueDate time.Time
}

The Todo has three values:

A database model
Some text
A due date for the todo

Define your input structs

Next, you defined two structures for handling request input:

handler.go
type CreateInput struct {
	Text    string    `form:"text"`
	DueDate time.Time `form:"dueDate"`
}

type UpdateInput struct {
	Text string `form:"text"`
}

When you create a todo, you'll pass the todo's text and a due date. When you update a todo, you'll pass only the text. Here, you defined the structures for parsing and storing that information.

Define your `TodoCrudHandler`

Next, you defined a handler for CRUD requests:

handler.go
type TodoCrudHandlerV0 struct {
	repo db_repo.Repository
}

Here, the handler has a repo, which implements the Repository interface. This repo implements model-based methods for creating, reading, updating, and deleting resources.

The "read" method is a special case for repo because it implements multiple handlers for reading information:

Read()
Query()
Count()
GetModelId()
GetModelName()
GetMetadata()

info

The V0 in TodoCrudHandlerV0 sets up a structure for you to version this handler later.

Later, you implement the methods for this struct.

Implement a constructor

Next, you implemented a constructor for your TodoCrudHandlerV0:

handler.go
func NewTodoCrudHandler(ctx context.Context, config cfg.Config, logger log.Logger) (*TodoCrudHandlerV0, error) {
	// Declare `error` and `repo` variables.
	var err error
	var repo db_repo.Repository

	// Try to create a new `Repository` given a configuration, a logger, and settings. If there is an error, you return it.
	if repo, err = db_repo.New(ctx, config, logger, settings); err != nil {
		return nil, fmt.Errorf("can not create db_repo.Repositorys: %w", err)
	}

	// Create a new `TodoCrudHandlerV0` with that repo.
	handler := &TodoCrudHandlerV0{
		repo: repo,
	}

	// Return the handler.
	return handler, nil
}

Implement the methods for your handler

Finally, you implemented the following methods for your TodoCrudHandlerV0:

handler.go
func (h TodoCrudHandlerV0) GetRepository() crud.Repository {
	return h.repo
}

This returns the handler's repository object.

handler.go
func (h TodoCrudHandlerV0) GetModel() db_repo.ModelBased {
	return &Todo{}
}

This returns the Todo model.

handler.go
func (h TodoCrudHandlerV0) GetCreateInput() interface{} {
	return &CreateInput{}
}

This returns the CreateInput struct.

handler.go
func (h TodoCrudHandlerV0) TransformCreate(ctx context.Context, input interface{}, model db_repo.ModelBased) error {
	in := input.(*CreateInput)
	m := model.(*Todo)

	m.Text = in.Text
	m.DueDate = in.DueDate

	return nil
}

This parses the Text and DueDate from a CreateInput object and sets them on a Todo model. This is used for Create requests.

handler.go
func (h TodoCrudHandlerV0) GetUpdateInput() interface{} {
	return &UpdateInput{}
}

This returns the UpdateInput struct.

handler.go
func (h TodoCrudHandlerV0) TransformUpdate(ctx context.Context, input interface{}, model db_repo.ModelBased) error {
	in := input.(*UpdateInput)
	m := model.(*Todo)

	m.Text = in.Text

	return nil
}

This parses the Text from an UpdateInput object and sets them on a Todo model. This is used for Update requests.

handler.go
func (h TodoCrudHandlerV0) TransformOutput(ctx context.Context, model db_repo.ModelBased, apiView string) (interface{}, error) {
	return model, nil
}

This simply returns the model with no transformations.

handler.go
func (h TodoCrudHandlerV0) List(ctx context.Context, qb *db_repo.QueryBuilder, apiView string) (interface{}, error) {
	var err error

	// Instatiate a list of Todo objects, called result.
	result := make([]*Todo, 0)

	// Query the database using a Context and a QueryBuilder. If it finds the results, it sets them on result. Otherwise, it returns an error.
	if err = h.repo.Query(ctx, qb, &result); err != nil {
		return nil, fmt.Errorf("can not query todo items: %w", err)
	}

	// Transform each result with TransformOutput().
	out := make([]interface{}, len(result))
	for i, res := range result {
		if out[i], err = h.TransformOutput(ctx, res, apiView); err != nil {
			return nil, err
		}
	}

	// Return the transformed results.
	return out, nil
}

Now, you've implemented the handlers and logic for a model-based CRUD API. Next, you'll implement a main function that applies this logic.

Implement main.go

In main.go, add the following code:

main.go

package main

import (
	"context"
	"fmt"

	"github.com/justtrackio/gosoline/pkg/application"
	"github.com/justtrackio/gosoline/pkg/cfg"
	"github.com/justtrackio/gosoline/pkg/httpserver"
	"github.com/justtrackio/gosoline/pkg/httpserver/crud"
	"github.com/justtrackio/gosoline/pkg/log"
)


func main() {
	definer := func(ctx context.Context, config cfg.Config, logger log.Logger) (*httpserver.Definitions, error) {
		// Instantiated a new Definitions object.
		def := &httpserver.Definitions{}

		var err error
		var handler crud.Handler

		// Created a new CRUD handler.
		if handler, err = NewTodoCrudHandler(ctx, config, logger); err != nil {
			return nil, fmt.Errorf("can not create trip handler: %w", err)
		}

		// Add CRUD handlers to your definitions. This is a convenience method for adding handlers for Create, Read, Update, Delete, and List.
		crud.AddCrudHandlers(logger, def, 0, "todo", handler)

		return def, nil
	}

	// Run your server with the definitions.
	application.RunHttpDefaultServer(definer)
}

Now, you'll walkthrough this file in detail to learn how it works.

Import dependencies

At the top of main.go, you declared the package and imported some dependencies:

main.go
package main

import (
	"context"
	"fmt"

	"github.com/justtrackio/gosoline/pkg/application"
	"github.com/justtrackio/gosoline/pkg/cfg"
	"github.com/justtrackio/gosoline/pkg/httpserver"
	"github.com/justtrackio/gosoline/pkg/httpserver/crud"
	"github.com/justtrackio/gosoline/pkg/log"
)

Here, you declared the package as main. Then, you imported the context and fmt modules along with several gosoline dependencies.

Implement main()

Then, you implemented main():

main.go
func main() {
	definer := func(ctx context.Context, config cfg.Config, logger log.Logger) (*httpserver.Definitions, error) {
		// Instantiated a new Definitions object.
		def := &httpserver.Definitions{}

		var err error
		var handler crud.Handler

		// Created a new CRUD handler.
		if handler, err = NewTodoCrudHandler(ctx, config, logger); err != nil {
			return nil, fmt.Errorf("can not create trip handler: %w", err)
		}

		// Add CRUD handlers to your definitions. This is a convenience method for adding handlers for Create, Read, Update, Delete, and List.
		crud.AddCrudHandlers(logger, def, 0, "todo", handler)

		return def, nil
	}

	// Run your server with the definitions.
	application.RunHttpDefaultServer(definer)
}

Now that you have all your logic, you'll move on to setting up your server and docker container.

Configure your server

In config.dist.yml, configure your server:

config.dist.yml
env: dev
app_project: gosoline
app_family: how-to
app_group: crud
app_name: server

db:
  default:
    driver: mysql
    parse_time: true
    uri:
      host: 127.0.0.1
      port: 3306
      user: gosoline
      password: gosoline
      database: gosoline
    migrations:
      enabled: true
      table_prefixed: false
      path: migrations

Here, you set some minimal configurations for your web server. You also configured your MySQL database, including the migrations you'll run to build a table for your todos. This migration will live in the migrations directory. You'll add this next.

Add an initial migration

In migrations/1_initialize_schema.up.sql, add a migration that creates a todos table:

migrations/1_initialize_schema.up.sql
-- +goose Up
-- +goose StatementBegin
create table todos
(
    id         int       auto_increment primary key,
    text       text      not null,
    due_date   timestamp not null,
    updated_at timestamp not null,
    created_at timestamp not null
);
-- +goose StatementEnd

Here, you created a table, called todos, with five columns:

id: The todo's identifier.
text: The todo's text.
due_date: The todo's due date.
updated_at: The last time the todo was updated.
created_at: The time the todo was created.

Configure docker-compose

Next, in docker-compose.yml, add the following configurations:

docker-compose.yml
version: "3.6"

services:
  mysql:
    image: mysql:8.0.31
    command: --sql_mode="NO_ENGINE_SUBSTITUTION" --log-bin-trust-function-creators=TRUE
    ports:
      - 3306:3306
    environment:
      - MYSQL_DATABASE=gosoline
      - MYSQL_USER=gosoline
      - MYSQL_PASSWORD=gosoline
      - MYSQL_ROOT_PASSWORD=gosoline

This sets up docker-compose to create a container running a MySQL server.

Test your work

Now it's time to test your work and make sure everything you created works properly.

Start MySQL

First, start your MySQL container:

docker-compose up

Now, you have a MySQL database running in a container. You can see it running on port 3306 with docker ps in another shell:

CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                               NAMES
ccf507fd70e4   mysql:8.0.31   "docker-entrypoint.s…"   10 minutes ago   Up 10 minutes   0.0.0.0:3306->3306/tcp, 33060/tcp   write-crud-sql-app-mysql-1

Run your server

In another shell, navigate to the root crud directory of this project and spin up your server:

go mod init crud/m
go mod tidy
go run .

You'll see logs of your server running.

Make requests

Finally, in a third shell, make requests to your service. For example, create a todo:

curl -d '{"text":"do it!", "dueDate":"2023-09-08T15:00:00Z"}' -H "Content-Type: application/json" -X POST localhost:8080/v0/todo

View the todo:

curl -X GET localhost:8080/v0/todo/1

Update the todo:

curl -d '{"text":"do it!!!"}' -H "Content-Type: application/json" -X PUT localhost:8080/v0/todo/1

View the todo again to observe the changes:

curl -X GET localhost:8080/v0/todo/1

List all todos:

curl -d '{}' -X POST localhost:8080/v0/todos

Delete a todo:

curl -X DELETE localhost:8080/v0/todo/1

Check the database

To verify that everything is working as you expected on the backend, check the database.

First, look up your database container id:

$ docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                               NAMES
ccf507fd70e4   mysql:8.0.31   "docker-entrypoint.s…"   19 minutes ago   Up 19 minutes   0.0.0.0:3306->3306/tcp, 33060/tcp   write-crud-sql-app-mysql-1

In this example, the container id is ccf507fd70e4, but it'll be different for you.

Next, log into mysql in the container:

docker exec -it ccf507fd70e4 mysql -u root -p

There are two things to note:

Use your MySQL container id here instead of ccf507fd70e4
Use the password gosoline, as you previously defined in docker-compose.yml

From the mysql console, change to the gosoline database:

mysql> use gosoline
Database changed

Finally, query the todos table to see your records:

mysql> select * from todos;
+----+----------+---------------------+---------------------+---------------------+
| id | text     | due_date            | updated_at          | created_at          |
+----+----------+---------------------+---------------------+---------------------+
|  1 | do it!!! | 2023-09-08 15:00:00 | 2023-09-12 09:35:22 | 2023-09-12 09:35:12 |
+----+----------+---------------------+---------------------+---------------------+

If you have no records, you'll need to create one:

curl -d '{"text":"do it!", "dueDate":"2023-09-08T15:00:00Z"}' -H "Content-Type: application/json" -X POST localhost:8080/v0/todo

Conclusion

And you're done! You've used gosoline to create a fully-fledged CRUD API service, complete with endpoints for:

Create
Read
Update
Delete
List

You also used docker to host a MySQL database and used a Todo model to handle API inputs and outputs as well as to store data in the table.

Before you begin​

Set up your file structure​

Implement handler.go​

Import dependencies​

Define your database settings​

Define your Todo struct​

Define your input structs​

Define your TodoCrudHandler​

Implement a constructor​

Implement the methods for your handler​

Implement main.go​

Import dependencies​

Implement main()​

Configure your server​

Add an initial migration​

Configure docker-compose​

Test your work​

Start MySQL​

Run your server​

Make requests​

Check the database​

Conclusion​

Before you begin

Set up your file structure

Implement handler.go

Import dependencies

Define your database settings

Define your Todo struct

Define your input structs

Define your `TodoCrudHandler`

Implement a constructor

Implement the methods for your handler

Implement main.go

Import dependencies

Implement main()

Configure your server

Add an initial migration

Configure docker-compose

Test your work

Start MySQL

Run your server

Make requests

Check the database

Conclusion