Go и SQL базы данных
Перевод статьи "Practical Persistence in Go: SQL Databases".
Это первый туториал из серии материалов про работу с данными в веб приложениях.
В этом посте мы погрузимся в работу с SQL базами данных. Я объясню работу с стандартным пакетом database/sql, приведу примеры рабочих приложений и продемонстрирую несколько хитростей для более красивого структурирования кода.
Для начала, вам необходимо установить драйвера для работы с database/sql.
В этом посте мы будем использовать Postgres и замечательный драйвер pg. Тем не менее, весь код из этого туториала должен нормально работает и с другими драйверами, включая MySQL и SQLite. По ходу я буду указывать на специфические для Postgres моменты(которых будет не очень много).
$ go get github.com/lib/pq
Основы
Давайте напишем простое приложение для книжного магазина, которое будет выполнять CRUD операции с нашей таблицей для книг.
Прежде всего, нам нужно создать эту самую таблицу для книг, как показано ниже:
CREATE TABLE books (
isbn char(14) NOT NULL,
title varchar(255) NOT NULL,
author varchar(255) NOT NULL,
price decimal(5,2) NOT NULL
);
INSERT INTO books (isbn, title, author, price) VALUES
('978-1503261969', 'Emma', 'Jayne Austen', 9.44),
('978-1505255607', 'The Time Machine', 'H. G. Wells', 5.99),
('978-1503379640', 'The Prince', 'Niccolò Machiavelli', 6.99);
ALTER TABLE books ADD PRIMARY KEY (isbn);
После этого, необходимо настроить свое Go окружение, создать папку bookstore и файл main.go:
$ cd $GOPATH/src
$ mkdir bookstore && cd bookstore
$ touch main.go
Давайте начнем с простого кода, который будет выполнять запрос SELECT * FROM books и выводить результат в консоль.
package main
import (
_ "github.com/lib/pq"
"database/sql"
"fmt"
"log"
)
type Book struct {
isbn string
title string
author string
price float32
}
func main() {
db, err := sql.Open("postgres", "postgres://user:pass@localhost/bookstore")
if err != nil {
log.Fatal(err)
}
rows, err := db.Query("SELECT * FROM books")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
bks := make([]*Book, 0)
for rows.Next() {
bk := new(Book)
err := rows.Scan(&bk.isbn, &bk.title, &bk.author, &bk.price)
if err != nil {
log.Fatal(err)
}
bks = append(bks, bk)
}
if err = rows.Err(); err != nil {
log.Fatal(err)
}
for _, bk := range bks {
fmt.Printf("%s, %s, %s, £%.2f\n", bk.isbn, bk.title, bk.author, bk.price)
}
}
В этом куске кода довольно много разных действий. Пройдемся по шагам и рассмотрим как все это работает.
Первый интересный момент, это импортирование пакета драйвера. Мы ничего не используем напрямую из этого пакета, а это означает, что компилятор Go ругнется, если вы попробуете импортировать пакет как обычно. Но нам необходим вызов функции init() этого пакета для того, чтобы драйвер зарегистрировал себя для использования в database/sql. Мы можем обойти это ограничение используя пустой алиас для импортируемого пакета. И это означает, что pq.init() будет выполняться, но благодаря алиасу мы избавимся от ошибок во время компиляции. Такая практика является стандартом для большинства SQL драйверов в Go.
Далее, мы определим наш тип для книги, в котором поля и типы полей будут зависеть от таблицы books. Тут стоит уточнить, что мы можем безопасно использовать string и float32, так как мы указали NOT NULL для колонок в нашей таблице. Если в таблице есть поля, которые могут содержать NULL, то следует использовать типы sql.NullString и sql.NullFloat64 (тут можно глянуть рабочий пример). Вообще, если у вас есть возможность, старайтесь избегать полей, в которых могут быть NULL значения.
В функции main() мы инициализируем экземпляр sql.DB с помощью вызова sql.Open(). Мы указываем название нашего драйвера(в нашем случае это "postgres") и строку соединения, формат которой должен быть описан в документации к драйверу. Важное замечание, sql.DB это не соединение с базой, это некоторая абстракция над пулом соединений. Вы можете менять максимальное количество открытых и простаиваемых соединений в пуле с помощью методов db.SetMaxOpenConns() и db.SetMaxIdleConns() соответственно. И обратите внимание, что sql.DB можно безопасно использовать в конкурентных приложениях(которыми являются и веб-приложения).
Рассмотрим использованные стандартные паттерны:
- Мы получаем данные из таблицы, используя метод
DB.Query()и присваиваем результат переменнойrows. После этого, мы пользуемсяdefer rows.Close(), чтобы наверняка закрыть сет с результатами до выхода из функции. Очень важно не забывать закрывать сет. Все время, пока открыт сет, используемое соединение невозможно вернуть в пул. Если вдруг что-то пойдет не так и ваш сет не будет закрываться, то соединения в пуле могут быстро закончиться. Еще одна ловушка в том(и это оказалось для меня сюрпризом), чтоdeferдолжен идти после проверки на ошибкиDB.Query(). ЕслиDB.Query()вернет ошибку, то вместо сета будет полученnilи при вызовеrows.Close()стрельнет паника. - Для итерации по строкам мы используем
rows.Next(). Этот метод подготавливает строку для использования методаrows.Scan(). Не забывайте, что по завершению итерации по всем строкам сет автоматически закрывается и соединение возвращается в пул. - Мы используем метод
rows.Scan(), чтобы скопировать значения всех полей из строки в созданный нами экземплярBook. Далее, мы проверяем была ли ошибка при работе методаrows.Scan()и добавляем новый экземплярBookв слайсbks, который мы создали ранее. - После итераций с помощью
rows.Next()мы вызываемrows.Err(). Этот метод возвращает любую ошибку, которая произошла во время выполнения итераций. Этот момент достаточно важен, он позволяет убедиться, что мы прошлись по всему сету без ошибок.
Если все хорошо и мы нормально заполнили на слайс bks, то теперь мы итерируемся по нему и выводим информацию в консоль.
Если вы запустите код, то должно получиться что-то такое:
$ go run main.go
978-1503261969, Emma, Jayne Austen, £9.44
978-1505255607, The Time Machine, H. G. Wells, £5.99
978-1503379640, The Prince, Niccolò Machiavelli, £6.99
Использование в веб-приложении
Давайте изменим наш код, что бы получилось RESTful веб-приложение с 3 роутами:
- GET /books – Список всех книг в магазине
- GET /books/show – Информация о конкретной книге по ISBN
- POST /books/create – Добавление новой книги в магазин
Мы уже реализовали основную логику необходимую для GET /books. Давайте адаптируем ее для использования в HTTP хендлере booksIndex() нашего приложения.
package main
import (
_ "github.com/lib/pq"
"database/sql"
"fmt"
"log"
"net/http"
)
type Book struct {
isbn string
title string
author string
price float32
}
var db *sql.DB
func init() {
var err error
db, err = sql.Open("postgres", "postgres://user:pass@localhost/bookstore")
if err != nil {
log.Fatal(err)
}
if err = db.Ping(); err != nil {
log.Fatal(err)
}
}
func main() {
http.HandleFunc("/books", booksIndex)
http.ListenAndServe(":3000", nil)
}
func booksIndex(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), 405)
return
}
rows, err := db.Query("SELECT * FROM books")
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
defer rows.Close()
bks := make([]*Book, 0)
for rows.Next() {
bk := new(Book)
err := rows.Scan(&bk.isbn, &bk.title, &bk.author, &bk.price)
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
bks = append(bks, bk)
}
if err = rows.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
for _, bk := range bks {
fmt.Fprintf(w, "%s, %s, %s, £%.2f\n", bk.isbn, bk.title, bk.author, bk.price)
}
}
И в чем же тут отличия?
- Мы используемые функцию
init()для настройки нашего пула соединений и указываем его в качестве значения глобальной переменнойdb. Мы используем глобальную переменную, которая предоставляет доступ к пулу соединений, чтобы иметь возможность использовать ее в разных HTTP хендлерах, но это не единственный возможный способ. Так какsql.Open()не проверяет соединение, то мы вызываемDB.Ping(), чтобы убедиться, что все работает нормально. - В хендлере
booksIndexмы возвращаем405 Method Not Allowedответ для всех не GET запросов. Дальше мы работаем с нашими данными. Все работает как в примере выше, за исключением что ошибки теперь возвращаются как HTTP ответ и нет выхода из программы. В результате мы записываем описания книг как обычный текст вhttp.ResponseWriter.
Запускаем приложение и делаем запрос к нему:
$ curl -i localhost:3000/books
HTTP/1.1 200 OK
Content-Length: 205
Content-Type: text/plain; charset=utf-8
978-1503261969, Emma, Jayne Austen, £9.44
978-1505255607, The Time Machine, H. G. Wells, £5.99
978-1503379640, The Prince, Niccolò Machiavelli, £6.99
Выборка одной строки
Для GET /books/show нам нужно реализовать получение одной книги из базы по ее ISBN, который будет указываться как параметр в запросе:
/books/show?isbn=978-1505255607
Для этого мы добавим хендлер bookShow():
// ...
func main() {
http.HandleFunc("/books", booksIndex)
http.HandleFunc("/books/show", booksShow)
http.ListenAndServe(":3000", nil)
}
// ...
func booksShow(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, http.StatusText(405), 405)
return
}
isbn := r.FormValue("isbn")
if isbn == "" {
http.Error(w, http.StatusText(400), 400)
return
}
row := db.QueryRow("SELECT * FROM books WHERE isbn = $1", isbn)
bk := new(Book)
err := row.Scan(&bk.isbn, &bk.title, &bk.author, &bk.price)
if err == sql.ErrNoRows {
http.NotFound(w, r)
return
} else if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
fmt.Fprintf(w, "%s, %s, %s, £%.2f\n", bk.isbn, bk.title, bk.author, bk.price)
}
Первым делом, в обработчике проверяется действительно ли пришел GET запрос.
После этого, мы используем метод Request.FormValue() для получения параметров из строки запроса. В случае если нет необходимых параметров, то мы получаем пустую строку и возвращаем ответ 400 Bad Request.
Тут мы подходим к самому интересному. Метод DB.QueryRow() работает аналогично DB.Query(), но получает только одну строку.
Так как у нас есть некоторый ненадежный данные от пользователя(переменная isbn), то в нашем SQL запросе нужно использовать плейсхолдеры для параметров, сами значения мы указываем как аргументы после строки запроса.
db.QueryRow("SELECT * FROM books WHERE isbn = $1", isbn)
Если чуть углубиться, то можно обнаружить, что db.QueryRow (а также db.Query() и db.Exec()) создают "подготовленные выражения"(prepared statement) в базе данных и выполняют запросы, подставляя параметры в плейсхолдеры этих выражений. Это означает, что все три метода безопасны в плане SQL-инъекций, если пользоваться ими правильно. Вот что говорит нам википедия:
Подготовленные выражения устойчивы к SQL инъекциям, поскольку значения параметров, которые передаются позже с использованием другого протокола, не нужно ескейпить. Если оригинальное выражение построено не на основании внешнего ввода, то инъекции не может произойти. В зависимости от базы данных, плейсхолдеры указываются по разному. В Postgres используется нотация
$N, но в MySQL, SQL Server и в некоторых других используется символ?.
Окей, давайте вернемся к нашему коду.
После получения строки с помощью DB.QueryRow(), мы используем row.Scan() для копирования значений в наш новы объект Book. Важно, что мы не узнаем про ошибки выполнения запроса в методе DB.QueryRow(), пока не вызовем метод row.Scan().
Если ваш запрос не нашел ни одной строки, то вызов row.Scan() вернет ошибку sql.ErrNoRows. Мы выполняем проверку на эту ошибку и, если ничего не найдено, возвращаем 404 Not Found ответ. Если возникают другие ошибку, то возвращаем 500 Internal Server Error.
Если все хорошо, то мы записываем в http.ResponseWriter информацию по запрашиваемой книге.
Давайте попробуем:
$ curl -i localhost:3000/books/show?isbn=978-1505255607
HTTP/1.1 200 OK
Content-Length: 54
Content-Type: text/plain; charset=utf-8
978-1505255607, The Time Machine, H. G. Wells, £5.99
Если вы попробуете указывать разные значения ISBN, то можете увидеть как меняется результат ответа. В случае неправильного запроса, вы должны получить соответствующий код ошибки.
Выполнение выражений
Для нашего роута POST /books/create мы создадим хендлер booksCreate(), в котором будем использовать DB.Exec() для выполнения выражения INSERT. Вы можете использовать схожий подход для UPDATE, DELETE или других операций, которые не подразумевают получение результата в виде строк таблиц.
Код выглядит так:
// ...
import (
_ "github.com/lib/pq"
"database/sql"
"fmt"
"log"
"net/http"
"strconv"
)
// ...
func main() {
http.HandleFunc("/books", booksIndex)
http.HandleFunc("/books/show", booksShow)
http.HandleFunc("/books/create", booksCreate)
http.ListenAndServe(":3000", nil)
}
// ...
func booksCreate(w http.ResponseWriter, r *http.Request) {
if r.Method != "POST" {
http.Error(w, http.StatusText(405), 405)
return
}
isbn := r.FormValue("isbn")
title := r.FormValue("title")
author := r.FormValue("author")
if isbn == "" || title == "" || author == "" {
http.Error(w, http.StatusText(400), 400)
return
}
price, err := strconv.ParseFloat(r.FormValue("price"), 32)
if err != nil {
http.Error(w, http.StatusText(400), 400)
return
}
result, err := db.Exec("INSERT INTO books VALUES($1, $2, $3, $4)", isbn, title, author, price)
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
rowsAffected, err := result.RowsAffected()
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
fmt.Fprintf(w, "Book %s created successfully (%d row affected)\n", isbn, rowsAffected)
}
Думаю, вы уже находите много знакомого в этом коде.
В хендлере booksCreate() мы проверяем, действительно ли пришел POST запрос и получаем параметры из запроса с помощью request.FormValue(). Мы проверяем наличие всех необходимых параметров, а цену еще и конвертируем в float с помощью strconv.ParseFloat().
После этого, мы используем db.Exec() с указанием полученных парметров, аналогично как мы делали это ранее. Важно, что DB.Exec(), DB.Query() и DB.QueryRow(), - это функции которое могут принимать переменное число параметров.
Метод db.Exec() в качестве результата возвращает объект, который удовлетворяет интерфейс sql.Result. При необходимости, этот результат можно использовать или не учитывать, используя пустой идентификатор.
Интерфейс sql.Result предоставляет метод LastInsertId(), который используется для получения последнего значения автоинкремента. Также, можно использовать метод RowsAffected(), который возвращает число строк, затронутых в запросе(удаленных, обновленных, новых и т.д.). В нашем случае, используется второй описанный метод, мы получаем количество строк и формируем сообщение.
Стоит отметить, что не все драйвера поддерживают методы LastInsertId() и RowsAffected() и при их вызове вы получите ошибку. К примеру, драйвер pq не поддерживает метод LastInsertId() и, если вам необходим подобный функционал, то прийдется использовать подход вроде этого.
Давайте проверим роут /books/create с передачей необходимых параметров в POST:
$ curl -i -X POST -d "isbn=978-1470184841&title=Metamorphosis&author=Franz Kafka&price=5.90" \
localhost:3000/books/create
HTTP/1.1 200 OK
Content-Length: 58
Content-Type: text/plain; charset=utf-8
Book 978-1470184841 created successfully (1 row affected)
Использование DB.Prepare()
Возможно, вам стало интересно, почему не используется DB.Prepare().
Как я объяснял выше, методы методы DB.Query(), DB.Exec() и DB.QueryRow() создают подготовленные выражения в базе данных, запускают их с указанными параметрами и затем закрывают(точнее деаллоцируют) эти выражения.
Недостатки использования такого подхода очевидны. У нас аж три обращения к базе данных на каждый HTTP запрос. Чтобы избежать этого, мы можем воспользоваться DB.Prepare() (например, в функции init()).
Но на самом деле все не так просто. Подготовленные выражения работают только с конкретной сессией базы данных. Когда сессия заканчивается, подготовленные выражения необходимо создавать заново. Это может произойти, если есть большой таймаут между запросами или вам пришлось рестартовать базу.
Для веб-приложений, в которых задержка имеет большое значение, возможно стоит заморочиться и добавить некоторый мониторинг, который будет реинициализировать подготовленные выражения. Но в таком приложении, как наше, это слишком большой оверхед и нам достаточно использовать DB.Query() как есть.
В этом треде описанная проблема обсуждается несколько глубже.
Рефакторинг
В данный момент, вся наша логика работы с базой перемешана с обработкой HTTP запросов. И это отличный подвод для рефакторинга, который позволит упростить наш код и сделать его более логичным.
Но этот туториал уже и так достаточно большой, поэтому оставим это для следующего поста - "Practical Persistence in Go: Organising Database Access" (в скором времени).
Дополнительные инструменты
Пакет Sqlx от Jason Moiron предоставляет расширенный функционал для работы с базой, включая именованные плейсхолдеры, и автоматический маршалинг результатов запроса в структуры.
Если вам интересны более ORM-ориентированные подходы, то взгляните в сторону Modl того же автора или gorp от James Cooper.
Пакет null может помочь вам при работе с null-значениями.
Позже, я нашел отличный туториал: "go-database-sql.org". Обратите особое внимание на раздел про сюрпризы и ограничения.