In this guide, you’ll learn how to connect to a PlanetScale MySQL database with Go by exploring a sample API built using the Gin routing framework.Prerequisites:
Start in PlanetScale by creating a new database. From the dashboard, click “New Database”, then “Create new database”. Name the database products_db, select the desired Plan type, and click “Create database”.By default, web console access to production branches is disabled to prevent accidental deletion. From your database’s dashboard page, click on the “Settings” tab, check the box labelled “Allow web console access to production branches”, and click “Save database settings”.Then, click on the “Console” tab, then “Connect”.
Run the following two commands to create a sample table and insert some data:
Copy
Ask AI
CREATE TABLE `products` ( `id` int PRIMARY KEY AUTO_INCREMENT, `name` varchar(100) NOT NULL, `price` int NOT NULL);INSERT INTO `products` (name, price) VALUES ('Cyberfreak 2076', 40), ('Destination 2: Shining Decline', 20), ('Edge Properties 3', 15);
Finally, head to the “Dashboard” tab and click “Connect”.
On the following page, click “Create password” to generate a new password for your database. Then click Go in the Select your language or framework section, and copy the contents of the .env file. You’ll need it for the next section.
Open the project in VS Code and add a new file in the root of the project named .env, Populate the file with the contents taken from the Connect modal in the previous section.
Now that the project is running, let’s explore the code to see how everything works. All of the code is stored in main.go, with each of the core SQL operations mapped by HTTP method in the main function:
HTTP Method Name
Query Type
get
SELECT
post
INSERT
put
UPDATE
delete
DELETE
Copy
Ask AI
func main() { // Load in the `.env` file err := godotenv.Load() if err != nil { log.Fatal("failed to load env", err) } // Open a connection to the database db, err = sql.Open("mysql", os.Getenv("DSN")) if err != nil { log.Fatal("failed to open db connection", err) } // Build router & define routes router := gin.Default() router.GET("/products", GetProducts) router.GET("/products/:productId", GetSingleProduct) router.POST("/products", CreateProduct) router.PUT("/products/:productId", UpdateProduct) router.DELETE("/products/:productId", DeleteProduct) // Run the router router.Run()}
Open the tests.http file, which contains HTTP requests that can be sent to test the API. Running the get {{hostname}}/products test is the equivalent of running SELECT * FROM products in SQL and returning the results as JSON.
WarningIf you do not wish to use VS Code with the Rest Client plugin, you may use tests.http as a reference for your preferred IDE and API testing software.
This is the GetProducts function defined in main.go. Notice how the query variable is the SELECT statement, which is passed into db.Query before being scanned into a slice of Product structs.
To pass parameters into queries, you may use a ? as a placeholder for the parameter. For example, GetSingleProduct uses a query with a WHERE clause that is passed into the db.QueryRow function along with the query string.
Copy
Ask AI
func GetSingleProduct(c *gin.Context) { productId := c.Param("productId") productId = strings.ReplaceAll(productId, "/", "") productIdInt, err := strconv.Atoi(productId) if err != nil { log.Fatal("(GetSingleProduct) strconv.Atoi", err) } var product Product // `?` is a placeholder for the parameter query := `SELECT * FROM products WHERE id = ?` // `productIdInt` is passed in with the query err = db.QueryRow(query, productIdInt).Scan(&product.Id, &product.Name, &product.Price) if err != nil { log.Fatal("(GetSingleProduct) db.Exec", err) } c.JSON(http.StatusOK, product)}
Parameters in queries are populated in the order they are passed into the respective db function, as demonstrated in CreateProduct.
Copy
Ask AI
func CreateProduct(c *gin.Context) { var newProduct Product err := c.BindJSON(&newProduct) if err != nil { log.Fatal("(CreateProduct) c.BindJSON", err) } // This query has multiple `?` parameter placeholders query := `INSERT INTO products (name, price) VALUES (?, ?)` // The `Exec` function takes in a query, as well as the values for // the parameters in the order they are defined res, err := db.Exec(query, newProduct.Name, newProduct.Price) if err != nil { log.Fatal("(CreateProduct) db.Exec", err) } newProduct.Id, err = res.LastInsertId() if err != nil { log.Fatal("(CreateProduct) res.LastInsertId", err) } c.JSON(http.StatusOK, newProduct)}