Skip to main content

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:

  1. Go (version 1.16 or later)
  2. Gin framework
  3. 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": "john@example.com"}'

Expected Output:

json
{
  "id": 1,
  "username": "johndoe",
  "email": "john@example.com",
  "created_at": "2023-09-10T12:34:56Z"
}

Get All Users​

bash
curl http://localhost:8080/users

Expected Output:

json
[
  {
    "id": 1,
    "username": "johndoe",
    "email": "john@example.com",
    "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": "john@example.com",
  "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": "john_updated@example.com"}'

Expected Output:

json
{
  "id": 1,
  "username": "johndoe_updated",
  "email": "john_updated@example.com",
  "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​

  1. Use Prepared Statements: Always use prepared statements to prevent SQL injection, as demonstrated in our examples.

  2. Handle Database Connections Properly: Open the connection at the start of your application and close it when your application terminates.

  3. Add Transaction Support: For operations that affect multiple rows, use transactions to ensure data consistency.

  4. Implement Proper Error Handling: Make sure to handle database errors gracefully and provide meaningful feedback to the user.

  5. Use Connection Pooling: For larger applications, configure connection pooling to manage database connections efficiently.

  6. Consider Using an ORM: For complex projects, consider using an Object-Relational Mapping (ORM) library like GORM to simplify database interactions.

  7. Implement Request Validation: Always validate user input before performing database operations.

  8. 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:

  1. Setting up a SQLite database connection
  2. Defining models and implementing CRUD operations
  3. Creating API endpoints with Gin
  4. Testing our API with HTTP requests
  5. Building a practical user registration and authentication system
  6. 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​

  1. Gin Framework Documentation
  2. SQLite Documentation
  3. Go Database/SQL Tutorial
  4. GORM - The Go ORM library

Exercises​

  1. Add email validation to ensure only valid email addresses can be registered
  2. Implement pagination for the list of users
  3. Add a search endpoint to find users by username or email
  4. Create a simple blog post system with user comments
  5. 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 feedback@compilenrun.com. I’d greatly appreciate your feedback! :)