Tools projects gradually like to use SQLite3 databases. There is no concurrency problem for tool-type projects, and there is no need to install additional database servers when deploying projects, which is very convenient.
1. Install the sqlite3 driver
go get /mattn/go-sqlite3
2. Go connection sqlite3 initialization method
var db *
func init() {
// Open or create a SQLite database file var err error
db, err = ("sqlite3", "./")
if err != nil {
(err)
}
// Test connection if err = (); err != nil {
(err)
}
}
This involves an init() function. In Go language, the init() function is a special function, which is automatically called when the program starts executing, and can be used to connect to the database initialization operation. Each package can contain any number of init() functions, and these functions are automatically executed when the package is imported. The init() function is usually used to initialize operations, such as setting variables, opening files or database connections, etc.
Features of the init() function:
Automatically called: The init() function does not require explicit calls, they are automatically executed when the package is imported.
Execution order:
- If there are multiple init() functions in a package, they are executed in order in the source code.
- If a package imports other packages, the init() function of the imported package is executed before the init() function of its package is imported.
- No parameters and return value: the init() function has no parameters and no return value.
- Cannot be called by other functions: the init() function can only be called by the runtime system, and cannot be called by other functions.
3. Test the Users structure
type Users struct {
ID int
Name string
Email string
}
Whether the initial letter of the structure field is capitalized determines whether the field is visible outside the package (i.e. whether it has public access rights)
Knowledge points:
First letter capital: If the first letter of an identifier (including structure fields, function names, variable names, etc.) is capitalized, then it can be accessed by other packages, that is, it is "exported" or "public".
First letter lowercase: If the initial letter of an identifier is lowercase, it can only be accessed inside the package it defines and is not visible to other packages, i.e. it is "unexported" or "private".
4. Users table CURD operation
// CreateUser Create new userfunc (u *Users) CreateUser() (int64, error) {
stmt, err := ("INSERT INTO users (name, email) VALUES (?, ?)")
if err != nil {
return 0, err
}
res, err := (, )
if err != nil {
return 0, err
}
return ()
}
// GetUserByID Get user based on IDfunc (u *Users) GetUserByID(id int) error {
row := ("SELECT id, name, email FROM users WHERE id = ?", id)
return (&, &, &)
}
// GetAllUsers Get all usersfunc GetAllUsers() ([]*Users, error) {
rows, err := ("SELECT id, name, email FROM users")
if err != nil {
return nil, err
}
defer ()
var users []*Users
for () {
user := &Users{}
if err := (&, &, &); err != nil {
return nil, err
}
users = append(users, user)
}
if err := (); err != nil {
return nil, err
}
return users, nil
}
// UpdateUser updates user informationfunc (u *Users) UpdateUser() (int64, error) {
stmt, err := ("UPDATE users SET name = ?, email = ? WHERE id = ?")
if err != nil {
return 0, err
}
res, err := (, , )
if err != nil {
return 0, err
}
return ()
}
// DeleteUser DeleteUserfunc (u *Users) DeleteUser() (int64, error) {
stmt, err := ("DELETE FROM users WHERE id = ?")
if err != nil {
return 0, err
}
res, err := ()
if err != nil {
return 0, err
}
return ()
}
The CURD operations here are all methods. A method is a special type of function that has a receiver. The receiver can be any type of value or pointer. The receiver here is (u *Users), using pointers.
5. Go uses sqlite3 database to implement CURD operations
//
package main
import (
"database/sql"
"fmt"
"log"
_ "/mattn/go-sqlite3" // Import sqlite3 driver)
type Users struct {
ID int
Name string
Email string
}
var db *
func init() {
// Open or create a SQLite database file var err error
db, err = ("sqlite3", "./")
if err != nil {
(err)
}
// Test connection if err = (); err != nil {
(err)
}
// Create user table createTableSQL := `
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
`
// Execute SQL statements _, err = (createTableSQL)
if err != nil {
(err)
}
}
// CreateUser Create new userfunc (u *Users) CreateUser() (int64, error) {
stmt, err := ("INSERT INTO users (name, email) VALUES (?, ?)")
if err != nil {
return 0, err
}
res, err := (, )
if err != nil {
return 0, err
}
return ()
}
// GetUserByID Get user based on IDfunc (u *Users) GetUserByID(id int) error {
row := ("SELECT id, name, email FROM users WHERE id = ?", id)
return (&, &, &)
}
// GetAllUsers Get all usersfunc GetAllUsers() ([]*Users, error) {
rows, err := ("SELECT id, name, email FROM users")
if err != nil {
return nil, err
}
defer ()
var users []*Users
for () {
user := &Users{}
if err := (&, &, &); err != nil {
return nil, err
}
users = append(users, user)
}
if err := (); err != nil {
return nil, err
}
return users, nil
}
// UpdateUser updates user informationfunc (u *Users) UpdateUser() (int64, error) {
stmt, err := ("UPDATE users SET name = ?, email = ? WHERE id = ?")
if err != nil {
return 0, err
}
res, err := (, , )
if err != nil {
return 0, err
}
return ()
}
// DeleteUser DeleteUserfunc (u *Users) DeleteUser() (int64, error) {
stmt, err := ("DELETE FROM users WHERE id = ?")
if err != nil {
return 0, err
}
res, err := ()
if err != nil {
return 0, err
}
return ()
}
func main() {
("Main function starts...")
// Create a user user := &Users{Name: "buddha", Email: "3539949705@"}
id, err := ()
if err != nil {
("Failed to create user: %v", err)
}
("Created user with ID: %d\n", id)
// Get the user user = &Users{}
if err := (int(id)); err != nil {
("Failed to get user: %v", err)
}
("User: ID: %d, Name: %s, Email: %s\n", , , )
// Update user = "buddha2080"
= "3539949704@"
affectedRows, err := ()
if err != nil {
("Failed to update user: %v", err)
}
("Updated %d rows\n", affectedRows)
// Get all users users, err := GetAllUsers()
if err != nil {
("Failed to get all users: %v", err)
}
for _, u := range users {
("User: id: %d, name: %s, email: %s\n", , , )
}
// Delete the user affectedRows, err = ()
if err != nil {
("Failed to delete user: %v", err)
}
("Deleted %d rows\n", affectedRows)
("Main function ends...")
}
This is the article about Golang's CURD operation using sqlite3 database. For more information about CURD operation of Go sqlite3 database, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!