gorm

gorm Dialect for Spanner

Google Cloud Spanner ORM for Go's GORM implementation.

This project contains gorm dialects for both Spanner GoogleSQL and Spanner PostgreSQL databases.

GoogleSQL

This example shows how to connect gorm to a Spanner GoogleSQL database.

import (
	"fmt"

	spannergorm "github.com/googleapis/go-gorm-spanner"
	_ "github.com/googleapis/go-sql-spanner"
	"gorm.io/gorm"
)

func helloWorld(projectId, instanceId, databaseId string) error {
	db, err := gorm.Open(spannergorm.New(spannergorm.Config{
		DriverName: "spanner",
		DSN:        fmt.Sprintf("projects/%s/instances/%s/databases/%s", projectId, instanceId, databaseId),
	}), &gorm.Config{PrepareStmt: true})
	if err != nil {
		return fmt.Errorf("failed to open database connection: %v\n", err)
	}
	var msg string
	if err := db.Raw("SELECT 'Hello World!'").Scan(&msg).Error; err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	fmt.Println(msg)

	return nil
}

See the samples directory for more examples.

PostgreSQL

This example shows how to connect gorm to a Spanner PostgreSQL database.

import (
	"fmt"

	spannerpg "github.com/googleapis/go-gorm-spanner/postgresql"
	_ "github.com/googleapis/go-sql-spanner"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

func helloWorld(projectId, instanceId, databaseId string) error {
	db, err := gorm.Open(spannerpg.New(postgres.Config{
		DSN: fmt.Sprintf("projects/%s/instances/%s/databases/%s", projectId, instanceId, databaseId),
	}), &gorm.Config{})
	if err != nil {
		return fmt.Errorf("failed to open database connection: %v\n", err)
	}
	var msg string
	if err := db.Raw("SELECT $1::varchar as greeting", "Hello World from Spanner PostgreSQL!").Scan(&msg).Error; err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	fmt.Println(msg)

	return nil
}

See the PostgreSQL samples directory for more examples for how to use this library with a Spanner PostgreSQL database.

Connection URL Properties

The Cloud Spanner GORM supports the following connection URL properties

Commonly Used Properties

• credentials (String): File name for the credentials to use. The connection will use the default credentials of the environment if no credentials file is specified in the connection string. Example: projects/my-project/instances/my-instance/databases/my-db;credentials=/path/to/credentials.json
• optimizerVersion (String): Sets the default query optimizer version to use for this connection. See also https://cloud.google.com/spanner/docs/query-optimizer/query-optimizer-versions.
• isolationLevel (String): Sets the default isolation level for read/write transaction. The default is sql.LevelSerializable. Other supported values are sql.LevelRepeatableRead. Example: fmt.Sprintf("projects/my-project/instances/my-instance/databases/my-db;isolationLevel=%s", sql.LevelRepeatableRead)

Note that Spanner gorm uses a single multiplexed session for all operations from version 1.9.0 and higher. Multiplexed sessions can handle any number of concurrent queries and transactions. This means that there is no need to configure the minimum and maximum number of sessions for gorm.

See https://cloud.google.com/spanner/docs/sessions#multiplexed_sessions for more information about multiplexed sessions.

Advanced Properties

• numChannels (int): Sets the number of gRPC channels to use. Defaults to 4.
• retryAbortsInternally (boolean): Boolean that indicates whether the connection should automatically retry aborted errors. The default is true.
• disableRouteToLeader (boolean): Boolean that indicates if all the requests of type read-write and PDML need to be routed to the leader region. The default is false.
• usePlainText (boolean): : Boolean that indicates whether the connection should use plain text communication or not. Set this to true to connect to local mock servers that do not use SSL. Example: projects/test-project/instances/test-instance/databases/test-db;usePlainText=true

Example: projects/my-project/instances/my-instance/databases/my-db;numChannels=4;retryAbortsInternally=true;disableRouteToLeader=false;usePlainText=false

Additional Spanner Configuration

You can also connect gorm to Spanner using a driver.Connector. This allows you to supply additional configuration for the Spanner client that should be used for gorm:

// Create a function that sets the Spanner client configuration for the database connection.
configureFunction := func(config *spanner.ClientConfig, opts *[]option.ClientOption) {
    // Set a default query optimizer version that the client should use.
    config.QueryOptions = spanner.QueryOptions{Options: &spannerpb.ExecuteSqlRequest_QueryOptions{OptimizerVersion: "1"}}
}
// Create a ConnectorConfig to use with the Spanner database/sql driver.
config := spannerdriver.ConnectorConfig{
    Project:      projectId,
    Instance:     instanceId,
    Database:     databaseId,
    Configurator: configureFunction,
}
// Create a Connector for Spanner. This Connector instance should be re-used for all gorm connections.
c, err := spannerdriver.CreateConnector(config)
db, err := gorm.Open(
    spannergorm.New(spannergorm.Config{Connector: c}),
    &gorm.Config{PrepareStmt: true})

See custom_spanner_config.go for a working sample application.

Emulator

See the Google Cloud Spanner Emulator support to learn how to start the emulator. When the emulator has been started and the environment variable has been set, gorm will automatically connect to the emulator instead of Cloud Spanner.

$ gcloud emulators spanner start
$ export SPANNER_EMULATOR_HOST=localhost:9010

Go Versions Supported

The Spanner gorm dialect follows the Go release policy and supports the two latest major Go versions.

Data Types

Spanner supports the following data types in combination with gorm.

See data_types.go for a working sample for each data type.

You can also use arrays and protobuf columns with gorm. See the following samples for how to map and use those types:

• Arrays: array_data_type.go
• Protobuf: protobuf_columns.go

Auto-increment Primary Keys

Columns that are marked as auto-increment in gorm use IDENTITY columns in Spanner by default. IDENTITY columns use a backing bit-reversed sequence for value generation. These values are guaranteed to be unique and safe to use as generated primary key values with Spanner. See https://cloud.google.com/spanner/docs/primary-key-default-value#identity-columns for more information on IDENTITY columns.

The following model uses an IDENTITY column with a backing bit-reversed sequence for primary key values:

type singer struct {
	gorm.Model
	Name string
}

Sequence Kind

IDENTITY columns by default use a bit-reversed sequence to generate values. You can set the type of sequence to use in the gorm dialector configuration. The default and currently only supported value is BIT_REVERSED_POSITIVE.

In addition, the configuration option supports two special values:

• DISABLED: Disable the use of IDENTITY columns and use bit-reversed sequences instead. See below for more information.
• AUTO_INCREMENT: Generate AUTO_INCREMENT columns instead of IDENTITY columns. AUTO_INCREMENT columns use the default sequence kind that has been configured in the database. See https://cloud.google.com/spanner/docs/primary-key-default-value#serial-auto-increment for more information on AUTO_INCREMENT columns.

Example:

// This dialector uses AUTO_INCREMENT columns instead of IDENTITY columns.
dialector := New(Config{
    DriverName:          "spanner",
    DSN:                 fmt.Sprintf("projects/my-project/instances/my-instance/databases/my-database"),
    DefaultSequenceKind: "AUTO_INCREMENT",
})

Sequences

You can also manually assign a sequence to be used for primary key generation for a model. Use the gorm_sequence_name tag to specify a sequence name.

This model uses a sequence named singer_sequence for primary key generation.

type singer struct {
	ID       uint `gorm:"primarykey" gorm_sequence_name:"singer_sequence"`
	Name     string
}

You can also configure Spanner gorm to use sequences for to generate primary key values for all models. Set the DefaultSequenceKind in the dialector configuration for this:

dialector := New(Config{
    DriverName:          "spanner",
    DSN:                 fmt.Sprintf("projects/my-project/instances/my-instance/databases/my-database"),
    DefaultSequenceKind: "DISABLED",
})

// This model uses a bit-reversed sequence for primary key generation,
// as DefaultSequenceKind has been set to DISABLED in the configuration.
type singer struct {
    gorm.Model
    Name string
}

AutoMigrate Dry Run

The Spanner gorm dialect supports dry-runs for auto-migration. Use this to get the DDL statements that would be generated and executed by auto-migration. You can manually verify and modify these statements to optimize your data model.

Example:

tables := []interface{}{&singer{}, &album{}}

// Unwrap the underlying SpannerMigrator interface. This interface supports
// the `AutoMigrateDryRun` method, which does not actually execute the
// generated statements, and instead just returns these as an array.
m := db.Migrator()
migrator, ok := m.(spannergorm.SpannerMigrator)
if !ok {
    return fmt.Errorf("unexpected migrator type: %v", m)
}
statements, err := migrator.AutoMigrateDryRun(tables...)

Limitations

The Spanner gorm dialect has the following known limitations:

For the complete list of the limitations, see the Spanner GORM limitations.

Nested Transactions

gorm uses savepoints for nested transactions. Savepoints are currently not supported by Cloud Spanner. Nested transactions can therefore not be used with GORM.

Authorization

By default, each API will use Google Application Default Credentials for authorization credentials used in calling the API endpoints. This will allow your application to run in many environments without requiring explicit configuration.

Contributing

Contributions are welcome. Please, see the CONTRIBUTING document for details.

Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms. See Contributor Code of Conduct for more information.