I have .sql file which has lots of database creation , deletion , population stuff. Is it possible to have a go function which can excute a sql file. I am using postgres as my database and using lib/pq driver for all database transactions. But I am open to any library for executing this sql file in my golang project.
You can use os/exec package of standard library. No database driver is required. Code would look something like this for postgreSQL:
cmd := exec.Command("psql", "-U", psqlUser, "-h", psqlHost, "-d", psqlDBName, "-a", "-f", sqlFilePath)
var out, stderr bytes.Buffer
cmd.Stdout = &out
cmd.Stderr = &stderr
err := cmd.Run()
if err != nil {
log.Fatalf("Error executing query. Command Output: %+v
: %+v, %v", out.String(), stderr.String(), err)
}
You can just split file into separate requests and execute them one-by-one:
file, err := ioutil.ReadAll("/path/to/file")
if err != nil {
// handle error
}
requests := strings.Split(string(file), ";")
for _, request := range requests {
result, err := db.Exec(request)
// do whatever you need with result and error
}
I found dotsql in search of similar demand. You can load named sql statements/prepare statements from a specific file and execute.
// Get a database handle
db, err := sql.Open("sqlite3", ":memory:")
// Loads queries from file
dot, err := dotsql.LoadFromFile("queries.sql")
// Run queries
res, err := dot.Exec(db, "create-users-table")
res, err := dot.Exec(db, "create-user", "User Name", "main@example.com")
rows, err := dot.Query(db, "find-users-by-email", "main@example.com")
row, err := dot.QueryRow(db, "find-one-user-by-email", "user@example.com")
stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()
It's too much trouble if you are going to execute it using a command line. You have to deal with issues like setting your passwords, making sure the path variables are properly set, etc. I think the bets way is to use the database driver and just call it using Go.
In the following example, I'm using pgx implementation of sql driver for Postgres. You can do it with any driver implementation of your choice.
path := filepath.Join("path", "to", "script.sql")
c, ioErr := ioutil.Readfile(path)
if ioErr != nil {
// handle error.
}
sql := string(c)
_, err := *pgx.Conn.Exec(sql)
if err != nil {
// handle error.
}
Explanation: