Summary: in this tutorial, you’ll learn how to connect to an SQL Server database from a Go using the Microsoft SQL Server driver.
Creating a new Go project
Step 1. Open your terminal and create a project directory called sqlservergo:
mkdir sqlservergoCode language: Go (go)Step 2. Navigate to the project directory:
cd sqlsevergoCode language: Go (go)Step 3. Executing the go mod init command to create a go.mod file to track the dependencies:
go mod init sqlservergoCode language: Go (go)This command will create a go.mod file in the project directory, which stores the Go version and the project dependencies.
Step 4. Install the godotenv and go-mssqldb packages by executing the following commands on your terminal:
go get github.com/joho/godotenv
go get github.com/microsoft/go-mssqldb@latestCode language: Go (go)- The
godotenvallows you to read SQL Server configuration parameters from the.envfile, which makes your program more configurable and secure. - The
go-mssqldbis a Microsoft SQL Server Driver written in Go.
Step 5. Create a BookStore database and a user account that has sufficient privileges.
Step 6. Create a new .env file within the project directory with the following SQL Server configuration:
DB_SERVER=localhost
DB_PORT=1433
DB_USER=joe
DB_PASSWORD=YourPassword
DB_NAME=BookStoreCode language: Go (go)Please replace the YourPassword with your password.
Step 7. Create db.go and main.go files within the project directory
Connecting to SQL Server database
The db.go file will store the code that interacts with the SQL Server database:
package main
import (
"database/sql"
"fmt"
"os"
"github.com/joho/godotenv"
_ "github.com/microsoft/go-mssqldb"
)
type DBConfig struct {
Server string
Port string
User string
Password string
Database string
}
func LoadConfig() (*DBConfig, error) {
err := godotenv.Load()
if err != nil {
return nil, fmt.Errorf("error loading .env file: %w", err)
}
config := &DBConfig{
Server: os.Getenv("DB_SERVER"),
Port: os.Getenv("DB_PORT"),
User: os.Getenv("DB_USER"),
Password: os.Getenv("DB_PASSWORD"),
Database: os.Getenv("DB_NAME"),
}
// Validate that all necessary environment variables are set
if config.Server == "" || config.Port == "" || config.User == "" || config.Password == "" || config.Database == "" {
return nil, fmt.Errorf("missing one or more required environment variables")
}
return config, nil
}
func Connect(config *DBConfig) (*sql.DB, error) {
connString := fmt.Sprintf("server=%s;port=%s;user id=%s;password=%s;database=%s", config.Server, config.Port, config.User, config.Password, config.Database)
db, err := sql.Open("sqlserver", connString)
if err != nil {
return nil, fmt.Errorf("error creating a SQL Server database connection: %w", err)
}
// Verify the connection
err = db.Ping()
if err != nil {
return nil, fmt.Errorf("error verifying connection to the SQL Server: %w", err)
}
return db, nil
}Code language: Go (go)How it works.
Step 1. Declare the main package:
package mainCode language: Go (go)Step 2. Import necessary packages:
import (
"database/sql"
"fmt"
"os"
"github.com/joho/godotenv"
_ "github.com/microsoft/go-mssqldb"
)Code language: Go (go)Step 3. Define a struct that stores the database configuration:
type DBConfig struct {
Server string
Port string
User string
Password string
Database string
}Code language: Go (go)Step 4. Define a LoadConfig() function that loads database configuration from the .env file:
func LoadConfig() (*DBConfig, error) {
err := godotenv.Load()
if err != nil {
return nil, fmt.Errorf("error loading .env file: %w", err)
}
config := &DBConfig{
Server: os.Getenv("DB_SERVER"),
Port: os.Getenv("DB_PORT"),
User: os.Getenv("DB_USER"),
Password: os.Getenv("DB_PASSWORD"),
Database: os.Getenv("DB_NAME"),
}
// Validate that all necessary environment variables are set
if config.Server == "" || config.Port == "" || config.User == "" || config.Password == "" || config.Database == "" {
return nil, fmt.Errorf("missing one or more required environment variables")
}
return config, nil
}Code language: Go (go)Step 5. Define the Connect() that connects to the SQL Server database:
func Connect(config *DBConfig) (*sql.DB, error) {
connString := fmt.Sprintf("server=%s;port=%s;user id=%s;password=%s;database=%s", config.Server, config.Port, config.User, config.Password, config.Database)
db, err := sql.Open("sqlserver", connString)
if err != nil {
return nil, fmt.Errorf("error creating a SQL Server database connection: %w", err)
}
// Verify the connection
err = db.Ping()
if err != nil {
return nil, fmt.Errorf("error verifying connection to the SQL Server: %w", err)
}
return db, nil
}Code language: Go (go)The main.go function includes the main function that uses the functions from the db.go file to connect to the SQL Server database:
package main
import "fmt"
func main() {
// Load the database configuration
config, err := LoadConfig()
if err != nil {
fmt.Println(err)
return
}
// Connect to the database
db, err := Connect(config)
if err != nil {
fmt.Println(err)
return
}
defer db.Close()
fmt.Println("Successfully connected to SQL Server database.")
}Code language: Go (go)How it works.
Step 1. Load the database configuration by calling the LoadConfig() function:
config, err := LoadConfig()
if err != nil {
fmt.Println(err)
return
}Code language: Go (go)Step 2. Connect to the SQL Server database by calling the Connect() function:
db, err := Connect(config)
if err != nil {
fmt.Println(err)
return
}Code language: Go (go)Step 3. Close the database connection once the main function exits:
defer db.Close()Code language: Go (go)Step 4. Display a success message:
fmt.Println("Successfully connected to SQL Server database.")Code language: Go (go)Running the program
Execute the following command from your terminal to run the go program:
go run main.go db.goCode language: Go (go)It’ll return the following output if the connection succeeds:
Successfully connected to SQL Server database.Code language: Go (go)If the password is not valid, you’ll get the following error:
error verifying connection to the SQL Server: mssql: login error: Login failed for user 'joe'.Code language: Go (go)In this case, you need to check the DB_USER and DB_PASSWORD in the .env file.
If the SQL Server is not running, you’ll get the following error:
error verifying connection to the SQL Server: unable to open tcp connection with host 'localhost:1433': dial tcp [::1]:1433: connectex: No connection could be made because the target machine actively refused it.Code language: Go (go)Please ensure the SQL Server is running at DB_SERVER and listening on the port DB_PORT.
Summary
- Use the Microsoft SQL Server driver to connect a Go program to the SQL Server.
