26: Processing Data

WARNING This exercise is in DRAFT status, so there may be errors. If you find any, please email me at help@learncodethehardway.com so I can fix them.

I'm now going to have you apply what you know about struct tags from Exercise 25 to the problem of loading and exporting a database. In this exercise you'll use three packages to access a Sqlite3 database:

• go-sqlite3 -- This provides access to Sqlite3 databases.
• sqlx -- This is a nice API for SQL databases.
• Go's official database/sql -- We don't access this in our code, but it's used by the others under the hood and comes up sometimes when you use them.

Take some time now to study those libraries, use their documentation to create their examples.

SQL->JSON Setup

We need to do some setup before you can start. First, make sure you have sqlite3 available on your computer:

sqlite3 --version

If you get an error then you need to install it. Next you need the sql_to_json.sql file to kick off your db.sqlite3. You load it like this:

sqlite3 db.sqlite3 -init "sql_to_json.sql"

When you run this it will prompt you and wait. Just type .quit to exit sqlite3.

With that your db.sqlite3 is configured to have one table named customer and that table will have some data to play with in the following code.

SQL->JSON Code

We can now write the code for the SQL database to .json converter. I recommend making a new project named something like ex26_sql_to_json.

View Source file ex26/main.go Only

package main

import (
    "log"
    _ "github.com/mattn/go-sqlite3"
    "github.com/jmoiron/sqlx"
    "encoding/json"
    "os"
)

type Customer struct {
    Id int64 `db:"id"`
    Name string `db:"name"`
    Address string `db:"address"`
    City string `db:"city"`
    Zip string `db:"zip"`
}

func main() {
    db, err := sqlx.Connect("sqlite3", "db.sqlite3")
    if err != nil { log.Fatal(err) }

    customers := []Customer{}
    err = db.Select(&customers, "SELECT * FROM customer ORDER BY name ASC")
    if err != nil { log.Fatal(err) }

    json_out, err := json.MarshalIndent(customers, "", "  ")
    if err != nil { log.Fatal(err) }

    err = os.WriteFile("customers.json", json_out, 0644)
    if err != nil { log.Fatal(err) }
}

In this code there shouldn't be anything new other than the sqlx package. At this point in our programming adventure I'm hoping you can figure out how it works, and if not the sqlx documentation is reasonable.

Time to fly little bird. Your wings are grown.

JSON->SQL Code

Before you write this code you need recreate the db.sqlite3 file to have no data in it. Download this new json_to_sql.sql and do this:

rm db.sqlite3
sqlite3 db.sqlite3 -init "json_to_sql.sql"
.quit

That last .quit is to get out of sqlite3. Once you've done this you'll have a database with the correct schema, but no data.

Now that you have a customers.json file from the previous code, you can create another tool that loads .json files into the database. I'd make a new project named something like ex26a.

View Source file ex26a/main.go Only

package main

import (
    "log"
    _ "github.com/mattn/go-sqlite3"
    "github.com/jmoiron/sqlx"
    "encoding/json"
    "os"
)

type Customer struct {
    Id int64 `db:"id"`
    Name string `db:"name"`
    Address string `db:"address"`
    City string `db:"city"`
    Zip string `db:"zip"`
}

func main() {
    customers := []Customer{}
    json_data, err := os.ReadFile("customers.json")
    if err != nil { log.Fatal(err) }

    err = json.Unmarshal(json_data, &customers)
    if err != nil { log.Fatal(err) }

    db, err := sqlx.Connect("sqlite3", "db.sqlite3")
    if err != nil { log.Fatal(err) }

    tx := db.MustBegin()
    for _, customer := range customers {
        _, err = tx.NamedExec(`INSERT INTO customer
                (name, address, city, zip) VALUES
                (:name, :address, :city, :zip)`, customer)

        if err != nil { log.Fatal(err) }
    }
    tx.Commit()
}

The most important lines to study in this code are:

_, err = tx.NamedExec(`INSERT INTO customer
        (name, address, city, zip) VALUES
        (:name, :address, :city, :zip)`, customer)

WARNING! This is not multiple lines, it is one single line. The INSERT INTO customer is actually inside a multi-line string so it carries on, and if you "flatten" it you would have this:

INSERT INTO customer (name, address, city, zip) VALUES (:name, :address, :city, :zip)

Other than that, this is another example of data conversion using Go's wonderful struct tags. We'll be getting quite a lot of value out of these in later modules for the course.

The Practice

1. Break It -- As usual, the way to break this kind of code is to destroy the data inputs. Try running the SQL->JSON converter but don't initialize the database. Break the JSON->SQL version by mangling the customers.json file.
2. Change It -- If you know some SQL try to add another table and load that as well, or add a field to store.
3. Recreate It -- As with the previous exercise, I feel you'd do better coming up with your own data and writing a converter for it. If you're out of ideas then go ahead and try to recreate this, but keep in mind it's large so may take a while.

Study Drills

1. Re-read the Go struct specification and see if it makes more sense then when you first read it.
2. Try rewriting this code to use XML instead of JSON.