What is Sqlite?
SQLite is a software library that provides a relational database management system (RDBMS). It is a lightweight, serverless, and self-contained database engine that allows users to store and manage data locally on a device without the need for a separate server.
SQLite is an open-source, public domain software, which means that it can be used by anyone without any license fees. It is widely used in mobile applications, desktop applications, and embedded systems where a simple, lightweight, and reliable database management system is required.
Why would I use Sqlite?
There are several reasons why you would want to use SQLite in your application:
- Easy to Use: SQLite is easy to use and can be set up quickly in your application. It has a simple and intuitive SQL syntax that makes it easy to create, read, update, and delete data (CRUD).
- Lightweight: SQLite is a small and lightweight database engine, which makes it ideal for use in mobile and embedded applications where resources are limited.
- No Server Required: SQLite is a serverless database engine, which means that you don't need to set up a separate server to use it. You can simply include the SQLite library in your application and start using it.
- Cross-Platform: SQLite is a cross-platform database engine, which means that it can run on various operating systems, including Windows, macOS, Linux, iOS, and Android.
How to set up go-sqlite3 in my Go project
To use SQLite in your Go application, you need to install the go-sqlite3 package, which is a Go wrapper for the SQLite library. Follow these steps to install go-sqlite3 in your project:
Before you can use go-sqlite3, you need to install SQLite on your system. For mac users, sqlite3 is pre-installed on your machine. For Ubuntu servers you'll be able to run the following command: sudo apt-get install sqlite3.
Install go-sqlite3 package
To install the go-sqlite3 package, you can use the following go get command to fetch and install the package: go get github.com/mattn/go-sqlite3
Create and Open a connection to the SQLite database
To create a database, in your terminal run the command sqlite3 db.sqlite. This will create the file and open the database. From here you can go ahead and exit the terminal pressing ctrl + c twice.
To open a connection to the SQLite database in Go, you'll need to use the sql.Open() function, which takes two arguments: the driver name and the data source name.
import (
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
db, err := sql.Open("sqlite3", "db.sqlite")
if err != nil {
panic(err)
}
defer db.Close()Here we are opening a connection to a SQLite database called "db.sqlite". The sql.Open() function returns a *sql.DB object, which we will use to interact with the database.
How to insert data in to your table
Prepared statements allow you to pre-compile a SQL statement and reuse it with different parameter values thus reducing the overhead of parsing and optimizing the query each time it is executed. Using prepared statements also helps prevent SQL injection attacks by automatically escaping special characters in the parameter values. To insert data using prepared statements, you can use the Prepare() method of the *sql.DB object to prepare the statement with placeholders for the values to be inserted. Then, you can use the Exec() method with the appropriate arguments to execute the statement with the desired parameter values.
p := BlogPost{}
query := `INSERT INTO posts(title, post) VALUES (?, ?)`
statement, err := s.DB.Prepare(query)
if err != nil {
return err
}
_, err = statement.Exec(p.Title, p.Content)
if err != nil {
return err
}How to query data from the table
The be low code makes use of QueryRow() method is used to execute the SQL statement and retrieve the single result, while the Scan() method is used to populate the fields of the BlogPost struct with the values returned by the query. If the query fails or does not return any results, an error is returned
item := &BlogPost{}
err := s.DB.QueryRow("SELECT * FROM posts WHERE title = ? LIMIT 1", title).
Scan(&item.Title, &item.Post)
if err != nil {
return nil, err
}Now that we've installed, inserted and queried data from the database, you'll probably want to build a Go binary, which comes with its own interesting set of problems.
Building a Go binary using go-sqlite3 and cgo
Here we're making the assumption that you want to build a Linux Go binary and are working on a mac. Because we are cross compiling and because go-sqlite3 makes use of cgo we'll need to enable the CGO_ENABLED=1 environment variable as well as pass in others to let the build process know what we desire and how to build the binary. But first we need to install zig.
Zig is a powerful cross-compiler for C and C++ and it's able to facilitate cross-compilation due to the inclusion of standard libraries. Zig also offers a flag-compatible interface for gcc which allows us to compile for linux based systems from within a mac.
Having first run brew install zig the following line should allow you to build a linux distribution binary:
CGO_ENABLED=1 GOOS=linux GOARCH=amd64 CC="zig cc -target x86_64-linux" CXX="zig c++ -target x86_64-linux" go build --tags extended .If you're looking to build for a mac only based environment then following should also work without the need for zig:
CGO_ENABLED=1 GOOS=darwin GOARCH=amd64 go build --tags extended