Golang SQLite3: Working with SQLite in Go

Have you ever needed to store or access data from a lightweight database in your Go applications? Look no further than SQLite, the self-contained, serverless, zero-configuration SQL database engine. SQLite is perfect for situations where you need reliable data storage that doesn’t require running a separate database server process.

In this comprehensive guide, you’ll learn how to integrate SQLite into your Golang applications using the sqlite3 package. We’ll cover everything from creating tables and inserting data to running complex queries and transactions.

Whether you’re building a desktop app, web service, or any other Go program needing simple local data storage, mastering Golang’s SQLite3 capabilities is a must-have skillset for your toolbox.

We’ll be using real-world analytics dataset examples throughout this guide to demonstrate practical usage of SQLite in the Go language. So strap in, and let’s get building!

Key Advantages of Using SQLite in Go

Before we dig into the code, let’s briefly highlight why SQLite is an excellent fit for many Golang use cases:

• Lightweight – No need to run a separate database server process
• Self-Contained – Stores entire database in a single cross-platform disk file
• Serverless – Ideal for desktop apps and devices like mobile phones
• Reliable – Atomic commit by default to avoid data corruption
• Full-featured – Supports most standard SQL like other database engines
• Zero Config – Get up and running in minutes with minimal setup
• Battle-tested – Used in many commercial products and runs on nearly all OSes

With embedded use cases in mind like mobile apps, game data, device storage, kiosks and more, SQLite checks many of the right boxes. Luckily for Go developers, interfacing with it is straightforward using the sqlite3 package.

Also read: 7 Advantages of SQLite

Now, let’s get coding!

Installing SQLite3 Driver in Go

We’ll be using the official sqlite3 library packaged with Golang to work with SQLite databases.

First, make sure you have Golang installed and configured on your system. SQLite3 support comes out of the box, so no need to explicitly install additional packages.

Just import "database/sqlite" in your code, and you’re ready to roll.

import (
  "database/sqlite"
  // ...other imports
)

That’s it! Now we can focus on the programmatic usage of SQLite in Go.

Creating a New SQLite Database File

First things first – we need an actual SQLite file on disk to store our database contents.

This is as simple as specifying the desired file path when creating your new SQLite connection:

db, err := sqlite.Open("analytics.db")

This will create a new (or open an existing) SQLite database file called analytics.db in the current working directory of your app.

You can specify absolute or relative paths here as needed. Generally a good idea to use relative and keep the file bundled within your application directory structure.

Opening & Closing the SQLite Database Connection

Now that we’ve initialized the SQLite database file on disk, we need to use the returned *Sqlite.DB connection pool handle for running queries and statements:

db, err := sqlite.Open("analytics.db")
defer db.Close()

This pattern ensures the database connection pool is closed adequately after we finish executing ideas.

Having open unused connections hanging around can lead to resource leaks. So always defer db.Close() after opening your SQLite database.

With that foundation in place, let’s move on to the good stuff – creating tables!

Creating SQLite Tables in Go

The structural foundation of any relational database is its schema – the tables and other objects that shape the storage layout.

Here’s an example of creating a simple analytics events table with ID, source, and payload columns:

sql := `CREATE TABLE events (
          id INTEGER PRIMARY KEY,  
          source TEXT NOT NULL,
          payload JSON NOT NULL
       );`

_, err := db.Exec(sql)

We use the db.Exec() method to execute the SQL creation statement. This makes the new blank events table ready for us to start inserting data.

Let’s build off this idea and create an example database to track analytics events from different systems:

sql := `CREATE TABLE analytics (
          id INTEGER PRIMARY KEY,
          user_id INT NOT NULL,  
          event_type TEXT NOT NULL,
          properties JSON NOT NULL,
          timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
       );`

_, err := db.Exec(sql)

Here we’ve set up columns to capture user_id, event_type, event properties JSON, and automatic timestamp on each event record inserted.

This analytics table is now ready for us to track user activity events!

Inserting Data into SQLite Tables

Creating empty tables is only part of the fun. Now let’s actually insert some rows of sample analytical data:

stmt, err := db.Prepare("INSERT INTO analytics(user_id, event_type, properties) values(?, ?, ?)")

// User 123 triggered Search Event  
_, err = stmt.Exec(123, "search", `{"query":"Golang SQLite"}`)

// User 456 triggered Purchase Event
_, err = stmt.Exec(456, "purchase", `{"amount": 59.99}`) 

