Database Integration with GORM

In this tutorial, we'll learn how to implement a CRUD API with database integration using the Gonyx framework's contrib/gormkit package which wraps around the GORM library.

What You'll Learn

How to set up database models with GORM in a Gonyx application
How to create database operations for CRUD functionality
How to implement controllers that interact with database models
How to initialize database connections in your application

Prerequisites

Before starting this tutorial, make sure you've:

Completed the Quick Start tutorial
Basic understanding of Go programming
Familiarity with relational databases
Go 1.23.0 or later installed

Project Setup

We'll build on the project we created in the Quick Start tutorial. If you haven't completed that tutorial, please do so first.

tip

First, create a new Gonyx project using the CLI:

gonyx init gonyx-user-db --path .
cd gonyx-user-db

Step 1: Configure Database Connection

First, we need to configure the database connection in our application. We'll need to update two configuration files:

First, add the database module to your base configuration:

configs/dev/base.json
{
  "name": "gonyx-user-db",
  "config_must_watched": true,
  "config_remote_addr": "0.0.0.0:7777",
  "config_remote_infra": "grpc",
  "config_remote_duration": 300,
  "modules": [
    {"name":"logger", "type": "local"},
    {"name":"http", "type": "local"},
    {"name":"protobuf", "type": "local"},
    {"name":"db", "type": "local"}
  ]
}

Then create a database configuration file. Gonyx supports multiple database types and connections. Here's a comprehensive sample configuration:

configs/dev/db.json
{
  "connections": ["server1"],
  "server1": {
    "type": "postgresql",
    "db": "gonyx_demo",
    "username": "postgres",
    "password": "postgres",
    "host": "localhost",
    "port": "5432",
    "options": {
      "sslmode": "disable",
      "TimeZone": "Asia/Tehran"
    },
    "specific_config": {
      "prefer_simple_protocol": true,
      "without_returning": false
    },
    "config": {
      "skip_default_transaction": false,
      "prepare_stmt": true,
      "disable_automatic_ping": false
    },
    "logger": {
      "slow_threshold": 1000,
      "log_level": "debug"
    }
  }
}

Understanding the Database Configuration

The Gonyx database configuration has several important sections:

connections: An array listing which server configurations should be enabled. In our example, we're only using server1.
Database Server Configuration:
- Each server (like server1) has its own configuration block
- type: The database type (postgresql, mysql, sqlite, etc.)
- Connection details (db, username, password, host, port)
- options: Database-specific connection parameters
GORM Configuration:
- specific_config: Database-specific GORM settings
- config: General GORM settings
- logger: Configuration for the GORM logger

Gonyx supports multiple database types:

SQLite

{
  "server1": {
    "type": "sqlite",
    "db": "file.db",
    "options": {
      "mode": "memory",
      "cache": "shared",
      "_fk": "1"
    }
  }
}

MySQL

{
   "server2": {
      "type": "mysql",
      "db": "databasename",
      "username": "username",
      "password": "password",
      "host": "127.0.0.1",
      "port": "3306",
      "protocol": "tcp",
      "options": {
         "charset": "utf8mb4",
         "parseTime": "True",
         "loc": "Local"
      }
   }
}

For our tutorial, we'll keep the configuration simple with a PostgreSQL database. Make sure to adjust the connection details to match your database credentials.

Step 2: Create the Database Model

First, let's create a new file called model.go in the app directory to define our database model:

app/model.go
/*
Create By Gonyx Framework

Copyright © 2025
Project: gonyx-user-db
File: "app/model.go"
------------------------------
*/

package app

import (
	"github.com/Blocktunium/gonyx/contrib/gormkit"
	"gorm.io/gorm"
)

const (
	SqlDbServerName = "server1"
)

// User represents a user entity in our system
type User struct {
	gorm.Model
	Name  string `json:"name" binding:"required" gorm:"not null"`
	Email string `json:"email" binding:"required,email" gorm:"uniqueIndex;not null"`
}

// InitializeDatabaseModels prepares the database schema and migrations for all models
func InitializeDatabaseModels() error {
	// Migrate the User model
	if err := gormkit.GetManager().Migrate(SqlDbServerName, &User{}); err != nil {
		return err
	}

	return nil
}

Let's break down the key components of this model:

Model Definition

We define a User struct that embeds gorm.Model, which automatically includes ID, CreatedAt, UpdatedAt, and DeletedAt fields. We also add:

Name: The user's name with validation for required field
Email: The user's email with validation for required field and email format, plus a unique index constraint

Database Initialization

The InitializeDatabaseModels function:

Gets a database connection from the gormkit manager
Calls Migrate() to automatically create the database table based on our User struct

