Introduction

SQLite is a lightweight, embedded relational database that's commonly used in Go applications due to its simplicity and ease of use. In this guide, you'll learn how to perform basic database operations with SQLite in a Go application. We'll cover creating a database, creating tables, inserting, querying, updating, and deleting data. Sample code is provided for each operation to illustrate how to work with SQLite and Go effectively.


Installing SQLite Driver

To work with SQLite in Go, you'll need to install an SQLite driver. The most commonly used SQLite driver for Go is "mattn/go-sqlite3." You can install it using the following command:

go get github.com/mattn/go-sqlite3

Creating a Database

To create an SQLite database, you need to open or create a file that will serve as the database. Here's an example of creating an SQLite database:

package main
import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
    "log"
)
func main() {
    db, err := sql.Open("sqlite3", "my-database.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    // Database is created if it doesn't exist
}

Creating Tables

Tables are used to store data in an SQLite database. You can define a table schema and execute a SQL statement to create a table. Here's an example of creating a "users" table:

// Create a "users" table
_, err := db.Exec(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT,
        email TEXT
    )
`)
if err != nil {
    log.Fatal(err)
}

Inserting Data

To insert data into a table, you can use an SQL INSERT statement. Here's an example of inserting a new user into the "users" table:

// Insert a new user
_, err := db.Exec("INSERT INTO users (username, email) VALUES (?, ?)", "john_doe", "john@example.com")
if err != nil {
    log.Fatal(err)
}

Querying Data

You can use SQL SELECT statements to query data from a table. Here's an example of querying all users from the "users" table:

// Query all users
rows, err := db.Query("SELECT * FROM users")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
// Iterate through the results
for rows.Next() {
    var id int
    var username, email string
    if err := rows.Scan(&id, &username, &email); err != nil {
        log.Fatal(err)
    }
    // Process the retrieved data
}

Updating Data

To update data in a table, you can use SQL UPDATE statements. Here's an example of updating a user's email:

// Update a user's email
_, err := db.Exec("UPDATE users SET email = ? WHERE id = ?", "new_email@example.com", 1)
if err != nil {
    log.Fatal(err)
}

Deleting Data

To delete data from a table, you can use SQL DELETE statements. Here's an example of deleting a user from the "users" table:

// Delete a user
_, err := db.Exec("DELETE FROM users WHERE id = ?", 1)
if err != nil {
    log.Fatal(err)
}

Conclusion

Working with SQLite in Go for basic database operations is straightforward and efficient. In this guide, we covered creating a database, creating tables, inserting, querying, updating, and deleting data using sample code examples. You can use these fundamentals to build more complex applications with SQLite databases.


Further Resources

To further explore SQLite and Go database operations, consider the following resources: