Background introduction

dotsql is not an ORM or a builder of SQL query statements, but a tool that can carry out several lines in an SQL file, very similar to the reading of ini configuration files. If not, 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 

The above is the statement defined in the SQL file. We can see clearly that each statement is annotated in the way of -- name. As a developer, you can select the statement to execute according to the marked name.

Quick start

Preparation

Directory structure overview

.
├── data.sql
├── go.mod
├── go.sum
└── main.go

Initialize project

go mod init dotsql

Create the data.sql file and type the following SQL. It's just an example. 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 sqlite driver first

go get github.com/mattn/go-sqlite3

Code demo

Now write the code and import the go SQLite3 Library

import _ "github.com/mattn/go-sqlite3"

Get the database handle of sqlite3

db, _ := sql.Open("sqlite3", ":memory:")

Load data.sql file

dot, _ := dotsql.LoadFromFile("data.sql")

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

dot.Exec(db, "create-users-table")

You can find the corresponding statement from the comment. It 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 to insert the table data

dot.Exec(db, "create-user", "User Name", "main@example.com")

Let's try to query the table data again. Note that at present, all operations are performed based on the 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)
	}

The Query method returns the type of * sql.Rows. Students can traverse the value test by themselves and achieve success!

Other gameplay

We can prepare SQL statements before executing them at the right time

stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()

In the same way, we can combine multiple SQL files for value taking

dot1, err := dotsql.LoadFromFile("queries1.sql")
dot2, err := dotsql.LoadFromFile("queries2.sql")
dot := dotsql.Merge(dot1, dot2)

Thank you for watching. If you think the article is helpful, please pay attention to the official account of "Ping Yi" and focus on the principle of Go language and technology.