SQL syntax error when executing MySQL script using Go during Heroku deployment

I am deploying an API made using Go and MySQl for the database to Heroku. I am following this guide on it and set up everything but now I am trying to execute a MySQL script to set up the tables with some dummy data. But I am constantly getting errors saying that the script is wrong even though I have used it locally with no issues. I have already connected the MySQL database to the Heroku environment and starting the database throws no errors.

Here are the logs showing the error when deploying it to Heroku:

2021-06-05T12:49:16.442359+00:00 app[web.1]: ERROR  2021/06/05 12:49:16 main.go:45: Error executing SQL script : Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE assessment;

schema.sql

CREATE DATABASE assessment;

USE assessment;

CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    email VARCHAR(255) NOT NULL
);
INSERT INTO `users` VALUES (1,"[email protected]");
INSERT INTO `users` VALUES (2,"[email protected]");
INSERT INTO `users` VALUES (3,"[email protected]");
INSERT INTO `users` VALUES (4,"[email protected]");
INSERT INTO `users` VALUES (5,"[email protected]");

CREATE TABLE features (
    feature_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id INTEGER NOT NULL,
    feature_name VARCHAR(100) NOT NULL,
    can_access BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) 
);

INSERT INTO `features` VALUES (1, 1, "automated-investing", 1);
INSERT INTO `features` VALUES (2, 1, "crypto", 0);
INSERT INTO `features` VALUES (3, 2, "crypto", 0);
INSERT INTO `features` VALUES (4, 3, "automated-investing", 0);
INSERT INTO `features` VALUES (5, 4, "automated-investing", 1);
INSERT INTO `features` VALUES (7, 1, "financial-tracking", 1);
INSERT INTO `features` VALUES (8, 2, "financial-tracking", 0);
INSERT INTO `features` VALUES (9, 3, "financial-tracking", 1);
INSERT INTO `features` VALUES (10, 4, "financial-tracking", 0);

main.go

package main

import (
    "io/ioutil"
    "log"
    "net/http"
    "os"
    "time"

    _ "github.com/go-sql-driver/mysql"
    "github.com/gorilla/mux"
    "github.com/joho/godotenv"

    "github.com/yudhiesh/api/controller"
    "github.com/yudhiesh/api/middleware"
)

func main() {
    router := mux.NewRouter()
    router.Use(middleware.ResponseHeaders)
    router.Use(middleware.LogRequest)
    errorLog := log.New(os.Stderr, "ERROR\t", log.Ldate|log.Ltime|log.Lshortfile)
    infoLog := log.New(os.Stdout, "INFO\t", log.Ldate|log.Ltime)
    err := godotenv.Load(".env")
    port := os.Getenv("PORT")
    if port == "" {
        errorLog.Fatal("$PORT is not set")
    }
    dsn := os.Getenv("DSN")
    if dsn == "" {
        errorLog.Fatal("$DATABASE_URL is not set")
    }
    db, err := controller.OpenDB(dsn)
    if err != nil {
        errorLog.Fatal(err)
    }
    defer db.Close()
    c, ioErr := ioutil.ReadFile("./schema.sql")
    sqlScript := string(c)
    if ioErr != nil {
        errorLog.Fatalf("Error loading SQL schema : %s", ioErr)
    }
    _, err = db.Exec(sqlScript)
    if err != nil {
        // FAILS HERE!
        errorLog.Fatalf("Error executing SQL script : %s", err)
    }
    app := &controller.Application{
        DB:       db,
        ErrorLog: errorLog,
        InfoLog:  infoLog,
    }
    addr := ":" + port
    server := &http.Server{
        Handler:      router,
        Addr:         addr,
        WriteTimeout: 15 * time.Second,
        ReadTimeout:  15 * time.Second,
    }
    router.HandleFunc("/feature", app.GetCanAccess).Methods("GET")
    router.HandleFunc("/feature", app.InsertFeature).Methods("POST")
    http.Handle("/", router)
    infoLog.Printf("Connected to port %s", port)
    errorLog.Fatal(server.ListenAndServe())
}

Solution

Most interfaces to run queries against MySQL do not support multi-query. In other words, they don't allow multiple SQL statements separated by semicolons, they only support one SQL statement per call.

In your case it returned a syntax error on USE ... because given that the input is parsed as a single statement, there is no USE in the CREATE DATABASE statement.

CREATE DATABASE assessment; USE assessment;

This means if you want to process a file of many SQL statements, you can't just do what you're doing and treat the entire file as one string to pass to a single call of Exec():

c, ioErr := ioutil.ReadFile("./schema.sql")
sqlScript := string(c)
...
_, err = db.Exec(sqlScript)

You must split the content of that file into individual SQL statements, and then loop over those statements, running each one at a time.

This is more complicated than it sounds, because:

SQL scripts may contain a DELIMITER statement that changes the character between statements from semicolon to something else.
There might be semicolons inside string literals or inside comments.
Some statements such as CREATE PROCEDURE, CREATE TRIGGER, etc. may contain semicolons between statements in the body of the routine. You don't want these semicolons to be the end of the statement, you want to include all the content to the end of the routine definition.
The DELIMITER statement itself can't be executed by the MySQL Server. It only control the client. So you must treat it as an exception that isn't sent to the server in your loop. In fact, there are a bunch of other mysql client builtin commands which must be treated similarly if you find them in an SQL script.

If you eventually code all this logic, you've basically reimplemented the MySQL command-line client in Go.

It would be far quicker and simpler if you skip past all that coding work, and just run the MySQL command-line client using os.exec.Command. Think about it — it'll save you weeks of coding work, duplicating all the nuanced features of running SQL scripts that is already implemented in the MySQL client.