Creating Go programs that use Cloud SQL

9/12/15: The code works in this section, but there's little explanatory text because I'm doing the examples first. Explanations will come later

Example programs in this chapter

Example 1: Open a Cloud SQL database and create a table
Example 2: Open a Cloud SQL database and create a table through command-line options
Example 3: Create a record in a Cloud SQL database
Example 4: Search for records in a Cloud SQL database

Obtaining the MySQL driver using go get

To use MySQL with Go you need a driver. Go MySQL Driver is a popular and well-supported one.

# May need to precede with sudo
$ go get github.com/go-sql-driver/mysql

go get "status 69" error message

If you get a status 69 error message as shown below, precede the go get statement with sudo and enter your adminstrative password when asked.

# cd .; git clone https://github.com/go-sql-driver/mysql /Users/tom/go/src/github.com/go-sql-driver/mysql

Agreeing to the Xcode/iOS license requires admin privileges, please re-run as root via sudo.

package github.com/go-sql-driver/mysql: exit status 69

Example cloudsql1.go: Create a Google Cloud SQL table with options set at the command line

For me, half the battle was just getting a working sample of connecting to CloudSQL-the sql.Open() examples that I could find never showed the real thing.

This shows how to create the right connection string, open a database connection, create a table named exercisemysql1, and exit.

The values used for the user name, database name, and so on are const strings in which you will overwrite the values with your own confidential ones:

    // REPLACE WITH YOUR OWN VALUES
    // User created in Cloud SQL dashboard
    const dbUserName = "sampleuser"

    // Database created in Cloud SQL dashboard
    const dbName = "tomsdatabase"

    // Password for Cloud SQL database
    const dbPassword = "Samplepassword45"

    // IP address of Cloud SQL database
    const dbIP = "tcp(173.194.255.122:3306)"

They will be generated in this format for the call to sql.Open():

sampleuser:Samplepassword45@tcp(173.194.255.122:3306)/tomsdatabase

// cloudsql1.go - Creates Google Cloud SQL table
package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

func main() {
    const dbUserName = "sampleuser"
    const dbName = "tomsdatabase"
    //const dbIP = "2001:4860:4864:1:de34:1928:6ae4:7058"
    const dbIP = "tcp(169.191.255.122:3306)"
    const dbOpenString = dbUserName + ":" + dbPassword + "@" + dbIP + "/" +
    db, err := sql.Open("mysql", dbOpenString);
    if err != nil {
        log.Println("sql.Open(" +
            dbOpenString +
            "\"mysql, \"")
    }
    defer db.Close()
    log.Println("Pinging database. This may take a moment.")
    err = db.Ping()
    if err != nil {
        log.Println("db.Ping() call failed:");
        log.Println(err)
    }
    _, err = db.Exec(
        `CREATE TABLE IF NOT EXISTS exercisecloudsql101
        (id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        description TEXT, PRIMARY KEY (id))`)

    if err != nil {
        log.Println("CREATE TABLE failed:")
        log.Println(err) }
    }
`

Example cloudsql2.go: Create a Google Cloud SQL table with options set at the command line

The example cloudsql2.go shows how to create the right connection string, open a database connection, create a table named exercisemysql2, and exit. That's if you use the default values. All options are now configurable via command line, so, for example, this would create a sample table named customer.

$ ./cloudsql2 -table customer

`

// cloudsql2.go - Creates Google Cloud SQL database
// Can pass the following as command line parameters:
// Google Cloud SQL user name
// Google Cloud SQL database name
// Google Cloud SQL password
// IP address of Google Cloud SQL database instance
// Google Cloud SQL port to use for connection
// Name of table to create inside the database

// Examples:
// $ ./cloudsql2 -table customer
// Default:
// db, err := sql.Open(
//   "mysql",
//   "sampleuser:Foobar1@tcp(173.194.255.122:3306)/tomsdatabase"

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
    "flag"
)

func main() {

    // Command-line flags

    var cmdLine string;

    // Username, like mysql -u option
    uOption := flag.String("u", "sampleuser", "Database username, equivalent to mysql -u option")

    // Database name, similar to mysql -database options
    databaseOption := flag.String("database", "tomsdatabase", "CloudSQL database to open")

    // Password for Cloud SQL database, similar to the mysql -p option.
    passwordOption := flag.String("password", "Foobar1", "Cloud SQL port to use for connection")

    // IP address of Cloud SQL database instance to connect to, like mysql --bind-address option
    bindAddressOption := flag.String("bind-address", "173.194.255.122", "--bind-address is the IP address of the Cloud SQL server instance")

    // Server's port number to use for connection. Equivalent to mysql --port option
    portOption := flag.String("port", "3306", "--port is the port used on the Cloud SQL server instance");

    // Name of table to create
    tableName := flag.String("table", "exercisecloudsql2", "--table is the name of the table to create")

    // Parse command-line flags. Use default values or replace with those specified.
    flag.Parse();

    // Build intializer for sqlOpen() and display it for educational purposes.
    cmdLine = *uOption+":"+*passwordOption+"@tcp("+*bindAddressOption+":"+*portOption+")/"+*databaseOption
    log.Println("sqlOpen(): " + cmdLine + "\n"); 

    // Create database connection & 
    // create connection pool.
    // Does not do an actual database open, despite the name. 
    // Do this only once.
    db, err := sql.Open("mysql",
        cmdLine)    

    // Check for error before the defer.
    if err != nil {
        log.Println(err)
    }

    // Close connection & return database resources
    // when function exits. Normally a database
    // connection lasts much longer.
    defer db.Close()

    // Verify the data source is valid.
    log.Println("Pinging the database. This may take a moment...")
    err = db.Ping()
    if err != nil {
        log.Println(err)
    } else {
        // Create a table named by the --table option.
        _, err = db.Exec(
            "CREATE TABLE IF NOT EXISTS " + *tableName + " " +
            "(id INT NOT NULL AUTO_INCREMENT, " + 
            "name VARCHAR(100) NOT NULL, " + 
            "description TEXT, PRIMARY KEY (id))")

        if err != nil {
            log.Println(err)
        }
    }
}
`

Example cloudsql3.go: Create a Google Cloud SQL table and add a record, with options set at the command line

The example cloudsql3.go builds on the previous example and now inserts a record into the database.

// cloudsql3.go - Creates Google Cloud SQL database table &
// adds a record
// Can pass the following as command line parameters:
// Google Cloud SQL user name
// Google Cloud SQL database name
// Google Cloud SQL password
// IP address of Google Cloud SQL database instance
// Google Cloud SQL port to use for connection
// Name of table to create inside the database

// Examples:
// $ ./cloudsql3 -table customer

//
// $
// Default:
// sampleuser:Foobar1@tcp(173.194.255.122:3306)/tomsdatabase

package main

import (
    "database/sql"
    "flag"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

func main() {

    // Command-line flags

    var cmdLine string
    // Username, like mysql -u option
    uOption := flag.String(
        "u",
        "sampleuser",
        "Database username, equivalent to mysql -u option")

    // Database name, similar to mysql -database options
    databaseOption := flag.String(
        "database",
        "tomsdatabase",
        "CloudSQL database to open")

    // Password for Cloud SQL database, similar to the
    // mysql -p option.
    passwordOption := flag.String(
        "password",
        "Foobar1",
        "Cloud SQL port to use for connection")

    // IP address of Cloud SQL database instance to
    // connect to, like mysql --bind-address option
    bindAddressOption := flag.String(
        "bind-address",
        "173.194.256.122",
        "--bind-address IP addrs of Cloud SQL instance")

    // Server's port number to use for connection.
    // Equivalent to mysql --port option
    portOption := flag.String(
        "port",
        "3306",
        "--port Port used on the Cloud SQL instance")

    // Name of table to create
    tableName := flag.String(
        "table",
        "exercisecloudsql3",
        "--table Name of database table to create")

    // Parse command-line flags.
    // Use default values or replace with those specified.
    flag.Parse()

    // Build intializer for sqlOpen() and display it for
    // educational purposes.
    cmdLine =
        *uOption + ":" +
            *passwordOption +
            "@tcp(" + *bindAddressOption +
            ":" + *portOption + ")/" +
            *databaseOption

    log.Println("sqlOpen(): " + cmdLine + "\n")

    // Create database connection &
    // create connection pool.
    // Does not do an actual database open, despite its name
    db, err := sql.Open("mysql", cmdLine)
    // Check for error before the defer.
    if err != nil {
        log.Println(err)
    }
    // Close connection & return database resources
    // when function exits. Normally a database
    // connection lasts much longer.
    defer db.Close()
    // Verify the data source is valid.
    log.Println("Opening database. Please wait...")
    err = db.Ping()
    if err != nil {
        log.Println(err)
    } else {
        // Create a table named by the --table option.
        _, err = db.Exec(
            "CREATE TABLE IF NOT EXISTS " + 
                *tableName + " " +
                "(id INT NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100) NOT NULL, " +
                "description TEXT, PRIMARY KEY (id))")
        if err != nil {
            log.Println(err)
        }
    }

    // Prepare to add a record. Leave placeholders. 
    // These will be used to fill in both 
    // non autoincrement fields.
    stmt, err := db.Prepare("INSERT INTO " +
        *tableName + "(name, description) VALUES(?, ?)")
    if err != nil {
        log.Fatal(err)
    }

    // Insert data where the placeholders were.
    res, err := stmt.Exec(
        "Cajun Mens Boots",
        "Swamp tested and alligator approved")
    if err != nil {
        log.Fatal(err)
    }

    lastId, err := res.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }

    // First variable is # of rows changed by 
    // this operation.
    _, err = res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    log.Printf("Updated record # %d.\n", lastId)
}

`

