Gin SQLite Integration

Introduction

SQLite is a popular lightweight, disk-based database that doesn't require a separate server process. It's an excellent choice for smaller applications, prototypes, or when you need a simple embedded database solution. Integrating SQLite with the Gin web framework allows you to build powerful web applications with persistent data storage.

In this guide, we'll learn how to connect a Gin application to a SQLite database, perform basic CRUD (Create, Read, Update, Delete) operations, and implement some common patterns used in production applications.

Prerequisites

Before we start, make sure you have the following installed:

Go (version 1.16 or later)
Gin framework
SQLite database driver for Go

If you haven't installed these dependencies yet, you can do so with the following commands:

bash
# Install Gin framework
go get -u github.com/gin-gonic/gin

# Install SQLite driver
go get -u github.com/mattn/go-sqlite3

Setting up the Project Structure

Let's start by creating a simple project structure:

gin-sqlite-demo/
├── main.go
├── models/
│   └── user.go
├── database/
│   └── database.go
└── go.mod

Step 1: Initialize the Go Module

First, let's initialize our Go module:

bash
mkdir gin-sqlite-demo
cd gin-sqlite-demo
go mod init gin-sqlite-demo

Step 2: Setting up the Database Connection

Create a file called database/database.go to handle database connection:

go
package database

import (
	"database/sql"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

var DB *sql.DB

// InitDB initializes the SQLite database connection
func InitDB() {
	var err error
	DB, err = sql.Open("sqlite3", "./sqlite-database.db")
	if err != nil {
		log.Fatal(err)
	}

	// Test the connection
	err = DB.Ping()
	if err != nil {
		log.Fatal(err)
	}

	log.Println("Connected to the SQLite database successfully")

	// Create tables if they don't exist
	createTables()
}

// createTables creates tables if they don't exist
func createTables() {
	createUsersTable := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		email TEXT NOT NULL UNIQUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	_, err := DB.Exec(createUsersTable)
	if err != nil {
		log.Fatal(err)
	}
}

// CloseDB closes the database connection
func CloseDB() {
	if DB != nil {
		DB.Close()
		log.Println("Database connection closed")
	}
}

This file sets up the database connection and creates a users table if it doesn't exist yet.

Step 3: Defining Data Models

Create a file called models/user.go to define our User model:

go
package models

import (
	"database/sql"
	"gin-sqlite-demo/database"
	"log"
	"time"
)

type User struct {
	ID        int64     `json:"id"`
	Username  string    `json:"username"`
	Email     string    `json:"email"`
	CreatedAt time.Time `json:"created_at"`
}

// CreateUser adds a new user to the database
func CreateUser(username, email string) (User, error) {
	stmt, err := database.DB.Prepare("INSERT INTO users(username, email) VALUES(?, ?)")
	if err != nil {
		return User{}, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(username, email)
	if err != nil {
		return User{}, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return User{}, err
	}

	return GetUserByID(id)
}

// GetAllUsers retrieves all users from the database
func GetAllUsers() ([]User, error) {
	rows, err := database.DB.Query("SELECT id, username, email, created_at FROM users")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		var createdAt string
		err := rows.Scan(&u.ID, &u.Username, &u.Email, &createdAt)
		if err != nil {
			return nil, err
		}
		
		// Parse the time string to time.Time
		t, err := time.Parse("2006-01-02 15:04:05", createdAt)
		if err != nil {
			log.Printf("Error parsing time: %v", err)
			// Use current time as fallback
			u.CreatedAt = time.Now()
		} else {
			u.CreatedAt = t
		}
		
		users = append(users, u)
	}

	return users, nil
}

// GetUserByID retrieves a user by ID
func GetUserByID(id int64) (User, error) {
	var u User
	var createdAt string
	
	err := database.DB.QueryRow("SELECT id, username, email, created_at FROM users WHERE id = ?", id).
		Scan(&u.ID, &u.Username, &u.Email, &createdAt)
	
	if err != nil {
		if err == sql.ErrNoRows {
			return User{}, nil // No user found
		}
		return User{}, err
	}
	
	// Parse the time string to time.Time
	t, err := time.Parse("2006-01-02 15:04:05", createdAt)
	if err != nil {
		log.Printf("Error parsing time: %v", err)
		// Use current time as fallback
		u.CreatedAt = time.Now()
	} else {
		u.CreatedAt = t
	}
	
	return u, nil
}

// UpdateUser updates an existing user
func UpdateUser(id int64, username, email string) (User, error) {
	stmt, err := database.DB.Prepare("UPDATE users SET username = ?, email = ? WHERE id = ?")
	if err != nil {
		return User{}, err
	}
	defer stmt.Close()

	_, err = stmt.Exec(username, email, id)
	if err != nil {
		return User{}, err
	}

	return GetUserByID(id)
}

// DeleteUser removes a user from the database
func DeleteUser(id int64) error {
	stmt, err := database.DB.Prepare("DELETE FROM users WHERE id = ?")
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(id)
	return err
}

This file defines our User model and provides CRUD operations for user management.

Step 4: Setting up Gin Routes

Now, let's create our main application file main.go where we'll define all our API endpoints:

go
package main

import (
	"gin-sqlite-demo/database"
	"gin-sqlite-demo/models"
	"net/http"
	"strconv"

	"github.com/gin-gonic/gin"
)

func main() {
	// Initialize database
	database.InitDB()
	defer database.CloseDB()

	// Set up Gin router
	router := gin.Default()
	
	// Define API routes
	router.GET("/users", getAllUsers)
	router.GET("/users/:id", getUserByID)
	router.POST("/users", createUser)
	router.PUT("/users/:id", updateUser)
	router.DELETE("/users/:id", deleteUser)

	// Run the server
	router.Run(":8080")
}

// Handler to get all users
func getAllUsers(c *gin.Context) {
	users, err := models.GetAllUsers()
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, users)
}

// Handler to get a single user by ID
func getUserByID(c *gin.Context) {
	id, err := strconv.ParseInt(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid user ID"})
		return
	}

	user, err := models.GetUserByID(id)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	if user.ID == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": "User not found"})
		return
	}

	c.JSON(http.StatusOK, user)
}

// Handler to create a new user
func createUser(c *gin.Context) {
	var input struct {
		Username string `json:"username" binding:"required"`
		Email    string `json:"email" binding:"required"`
	}

	if err := c.ShouldBindJSON(&input); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	user, err := models.CreateUser(input.Username, input.Email)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusCreated, user)
}

// Handler to update an existing user
func updateUser(c *gin.Context) {
	id, err := strconv.ParseInt(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid user ID"})
		return
	}

	var input struct {
		Username string `json:"username" binding:"required"`
		Email    string `json:"email" binding:"required"`
	}

	if err := c.ShouldBindJSON(&input); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	user, err := models.UpdateUser(id, input.Username, input.Email)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	if user.ID == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": "User not found"})
		return
	}

	c.JSON(http.StatusOK, user)
}

// Handler to delete a user
func deleteUser(c *gin.Context) {
	id, err := strconv.ParseInt(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid user ID"})
		return
	}

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

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

Step 5: Running the Application

Now you can run your application with:

bash
go run main.go

Your API will be available at http://localhost:8080.

Testing the API

You can test your API using cURL, Postman, or any other HTTP client. Here are some examples using cURL:

Create a User

bash
curl -X POST http://localhost:8080/users \
  -H "Content-Type: application/json" \
  -d '{"username": "johndoe", "email": "[email protected]"}'

Expected Output:

json
{
  "id": 1,
  "username": "johndoe",
  "email": "[email protected]",
  "created_at": "2023-09-10T12:34:56Z"
}

Get All Users

bash
curl http://localhost:8080/users

Expected Output:

json
[
  {
    "id": 1,
    "username": "johndoe",
    "email": "[email protected]",
    "created_at": "2023-09-10T12:34:56Z"
  }
]

Get a Specific User

bash
curl http://localhost:8080/users/1

Expected Output:

json
{
  "id": 1,
  "username": "johndoe",
  "email": "[email protected]",
  "created_at": "2023-09-10T12:34:56Z"
}

Update a User

bash
curl -X PUT http://localhost:8080/users/1 \
  -H "Content-Type: application/json" \
  -d '{"username": "johndoe_updated", "email": "[email protected]"}'

Expected Output:

json
{
  "id": 1,
  "username": "johndoe_updated",
  "email": "[email protected]",
  "created_at": "2023-09-10T12:34:56Z"
}

Delete a User

bash
curl -X DELETE http://localhost:8080/users/1

Expected Output:

json
{
  "message": "User deleted successfully"
}

Real-World Example: User Registration System

Now let's expand our example to a more practical use case: a simple user registration system that includes password hashing and authentication. We'll modify our code to support this feature.

First, let's update our database schema to include passwords:

go
// In database/database.go, update the createTables() function

func createTables() {
	createUsersTable := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		email TEXT NOT NULL UNIQUE,
		password TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	_, err := DB.Exec(createUsersTable)
	if err != nil {
		log.Fatal(err)
	}
}

Next, let's update our User model to include password hashing:

go
// models/user.go
package models

import (
	"database/sql"
	"gin-sqlite-demo/database"
	"log"
	"time"
	
	"golang.org/x/crypto/bcrypt"
)

type User struct {
	ID        int64     `json:"id"`
	Username  string    `json:"username"`
	Email     string    `json:"email"`
	Password  string    `json:"-"` // The "-" means this field won't be included in JSON output
	CreatedAt time.Time `json:"created_at"`
}

// HashPassword converts a plain text password to a hashed one
func HashPassword(password string) (string, error) {
	bytes, err := bcrypt.GenerateFromPassword([]byte(password), 12)
	return string(bytes), err
}

// CheckPasswordHash compares a password with a hash
func CheckPasswordHash(password, hash string) bool {
	err := bcrypt.CompareHashAndPassword([]byte(hash), []byte(password))
	return err == nil
}

// CreateUser adds a new user with hashed password
func CreateUser(username, email, password string) (User, error) {
	hashedPassword, err := HashPassword(password)
	if err != nil {
		return User{}, err
	}

	stmt, err := database.DB.Prepare("INSERT INTO users(username, email, password) VALUES(?, ?, ?)")
	if err != nil {
		return User{}, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(username, email, hashedPassword)
	if err != nil {
		return User{}, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return User{}, err
	}

	return GetUserByID(id)
}

// Authenticate checks if the username/email and password combination is valid
func Authenticate(login, password string) (User, bool) {
	var user User
	var dbPassword string
	var createdAt string
	
	// Try to find by username first
	err := database.DB.QueryRow("SELECT id, username, email, password, created_at FROM users WHERE username = ?", login).
		Scan(&user.ID, &user.Username, &user.Email, &dbPassword, &createdAt)
	
	// If not found, try by email
	if err != nil {
		err = database.DB.QueryRow("SELECT id, username, email, password, created_at FROM users WHERE email = ?", login).
			Scan(&user.ID, &user.Username, &user.Email, &dbPassword, &createdAt)
		
		if err != nil {
			return User{}, false
		}
	}

	// Check if password matches
	if !CheckPasswordHash(password, dbPassword) {
		return User{}, false
	}
	
	// Parse the time string to time.Time
	t, err := time.Parse("2006-01-02 15:04:05", createdAt)
	if err == nil {
		user.CreatedAt = t
	} else {
		user.CreatedAt = time.Now()
	}

	return user, true
}

// Update other functions as needed...

Finally, let's add authentication endpoints to our main.go:

go
// In main.go, add these new routes
router.POST("/register", registerUser)
router.POST("/login", loginUser)

// And add these handler functions:

// Handler to register a new user
func registerUser(c *gin.Context) {
	var input struct {
		Username string `json:"username" binding:"required"`
		Email    string `json:"email" binding:"required"`
		Password string `json:"password" binding:"required"`
	}

	if err := c.ShouldBindJSON(&input); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	user, err := models.CreateUser(input.Username, input.Email, input.Password)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusCreated, user)
}

// Handler to login a user
func loginUser(c *gin.Context) {
	var input struct {
		Login    string `json:"login" binding:"required"` // Can be username or email
		Password string `json:"password" binding:"required"`
	}

	if err := c.ShouldBindJSON(&input); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	user, ok := models.Authenticate(input.Login, input.Password)
	if !ok {
		c.JSON(http.StatusUnauthorized, gin.H{"error": "Invalid credentials"})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"message": "Login successful",
		"user": user,
	})
}

