Connecting to a Database

GORM officially supports the databases MySQL, PostgreSQL, SQLite, SQL Server, and TiDB

MySQL

import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
)

func main() {
// refer https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
}

NOTE:
To handle time.Time correctly, you need to include parseTime as a parameter. (more parameters)
To fully support UTF-8 encoding, you need to change charset=utf8 to charset=utf8mb4. See this article for a detailed explanation

MySQL Driver provides a few advanced configurations which can be used during initialization, for example:

db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "gorm:gorm@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: true, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: true, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false, // auto configure based on currently MySQL version
}), &gorm.Config{})

Customize Driver

GORM allows to customize the MySQL driver with the DriverName option, for example:

import (
_ "example.com/my_mysql_driver"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)

db, err := gorm.Open(mysql.New(mysql.Config{
DriverName: "my_mysql_driver",
DSN: "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8&parseTime=True&loc=Local", // data source name, refer https://github.com/go-sql-driver/mysql#dsn-data-source-name
}), &gorm.Config{})

Existing database connection

GORM allows to initialize *gorm.DB with an existing database connection

import (
"database/sql"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)

sqlDB, err := sql.Open("mysql", "mydb_dsn")
gormDB, err := gorm.Open(mysql.New(mysql.Config{
Conn: sqlDB,
}), &gorm.Config{})

PostgreSQL

import (
"gorm.io/driver/postgres"
"gorm.io/gorm"
)

dsn := "host=localhost user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

We are using pgx as postgres’s database/sql driver, it enables prepared statement cache by default, to disable it:

// https://github.com/go-gorm/postgres
db, err := gorm.Open(postgres.New(postgres.Config{
DSN: "user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai",
PreferSimpleProtocol: true, // disables implicit prepared statement usage
}), &gorm.Config{})

Customize Driver

GORM allows to customize the PostgreSQL driver with the DriverName option, for example:

import (
_ "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/postgres"
"gorm.io/gorm"
)

db, err := gorm.Open(postgres.New(postgres.Config{
DriverName: "cloudsqlpostgres",
DSN: "host=project:region:instance user=postgres dbname=postgres password=password sslmode=disable",
})

Existing database connection

GORM allows to initialize *gorm.DB with an existing database connection

import (
"database/sql"
"gorm.io/driver/postgres"
"gorm.io/gorm"
)

sqlDB, err := sql.Open("pgx", "mydb_dsn")
gormDB, err := gorm.Open(postgres.New(postgres.Config{
Conn: sqlDB,
}), &gorm.Config{})

SQLite

import (
"gorm.io/driver/sqlite" // Sqlite driver based on CGO
// "github.com/glebarez/sqlite" // Pure go SQLite driver, checkout https://github.com/glebarez/sqlite for details
"gorm.io/gorm"
)

// github.com/mattn/go-sqlite3
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})

NOTE: You can also use file::memory:?cache=shared instead of a path to a file. This will tell SQLite to use a temporary database in system memory. (See SQLite docs for this)

SQL Server

import (
"gorm.io/driver/sqlserver"
"gorm.io/gorm"
)

// github.com/denisenkom/go-mssqldb
dsn := "sqlserver://gorm:LoremIpsum86@localhost:9930?database=gorm"
db, err := gorm.Open(sqlserver.Open(dsn), &gorm.Config{})

TiDB

TiDB is compatible with MySQL protocol. You can follow the MySQL part to create a connection to TiDB.

There are some points noteworthy for TiDB:

  • You can use gorm:"primaryKey;default:auto_random()" tag to use AUTO_RANDOM feature for TiDB.
  • TiDB supported SAVEPOINT from v6.2.0, please notice the version of TiDB when you use this feature.
  • TiDB supported FOREIGN KEY from v6.6.0, please notice the version of TiDB when you use this feature.
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)

type Product struct {
ID uint `gorm:"primaryKey;default:auto_random()"`
Code string
Price uint
}

func main() {
db, err := gorm.Open(mysql.Open("root:@tcp(127.0.0.1:4000)/test"), &gorm.Config{})
if err != nil {
panic("failed to connect database")
}

db.AutoMigrate(&Product{})

insertProduct := &Product{Code: "D42", Price: 100}

db.Create(insertProduct)
fmt.Printf("insert ID: %d, Code: %s, Price: %d\n",
insertProduct.ID, insertProduct.Code, insertProduct.Price)

readProduct := &Product{}
db.First(&readProduct, "code = ?", "D42") // find product with code D42

fmt.Printf("read ID: %d, Code: %s, Price: %d\n",
readProduct.ID, readProduct.Code, readProduct.Price)
}

Clickhouse

https://github.com/go-gorm/clickhouse

import (
"gorm.io/driver/clickhouse"
"gorm.io/gorm"
)

func main() {
dsn := "tcp://localhost:9500?database=gorm&username=gorm&password=gorm&read_timeout=10&write_timeout=20"
db, err := gorm.Open(clickhouse.Open(dsn), &gorm.Config{})

// Auto Migrate
db.AutoMigrate(&User{})
// Set table options
db.Set("gorm:table_options", "ENGINE=Distributed(cluster, default, hits)").AutoMigrate(&User{})

// Insert
db.Create(&user)

// Select
db.Find(&user, "id = ?", 10)

// Batch Insert
var users = []User{user1, user2, user3}
db.Create(&users)
// ...
}

Connection Pool

GORM using database/sql to maintain connection pool

sqlDB, err := db.DB()

// SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
sqlDB.SetMaxIdleConns(10)

// SetMaxOpenConns sets the maximum number of open connections to the database.
sqlDB.SetMaxOpenConns(100)

// SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
sqlDB.SetConnMaxLifetime(time.Hour)

Refer Generic Interface for details

Unsupported Databases

Some databases may be compatible with the mysql or postgres dialect, in which case you could just use the dialect for those databases.

For others, you are encouraged to make a driver, pull request welcome!

Platinum Sponsors

Gold Sponsors

Platinum Sponsors

Gold Sponsors