Example cloudsql4.go: Query a Google Cloud SQL table and display the results.

The example cloudsql4.go inserts a record into a database, then searches for that record with a SQL query, showing all returned rows.

// cloudsql4.go - Creates Google Cloud SQL database table &
// adds a record, then does a query and returns all matching rows.
// TODO:
// Document the query it does
// Maybe add some better sample data since right now
// it's just a bunch of duplicated records.
// Can pass the following as command line parameters:
// Google Cloud SQL user name
// Google Cloud SQL database name
// Google Cloud SQL password
// IP address of Google Cloud SQL database instance
// Google Cloud SQL port to use for connection
// Name of table to create inside the database

// Examples:
// $ ./cloudsql4 -table customer

//
// $
// Default:
// sampleuser:Foobar1@tcp(173.194.255.122:3306)/tomsdatabase

package main

import (
    "fmt"
   //"io/ioutil"
    "database/sql"
    "flag"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type Product struct {
    Name string
    Description []byte
}


func main() {

    // Command-line flags

    var cmdLine string
    // Username, like mysql -u option
    uOption := flag.String(
        "u",
        "sampleuser",
        "Database username, equivalent to mysql -u option")

    // Database name, similar to mysql -database options
    databaseOption := flag.String(
        "database",
        "tomsdatabase",
        "CloudSQL database to open")

    // Password for Cloud SQL database, similar to the
    // mysql -p option.
    passwordOption := flag.String(
        "password",
        "Foobar1",
        "Cloud SQL port to use for connection")

    // IP address of Cloud SQL database instance to
    // connect to, like mysql --bind-address option
    bindAddressOption := flag.String(
        "bind-address",
        "173.194.256.122",
        "--bind-address IP addrs of Cloud SQL instance")

    // Server's port number to us:e for connection.
    // Equivalent to mysql --port option
    portOption := flag.String(
        "port",
        "3306",
        "--port Port used on the Cloud SQL instance")

    // Name of table to create
    tableName := flag.String(
        "table",
        "exercisemysql3",
        "--table Name of database table to create")

    // Parse command-line flags.
    // Use default values or replace with those specified.
    flag.Parse()

    // Build intializer for sqlOpen() and display it for
    // educational purposes.
    cmdLine =
        *uOption + ":" +
            *passwordOption +
            "@tcp(" + *bindAddressOption +
            ":" + *portOption + ")/" +
            *databaseOption

    log.Println("sqlOpen(): " + cmdLine + "\n")

    // Create database connection &
    // create connection pool.
    // Does not do an actual database open, despite its name
    db, err := sql.Open("mysql", cmdLine)
    // Check for error before the defer.
    if err != nil {
        log.Println(err)
    }
    // Close connection & return database resources
    // when function exits. Normally a database
    // connection lasts much longer.
    defer db.Close()
    // Verify the data source is valid.
    log.Println("Opening database. Please wait...")
    err = db.Ping()
    if err != nil {
        log.Println(err)
    } else {
        // Create a table named by the --table option.
        _, err = db.Exec(
            "CREATE TABLE IF NOT EXISTS " + 
                *tableName + " " +
                "(id INT NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100) NOT NULL, " +
                "description TEXT, PRIMARY KEY (id))")
        if err != nil {
            log.Println(err)
        }
    }

    // Prepare to add a record. Leave placeholders. 
    // These will be used to fill in both 
    // non autoincrement fields.
    stmt, err := db.Prepare("INSERT INTO " +
        *tableName + "(name, description) VALUES(?, ?)")
    if err != nil {
        log.Fatal(err)
    }

    // Insert data where the placeholders were.
    res, err := stmt.Exec(
        "Cajun Mens Boots",
        "Swamp tested and alligator approved")
    if err != nil {
        log.Fatal(err)
    }

    lastId, err := res.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }

    // First variable is # of rows changed by 
    // this operation.
    _, err = res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    log.Printf("Updated record # %d.\n", lastId)


    rows, err := db.Query(
        "SELECT * from " +
        *tableName + " " +
        "WHERE NAME LIKE '%Cajun%'")
    if err != nil {
        log.Fatal(err)
    }

    for rows.Next() {
        var description string
        var id int
        var name string
        if err := rows.Scan(&id, &name, &description); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%s\n", name)
    }   

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
}

