A detailed explanation of dotsql in the Go library series
- 2020-11-03 22:12:52
- OfStack
Isn't it interesting to be able to single out one or more lines of SQL for execution? Today we are going to introduce dotsql, an interesting library.
background
dotsql is not an ORM, nor is it a builder for SQL queries, but rather a tool that can pull out a few lines in an SQL file for execution, much like reading an ini configuration file. If that doesn't make sense, let's look at the following.
-- name: create-users-table
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(255),
email VARCHAR(255)
);
-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)
-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?
-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1
-- name: drop-users-table
DROP TABLE users
Above are the statements defined in the SQL file, and we can clearly see that each statement is "annotated" in the form of -- name, and that as a developer, you can pick and choose the execution based on the name of the marked statement.
Quick learning
The preparatory work
Overview of directory Structure
├ ─ ─ data sql
├ ─ ─ go mod
├ ─ ─ go sum
└ ─ ─ main go
Initialize project
[go mod init dotsql
]To create the data.sql file, type SQL as shown below. The content can be customized.
-- name: create-users-table
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(255),
email VARCHAR(255)
);
-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)
-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?
-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1
--name: drop-users-table
DROP TABLE users
For convenience, we'll use the sqlite demo and store it in memory, so install the sqlite driver first
[go get github.com/mattn/go-sqlite3
]Code demo
Now write the code to import the go-sqlite3 library
[import _ "github.com/mattn/go-sqlite3"
]Gets the database handle to sqlite3
[db, _ := sql.Open("sqlite3", ":memory:")
]Load the data.sql file
[dot, _ := dotsql.LoadFromFile("data.sql")
]The first parameter to the Exec method needs to be passed in a handle
[dot.Exec(db, "create-users-table")
]The corresponding statement can be found in the comment, which is an operation to create a table
-- name: create-users-table
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(255),
email VARCHAR(255)
);
Then execute statement # 2 to insert table data
[dot.Exec(db, "create-user", "User Name", "main@example.com")
]Let's try querying the table data again, noting here that all operations are currently selected based on defined tags
rows, _ := dot.Query(db, "find-users-by-email", "main@example.com")
var (
id int
name string
email string
)
for rows.Next() {
rows.Scan(&id, &name, &email)
fmt.Println(id, name, email)
}
Query method returns * ES109en. Rows type, students can walk through the value test by themselves, the job is done!
Other functions
We can prepare the SQL statement first and execute it when appropriate
stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()
Similarly, multiple SQL files can be combined and evaluated
dot1, err := dotsql.LoadFromFile("queries1.sql")
dot2, err := dotsql.LoadFromFile("queries2.sql")
dot := dotsql.Merge(dot1, dot2)
conclusion