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.
