直接从文件或字符串在Go中执行SQL脚本

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, ";")

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.