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()

Elwinar · Accepted Answer · 2015-03-03 14:43:26Z

7

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.

answered Mar 3, 2015 at 14:43

Elwinar

9,5791 gold badge35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Casper Beyer Over a year ago

Dead link to rambler.

Elwinar Over a year ago

Hum… nope. Github repository is still there…. But Github had a production incident yesterday, with database consistency issues throughout the day.

Salvador Dali · Accepted Answer · 2016-06-17 06:55:25Z

1

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())

answered Jun 17, 2016 at 6:55

Salvador Dali

224k151 gold badges726 silver badges766 bronze badges

Collectives™ on Stack Overflow

Execute SQL script in Go directly from file or string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related