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 SQLite 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

You'll then need a bit of SQL to create your database. Copy-paste this into a file named sql_to_json.sql:

View Source file ex26/sql_to_json.sql Only

DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
    id INTEGER PRIMARY KEY,
    name TEXT,
    address TEXT,
    city TEXT,
    zip TEXT
);

INSERT INTO customer (name, address, city, zip) values ('Zed', '1010 Blank Lane', 'San Francisco, CA', '94102');
INSERT INTO customer (name, address, city, zip) values ('Joe', '345 Blank Lane', 'San Francisco, CA', '94102');
INSERT INTO customer (name, address, city, zip) values ('Mary', '9823 Blank Lane', 'New York, NY', '07020');
INSERT INTO customer (name, address, city, zip) values ('Alfred', '2222 Blank Lane', 'New York, NY', '07020');
INSERT INTO customer (name, address, city, zip) values ('Gonzo', '874 Blank Lane', 'Salt Lake City, UT', '84044');
INSERT INTO customer (name, address, city, zip) values ('Blaine', '3488 Blank Lane', 'Salt Lake City, UT', '84044');
INSERT INTO customer (name, address, city, zip) values ('Justine', '010102 Blank Lane', 'Chicago, IL', '60007');
INSERT INTO customer (name, address, city, zip) values ('Alberta', '65 Blank Lane', 'Chicago, IL', '60007');

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

// WARNING: Don't forget to run: go mod tidy
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) }
}

WARNING You need to run go mod tidy to have go download all of the modules this code uses.

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 erase all of the data in customer to get a fresh table:

sqlite3 db.sqlite3 "delete from customer"

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

// NOTE: don't forget to clear the database
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.

Checking the Database

You can now run this command to confirm the database has contents:

sqlite3 db.sqlite3 "select * from customer"

You may want to do a full test like this as well:

sqlite3 db.sqlite3 "delete from customer"
sqlite3 db.sqlite3 "select * from customer"
# you should see nothing here
./ex26
sqlite3 db.sqlite3 "select * from customer"
# now you should see contents

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.