Here we:

1. Prepare an INSERT statement
2. Execute multiple inserts binding user_id, event_type, properties params

This is the most common way to insert parameterized data into a SQLite database from Go.

We can confirm these new rows were successfully added:

SELECT * FROM analytics;

id          user_id     event_type  properties                  timestamp
----------  ----------  ----------  ---------------------------  ------------------------------------
1           123         search      {"query":"Golang SQLite"}    2023-01-22 17:32:28
2           456         purchase    {"amount": 59.99}            2023-01-22 19:42:53

And there we have it – populating our analytics database and tracking user events!

Next, let’s explore running queries to access this data.

Querying Data with SQLite3 in Golang

Once tables contain rows of information, we need easy ways to query that data.

Let’s revisit our analytics database and pull some sample reports:

Fetch All User Search Events

rows, err := db.Query("SELECT * FROM analytics WHERE event_type=?", "search")
defer rows.Close() 

for rows.Next() {
  var id int
  var user_id int  
  var event_type string
  var properties string
  var timestamp time.Time 
  
  err := rows.Scan(&id, &user_id, &event_type, &properties, &timestamp)
  
  fmt.Printf("User %d performed search: %s\n", user_id, properties)  
}

Here we:

1. Parameterized query for all search events
2. Iterate result rows
3. Scan row data into output variables
4. Print details like user_id and search query

This allows quickly pulling analytic segments from the underlying data.

Report Monthly New Users

Let’s expand on the idea with a more complex monthly cohort query:

SELECT 
  strftime('%Y-%m', timestamp) AS month,
  COUNT(DISTINCT user_id) AS new_users
FROM analytics  
GROUP BY month
ORDER BY month;

This performs:

• Date truncation into months
• Distinct user counting
• Monthly cohorts

Enabling segmented analytics – new users over time, usage retention, etc.

The possibilities are vast once we start leveraging aggregate SQL capabilities!

Updating & Deleting SQLite Records

So far we’ve created tables, inserted new rows, and run SELECT queries. But data changes frequently, so let’s discuss updating and removing records next.

Updating Rows

stmt, err := db.Prepare("UPDATE analytics SET properties = ? WHERE id = ?")

_, err = stmt.Exec(
  `{"query":"Golang SQLite Tutorial"}`, 1) 

Here we UPDATE matching rows by id, binding new parameter values.

Deleting Rows

result, err := db.Exec("DELETE FROM analytics WHERE id = ?", 2)

rowsAffected, err := result.RowsAffected() // = 1

Similarly, we DELETE by id, returning the count of affected rows.

This makes modifying existing records a breeze!

Transactions: Maintaining Data Integrity

When making multiple changes, we often want atomic transactions to guarantee database consistency:

tx, err := db.Begin()

_, err = tx.Exec("INSERT INTO users...")
_, err = tx.Exec("INSERT INTO events...") 

if err != nil {
  tx.Rollback() // abort tx on error  
} else {
  tx.Commit() // attempt commit     
}

This wraps multiple statement executions in a transaction, maintaining ACID compliance:

• Atomic – all or nothing execution
• Consistent – valid data state after commit
• Isolated – intermediate state hidden from others
• Durable – changes persisted after commit

Keeping your data integrity intact!

Comparison of Popular Go SQLite3 Libraries

There are a few decent 3rd party SQLite packages beyond the standard library:

For most purposes, sticking with the vanilla database/sqlite driver gets the job done. But libraries like go-sqlite3 are great for advanced needs like greater speed.

Now you know the options available for integrating SQLite into your Go apps!

Summary – Key Takeaways

We’ve covered end-to-end usage of SQLite from Go – from installation through CRUD operations. Here are the key concepts we discussed:

• Lightweight Serverless DB – SQLite is perfect for local data in apps & devices
• Create Database Files – Simple file paths get you started
• Table Schemas – Define column data types with SQL
• Insert Data – Parameterized statements avoid SQLi risks
• Query Data – Pull user events, segments, reports, and more
• Update/Delete – Modify existing records
• Transactions – Maintain data integrity
• Robust Ecosystem – Multiple package options for needs

With these techniques under your belt, get ready to build feature-rich Go applications powered by embedded SQLite databases!

The serverless local data storage helps simplify architecture-no network ops needed! And the productive SQL interface helps ramp developers of all skill levels quickly.

So next time your Go app needs reliable local data persistence, reach for SQLite3 to empower your application needs.