The gorm.Model embedding gives us these fields automatically:

ID uint: Primary key
CreatedAt time.Time: Record creation time
UpdatedAt time.Time: Record last update time
DeletedAt gorm.DeletedAt: For soft delete support

Step 3: Implementing CRUD Operations

Now that we have our data model in place, let's implement each CRUD operation by creating both the database functions and the corresponding API endpoints.

First, add this import to your model.go file:

import (
	...
	"errors"
)

Let's create a basic controller structure in controller.go before implementing each feature:

app/controller.go (Basic Structure)
// UserController - a controller that handles user CRUD operations
type UserController struct {}

// NewUserController creates a new instance of UserController
func NewUserController() *UserController {
	return &UserController{}
}

// GetName - return the name of the controller to be used as part of the route
func (ctrl *UserController) GetName() string { return "Users" }

// Routes - returning controller specific routes to be registered
func (ctrl *UserController) Routes() []gonyxhttp.HttpRoute {
	return []gonyxhttp.HttpRoute{
		// We'll add routes for each CRUD operation here
	}
}

Now, let's implement each feature one by one:

3.1 Implementing "List All Users" (GET /)

Model Function

app/model.go (UserFindAll)
// UserFindAll retrieves all users
func UserFindAll() (*[]User, error) {
	// Get the GORM DB connection from gormkit using our database server name constant
	db, err := gormkit.GetManager().GetDb(SqlDbServerName)
	if err != nil {
		// If we can't get a database connection, return the error immediately
		return nil, err
	}

	// Create an empty slice to hold all the user records
	var users []User
	// Execute a SELECT query to get all records from the users table
	// The query is automatically constructed by GORM based on the User struct
	result := db.Find(&users)

	// Check if the query encountered any errors
	if result.Error != nil {
		// Create a detailed error that includes the SQL statement for easier debugging
		return nil, gormkit.NewSelectQueryErr(result.Statement.SQL.String(), result.Error)
	}

	// Return a pointer to the slice of users
	// Using a pointer avoids unnecessary copying of the data
	return &users, nil
}

Controller Function

app/controller.go (GetAllUsers)
// GetAllUsers retrieves all users
func (ctrl *UserController) GetAllUsers(c *gin.Context) {
	users, err := UserFindAll()
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	c.JSON(http.StatusOK, users)
}

Route

Add this route to your Routes() function in UserController:

{
	Method:    gonyxhttp.MethodGet,
	Path:      "/",
	RouteName: "getAllUsers",
	F:         ctrl.GetAllUsers,
},

3.2 Implementing "Get User by ID" (GET /:id)

Model Function

app/model.go (UserFindByID)
// UserFindByID retrieves a user by ID
func UserFindByID(id int64) (*User, error) {
	// Get the GORM DB connection from gormkit
	db, err := gormkit.GetManager().GetDb(SqlDbServerName)
	if err != nil {
		return nil, err
	}

	// Create a user variable to hold the result
	var user User
	// Use a WHERE clause with a parameterized query to find by primary key
	// The ? placeholder prevents SQL injection attacks
	// First() will return ErrRecordNotFound if no record is found
	result := db.Where("id = ?", id).First(&user)

	// Check for errors including record not found
	if result.Error != nil {
		// Create a detailed error with the query for debugging
		return nil, gormkit.NewSelectQueryErr(result.Statement.SQL.String(), result.Error)
	}

	// Return a pointer to the found user
	return &user, nil
}

Controller Function

app/controller.go (GetUserByID)
// GetUserByID retrieves a user by ID
func (ctrl *UserController) GetUserByID(c *gin.Context) {
	idParam := c.Param("id")
	id, err := strconv.ParseInt(idParam, 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid user ID"})
		return
	}

	user, err := UserFindByID(id)
	if err != nil {
		status := http.StatusInternalServerError
		if err.Error() == "user not found" {
			status = http.StatusNotFound
		}
		c.JSON(status, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, user)
}

Route

Add this route to your Routes() function:

{
	Method:    gonyxhttp.MethodGet,
	Path:      "/:id",
	RouteName: "getUserById",
	F:         ctrl.GetUserByID,
},

3.3 Implementing "Create User" (POST /)

Model Function

app/model.go (UserCreate)
// UserCreate inserts a new user into the database
func UserCreate(user *User) (*User, error) {
	// Get the GORM DB connection from gormkit
	db, err := gormkit.GetManager().GetDb(SqlDbServerName)
	if err != nil {
		return nil, err
	}

	// Use GORM's Create method to insert the new user
	// This will automatically:
	// 1. Generate a new ID if not provided
	// 2. Fill in CreatedAt and UpdatedAt timestamps
	// 3. Run any defined hooks or validations
	result := db.Create(user)

	// Check if the insertion encountered any errors
	// Common errors include validation failures or constraint violations
	if result.Error != nil {
		return nil, result.Error
	}

	// Return the created user with its new ID and timestamps
	return user, nil
}

Controller Function

app/controller.go (CreateUser)
// CreateUser creates a new user
func (ctrl *UserController) CreateUser(c *gin.Context) {
	var user User
	if err := c.ShouldBindJSON(&user); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	createdUser, err := UserCreate(&user)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusCreated, createdUser)
}

Route

Add this route to your Routes() function:

{
	Method:    gonyxhttp.MethodPost,
	Path:      "/",
	RouteName: "createUser",
	F:         ctrl.CreateUser,
},

3.4 Implementing "Update User" (PUT /:id)

Model Function

app/model.go (UserUpdate)
// UserUpdate updates a user's information
func UserUpdate(user *User) error {
	// Get the GORM DB connection from gormkit
	db, err := gormkit.GetManager().GetDb(SqlDbServerName)
	if err != nil {
		return err
	}

	// Use GORM's Save method to update the entire record
	// This automatically updates the UpdatedAt timestamp
	// Save() will update all fields, not just the changed ones
	result := db.Save(user)

	// Check for errors during the update operation
	if result.Error != nil {	
		// Create a detailed error message with the entity and ID for easier debugging
		return gormkit.NewUpdateModelErr("User", map[string]any{"id": user.ID}, result.Error)
	}

	// If no rows were affected, the user doesn't exist in the database
	if result.RowsAffected == 0 {
		return gormkit.NewUpdateModelErr("User", map[string]any{"id": user.ID}, errors.New("user not found"))
	}

	return nil
}

Controller Function

app/controller.go (UpdateUser)
// UpdateUser updates an existing user
func (ctrl *UserController) UpdateUser(c *gin.Context) {
	idParam := c.Param("id")
	id, err := strconv.ParseInt(idParam, 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid user ID"})
		return
	}

	// Get the existing user first
	existingUser, err := UserFindByID(id)
	if err != nil {
		status := http.StatusInternalServerError
		c.JSON(status, gin.H{"error": err.Error()})
		return
	}

	// Bind the updated data
	var updatedData User
	if err := c.ShouldBindJSON(&updatedData); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	// Update fields
	existingUser.Name = updatedData.Name
	existingUser.Email = updatedData.Email

	if err := UserUpdate(existingUser); err != nil {
		status := http.StatusInternalServerError
		c.JSON(status, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, existingUser)
}

Route

Add this route to your Routes() function:

{
	Method:    gonyxhttp.MethodPut,
	Path:      "/:id",
	RouteName: "updateUser",
	F:         ctrl.UpdateUser,
},

3.5 Implementing "Delete User" (DELETE /:id)

Model Function

app/model.go (UserDelete)
// UserDelete removes a user from the database
func UserDelete(id int64) error {
	// Get the GORM DB connection from gormkit
	db, err := gormkit.GetManager().GetDb(SqlDbServerName)
	if err != nil {
		return err
	}

	// Use a parameterized query with Where to find the record first
	// Then apply Delete on the User model
	// Since our User struct embeds gorm.Model, this performs a soft delete
	// by setting the DeletedAt field rather than actually removing the record
	result := db.Where("id = ?", id).Delete(&User{})

	// Check for any errors during the delete operation
	if result.Error != nil {
		// Create a detailed error with context for easier debugging
		return gormkit.NewDeleteModelErr("User", map[string]any{"id": id}, result.Error)
	}

	// The record was successfully marked as deleted (soft delete)
	return nil
}

Controller Function

app/controller.go (DeleteUser)
// DeleteUser deletes a user
func (ctrl *UserController) DeleteUser(c *gin.Context) {
	idParam := c.Param("id")
	id, err := strconv.ParseInt(idParam, 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid user ID"})
		return
	}

	if err := UserDelete(id); err != nil {
		status := http.StatusInternalServerError
		c.JSON(status, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, gin.H{"message": "User deleted successfully"})
}

Route

Add this route to your Routes() function:

{
	Method:    gonyxhttp.MethodDelete,
	Path:      "/:id",
	RouteName: "deleteUser",
	F:         ctrl.DeleteUser,
},

Step 4: Update the App Entry Point

With our CRUD operations fully implemented, we need to update the app entry point to initialize our database schema and register our controller. Update your app.go file:

app/app.go
func (app *App) Init() {
    // Initialize the database models and schema
    if err := InitializeDatabaseModels(); err != nil {
        panic(err)
    }
    
    // Register the user controller for CRUD operations
    userController := NewUserController()
    engine.RegisterRestfulController(userController)

    // Register other controllers...
}

This initializes the database schema and registers our UserController with the Gonyx engine.

What Changed from the Default Code

When comparing to the default code generated by the Gonyx framework, we made several changes:

Added New File:
- Created the entire model.go file for database operations
Modified Existing Files:
- Updated controller.go to add the UserController
- Updated app.go to initialize database models and register our controller
Removed Code:
- Removed placeholder code from the Quick Start tutorial

Running and Testing the Application

To test the application, start your Gonyx server:

gonyx runserver

Then use a tool like cURL or Postman to interact with the API endpoints:

Create a User

curl -X POST http://localhost:8080/api/Users/ \
  -H "Content-Type: application/json" \
  -d '{"name":"John Doe","email":"john@example.com"}'

Expected Response:

{
  "name": "John Doe",
  "email": "john@example.com"
}

Get All Users

curl -X GET http://localhost:8080/api/Users/

Expected Response:

[
  {
    "id": 1,
    "name": "John Doe",
    "email": "john@example.com",
    "created_at": "2025-04-03T15:20:30.123456Z",
    "updated_at": "2025-04-03T15:20:30.123456Z"
  }
]

Get User by ID

curl -X GET http://localhost:8080/api/Users/1

Expected Response:

{
  "id": 1,
  "name": "John Doe",
  "email": "john@example.com",
  "created_at": "2025-04-03T15:20:30.123456Z",
  "updated_at": "2025-04-03T15:20:30.123456Z"
}

Update a User

curl -X PUT http://localhost:8080/api/Users/1 \
  -H "Content-Type: application/json" \
  -d '{"name":"Jane Pi"}'

Expected Response:

{
  "id": 1,
  "name": "Jane Pi",
  "email": "jane@example.com",
  "created_at": "2025-04-03T15:20:30.123456Z",
  "updated_at": "2025-04-03T15:25:40.789012Z"
}

Delete a User

curl -X DELETE http://localhost:8080/api/Users/1

Expected Response:

{
  "message": "User deleted successfully"
}

Conclusion

In this tutorial, you learned how to:

Configure a database connection in a Gonyx application
Create database models with GORM
Implement CRUD operations using the gormkit package
Build a RESTful API that interacts with a database
Initialize database models at application startup

The contrib/gormkit package makes it easy to integrate GORM into your Gonyx applications, providing a clean and efficient way to work with relational databases.

Next Steps

Now that you've created a basic CRUD API with database integration, you might want to explore:

Adding validation to your models
Implementing pagination for list endpoints
Creating relationships between multiple models
Adding authentication and authorization

Additional Resources

GORM Documentation - For more advanced database operations
Gonyx Framework Documentation - For more information about the Gonyx framework

What You'll Learn​

Prerequisites​

Project Setup​

Step 1: Configure Database Connection​

Understanding the Database Configuration​

SQLite​

MySQL​

Step 2: Create the Database Model​

Model Definition​

Database Initialization​

Step 3: Implementing CRUD Operations​

3.1 Implementing "List All Users" (GET /)​

Model Function​

Controller Function​

Route​

3.2 Implementing "Get User by ID" (GET /:id)​

Model Function​

Controller Function​

Route​

3.3 Implementing "Create User" (POST /)​

Model Function​

Controller Function​

Route​

3.4 Implementing "Update User" (PUT /:id)​

Model Function​

Controller Function​

Route​

3.5 Implementing "Delete User" (DELETE /:id)​

Model Function​

Controller Function​

Route​

Step 4: Update the App Entry Point​

What Changed from the Default Code​

Running and Testing the Application​

Create a User​

Get All Users​

Get User by ID​

Update a User​

Delete a User​

Conclusion​

Next Steps​

Additional Resources​

What You'll Learn

Prerequisites

Project Setup

Step 1: Configure Database Connection

Understanding the Database Configuration

SQLite

MySQL

Step 2: Create the Database Model

Model Definition

Database Initialization

Step 3: Implementing CRUD Operations

3.1 Implementing "List All Users" (GET /)

Model Function

Controller Function

Route

3.2 Implementing "Get User by ID" (GET /:id)

Model Function

Controller Function

Route

3.3 Implementing "Create User" (POST /)

Model Function

Controller Function

Route

3.4 Implementing "Update User" (PUT /:id)

Model Function

Controller Function

Route

3.5 Implementing "Delete User" (DELETE /:id)

Model Function

Controller Function

Route

Step 4: Update the App Entry Point

What Changed from the Default Code

Running and Testing the Application

Create a User

Get All Users

Get User by ID

Update a User

Delete a User

Conclusion

Next Steps

Additional Resources