Connecting to a Database - Gorm Io

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

MySQL

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

func main() {
  // auf https://github.com/go-sql-driver/mysql#dsn-data-source-name finden sich mehr Informationen
  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 warn %} 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 {% endnote %}

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{})

GaussDB

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

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

We are using gaussdb-go as gaussdb's database/sql driver, it enables prepared statement cache by default, to disable it:

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

Customize Driver

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

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

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

Existing database connection

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

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

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

Oracle Database

The GORM Driver for Oracle provides support for Oracle Database, enabling full compatibility with GORM's ORM capabilities. It is built on top of the Go Driver for Oracle (Godror) and supports key features such as auto migrations, associations, transactions, and advanced querying.

Prerequisite: Install Instant Client

To use ODPI-C with Godror, you’ll need to install the Oracle Instant Client on your system. Follow the steps on this page to complete the installation.

After that, you can connect to the database using the dataSourceName, which specifies connection parameters (such as username and password) using a logfmt-encoded parameter list.

The way you specify the Instant Client directory differs by platform:

macOS and Windows: You can set the libDir parameter in the dataSourceName.
Linux: The libraries must be in the system library search path before your Go process starts, preferably configured with "ldconfig". The libDir parameter does not work on Linux.

Example (macOS/Windows)

dataSourceName := `user="scott" password="tiger" 
                   connectString="dbhost:1521/orclpdb1"
                   libDir="/Path/to/your/instantclient_23_26"`

Example (Linux)

dataSourceName := `user="scott" password="tiger" 
                   connectString="dbhost:1521/orclpdb1"`

Getting Started

import (
  "github.com/oracle-samples/gorm-oracle/oracle"
  "gorm.io/gorm"
)

dataSourceName := `user="scott" password="tiger"
                   connectString="dbhost:1521/orclpdb1"`
db, err := gorm.Open(oracle.Open(dataSourceName), &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
  // "github.com/libtnb/sqlite" // Pure-Go SQLite driver, checkout https://github.com/libtnb/sqlite for details
  "gorm.io/gorm"
)

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

{% note warn %} 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) {% endnote %}

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:9000?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!