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

Question

I am working on a Golang script that automatically clones a database. I am using the go-sql driver, but I cannot find in the documentation a way to create a new database. Connecting to MySQL requires a URL scheme, for example:

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

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

How can i do this? Should I use a different driver?

Answer 1

You can perfectly use the go-sql driver. However, you need to use the mysql user who has the correct permissions 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) } } 

Please note that the database name is not specified in the connection string. We simply create the database after the connection (CREATE DATABASE command) and switch the connection to use it (USE command).

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

Answer 2

If you want to create a new database if it does not exist and use it directly in your program, keep in mind that database/sql supports connection pooling.

Therefore, an open database connection should preferably contain the database name. I saw "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 }