Introduction
GORM is an Object Relational Mapping (ORM) library for Golang. ORM converts data between incompatible type systems using object-oriented programming languages. An ORM library is a library that implements this technique and provides an object-oriented layer between relational databases and object-oriented programming languages. Do you have an application that you developed with GORM and now you want to migrate to a cloud database? Google Cloud Spanner now supports the GORM Object Relational Mapper. You can reuse existing GORM code with the Cloud Spanner, or write new features while leveraging existing knowledge.
The Cloud Spanner Go ORM implements all the classes and interfaces that are needed to use Cloud Spanner with GORM. Add the spanner GORM module to your project and connect to Cloud Spanner as follows:
Installing
Simple install the package to your $GOPATH with the go tool from shell:
code_block<ListValue: [StructValue([(‘code’, ‘$ go get -u github.com/googleapis/go-gorm’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eac631632e0>)])]>
Connecting
Connect to Cloud Spanner with GORM:
code_block<ListValue: [StructValue([(‘code’, ‘import (rnt_ “github.com/googleapis/go-sql-spanner”rntspannergorm “github.com/googleapis/go-gorm”rn)rnrn// Creates a prepared statement when executing any SQL and caches them to speed up future callsrndb, err := gorm.Open(spannergorm.New(spannergorm.Config{rntDriverName: “spanner”,rntDSN: “projects/MY-PROJECT/instances/MY-INSTANCE/databases/MY-DATABASE”,rn}), &gorm.Config{PrepareStmt: true})rnif err != nil {rntlog.Fatal(err)rn}rnrn// Print singers with more than 500 likes.rntype Singers struct {rntID int `gorm:”primarykey;autoIncrement:false”`rntName stringrntLikes intrn}rnvar singers []Singersrnif err := db.Where(“likes > ?”, 500).Find(&singers).Error; err != nil {rntlog.Fatal(err)rn}rnfor t := range singers {rntfmt.Println(t.ID, t.Name)rn}’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eac63163910>)])]>
Note that as GORM implementation uses the Go client to connect to Spanner, it is not necessary to specify a custom endpoint to connect to the Spanner emulator(a local, in-memory emulator, which you can use to develop and test your applications for free without creating a GCP Project or a billing account). The driver will automatically connect to the emulator if the SPANNER_EMULATOR_HOST environment variable has been set.
Usage
The Cloud Spanner GORM supports standard GORM features, like querying data using both positional and named parameters, managing associations and executing transactions. GORM also supports specific Spanner features such as interleaved tables, mutations, and stale reads by unwrapping the underlying connection. This means that you can use GORM to write code that is both powerful and scalable.
For example, the following code snippet shows a simple write and read operation to Spanner, backed by Spanner’s infinite scalability. If your product takes off, you don’t have to rewrite anything for sharding, etc. Just add more nodes!
code_block<ListValue: [StructValue([(‘code’, ‘package mainrnrnrnimport (rn “fmt”rn “log”rnrnrn “gorm.io/gorm”rn “gorm.io/gorm/logger”rn _ “github.com/googleapis/go-sql-spanner”rn spannergorm “github.com/googleapis/go-gorm-spanner”rn)rnrnrnfunc main() {rn // Open db connection.rn db, err := gorm.Open(spannergorm.New(spannergorm.Config{rn DriverName: “spanner”,rn DSN: “projects/my-project/instances/my-instance/databases/my-database”,rn }), &gorm.Config{PrepareStmt: true, IgnoreRelationshipsWhenMigrating: true, Logger: logger.Default.LogMode(logger.Error)})rn if err != nil {rn log.Fatalf(“failed to open database: %v”, err)rn }rnrnrn type User struct {rn gorm.Modelrn Name stringrn Email stringrn }rn if err := db.AutoMigrate(&User{}); err != nil {rn log.Fatalf(“failed to migrate: %v”, err)rn }rnrnrn // Insert a new userrn db.Save(&User{Name: “Alice”, Email: “
[email protected]”})rnrnrn // Read the user back outrn var user Userrn if err := db.First(&user, “email = ?”, “
[email protected]”).Error; err != nil {rn log.Fatalf(“Failed to find created data, got error: %v”, err)rn }rnrnrn // Print the user’s namern fmt.Println(user.Name)rn}’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eac63163ac0>)])]>
Interleaved Tables
Using interleaved tables for parent-child relationships in your schema can improve performance. Interleaving a child table with a parent means that the child records will be stored physically together with the parent. These relationships can be modeled and used as any other association in GORM.
code_block<ListValue: [StructValue([(‘code’, ‘CREATE SEQUENCE IF NOT EXISTS albums_seq OPTIONS (sequence_kind = “bit_reversed_positive”);rnrnrnCREATE TABLE IF NOT EXISTS `albums` (rn `id` INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(Sequence albums_seq)),rn `created_at` TIMESTAMP,rn `updated_at` TIMESTAMP,rn `deleted_at` TIMESTAMP,rn `title` STRING(MAX),rn `marketing_budget` FLOAT64,rn `release_date` date,rn `cover_picture` BYTES(MAX),rn `singer_id` INT64,rn CONSTRAINT `fk_singers_albums` FOREIGN KEY (`singer_id`) REFERENCES `singers`(`id`)rn) PRIMARY KEY (`id`);rnCREATE SEQUENCE IF NOT EXISTS tracks_seq OPTIONS (sequence_kind = “bit_reversed_positive”);rnrnrnCREATE TABLE IF NOT EXISTS `tracks` (rn `id` INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(Sequence tracks_seq)),rn `created_at` TIMESTAMP,rn `updated_at` TIMESTAMP,rn `deleted_at` TIMESTAMP,rn `track_number` INT64,rn `title` STRING(MAX),rn `sample_rate` FLOAT64,rn ) PRIMARY KEY (`id`,`track_number`), INTERLEAVE IN PARENT albums ON DELETE CASCADE;rnrn// Above schema can be mapped to below structs to work with GORMrntype Album struct {rn gorm.Modelrn Title stringrn MarketingBudget sql.NullFloat64rn ReleaseDate spanner.NullDatern CoverPicture []bytern SingerId int64rn Singer Singerrn Tracks []Track `gorm:”foreignKey:id”`rn}rnrn// Track is interleaved in Album. The ID column is both the first part of the primary key of Track, and arn// reference to the Album that owns the Track.rntype Track struct {rn gorm.Modelrn TrackNumber int64 `gorm:”primaryKey;autoIncrement:false”`rn Title stringrn SampleRate float64rn Album Album `gorm:”foreignKey:id”`rn}’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eac63163340>)])]>
Mutations
GORM will use Data Manipulation Language (DML) by default. You can run mutations by unwrapping the Spanner connection:
code_block<ListValue: [StructValue([(‘code’, ‘import (rntspannerdriver “github.com/googleapis/go-sql-spanner”rntspannergorm “github.com/googleapis/go-gorm”rn)rnrndb, err := gorm.Open(spannergorm.New(spannergorm.Config{rn DriverName: “spanner”,rn DSN: “projects/MY-PROJECT/instances/MY-INSTANCE/databases/MY-DATABASE”,rn}), &gorm.Config{PrepareStmt: true})rnif err != nil {rn log.Fatal(err)rn}rndatabaseSQL, _ := db.DB()rnconn, _ := databaseSQL.Conn(context.Background())rntype singer struct {rn gorm.Modelrn FirstName stringrn LastName stringrn Active boolrn}rnm, err := spanner.InsertOrUpdateStruct(“Singers”, &singer{rn Model: gorm.Model{rn ID: uint(1+rnd.Int()%(1000000-1)),rn CreatedAt: time.Now(),rn UpdatedAt: time.Now(),rn },rn FirstName: “test”,rn LastName: “test”,rn Active: true,rn})rnif err != nil {rn return errrn}rnconn.Raw(func(driverConn interface{}) error {rn return driverConn.(spannerdriver.SpannerConn).BufferWrite([]*spanner.Mutation{m})rn})’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eac631636d0>)])]>
Cloud Blog
