CODEWITHSUNDEEP
gosqlite
Computer User
Computer User

Reputation: 546

Instantiating an sqlite database with go?

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()
}

Upvotes: 13

Views: 13847

Answers (2)

Mr_Pink
Mr_Pink

Reputation: 109378

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)
}

Upvotes: 12

cd1
cd1

Reputation: 16534

You need to use the function Exec instead of Query to create a table. Take a look at the documentation for those functions:

func (*DB) Query

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) Exec

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

Upvotes: 2

Related Questions