Detailed explanation of the Go language library series dotsql

Introduction: Isn’t it very interesting to be able to take out a certain line or several lines of the SQL file to execute it alone? Today we will introduce this interesting library - dotsql.

Background introduction

dotsql is not an ORM or a builder of SQL query statements. It is a tool that can take out a few lines in an SQL file to execute, which is very similar to reading of an ini configuration file. If you don't understand it yet, let's look at the following content.

-- 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

The above are statements defined in SQL files. We can clearly see that each statement is "annotated" in the form of -- name, and as a developer, you can select statements based on the marked name to execute.

Get started quickly
Preparation

Directory structure overview

├──
├──
├──
└──

Initialize the project

go mod init dotsql

To create a file, type SQL as follows, it is just an example, and 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 use sqlite to demonstrate and store it in memory, so we need to install the sqlite driver first

go get /mattn/go-sqlite3

Code Demo

Now write the code and import the go-sqlite3 library

import _ "/mattn/go-sqlite3"

Get the database handle of sqlite3

db, _ := ("sqlite3", ":memory:")

Loading the file

dot, _ := ("")

Select a tag in the file to execute. The first parameter of the Exec method needs to be passed in a handle.

(db, "create-users-table")

You can find the corresponding statement from 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 the second statement and insert the table data

(db, "create-user", "User Name", "main@")

Let's try to query table data again. Note here that all operations are currently selected based on the defined tags.

rows, _ := (db, "find-users-by-email", "main@")
 var (
  id int
  name string
  email string
 )
 for () {
  (&id, &name, &email)
  (id, name, email)
 }

The Query method returns the * type, and students can traverse the value test by themselves, and the task is done!

Other ways to play

We can prepare SQL statements first and then execute them at the right time

stmt, err := (db, "drop-users-table")
result, err := ()

Similarly, we can merge multiple SQL files and then perform value operation.

dot1, err := ("")
dot2, err := ("")
dot := (dot1, dot2)

Summarize

This is the end of this article about dotsql in the Go language library series. For more related go language library dotsql content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!