20

I'm working on Golang script that automatically clone a database. I'm using go-sql-driver but i can't find in the documentation a way to create a new database. Connection to MySQL require an URL scheme like:

user:password@tcp(localhost:3306)/database_name

But the database not exists yet, I just want to connect to the server and then create a new one.

How can I do that? I have to use another driver?

gdg
  • 587
  • 1
  • 3
  • 10

2 Answers2

43

You can perfectly use the go-sql-driver. However, you need to use a mysql user which has the proper access rights to create new databases.

Here is an example:

func create(name string) {

   db, err := sql.Open("mysql", "admin:admin@tcp(127.0.0.1:3306)/")
   if err != nil {
       panic(err)
   }
   defer db.Close()

   _,err = db.Exec("CREATE DATABASE "+name)
   if err != nil {
       panic(err)
   }

   _,err = db.Exec("USE "+name)
   if err != nil {
       panic(err)
   }

   _,err = db.Exec("CREATE TABLE example ( id integer, data varchar(32) )")
   if err != nil {
       panic(err)
   }
}

Note that the database name is not provided in the connection string. We just create the database after the connection (CREATE DATABASE command), and switch the connection to use it (USE command).

Note: the VividCortex guys maintain a nice database/sql tutorial and documentation at http://go-database-sql.org/index.html

Didier Spezia
  • 70,911
  • 12
  • 189
  • 154
  • 3
    `database/sql` maintains a connection pool. I've seen "Error 1046: No database selected" pop up when the a new connection is made. Is there a way to run `db.Exec("USE "+name)` automatically after `database/sql` opens a new connection? – mlangenberg Aug 06 '16 at 19:51
  • Yes sure ... just put the database name in the connection string: "user:password@tcp(hostname:port)/dbname" – Didier Spezia Aug 07 '16 at 07:02
  • 1
    Ah, so first `sql.Open(...)` without database name and run `db.Exec("CREATE DATABASE IF NOT EXISTS " + name)`. Then close the connection, `sql.Open(.../name)` *with* the database name to create a database and use it safely with the pool. – mlangenberg Aug 09 '16 at 15:13
  • 3
    Honest quention: Isn't this a textbook example on how to open yourself up for sql injection? Is there no way to make this work using the placeholder parameters? –  Aug 30 '17 at 18:47
8

If you want to create a new database if it does not exist, and use it directly in your program, be aware that database/sql maintains a connection pool.

Therefore the opened database connection, should preferably contain the database name. I've seen "Error 1046: No database selected" when database/sql opens a new connection after using db.Exec("USE "+name) manually.

func createAndOpen(name string) *sql.DB {

   db, err := sql.Open("mysql", "admin:admin@tcp(127.0.0.1:3306)/")
   if err != nil {
       panic(err)
   }
   defer db.Close()

   _,err = db.Exec("CREATE DATABASE IF NOT EXISTS "+name)
   if err != nil {
       panic(err)
   }
   db.Close()

   db, err = sql.Open("mysql", "admin:admin@tcp(127.0.0.1:3306)/" + name)
   if err != nil {
       panic(err)
   }
   defer db.Close()
   return db
}
mlangenberg
  • 1,087
  • 11
  • 21