Execute SQL script in Go directly from file or string

Question

In my install script for my application I'm checking to see if the db contains any tables. If the database is empty I have DML and DDL SQL script I'd like to run.

It's not important that it reads the SQL from a seperate .sql file, so right now I've just put it directly into two strings - one for DDL and one for DML - and concatenated those.

My problem now is that I'm now getting this error, when trying to run the script for generating tables and inserting data into them with .Exec(sqlStr):

"pq: cannot insert multiple commands into a prepared statement"

I can of course do a workaround. Something like:

sqlStr := sqlDML + sqlDDL
sqlStmtSlice := strings.Split(sqlStr, ";")
for i:= 0; i < len(sqlStmtSlice) i++ {
    // Exec() each individual statement!
}

However, I'm not sure I like that method at all. Surely there has to be a much better way of just loading a SQL script from file and executing the whole batch, right? Do you know?

Ps. I'm using the PostgreSQL driver for Go, but I don't think that makes any difference.

Edit:

Since there doesn't seem to be any better solution to get this done at the time, I made a slight improvement to the above pseudo code, which is tested and seems to work just fine:

tx, err := db.Begin()

sqlStr := fmt.Sprintf(sqlDML + sqlDDL)
sqlStmtSlice := strings.Split(sqlStr, ";\r")

if err != nil {
    return err
}

defer func() {
    _ = tx.Rollback()
}()

for _, q := range sqlStmtSlice {
    _, err := tx.Exec(q)

    if err != nil {
        return err
    }
}

err = tx.Commit()

Answer 1

As far as I know, there isn't a real better method if multiple statement queries aren't allowed. It has nothing to do with the driver you're using, as this is a database/sql package limitation. Debating for whether or not it is a good design is another question (and I'm sure there is plenty already).

On the alternatives side, you could probably use a SQL Schema Migration tool or use inspiration from them. The general convention is to use a semantically inert marker, such as a comment, and split around theses.

For examples in golang, you can see:

• goose: full featured, migrations can be written in Go
• rambler: lightweight, SQL-only

Disclaimer: I'm rambler's developer. That said, you should definitively have a look at goose, which is really cool.

Answer 2

Have struggled with the same problem for some time. I needed it for a slightly different reason (initialize the database before running tests).

As Elwinar already mentioned, database/sql package does not allow this. The way I overcame this is by creating a python script (let's call it sql_runner.py) which executes a file and then run a go exec command to execute python script (do not forget to make it executable).

So a python command to run sql file: cursor.execute(open("setting_up.sql"), "r").read())

And part's of a Go code to run this file:

cmd := exec.Command("sql.py")

cmd.Stdout = os.Stdout
cmd.Stderr = os.Stderr
log.Println(cmd.Run())