`

Example cloudsql6.go: Search Google Cloud SQL table and displaly matching records

The example cloudsql6.go Creates a table, inserts some sample records, and searches for a value in that table.

// cloudsql6.go - Creates Google Cloud SQL database table &
// adds multiple records, queries for the word Cajun
// (can change on command line) and returns all matching rows.
// TODO:
// Document the query it does
// Maybe add some better sample data since right now
// it's just a bunch of duplicated records.
// Can pass the following as command line parameters:
// Google Cloud SQL user name
// Google Cloud SQL database name
// Google Cloud SQL password
// IP address of Google Cloud SQL database instance
// Google Cloud SQL port to use for connection
// Name of table to create inside the database

// Examples:
// $ ./cloudsql6 -table customer
// $ ./cloudsql6 --bind-address "173.194.255.122" --search Camouflage
//
// Default:
// sampleuser:Foobar1@tcp(173.194.255.122:3306)/tomsdatabase

package main

import (
    "fmt"
    "database/sql"
    "flag"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

// Go representation of database record for a Product item
type Product struct {
    Name        string
    Description []byte
}

func main() {


    // Command-line flags
    // Username, like mysql -u option
    uOption := flag.String(
        "u",
        "sampleuser",
        "Database username, equivalent to mysql -u option")

    // Database name, similar to mysql -database options
    databaseOption := flag.String(
        "database",
        "tomsdatabase",
        "CloudSQL database to open")

    // Password for Cloud SQL database, similar to the
    // mysql -p option.
    passwordOption := flag.String(
        "password",
        "Foobar1",
        "Cloud SQL port to use for connection")

    // IP address of Cloud SQL database instance to
    // connect to, like mysql --bind-address option
    bindAddressOption := flag.String(
        "bind-address",
        "173.194.256.122",
        "--bind-address IP addrs of Cloud SQL instance")

    // Server's port number to us:e for connection.
    // Equivalent to mysql --port option
    portOption := flag.String(
        "port",
        "3306",
        "--port Port used on the Cloud SQL instance")

    // Name of table to create
    tableName := flag.String(
        "table",
        "exercisemysql6",
        "--table Name of database table to create")

    // Search term (search is run after records are added
    // to database).
    searchTerm := flag.String(
        "search",
        "Cajun",
        "--Search for this after records added to database")
    // Parse command-line flags.
    // Use default values or replace with those specified.
    flag.Parse()

    // Build parameter string for sqlOpen() and display it for
    // educational purposes.
    cmdLine :=
        *uOption + ":" +
            *passwordOption +
            "@tcp(" + *bindAddressOption +
            ":" + *portOption + ")/" +
            *databaseOption

    log.Println("sqlOpen(): " + cmdLine + "\n")

    // Create database connection &
    // create connection pool.
    // Does not do an actual database open, despite its name
    db, err := sql.Open("mysql", cmdLine)
    // Check for error before the defer.
    if err != nil {
        log.Println(err)
    }

    // Close connection & return database resources
    // when function exits. Normally a database
    // connection lasts much longer.
    defer db.Close()
    // Verify the data source is valid.
    log.Println("Opening database. Please wait...")
    err = db.Ping()
    if err != nil {
        log.Println(err)
    } else {
        // Create a table named by the --table option.
        _, err = db.Exec(
            "CREATE TABLE IF NOT EXISTS " +
                *tableName + " " +
                "(id INT NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(100) NOT NULL, " +
                "description TEXT, PRIMARY KEY (id))")
        if err != nil {
            log.Println(err)
        }
    }

    // Populate database with a few records. 
    res, err := db.Exec("INSERT INTO " +
        *tableName + "(name, description) VALUES " +
        "('Cajun Boots for Men', 'Alligator-tough'), " +
        "('Camouflage 2 Person Tent', 'No-mallet setup'), " +
        "('Cajun Boots for Women', 'Lighter no-sweat fabric ');")
    if err != nil {
        log.Fatal(err)
    }

    lastId, err := res.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }

    // First variable is # of rows changed by
    // this operation.
    affected, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    log.Printf("Updated record # %d\n", lastId)
    log.Printf("Rows affected: %d\n", affected)

    // Search the name column for a record in the database
    rows, err := db.Query(
        "SELECT * from " +
            *tableName + " " +
            "WHERE NAME LIKE '%" + *searchTerm + "%'")
    if err != nil {
        log.Fatal(err)
    }

    // Display all rows matching the search
    log.Printf("Results of search for %s:\n", *searchTerm)
    for rows.Next() {
        var description string
        var id int
        var name string
        if err := rows.Scan(&id, &name, &description); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%s\n", name)
    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
}

`

Creating Go programs that use Cloud SQL

Creating Go programs that use Cloud SQL

9/12/15: The code works in this section, but there's little explanatory text because I'm doing the examples first. Explanations will come later

Example programs in this chapter

Obtaining the MySQL driver using go get

go get "status 69" error message

Example cloudsql1.go: Create a Google Cloud SQL table with options set at the command line

Example cloudsql2.go: Create a Google Cloud SQL table with options set at the command line

Example cloudsql3.go: Create a Google Cloud SQL table and add a record, with options set at the command line

Example cloudsql4.go: Query a Google Cloud SQL table and display the results.

Example cloudsql6.go: Search Google Cloud SQL table and displaly matching records

results matching ""

No results matching ""