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


Related articles: