How to create a new MySQL database with go-sql-driver

Question

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?

Kronfeld

Answer 1

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

Bistoury

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? – Sequence

Yes sure ... just put the database name in the connection string: "user:password@tcp(hostname:port)/dbname" – Bistoury

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. – Sequence

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? – Treaty

Answer 2

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
}

Sequence

The return db will be closed since you called defer db.Close(). – Mutineer