MySQL is an open-source relational database management system based on Structured Query Language(SQL). It is a relational database that organizes data into one or more tables in which data are related to each other.
Database Driver: A Database Driver implements a protocol for a database connection. The Driver is like an adapter that connects to a generic interface to a specific database.
Initial Setup:
Start MySQL server and install go MySQL driver with the following command.
go get -u github.com/go-sql-driver/mysql
Creating database object:
Create a database object with sql.Open. There no connection established with MySQL instead, it creates only a database object which can be used later.
db, err := sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<database-name>")
Replace 3306 if not using MySQL on the default port.
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
) func main() {
// create a database object which can be used
// to connect with database.
db, err := sql.Open( "mysql" , "root:passwd@tcp(0.0.0.0:3306)/user" )
// handle error, if any.
if err != nil {
panic(err)
}
// Now its time to connect with oru database,
// database object has a method Ping.
// Ping returns error, if unable connect to database.
err = db.Ping()
// handle error
if err != nil {
panic(err)
}
fmt.Print( "Pong\n" )
// database object has a method Close,
// which is used to free the resource.
// Free the resource when the function
// is returned.
defer db.Close()
} |
Output:
Execute Database Query: A database query can be done with Exec() and Query().
1. Creating a Database Table with SQL query and Exec().
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
) func main() {
// create a database object which can be used
// to connect with database.
db, err := sql.Open( "mysql" , "root:passwd@tcp(0.0.0.0:3306)/user" )
// handle error, if any.
if err != nil {
panic(err)
}
// database object has a method called Exec,
// it executes a database query, but it does
// not return any row as result.
// Here we create a database table with a SQL query.
_, err = db.Exec("CREATE TABLE user(id INT NOT NULL, name VARCHAR( 20 ),
PRIMARY KEY (ID));")
// handle error
if err != nil {
panic(err)
}
fmt.Print( "Successfully Created\n" )
// database object has a method Close,
// which is used to free the resource.
// Free the resource when the function
// is returned.
defer db.Close()
} |
Output:
2. Inserting a row into Database Table with SQL query in Query().
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
) func main() {
// create a database object which can be used
// to connect with database.
db, err := sql.Open( "mysql" , "root:passwd@tcp(0.0.0.0:3306)/user" )
// handle error, if any.
if err != nil {
panic(err)
}
// database object has a method called Query,
// It can execute a SQL query and return rows
// as result. Here we insert a row into the table,
// no row returned as result for this operation.
_, err = db.Query( "INSERT INTO user VALUES(1,'sam')" )
// handle error
if err != nil {
panic(err)
}
fmt.Print( "Successfully Inserted\n" )
// database object has a method Close,
// which is used to free the resource.
// Free the resource when the function
// is returned.
defer db.Close()
} |
Output:
3. Using SQL query in Query() to return all rows from the user table.
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
) func main() {
// create a database object which can be
// used to connect with database.
db, err := sql.Open( "mysql" , "root:passwd@tcp(0.0.0.0:3306)/user" )
// handle error, if any.
if err != nil {
panic(err)
}
// Here a SQL query is used to return all
// the data from the table user.
result, err := db.Query( "SELECT * FROM user" )
// handle error
if err != nil {
panic(err)
}
// the result object has a method called Next,
// which is used to iterate through all returned rows.
for result.Next() {
var id int
var name string
// The result object provided Scan method
// to read row data, Scan returns error,
// if any. Here we read id and name returned.
err = result.Scan(&id, &name)
// handle error
if err != nil {
panic(err)
}
fmt.Printf( "Id: %d Name: %s\n" , id, name)
}
// database object has a method Close,
// which is used to free the resource.
// Free the resource when the function
// is returned.
defer db.Close()
} |
Output: