Оффлайн
- Регистрация
- 1 Мар 2015
- Сообщения
- 1,481
- Баллы
- 155
Learn how to use SQL in Go (Golang) using SQLite! This tutorial covers connecting to a database, writing queries, creating tables, CRUD operations, and handling context and timeouts. You will be using exec, query, and queryrow, scan methods.
Setting up the project
Start by opening a terminal.
To connect to the database create a new file called main.go and add the following code:
package main
import (
"database/sql"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
}
We use the sql.Open function to open a connection to the database. The first argument is the driver name and the second argument is the database file path or the database connection string depending on the database.
We check if there is an error in opening the database connection. If there is an error, we log the error and exit the program.
The defer db.Close() ensures the database connection is closed when we are done.
Common database operations
Here is a list of the queries we will use:
Working with Orders & Database
Creating the OrderRepository struct
Create new folder called database and inside create a file called orders.go.
package database
import "database/sql"
type OrderRepository struct {
Db *sql.DB
}
type Order struct {
Id int
Product string
Amount int
}
We created a OrderRepository struct that holds the Db connection. This allows us to use it in the methods we will create. We also created an Order struct that will hold the order data.
Creating the order table
Let's add a method to create the order table.
func (r *OrderRepository) CreateTable() error {
_, err := r.Db.Exec(`CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product TEXT,
amount INTEGER
)`)
return err
}
We use the Exec method to execute the query. The Exec method is used for queries that don't return any data.
You can use a GUI tool like TablePlus to view the orders table.
Download it from .
You should now be able to see the orders table in the GUI.
Inserting an order
Add a method to insert order data into the database.
func (r *OrderRepository) Insert(order Order) error {
_, err := r.Db.Exec("INSERT INTO orders (product, amount) VALUES (?, ?)", order.Product, order.Amount)
return err
}
INSERT INTO orders (product, amount) VALUES (?, ?): Inserts a new row into the orders table with the product and amount values.
The question marks are placeholders for the values we want to insert.
Always use placeholders instead of concatenating strings to avoid SQL injection.
Get all orders
func (r *OrderRepository) GetAll() ([]Order, error) {
rows, err := r.Db.Query("SELECT * FROM orders")
if err != nil {
return nil, err
}
defer rows.Close()
var orders []Order
for rows.Next() {
var order Order
err := rows.Scan(&order.Id, &order.Product, &order.Amount)
if err != nil {
return nil, err
}
orders = append(orders, order)
}
return orders, nil
}
First we query the rows from the database using the Query method. The query method is used for queries that return data. The * is a wildcard it means that we want to select all columns.
We use defer rows.Close() to close the rows after we are done with them. This is to avoid memory leaks.
Then we iterate over the rows and scan the data into the Order struct. We check if there is an error in scanning the data and return it if there is one.
We append each order to the orders slice.
Finally we return the orders.
Retrieve a single order by id
func (r *OrderRepository) GetById(id int) (Order, error) {
var order Order
err := r.Db.QueryRow("SELECT * FROM orders WHERE id = ?", id).Scan(&order.Id, &order.Product, &order.Amount)
if err != nil {
return Order{}, err
}
return order, nil
}
Here we use the QueryRow method. Difference between the Query method and the QueryRow method is that the Query method returns multiple rows and the QueryRow method returns a single row. Then we pass the id as a placeholder to the where clause and scan the data into the Order struct.
If there is an error we return an empty Order and the error otherwise we return the order.
Update an order
func (r *OrderRepository) Update(order Order) error {
_, err := r.Db.Exec("UPDATE orders SET product = ?, amount = ? WHERE id = ?", order.Product, order.Amount, order.Id)
return err
}
UPDATE orders SET product = ?, amount = ? WHERE id = ?: Updates the product and amount columns for the row with the given id.
Delete an order
func (r *OrderRepository) Delete(id int) error {
_, err := r.Db.Exec("DELETE FROM orders WHERE id = ?", id)
return err
}
DELETE FROM orders WHERE id = ?: Deletes the row with the given id.
Testing create tables
Let's try it out. Add the following code to the main.go file:
package main
import (
"database/sql"
"log"
"sql-in-go/database"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbConnection, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal("Error opening database:", err)
}
defer dbConnection.Close()
orderRepository := &database.OrderRepository{Db: dbConnection}
err = orderRepository.CreateTable()
if err != nil {
log.Fatal("Error creating orders table:", err)
}
}
We create the OrderRepository struct and passed the database connection to it.
After that we call the CreateTable method for the repository to create the table and check if there are any errors.
Open a terminal and run the following command:
go run main.go
It should create the shop.db file and the orders table inside it.
Inserting and retrieving data
Update the main.go file to insert data into the database.
package main
import (
"database/sql"
"log"
"sql-in-go/database"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbConnection, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal("Error opening database:", err)
}
defer dbConnection.Close()
orderRepository := &database.OrderRepository{Db: dbConnection}
err = orderRepository.CreateTable()
if err != nil {
log.Fatal("Error creating orders table:", err)
}
err = orderRepository.Insert(database.Order{Product: "Laptop", Amount: 10})
if err != nil {
log.Fatal("Error inserting order:", err)
}
err = orderRepository.Insert(database.Order{Product: "Keyboard", Amount: 50})
if err != nil {
log.Fatal("Error inserting order:", err)
}
orders, err := orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
}
Updating & Deleting data
The only thing left to do is to update and delete data.
Update the main.go file to update and delete data.
package main
import (
"database/sql"
"log"
"sql-in-go/database"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbConnection, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal("Error opening database:", err)
}
defer dbConnection.Close()
orderRepository := &database.OrderRepository{Db: dbConnection}
err = orderRepository.CreateTable()
if err != nil {
log.Fatal("Error creating orders table:", err)
}
err = orderRepository.Insert(database.Order{Product: "Laptop", Amount: 10})
if err != nil {
log.Fatal("Error inserting order:", err)
}
err = orderRepository.Insert(database.Order{Product: "Keyboard", Amount: 50})
if err != nil {
log.Fatal("Error inserting order:", err)
}
orders, err := orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
order, err := orderRepository.GetById(orders[0].Id)
if err != nil {
log.Fatal("Error getting order:", err)
}
order.Amount = 1500
err = orderRepository.Update(order)
if err != nil {
log.Fatal("Error updating order:", err)
}
orders, err = orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
err = orderRepository.Delete(order.Id)
if err != nil {
log.Fatal("Error deleting order:", err)
}
orders, err = orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
}
Context & Timeout
You can use context and timeout to set a deadline for the query execution.
The Exec, Query, and QueryRow methods also have a context version that takes a context as an argument.
ExecContext, QueryContext, and QueryRowContext.
The reason why we use context and timeout is to avoid blocking the database connection for too long.
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
err := r.Db.QueryContext(ctx, "SELECT * FROM orders")
Here we get the context from the context.Background() function and set a timeout of 5 seconds for the query execution. If the query execution takes longer than 5 seconds, it will be canceled.
Conclusion
In this tutorial, you learned how to use SQL in Go (Golang) with SQLite. You learned how to create, insert, retrieve, update, and delete data from the database.
Full source code can be found here
I hope you enjoyed this tutorial and learned something new.
Originally published on
Setting up the project
Start by opening a terminal.
Initialize a new project:
go mod init sql-in-go
Install the dependencies:
Install SQLite:
go get -u github.com/mattn/go-sqlite3
To connect to the database create a new file called main.go and add the following code:
package main
import (
"database/sql"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
}
We use the sql.Open function to open a connection to the database. The first argument is the driver name and the second argument is the database file path or the database connection string depending on the database.
We check if there is an error in opening the database connection. If there is an error, we log the error and exit the program.
The defer db.Close() ensures the database connection is closed when we are done.
Common database operations
Here is a list of the queries we will use:
| Operation | Description | Example |
|---|---|---|
| Create | Create a new table if it does not exist | CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, product TEXT, amount INTEGER); |
| Insert | Insert new data into a table | INSERT INTO orders (product, amount) VALUES ('Laptop', 1000); |
| Query all | Retrieve all data from a table | SELECT * FROM orders; |
| Query with condition | Retrieve rows matching a condition from a table | SELECT * FROM orders WHERE id = 1; |
| Update | Modify existing data in a table | UPDATE orders SET amount = 1500 WHERE id = 1; |
| Delete | Remove data from a table | DELETE FROM orders WHERE id = 1; |
Creating the OrderRepository struct
Create new folder called database and inside create a file called orders.go.
package database
import "database/sql"
type OrderRepository struct {
Db *sql.DB
}
type Order struct {
Id int
Product string
Amount int
}
We created a OrderRepository struct that holds the Db connection. This allows us to use it in the methods we will create. We also created an Order struct that will hold the order data.
Creating the order table
Let's add a method to create the order table.
func (r *OrderRepository) CreateTable() error {
_, err := r.Db.Exec(`CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product TEXT,
amount INTEGER
)`)
return err
}
We use the Exec method to execute the query. The Exec method is used for queries that don't return any data.
- CREATE TABLE IF NOT EXISTS: Creates a table only if it doesn't already exist.
- INTEGER PRIMARY KEY AUTOINCREMENT: Sets up a unique identifier for each row that automatically increases.
- TEXT: Represents a column for text data.
- INTEGER: Represents a column for numeric data.
You can use a GUI tool like TablePlus to view the orders table.
Download it from .
You should now be able to see the orders table in the GUI.
Inserting an order
Add a method to insert order data into the database.
func (r *OrderRepository) Insert(order Order) error {
_, err := r.Db.Exec("INSERT INTO orders (product, amount) VALUES (?, ?)", order.Product, order.Amount)
return err
}
INSERT INTO orders (product, amount) VALUES (?, ?): Inserts a new row into the orders table with the product and amount values.
The question marks are placeholders for the values we want to insert.
Always use placeholders instead of concatenating strings to avoid SQL injection.
Get all orders
func (r *OrderRepository) GetAll() ([]Order, error) {
rows, err := r.Db.Query("SELECT * FROM orders")
if err != nil {
return nil, err
}
defer rows.Close()
var orders []Order
for rows.Next() {
var order Order
err := rows.Scan(&order.Id, &order.Product, &order.Amount)
if err != nil {
return nil, err
}
orders = append(orders, order)
}
return orders, nil
}
First we query the rows from the database using the Query method. The query method is used for queries that return data. The * is a wildcard it means that we want to select all columns.
We use defer rows.Close() to close the rows after we are done with them. This is to avoid memory leaks.
Then we iterate over the rows and scan the data into the Order struct. We check if there is an error in scanning the data and return it if there is one.
We append each order to the orders slice.
Finally we return the orders.
Retrieve a single order by id
func (r *OrderRepository) GetById(id int) (Order, error) {
var order Order
err := r.Db.QueryRow("SELECT * FROM orders WHERE id = ?", id).Scan(&order.Id, &order.Product, &order.Amount)
if err != nil {
return Order{}, err
}
return order, nil
}
Here we use the QueryRow method. Difference between the Query method and the QueryRow method is that the Query method returns multiple rows and the QueryRow method returns a single row. Then we pass the id as a placeholder to the where clause and scan the data into the Order struct.
If there is an error we return an empty Order and the error otherwise we return the order.
Update an order
func (r *OrderRepository) Update(order Order) error {
_, err := r.Db.Exec("UPDATE orders SET product = ?, amount = ? WHERE id = ?", order.Product, order.Amount, order.Id)
return err
}
UPDATE orders SET product = ?, amount = ? WHERE id = ?: Updates the product and amount columns for the row with the given id.
Delete an order
func (r *OrderRepository) Delete(id int) error {
_, err := r.Db.Exec("DELETE FROM orders WHERE id = ?", id)
return err
}
DELETE FROM orders WHERE id = ?: Deletes the row with the given id.
Testing create tables
Let's try it out. Add the following code to the main.go file:
package main
import (
"database/sql"
"log"
"sql-in-go/database"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbConnection, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal("Error opening database:", err)
}
defer dbConnection.Close()
orderRepository := &database.OrderRepository{Db: dbConnection}
err = orderRepository.CreateTable()
if err != nil {
log.Fatal("Error creating orders table:", err)
}
}
We create the OrderRepository struct and passed the database connection to it.
After that we call the CreateTable method for the repository to create the table and check if there are any errors.
Open a terminal and run the following command:
go run main.go
It should create the shop.db file and the orders table inside it.
Inserting and retrieving data
Update the main.go file to insert data into the database.
package main
import (
"database/sql"
"log"
"sql-in-go/database"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbConnection, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal("Error opening database:", err)
}
defer dbConnection.Close()
orderRepository := &database.OrderRepository{Db: dbConnection}
err = orderRepository.CreateTable()
if err != nil {
log.Fatal("Error creating orders table:", err)
}
err = orderRepository.Insert(database.Order{Product: "Laptop", Amount: 10})
if err != nil {
log.Fatal("Error inserting order:", err)
}
err = orderRepository.Insert(database.Order{Product: "Keyboard", Amount: 50})
if err != nil {
log.Fatal("Error inserting order:", err)
}
orders, err := orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
}
Updating & Deleting data
The only thing left to do is to update and delete data.
Update the main.go file to update and delete data.
package main
import (
"database/sql"
"log"
"sql-in-go/database"
_ "github.com/mattn/go-sqlite3"
)
func main() {
dbConnection, err := sql.Open("sqlite3", "./shop.db")
if err != nil {
log.Fatal("Error opening database:", err)
}
defer dbConnection.Close()
orderRepository := &database.OrderRepository{Db: dbConnection}
err = orderRepository.CreateTable()
if err != nil {
log.Fatal("Error creating orders table:", err)
}
err = orderRepository.Insert(database.Order{Product: "Laptop", Amount: 10})
if err != nil {
log.Fatal("Error inserting order:", err)
}
err = orderRepository.Insert(database.Order{Product: "Keyboard", Amount: 50})
if err != nil {
log.Fatal("Error inserting order:", err)
}
orders, err := orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
order, err := orderRepository.GetById(orders[0].Id)
if err != nil {
log.Fatal("Error getting order:", err)
}
order.Amount = 1500
err = orderRepository.Update(order)
if err != nil {
log.Fatal("Error updating order:", err)
}
orders, err = orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
err = orderRepository.Delete(order.Id)
if err != nil {
log.Fatal("Error deleting order:", err)
}
orders, err = orderRepository.GetAll()
if err != nil {
log.Fatal("Error getting orders:", err)
}
log.Println(orders)
}
Context & Timeout
You can use context and timeout to set a deadline for the query execution.
The Exec, Query, and QueryRow methods also have a context version that takes a context as an argument.
ExecContext, QueryContext, and QueryRowContext.
The reason why we use context and timeout is to avoid blocking the database connection for too long.
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
err := r.Db.QueryContext(ctx, "SELECT * FROM orders")
Here we get the context from the context.Background() function and set a timeout of 5 seconds for the query execution. If the query execution takes longer than 5 seconds, it will be canceled.
Conclusion
In this tutorial, you learned how to use SQL in Go (Golang) with SQLite. You learned how to create, insert, retrieve, update, and delete data from the database.
Full source code can be found here
I hope you enjoyed this tutorial and learned something new.
Originally published on