Go Database Connection
Database connections in Go are managed through the database/sql package, which provides a generic interface for working with SQL databases. Understanding how to establish, configure, and manage database connections is fundamental to building data-driven applications. This comprehensive guide will teach you everything you need to know about database connections in Go.
Understanding Database Connections
What Are Database Connections?
Database connections in Go provide a way to interact with SQL databases through a standardized interface. They provide:
- Connection Establishment - Opening connections to database servers
- Connection Pooling - Managing multiple connections efficiently
- Driver Abstraction - Working with different database systems
- Connection Configuration - Setting connection parameters
- Connection Lifecycle - Managing connection creation and cleanup
The database/sql Package
Interface Design
The database/sql package provides a generic interface for SQL databases.
Driver Support
Support for various database drivers through the driver interface.
Basic Database Connection
Connecting to Different Databases
MySQL Connection
Connecting to MySQL databases using the MySQL driver.
PostgreSQL Connection
Connecting to PostgreSQL databases using the PostgreSQL driver.
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
_ "github.com/lib/pq"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Basic database connection examples
fmt.Println("Basic database connection examples:")
// MySQL connection
func mysqlConnection() {
// Connection string format: username:password@protocol(address)/dbname?param=value
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("MySQL connection established successfully")
// Connection info
fmt.Printf("MySQL connection: %+v\n", db.Stats())
}
mysqlConnection()
// PostgreSQL connection
func postgresqlConnection() {
// Connection string format: user=username password=password host=hostname port=port dbname=dbname sslmode=disable
dsn := "user=postgres password=password host=localhost port=5432 dbname=testdb sslmode=disable"
db, err := sql.Open("postgres", dsn)
if err != nil {
log.Fatalf("Failed to open PostgreSQL connection: %v", err)
}
defer db.Close()
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping PostgreSQL database: %v", err)
}
fmt.Println("PostgreSQL connection established successfully")
// Connection info
fmt.Printf("PostgreSQL connection: %+v\n", db.Stats())
}
postgresqlConnection()
// SQLite connection
func sqliteConnection() {
// SQLite connection string format: file:path?param=value
dsn := "./test.db"
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatalf("Failed to open SQLite connection: %v", err)
}
defer db.Close()
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping SQLite database: %v", err)
}
fmt.Println("SQLite connection established successfully")
// Connection info
fmt.Printf("SQLite connection: %+v\n", db.Stats())
}
sqliteConnection()
}
Connection Configuration
Connection Parameters
Configuring database connections with various parameters.
Connection Timeouts
Setting connection and query timeouts.
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// Connection configuration examples
fmt.Println("Connection configuration examples:")
// Configured MySQL connection
func configuredMySQLConnection() {
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local&timeout=30s&readTimeout=30s&writeTimeout=30s"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Configure connection pool
db.SetMaxOpenConns(25) // Maximum number of open connections
db.SetMaxIdleConns(5) // Maximum number of idle connections
db.SetConnMaxLifetime(5 * time.Minute) // Maximum lifetime of a connection
db.SetConnMaxIdleTime(1 * time.Minute) // Maximum idle time of a connection
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("Configured MySQL connection established successfully")
// Connection pool info
fmt.Printf("Connection pool stats: %+v\n", db.Stats())
}
configuredMySQLConnection()
// Connection with custom configuration
func customConfiguredConnection() {
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Advanced connection configuration
db.SetMaxOpenConns(100) // High concurrency
db.SetMaxIdleConns(10) // More idle connections
db.SetConnMaxLifetime(10 * time.Minute) // Longer connection lifetime
db.SetConnMaxIdleTime(2 * time.Minute) // Longer idle time
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("Custom configured MySQL connection established successfully")
// Connection pool info
fmt.Printf("Custom connection pool stats: %+v\n", db.Stats())
}
customConfiguredConnection()
}
Connection Pooling
Understanding Connection Pools
Connection Pool Benefits
Why connection pooling is important for database performance.
Pool Configuration
Configuring connection pools for optimal performance.
package main
import (
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// Connection pooling examples
fmt.Println("Connection pooling examples:")
// Connection pool demonstration
func connectionPoolDemo() {
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Configure connection pool
db.SetMaxOpenConns(10) // Maximum 10 open connections
db.SetMaxIdleConns(5) // Maximum 5 idle connections
db.SetConnMaxLifetime(5 * time.Minute) // Connection lifetime
db.SetConnMaxIdleTime(1 * time.Minute) // Idle connection lifetime
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("Connection pool configured successfully")
// Simulate concurrent database operations
var wg sync.WaitGroup
numGoroutines := 20
for i := 0; i < numGoroutines; i++ {
wg.Add(1)
go func(id int) {
defer wg.Done()
// Simulate database operation
time.Sleep(100 * time.Millisecond)
// Query database
var result int
err := db.QueryRow("SELECT 1").Scan(&result)
if err != nil {
fmt.Printf("Goroutine %d: Error querying database: %v\n", id, err)
return
}
fmt.Printf("Goroutine %d: Query successful, result: %d\n", id, result)
}(i)
}
wg.Wait()
// Show connection pool stats
fmt.Printf("Final connection pool stats: %+v\n", db.Stats())
}
connectionPoolDemo()
// Connection pool monitoring
func connectionPoolMonitoring() {
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Configure connection pool
db.SetMaxOpenConns(5) // Small pool for monitoring
db.SetMaxIdleConns(2) // Small idle pool
db.SetConnMaxLifetime(2 * time.Minute) // Short connection lifetime
db.SetConnMaxIdleTime(30 * time.Second) // Short idle time
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("Connection pool monitoring started")
// Monitor connection pool
go func() {
ticker := time.NewTicker(2 * time.Second)
defer ticker.Stop()
for {
select {
case <-ticker.C:
stats := db.Stats()
fmt.Printf("Pool stats - Open: %d, InUse: %d, Idle: %d, WaitCount: %d, WaitDuration: %v\n",
stats.OpenConnections, stats.InUse, stats.Idle, stats.WaitCount, stats.WaitDuration)
}
}
}()
// Simulate database operations
time.Sleep(10 * time.Second)
}
connectionPoolMonitoring()
}
Database Drivers
Working with Database Drivers
Driver Registration
Understanding how database drivers are registered and used.
Driver-specific Configuration
Configuring connections for specific database drivers.
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
_ "github.com/lib/pq"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// Database drivers examples
fmt.Println("Database drivers examples:")
// MySQL driver configuration
func mysqlDriverConfig() {
// MySQL-specific connection parameters
dsn := "root:password@tcp(localhost:3306)/testdb?" +
"charset=utf8mb4&" +
"parseTime=True&" +
"loc=Local&" +
"timeout=30s&" +
"readTimeout=30s&" +
"writeTimeout=30s&" +
"collation=utf8mb4_unicode_ci"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// MySQL-specific configuration
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("MySQL driver configured successfully")
// Test MySQL-specific features
var version string
err = db.QueryRow("SELECT VERSION()").Scan(&version)
if err != nil {
log.Printf("Failed to get MySQL version: %v", err)
} else {
fmt.Printf("MySQL version: %s\n", version)
}
}
mysqlDriverConfig()
// PostgreSQL driver configuration
func postgresqlDriverConfig() {
// PostgreSQL-specific connection parameters
dsn := "user=postgres " +
"password=password " +
"host=localhost " +
"port=5432 " +
"dbname=testdb " +
"sslmode=disable " +
"connect_timeout=30 " +
"statement_timeout=30000"
db, err := sql.Open("postgres", dsn)
if err != nil {
log.Fatalf("Failed to open PostgreSQL connection: %v", err)
}
defer db.Close()
// PostgreSQL-specific configuration
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping PostgreSQL database: %v", err)
}
fmt.Println("PostgreSQL driver configured successfully")
// Test PostgreSQL-specific features
var version string
err = db.QueryRow("SELECT version()").Scan(&version)
if err != nil {
log.Printf("Failed to get PostgreSQL version: %v", err)
} else {
fmt.Printf("PostgreSQL version: %s\n", version)
}
}
postgresqlDriverConfig()
// SQLite driver configuration
func sqliteDriverConfig() {
// SQLite-specific connection parameters
dsn := "file:test.db?" +
"cache=shared&" +
"mode=rwc&" +
"_journal_mode=WAL&" +
"_synchronous=NORMAL&" +
"_cache_size=1000&" +
"_foreign_keys=ON"
db, err := sql.Open("sqlite3", dsn)
if err != nil {
log.Fatalf("Failed to open SQLite connection: %v", err)
}
defer db.Close()
// SQLite-specific configuration
db.SetMaxOpenConns(1) // SQLite doesn't support multiple connections
db.SetMaxIdleConns(1)
db.SetConnMaxLifetime(0) // No connection lifetime limit
db.SetConnMaxIdleTime(0) // No idle time limit
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping SQLite database: %v", err)
}
fmt.Println("SQLite driver configured successfully")
// Test SQLite-specific features
var version string
err = db.QueryRow("SELECT sqlite_version()").Scan(&version)
if err != nil {
log.Printf("Failed to get SQLite version: %v", err)
} else {
fmt.Printf("SQLite version: %s\n", version)
}
}
sqliteDriverConfig()
}
Connection Management
Advanced Connection Management
Connection Lifecycle
Managing the complete lifecycle of database connections.
Connection Monitoring
Monitoring connection health and performance.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"sync"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
// Connection management examples
fmt.Println("Connection management examples:")
// Connection lifecycle management
func connectionLifecycleManagement() {
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Configure connection pool
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("Connection lifecycle management started")
// Simulate connection lifecycle
for i := 0; i < 5; i++ {
fmt.Printf("Iteration %d:\n", i+1)
// Show initial stats
stats := db.Stats()
fmt.Printf(" Initial stats: %+v\n", stats)
// Simulate some database operations
var wg sync.WaitGroup
for j := 0; j < 3; j++ {
wg.Add(1)
go func(id int) {
defer wg.Done()
var result int
err := db.QueryRow("SELECT 1").Scan(&result)
if err != nil {
fmt.Printf(" Error in goroutine %d: %v\n", id, err)
return
}
fmt.Printf(" Goroutine %d: Query successful\n", id)
}(j)
}
wg.Wait()
// Show final stats
stats = db.Stats()
fmt.Printf(" Final stats: %+v\n", stats)
// Wait before next iteration
time.Sleep(2 * time.Second)
}
}
connectionLifecycleManagement()
// Connection health monitoring
func connectionHealthMonitoring() {
dsn := "root:password@tcp(localhost:3306)/testdb?charset=utf8mb4&parseTime=True&loc=Local"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatalf("Failed to open MySQL connection: %v", err)
}
defer db.Close()
// Configure connection pool
db.SetMaxOpenConns(5)
db.SetMaxIdleConns(2)
db.SetConnMaxLifetime(2 * time.Minute)
db.SetConnMaxIdleTime(30 * time.Second)
// Test connection
err = db.Ping()
if err != nil {
log.Fatalf("Failed to ping MySQL database: %v", err)
}
fmt.Println("Connection health monitoring started")
// Health check function
healthCheck := func() error {
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
return db.PingContext(ctx)
}
// Monitor connection health
go func() {
ticker := time.NewTicker(5 * time.Second)
defer ticker.Stop()
for {
select {
case <-ticker.C:
if err := healthCheck(); err != nil {
fmt.Printf("Health check failed: %v\n", err)
} else {
fmt.Println("Health check passed")
}
// Show connection stats
stats := db.Stats()
fmt.Printf("Connection stats: Open=%d, InUse=%d, Idle=%d, WaitCount=%d\n",
stats.OpenConnections, stats.InUse, stats.Idle, stats.WaitCount)
}
}
}()
// Simulate database operations
time.Sleep(15 * time.Second)
}
connectionHealthMonitoring()
}
What You've Learned
Congratulations! You now have a comprehensive understanding of Go's database connection capabilities:
Basic Database Connection
- Understanding the
database/sqlpackage - Connecting to MySQL, PostgreSQL, and SQLite databases
- Testing connections with
Ping() - Understanding connection strings and parameters
Connection Configuration
- Configuring connection parameters for different databases
- Setting connection timeouts and limits
- Understanding database-specific configuration options
- Optimizing connection settings for performance
Connection Pooling
- Understanding connection pool benefits
- Configuring connection pool parameters
- Monitoring connection pool performance
- Handling concurrent database operations
Database Drivers
- Working with different database drivers
- Understanding driver registration
- Configuring driver-specific parameters
- Testing driver-specific features
Connection Management
- Managing connection lifecycle
- Monitoring connection health
- Implementing connection health checks
- Handling connection errors and recovery
Key Concepts
database/sql- Go's standard database interface- Connection Pool - Managing multiple database connections
- Driver - Database-specific implementation
- Connection String - Parameters for database connection
- Connection Lifecycle - Managing connection creation and cleanup
Next Steps
You now have a solid foundation in Go's database connection capabilities. In the next section, we'll explore SQL operations, which will help you perform CRUD operations and complex queries with your database connections.
Understanding database connections is crucial for building any data-driven application. These concepts form the foundation for all the more advanced database operations we'll cover in the coming sections.
Ready to learn about SQL operations? Let's explore CRUD operations and learn how to perform complex database queries efficiently!