This article introduces the SQLite database and how to use the SQLite3 package to operate a lightweight relational database.
sqlite overview
sqlite is an embedded relational database engine, officially described as a self-contained, serviceless, zero-configuration and transaction-enabled relational database engine. We often use it during product development, and can quickly verify and deploy products and quickly realize product demonstrations.
Go has a sql package, which provides a common interface for SQL (or SQL-like) databases. The sql package must be used with the database driver.
Create SQLITE database
Use the sqlite3 command line tool to create a database and query data.
sudo apt install sqlite3
Install the tools and create the database:
sqlite3 SQLite version 3.37.2 2022-01-06 13:25:41 Enter ".help" for usage hints. sqlite>
It is a parameter of the sqlite3 command, that is, the database name and the file name on the disk. If the file exists, it will be opened, and if it does not exist, it will be created:
sqlite> .tables sqlite> .exit $ ls
The .tables name lists all tables in the database, and there are currently no tables; the .exit command terminates the active session command line tool. The ls command displays the files in the current directory and you can see the files we created.
Print version
The following example prints the version of sqlite3, mainly throughSELECT SQLITE_VERSION()The statement returns the version number:
package main
import (
"database/sql"
"fmt"
"log"
_ "/mattn/go-sqlite3"
)
func main() {
db, err := ("sqlite3", ":memory:")
if err != nil {
(err)
}
defer ()
var version string
err = ("SELECT SQLITE_VERSION()").Scan(&version)
if err != nil {
(err)
}
(version)
}First import the package, here the import prefix is underscore, and the init function is executed. Then register the driver;
db, err := ("sqlite3", ":memory:")The Open function specifies the driver name and the data source name. This example connects to the in-memory database. Then call the function to ensure that the database is closed and prevent the start of a new query.
err = ("SELECT SQLITE_VERSION()").Scan(&version)QueryRow executes a query, returning at least one row. The Scan function copies the column from the return row to the version variable.
$ go run 3.39.4
Exec executes DML statements
The Exec function executes a query statement that does not return records, i.e. DML statement:
package main
import (
"database/sql"
"fmt"
"log"
_ "/mattn/go-sqlite3"
)
func main() {
db, err := ("sqlite3", "")
if err != nil {
(err)
}
defer ()
sts := `
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INT);
INSERT INTO cars(name, price) VALUES('Audi',52642);
INSERT INTO cars(name, price) VALUES('Mercedes',57127);
INSERT INTO cars(name, price) VALUES('Skoda',9000);
INSERT INTO cars(name, price) VALUES('Volvo',29000);
INSERT INTO cars(name, price) VALUES('Bentley',350000);
INSERT INTO cars(name, price) VALUES('Citroen',21000);
INSERT INTO cars(name, price) VALUES('Hummer',41400);
INSERT INTO cars(name, price) VALUES('Volkswagen',21600);
`
_, err = (sts)
if err != nil {
(err)
}
("table cars created")
}First create the database and generate a new database file:
db, err := ("sqlite3", "")Then create the table and insert the data. pass_, err = (sts)The statement executes DML.
Query records
The query statement returns a record that meets the criteria, typically SELECT, and the optional parameter is a placeholder:
package main
import (
"database/sql"
"fmt"
"log"
_ "/mattn/go-sqlite3"
)
func main() {
db, err := ("sqlite3", "")
if err != nil {
(err)
}
defer ()
rows, err := ("SELECT * FROM cars")
if err != nil {
(err)
}
defer ()
for () {
var id int
var name string
var price int
err = (&id, &name, &price)
if err != nil {
(err)
}
("%d %s %d\n", id, name, price)
}
}Execute the query statement:
rows, err := ("SELECT * FROM cars")Next prepares the next result row for the Scan method. Returns True successfully, and returns false if there is no result or an error occurs.
for () {
var id int
var name string
var price int
err = (&id, &name, &price)
if err != nil {
(err)
}
("%d %s %d\n", id, name, price)
}The Scan method obtains the field value and finally prints the recorded column value.
prepared statement with parameters
Using prepared statements, placeholders can be included instead of writing values directly in the statements to ensure the performance and security of database operations.
package main
import (
"database/sql"
"fmt"
"log"
_ "/mattn/go-sqlite3"
)
func main() {
db, err := ("sqlite3", "")
if err != nil {
(err)
}
defer ()
stm, err := ("SELECT * FROM cars WHERE id = ?")
if err != nil {
(err)
}
defer ()
var id int
var name string
var price int
cid := 3
err = (cid).Scan(&id, &name, &price)
if err != nil {
(err)
}
("%d %s %d\n", id, name, price)
}
Use the prepare function to execute the prepare statement to query a specific row:
stm, err := ("SELECT * FROM cars WHERE id = ?")Pass in parameters to the QueryRow function to replace the placeholder:
err = (cid).Scan(&id, &name, &price)
The above two functions can also be implemented in one step:
row := ("SELECT * FROM cars WHERE id = ?", cid)Returns the number of rows affected
RowsAffected Returns the number of rows affected by execution of DML statements:
package main
import (
"database/sql"
"fmt"
"log"
_ "/mattn/go-sqlite3"
)
func main() {
db, err := ("sqlite3", "")
if err != nil {
(err)
}
defer ()
res, err := ("DELETE FROM cars WHERE id IN (1, 2, 3)")
if err != nil {
(err)
}
n, err := ()
if err != nil {
(err)
}
("The statement has affected %d rows\n", n)
}
The above example uses the delete statement to delete the three-line record and then print the result for verification. The operation results are as follows:
$ go run The statement has affected 3 rows
Summarize
This is all about this article about Golang operating the sqlite3 database. For more related content on Golang operating the sqlite3 database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!