Don't forget to install the bcrypt package:

bash
go get -u golang.org/x/crypto/bcrypt

Now you have a fully functional user registration and authentication system with SQLite integration!

Best Practices for Gin SQLite Integration

Use Prepared Statements: Always use prepared statements to prevent SQL injection, as demonstrated in our examples.
Handle Database Connections Properly: Open the connection at the start of your application and close it when your application terminates.
Add Transaction Support: For operations that affect multiple rows, use transactions to ensure data consistency.
Implement Proper Error Handling: Make sure to handle database errors gracefully and provide meaningful feedback to the user.
Use Connection Pooling: For larger applications, configure connection pooling to manage database connections efficiently.
Consider Using an ORM: For complex projects, consider using an Object-Relational Mapping (ORM) library like GORM to simplify database interactions.
Implement Request Validation: Always validate user input before performing database operations.
Secure Sensitive Data: Never store passwords in plaintext. Always use proper hashing algorithms like bcrypt.

Summary

In this guide, we've learned how to integrate SQLite with a Gin application to build a RESTful API. We've covered:

Setting up a SQLite database connection
Defining models and implementing CRUD operations
Creating API endpoints with Gin
Testing our API with HTTP requests
Building a practical user registration and authentication system
Best practices for Gin SQLite integration

SQLite is an excellent choice for smaller applications or as a development database. For production applications with higher load, you might consider migrating to a more robust database system like PostgreSQL or MySQL, but the basic principles and patterns we've learned here will still apply.

Additional Resources

Exercises

Add email validation to ensure only valid email addresses can be registered
Implement pagination for the list of users
Add a search endpoint to find users by username or email
Create a simple blog post system with user comments
Implement JWT-based authentication to secure your API endpoints

By completing these exercises, you'll gain a deeper understanding of working with Gin and SQLite in your Go applications.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Setting up the Project Structure​

Step 1: Initialize the Go Module​

Step 2: Setting up the Database Connection​

Step 3: Defining Data Models​

Step 4: Setting up Gin Routes​

Step 5: Running the Application​

Testing the API​

Create a User​

Get All Users​

Get a Specific User​

Update a User​

Delete a User​

Real-World Example: User Registration System​

Best Practices for Gin SQLite Integration​

Summary​

Additional Resources​

Exercises​

Introduction

Prerequisites

Setting up the Project Structure

Step 1: Initialize the Go Module

Step 2: Setting up the Database Connection

Step 3: Defining Data Models

Step 4: Setting up Gin Routes

Step 5: Running the Application

Testing the API

Create a User

Get All Users

Get a Specific User

Update a User

Delete a User

Real-World Example: User Registration System

Best Practices for Gin SQLite Integration

Summary

Additional Resources

Exercises