How to exec sql file with commands in golang

Question

everyone, I have some troubles with postgresql in golang. I have an sql file (database.sql), and before starting my server I want to exec some command, it look's like that

CREATE TABLE forums (
  id BIGSERIAL primary key,
  slug TEXT NOT NULL UNIQUE,
  title TEXT,
  author TEXT references users(login),
  threads BIGINT DEFAULT 0,
  posts BIGINT DEFAULT 0
);

I know, that I should use db.Exec(request), but i have many requests ("CREATE TABLE user" and others...)

I have no idea, how to do that

Help, and thanks a lot!

CPlus · Accepted Answer · 2023-04-17 05:18:30Z

13

You can read the file's contents into a string and pass that to Exec.

query, err := ioutil.ReadFile("path/to/database.sql")
if err != nil {
    panic(err)
}
if _, err := db.Exec(query); err != nil {
    panic(err)
}

If your database.sql is formated in such a way, or it contains queries that break db.Exec, then you could use os/exec together with psql (if it's installed on the machine on which the code is being run).

cmd := exec.Command("psql", "-d", "database_name", "-f", "path/to/database.sql")
stderr, err := cmd.StderrPipe()
if err != nil {
    panic(err)
}

if err := cmd.Start(); err != nil {
    panic(err)
}

errout, _ := ioutil.ReadAll(stderr)
if err := cmd.Wait(); err != nil {
    fmt.Println(errout)
    panic(err)
}

If you have multiple SQL statements in the file, don't forget to set MultiStatements to true. Example:

config := mysql.Config{
    User:                 "User",
    Passwd:               "Password",
    Net:                  "tcp",
    Addr:                 "127.0.0.1:3306",
    DBName:               "DB_Name",
    MultiStatements:      true,
}

var err error
DB, err = sql.Open("mysql", config.FormatDSN())
if err != nil {
    log.Fatal(err)
    return err
}

edited Apr 17, 2023 at 5:18

CPlus

5,11048 gold badges33 silver badges77 bronze badges

answered Mar 29, 2018 at 7:34

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

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

1 Comment

Darlan Dieterich Over a year ago

I can execute various 'create table' with Exec?

Collectives™ on Stack Overflow

How to exec sql file with commands in golang

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related