Table of contents

1. SQLite3 installation (linux)

1.2 Online installation

  start installation

Create db in the directory

Execute table creation statement

2. Scheme selection

3. Write code

3.1 Preparation of the code running environment

Go locale

SQLite3 generate db

3.2 Operating SQLite3 to add, delete, modify and check the code

3.3 Verification results

1. SQLite3 installation (linux)

SQLite is an open source embedded relational database that implements a self-contained, zero-configuration, transaction-supported SQL database engine.

It is highly portable, easy to use, compact, efficient and reliable.

Unlike other databases (such as Mysql), SQLite is simple to install and run, making sure the SQLite binary exists to create, connect, and use the database. The installation methods are as follows:

• Direct installation via networking, sudo apt-get install sqlite3
• Offline installation
• docker image installation

1.2 Online installation

  start installation

sudo apt-get install sqlite3

Create db in the directory

sqlite3 /home/zhongqiu/golang_test.db

 

Execute table creation statement

create table userinfo( id INTEGER PRIMARY KEY AUTOINCREMENT,username VARCHAR(64) NULL,departname VARCHAR(64) NULL,created DATE NULL);

2. Scheme selection

SQLite, an embedded database project definitely worth considering, is the open source version of Access.

There are many drivers that support SQLite in the Go language, but many do not support the database/sql interface:

• GitHub - mattn/go-sqlite3: sqlite3 driver for go using database/sqlsqlite3 driver for go using database/sql. Contribute to mattn/go-sqlite3 development by creating an account on GitHub.https://github.com/mattn/go-sqlite3 Support database/sql interface, based on cgo (please Baidu cgo by yourself)
• https://github.com/feyeleanor/gosqlite3https://github.com/feyeleanor/gosqlite3 Does not support database/sql interface, based on cgo
• https://github.com/phf/go-sqlite3https://github.com/phf/go-sqlite3 Does not support database/sql interface, based on cgo

At present, there are few SQLite database drivers that support database/sql, and the use of standard interfaces is conducive to migration. This article uses the first driver.

3. Write code

3.1 Preparation of the code running environment

Go locale

Make sure that the Linux environment has the go language runtime environment, because the Golang code will be run on Linux. (Don't want to install SQLite3 on windows).

SQLite3 generate db

As shown in the figure below, the db generation directory:

3.2 Operating SQLite3 to add, delete, modify and check the code

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
)

type UserInfo struct {
	id         int64
	username   string
	departname string
	created    string
}

func main() {
	
	// Note that the db database path generated here must be correct
	db, err := sql.Open("sqlite3", "/home/zhongqiu/golang_test.db")
	checkErr(err)

	// insert data
	id := insertUser(db)

	// Update user data
	updateUser(db, id)

	// query user
	queryUser(db)

	// delete users
	deleteUser(db, id)

	db.Close()
}

// Insert user data
func insertUser(db *sql.DB) (id int64) {

	stmt, err := db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)")
	checkErr(err)

	res, err := stmt.Exec("Wang Wu", "Product Department", "2000-10-10")
	checkErr(err)

	id, err = res.LastInsertId()
	checkErr(err)

	fmt.Println("return after insert", id)

	return id
}

// Update user data
func updateUser(db *sql.DB, id int64) {
	stmt, err := db.Prepare("update userinfo set username =? where id =?")
	checkErr(err)

	res, err := stmt.Exec("Wang Wugai", id)
	checkErr(err)

	affected, err := res.RowsAffected()
	checkErr(err)

	fmt.Println("updated row count", affected)
}

// query user
func queryUser(db *sql.DB) {
	rows, err := db.Query("SELECT * FROM userinfo")
	checkErr(err)

	userInfo := UserInfo{}

	for rows.Next() {
		err := rows.Scan(&userInfo.id, &userInfo.username, &userInfo.departname, &userInfo.created)
		checkErr(err)

		fmt.Println("userinfo Inquire", userInfo)
	}
}

// delete users
func deleteUser(db *sql.DB, id int64) {
	stmt, err := db.Prepare("delete from userinfo where id =?")
	checkErr(err)

	res, err := stmt.Exec(id)
	checkErr(err)

	affected, err := res.RowsAffected()
	checkErr(err)

	fmt.Println("delete effect", affected)
}

// check for errors
func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

3.3 Verification results

PS:

1. When running, github.com/mattn/go-sqlite3 may not be pulled down, you can try to switch the source.
2. When you don't use Conn, you can try to close it.
3. It can be seen that when database/sql is used, it is almost exactly the same as the mysql operation process in this series of courses, but the unique method of SQLite (the folder path that the deployment service can access) is used in sql.Open.