Tutorial 18: Working with SQLite (or, Giving your data a permanent home)

If you've been following these tutorials, your program is finally doing real work, but it's still ephemeral—the moment the process dies, all that hard-won information vanishes into the void. It’s time to move beyond simple JSON files and into the world of persistent, queryable data. We're talking about SQLite.

Opening a connection

std/db is our interface for talking to the database. It handles the heavy lifting, but remember that a connection is a resource—you open it, you use it, and you'd better close it, or the system will eventually stop talking to you.

module main

import "std/db"

fn main() -> int {
    // Open the database file. If it doesn't exist, SQLite will create it.
    conn, err := db.Open("sqlite3", "todos.db")
    if err.message != "" {
        print("Couldn't open the DB: {err.message}")
        return 1
    }

    // SQL is the language we use to tell the DB what we want
    _, rerr := conn.Exec("CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, text TEXT)")
    if rerr.message != "" {
        print("Failed to initialize: {rerr.message}")
        conn.Close()
        return 1
    }

    conn.Close()
    return 0
}

Inserting data

When you're inserting, you're using Exec. A quick word of advice: always use db.QuoteString. If you try to build your SQL strings by hand, you are inviting the world's most frustrating security vulnerabilities into your project.

title := "Learn Skink"
// Always sanitize your inputs. Always.
sql := "INSERT INTO todos (text) VALUES (" + db.QuoteString(title) + ")"
_, err := conn.Exec(sql)
if err.message != "" {
    print("Insert went south: {err.message}")
    return 1
}

Querying data

Querying is a bit more involved because the database gives you back a cursor. You walk through it row by row, scanning the values into your variables. It’s methodical, reliable, and it beats guessing what’s in your files.

result, err := conn.Query("SELECT id, text FROM todos")
if err.message != "" {
    print("Query failed: {err.message}")
    return 1
}

for result.Next() {
    // Columns are indexed starting at 0
    id, _ := result.Scan(0)
    text, _ := result.Scan(1)
    print("{id}: {text}")
}

A word on housekeeping

SQLite is sturdy, but it's not magic. If you forget to conn.Close(), you might find your database locked or your file handles leaking. It’s the small stuff that keeps the system running for years.

Exercises

1. The Status Update: Modify your todos table to include a done integer column. It’s the first step toward actual task management.
2. The Helper: Write a dedicated function addTodo(conn: *db.Connection, text: string) to wrap your insertion logic. It’ll make your main function much cleaner.
3. The List: Write a function listTodos(conn: *db.Connection) that iterates through your database and prints every row. It's immensely satisfying to see your data live on after you kill the program.

Next

When you're ready to start looking for patterns in your data—or just trying to figure out if that user input is actually a valid email address—we'll talk about Pattern matching with regex.