Instantiating an sqlite database with go?

Question

I have extracted the code below from my application. I am trying to create a new directory, put an sqlite database in it, then create a table in the database.

Currently it creates the directory, a file for the db to exist in and it runs without any errors. However, the db file is empty. I cannot figure out why it is empty.

Does anyone know how to modify this code so the content remains in the database?

package main

import (
    "os"
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    os.MkdirAll("./data/1234", 0755)
    os.Create("./data/1234/data.db")

    db, err := sql.Open("sqlite3", "./data/1234/data.db")
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    _, err = db.Query("CREATE TABLE `customers` (`till_id` INTEGER PRIMARY KEY AUTOINCREMENT, `client_id` VARCHAR(64) NULL, `first_name` VARCHAR(255) NOT NULL, `last_name` VARCHAR(255) NOT NULL, `guid` VARCHAR(255) NULL, `dob` DATETIME NULL, `type` VARCHAR(1))")
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    db.Close()
}

Answer 1

You're not closing the database, so the changes aren't flushed to the database file.

Make sure you execute db.Close().

You then need to use the Exec method, not Query to modify the database.

_, err = db.Exec("CREATE TABLE `customers` (`till_id` INTEGER PRIMARY KEY AUTOINCREMENT, `client_id` VARCHAR(64) NULL, `first_name` VARCHAR(255) NOT NULL, `last_name` VARCHAR(255) NOT NULL, `guid` VARCHAR(255) NULL, `dob` DATETIME NULL, `type` VARCHAR(1))")
if err != nil {
    log.Fatal(err)
}