MySQL Tutorial: Creating a Table and Inserting Rows
Welcome to tutorial no. 2 in our MySQL tutorial series. In the first tutorial, we discussed how to connect to MySQL and create a database. In this tutorial, we will learn how to create a table and insert records into that table.
MySQL Series Index
Connecting to MySQL and creating a Database
Creating a Table and Inserting Rows
Selecting single and multiple rows
Prepared statements - WIP
Updating rows - WIP
Deleting rows - WIP
Create Table
We will be creating a table named product with the fields product_id, product_name, product_price, created_at and updated_at.
The MySQL query to create this table is provided below,
CREATE TABLE IF NOT EXISTS product(product_id int primary key auto_increment, product_name text, product_price int, created_at datetime default CURRENT_TIMESTAMP, updated_at datetime default CURRENT_TIMESTAMP)
product_id is an auto incremented int and it serves as the primary key. The default values of created_at and updated_at is set as the current timestamp. Now that we have query, let’s convert it into Go code and create our table.
The ExecContext method of the DB package executes any query that doesn’t return any rows. In our case, the create table query doesn’t return any rows and hence we will use the ExecContext() context method to create our table.
Let’s be a responsible developer and create a context with a timeout so that the create table query times out in case of any network partition or runtime errors.
1query := `CREATE TABLE IF NOT EXISTS product(product_id int primary key auto_increment, product_name text,
2 product_price int, created_at datetime default CURRENT_TIMESTAMP, updated_at datetime default CURRENT_TIMESTAMP)`
3
4ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
5defer cancelfunc()
In the above code, we have created a context with a 5 second timeout. Let’s go ahead and use this context in the ExecContext() method.
1res, err := db.ExecContext(ctx, query)
2if err != nil {
3 log.Printf("Error %s when creating product table", err)
4 return err
5}
We pass the created context and the MySQL query as parameters to the ExecContext method and return errors if any. The db is the database connection pool that was created in the previous tutorial /connect-create-db-mysql/. Please go through it to understand how to connect to MySQL and create a connection pool.
Now the table is created successfully. The result set returned from the call to ExecContext() contains a method that returns the number of rows affected. The create table statement doesn’t affect any rows but still, let’s check this out by calling the res.RowsAffected() method.
1rows, err := res.RowsAffected()
2 if err != nil {
3 log.Printf("Error %s when getting rows affected", err)
4 return err
5 }
6log.Printf("Rows affected when creating table: %d", rows)
The above code will print Rows affected when creating table: 0 since create table doesn’t affect any rows.
The entire code is provided below.
1package main
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log"
8 "time"
9
10 _ "github.com/go-sql-driver/mysql"
11)
12
13const (
14 username = "root"
15 password = "naveenr123"
16 hostname = "127.0.0.1:3306"
17 dbname = "ecommerce"
18)
19
20func dsn(dbName string) string {
21 return fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, hostname, dbName)
22}
23
24func dbConnection() (*sql.DB, error) {
25 db, err := sql.Open("mysql", dsn(""))
26 if err != nil {
27 log.Printf("Error %s when opening DB\n", err)
28 return nil, err
29 }
30
31 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
32 defer cancelfunc()
33 res, err := db.ExecContext(ctx, "CREATE DATABASE IF NOT EXISTS "+dbname)
34 if err != nil {
35 log.Printf("Error %s when creating DB\n", err)
36 return nil, err
37 }
38 no, err := res.RowsAffected()
39 if err != nil {
40 log.Printf("Error %s when fetching rows", err)
41 return nil, err
42 }
43 log.Printf("rows affected: %d\n", no)
44
45 db.Close()
46 db, err = sql.Open("mysql", dsn(dbname))
47 if err != nil {
48 log.Printf("Error %s when opening DB", err)
49 return nil, err
50 }
51 //defer db.Close()
52
53 db.SetMaxOpenConns(20)
54 db.SetMaxIdleConns(20)
55 db.SetConnMaxLifetime(time.Minute * 5)
56
57 ctx, cancelfunc = context.WithTimeout(context.Background(), 5*time.Second)
58 defer cancelfunc()
59 err = db.PingContext(ctx)
60 if err != nil {
61 log.Printf("Errors %s pinging DB", err)
62 return nil, err
63 }
64 log.Printf("Connected to DB %s successfully\n", dbname)
65 return db, nil
66}
67
68func createProductTable(db *sql.DB) error {
69 query := `CREATE TABLE IF NOT EXISTS product(product_id int primary key auto_increment, product_name text,
70 product_price int, created_at datetime default CURRENT_TIMESTAMP, updated_at datetime default CURRENT_TIMESTAMP)`
71 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
72 defer cancelfunc()
73 res, err := db.ExecContext(ctx, query)
74 if err != nil {
75 log.Printf("Error %s when creating product table", err)
76 return err
77 }
78 rows, err := res.RowsAffected()
79 if err != nil {
80 log.Printf("Error %s when getting rows affected", err)
81 return err
82 }
83 log.Printf("Rows affected when creating table: %d", rows)
84 return nil
85}
86
87func main() {
88 db, err := dbConnection()
89 if err != nil {
90 log.Printf("Error %s when getting db connection", err)
91 return
92 }
93 defer db.Close()
94 log.Printf("Successfully connected to database")
95 err = createProductTable(db)
96 if err != nil {
97 log.Printf("Create product table failed with error %s", err)
98 return
99 }
100}
I have included the code to connect to MySQL and create a database from the previous tutorial inside the dbConnection() function. The only change from the previous tutorial is that the defer statements in line no. 30 and line no. 52 are commented since we do not want the database to be closed immediately after returning from this function.
The main() function creates a new DB connection pool in line no. 89 and passes that to the createProductTable function in line no. 95. We defer the database close in line no. 93 so that the connection to the DB is closed when the program terminates. Run this program and you can see the following output,
2020/10/25 20:30:51 rows affected: 1
2020/10/25 20:30:51 Connected to DB ecommerce successfully
2020/10/25 20:30:51 Successfully connected to database
2020/10/25 20:30:51 Rows affected when creating table: 0
To verify whether the table has been created successfully, you can run desc product; in MySQL query browser and you can see that it returns the table schema.
Insert Row
The next step is to insert rows into the product table we just created. The query to insert a row into the product table is provided below,
INSERT INTO product(product_name, product_price) VALUES ("iPhone", 800);
Let’s discuss how to use the above query in Go and insert rows into the table.
Let’s first create a product struct to represent our product.
1type product struct {
2 name string
3 price int
4}
The second step is to create a prepared statement. Prepared statements are used to parametrize a SQL query so that the same query can be run with different arguments efficiently. It also helps prevent sql injection.
In our case, the parameters to the query are product_name and product_price. The way to create a prepared statement template is to replace the parameters with question mark ?. The prepared statement template of the following query
INSERT INTO product(product_name, product_price) VALUES ("iPhone", 800);
is
INSERT INTO product(product_name, product_price) VALUES (?, ?);
You can see that "iPhone" and 800 are replaced with question marks.
1func insert(db *sql.DB, p product) error {
2 query := "INSERT INTO product(product_name, product_price) VALUES (?, ?)"
3 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
4 defer cancelfunc()
5 stmt, err := db.PrepareContext(ctx, query)
6 if err != nil {
7 log.Printf("Error %s when preparing SQL statement", err)
8 return err
9 }
10 defer stmt.Close()
11}
Line no. 2 of the above code has the prepared statement template. In line no. 5, we create a prepared statement for our insert query using this template. As usual, we use a context with a timeout to handle network errors. The statement should be closed after use. So in the next line we defer the statement close.
The next step is to pass the necessary parameters to the prepared statement and execute it.
1res, err := stmt.ExecContext(ctx, p.name, p.price)
2if err != nil {
3 log.Printf("Error %s when inserting row into products table", err)
4 return err
5}
6rows, err := res.RowsAffected()
7if err != nil {
8 log.Printf("Error %s when finding rows affected", err)
9 return err
10}
11log.Printf("%d products created ", rows)
12return nil
The prepared statement expects two arguments namely the product name and the product price. The ExecContext method accepts a variadic list of interface{} arguments. The number of variadic arguments passed to it should match the number of question marks ? in the prepared statement template, else there will be a runtime error Column count doesn't match value count at row 1 when preparing SQL statement.
In our case, there are two question marks in the template and hence in the above code snippet, in line no. 1, we pass the two parameters product name and price to the ExecContext method.
The entire insert function is provided below.
1func insert(db *sql.DB, p product) error {
2 query := "INSERT INTO product(product_name, product_price) VALUES (?, ?)"
3 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
4 defer cancelfunc()
5 stmt, err := db.PrepareContext(ctx, query)
6 if err != nil {
7 log.Printf("Error %s when preparing SQL statement", err)
8 return err
9 }
10 defer stmt.Close()
11 res, err := stmt.ExecContext(ctx, p.name, p.price)
12 if err != nil {
13 log.Printf("Error %s when inserting row into products table", err)
14 return err
15 }
16 rows, err := res.RowsAffected()
17 if err != nil {
18 log.Printf("Error %s when finding rows affected", err)
19 return err
20 }
21 log.Printf("%d products created ", rows)
22 return nil
23}
Please add the following code to the end of main function to call the insert function.
1func main() {
2...
3
4p := product{
5 name: "iphone",
6 price: 950,
7 }
8err = insert(db, p)
9if err != nil {
10 log.Printf("Insert product failed with error %s", err)
11 return
12 }
13}
If everything goes well, the program will print 1 products created
You can check that the product has been inserted successfully by running select * from product; and you can see the following output in MySQL query browser.
Last Inserted ID
There might be a need to get the last inserted ID of an insert query with auto increment primary key. In our case, the product_id is an auto incremented int primary key. We might need the last inserted product id to reference in other tables. Say, we have a supplier table and would like to map suppliers once a new product is created. In this case, fetching the last inserted ID is essential. The LastInsertId method of the result set can be used to fetch this ID. Add the following code to the end of the insert function before return nil.
1func insert(db *sql.DB, p product) error {
2...
3
4 prdID, err := res.LastInsertId()
5 if err != nil {
6 log.Printf("Error %s when getting last inserted product", err)
7 return err
8 }
9 log.Printf("Product with ID %d created", prdID)
10 return nil
11}
When the program is run with the above code added, the line Product with ID 2 created will be printed. We can see that the ID of the last inserted product is 2.
Insert Multiple Rows
Let’s take our insert statement to the next level and try to insert multiple rows using a single query.
The MySQL syntax for inserting multiple rows is provided below
insert into product(product_name, product_price) values ("Galaxy","990"),("iPad","500")
The different rows to be inserted are separated by commas. Let’s see how to achieve this using Go.
The logic is to generate the ("Galaxy","990"),("iPad","500") after the values part of the query dynamically based on the number of products needed to be inserted. In this case, two products namely Galaxy and iPad have to be inserted. So there is a need to generate a prepared statement template of the following format.
insert into product(product_name, product_price) values (?,?),(?,?)
Let’s write the function to do this right away.
1func multipleInsert(db *sql.DB, products []product) error {
2 query := "INSERT INTO product(product_name, product_price) VALUES "
3 var inserts []string
4 var params []interface{}
5 for _, v := range products {
6 inserts = append(inserts, "(?, ?)")
7 params = append(params, v.name, v.price)
8 }
9}
We iterate over the products parameter passed to the function and for each product we append (?, ?) to the inserts slice in line no. 6. In the same for loop we append the actual parameters that should substitute the question marks ? to the params slice.
There is one more step remaining before the prepared statement template is ready. The inserts slice is of length 2 and it contains (?, ?) and (?, ?). These two have to be concatenated with a comma in the middle. The Join can be used to do that. It takes a string slice and a separator as parameters and joins the elements of the slice with the separator.
1queryVals := strings.Join(inserts, ",")
2query = query + queryVals
queryVals now contains (?, ?),(?, ?). We then concatenate query and queryVals to generate the final prepared statement template INSERT INTO product(product_name, product_price) VALUES (?, ?),(?, ?).
The remaining code is similar to the single row insert function. Here is the full function.
1func multipleInsert(db *sql.DB, products []product) error {
2 query := "INSERT INTO product(product_name, product_price) VALUES "
3 var inserts []string
4 var params []interface{}
5 for _, v := range products {
6 inserts = append(inserts, "(?, ?)")
7 params = append(params, v.name, v.price)
8 }
9 queryVals := strings.Join(inserts, ",")
10 query = query + queryVals
11 log.Println("query is", query)
12 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
13 defer cancelfunc()
14 stmt, err := db.PrepareContext(ctx, query)
15 if err != nil {
16 log.Printf("Error %s when preparing SQL statement", err)
17 return err
18 }
19 defer stmt.Close()
20 res, err := stmt.ExecContext(ctx, params...)
21 if err != nil {
22 log.Printf("Error %s when inserting row into products table", err)
23 return err
24 }
25 rows, err := res.RowsAffected()
26 if err != nil {
27 log.Printf("Error %s when finding rows affected", err)
28 return err
29 }
30 log.Printf("%d products created simulatneously", rows)
31 return nil
32}
The one difference which you could see is in line no. 20. We pass the slice as a variadic argument since ExecContext expects a variadic argument. The remaining code is the same.
Add the following lines to the end of the main function to call the multipleInsert function.
1func main() {
2...
3
4p1 := product{
5 name: "Galaxy",
6 price: 990,
7}
8p2 := product{
9 name: "iPad",
10 price: 500,
11}
12err = multipleInsert(db, []product{p1, p2})
13if err != nil {
14 log.Printf("Multiple insert failed with error %s", err)
15 return
16}
On running the program you can see
query is INSERT INTO product(product_name, product_price) VALUES (?, ?),(?, ?)
2 products created simultaneously
printed.
On querying the table, it can be confirmed that two products are inserted.
The entire code is available in at https://github.com/golangbot/mysqltutorial/blob/master/insert/main.go
This brings us to the end of this tutorial.
I hope you liked this tutorial. Please leave your feedback and comments. Please consider sharing this tutorial on twitter or LinkedIn. Have a good day.
Previous tutorial - Connecting to MySQL and Creating a DB using Go
Next tutorial - Selecting Single and Multiple Rows
