Using ent. with PlanetScale

Discussion

Why use ent. with PlanetScale?

ent. is an ORM for Go that provides a typed API to your DB schema. We previously used GORM and while it's a good option, we found ourselves searching for an alternative with stronger typing support and controlled schema migrations.

PlanetScale is a powerful serverless MySQL-compatible database that's super easy to set up and has a great free tier. It also provides other interesting features, like schema branching and merging, which we won't be discussing in this guide.

Why use an ORM with type checking?

With GORM queries typically look like this db.Where("name = ?", name).Find(&user). Since the queries use strings to define elements such as field names they can be prone to spelling mistakes/copy-paste errors, meaning testing and debugging are needed to resolve these issues when they occur.

By comparison, the equivalent in ent is db.User.Query().Where(user.NameEQ(name)).First(context.TODO()). As you can see, this query provides far stronger type safety due to the availability of functions such as user.NameEQ().

In our experience, this reduces the chance of errors and catches many errors before or during compile time.

To use versioned migrations or PlanetScale schema merges?

Currently, we don't use PlanetScale's schema merges, although we are exploring it. In the meantime, we're using versioned migrations. The thinking behind this is that we'll need versioned migrations in our dev branch regardless, so it's a good place to start.

Another question we have is what happens in the time between upgrading the software deployment and upgrading the schema? If you do the schema upgrade first, and the old app uses the newer schema you might get failures. If you do the deployment first then you will have a period where the old schema is still in use. In both of these cases, your app needs to be either forward or backward schema compatible.

We are currently running the schema upgrade within our app, so this is happening exactly when it is required. This provides the benefit of not needing schema compatibility. However, it also comes with the risk of causing downtime if the schema upgrade fails, so it's something we'll change in time.

Let's get coding

In this guide, we'll build a simple command-line application that interacts with a PlanetScale database using ent as an ORM. The steps we are going to take are:

Create a repo
Perform a basic setup of ent
Add a migration
Setup a PlanetScale DB
Run our app

Prerequisites

Go
A free PlanetScale account
PlanetScale CLI — You can also follow this tutorial in the PlanetScale admin dashboard, but the CLI will make setup quicker.

Setup the base project

cd $GOPATH/src/github.com/<ghuser>
mkdir entgo-planetscale
cd entgo-planetscale
go mod init github.com/<ghuser>/entgo-planetscale

Use ent to create some entities

go install entgo.io/ent/cmd/ent
ent init User

Change to versioned migrations

--- a/ent/generate.go
+++ b/ent/generate.go
@@ -1,3 +1,3 @@
 package ent

-//go:generate go run -mod=mod entgo.io/ent/cmd/ent generate ./schema
+//go:generate go run -mod=mod entgo.io/ent/cmd/ent generate --feature sql/versioned-migration ./schema

Run the generate

go generate ./...

Create a migration file

Run a local MySQL database that is empty, so we can create migrations against it. Remember to run this fresh each time you generate migrations.

In this example, we'll start the new database in a container using Docker:

docker run --name migration --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=test -d mysql

Create the main.go file as follows

package main

import (
  "context"
  "fmt"
  "os"

  "github.com/spf13/cobra"

  "github.com/nitrictech/entgo-planetscale-example/ent"
  "github.com/nitrictech/entgo-planetscale-example/ent/user"
)

var (
  db *ent.Client

  migrationExecuteCmd = &cobra.Command{
    Use:   "execute",
    Short: "Execute the migrations",
    RunE: func(cmd *cobra.Command, args []string) error {
      var err error
      db, err = mysqlConnectAndMigrate(os.Getenv("DSN"), true)
      return err
    },
  }
  migrationCreateCmd = &cobra.Command{
    Use:   "create <name>",
    Short: "Create a new migration",
    Args:  cobra.ExactArgs(1),
    RunE: func(cmd *cobra.Command, args []string) error {
      return createMigration(args[0])
    },
  }
  migrationCmd = &cobra.Command{
    Use: "migration",
  }

  rootCmd = &cobra.Command{
    Use:   "cmd",
    Short: "entgo + planetscale example",
  }
)

func init() {
  // migration commands
  migrationCmd.AddCommand(migrationCreateCmd)
  migrationCmd.AddCommand(migrationExecuteCmd)
  rootCmd.AddCommand(migrationCmd)
}

func main() {
  err := rootCmd.Execute()
  if err != nil {
    os.Exit(1)
  }
}

Create a second file mysql.go

package main

import (
  "context"
  "database/sql"
  "time"

  atlas "ariga.io/atlas/sql/migrate"
  "entgo.io/ent/dialect"
  entsql "entgo.io/ent/dialect/sql"
  "entgo.io/ent/dialect/sql/schema"
  _ "github.com/go-sql-driver/mysql"
  sqlmysql "github.com/go-sql-driver/mysql"
  gomigrate "github.com/golang-migrate/migrate/v4"
  _ "github.com/golang-migrate/migrate/v4/database/mysql"
  _ "github.com/golang-migrate/migrate/v4/source/file"
  "github.com/pkg/errors"
  "github.com/sirupsen/logrus"

  "github.com/nitrictech/entgo-planetscale-example/ent"
  "github.com/nitrictech/entgo-planetscale-example/ent/migrate"
  "github.com/nitrictech/entgo-planetscale-example/ent/migrate/migrations"
)

func createMigration(name string) error {
  if name == "" {
    return errors.New("migration name is required. Use: 'go run ./cmd/migration <name>'")
  }

  dir, err := atlas.NewLocalDir("ent/migrate/migrations")
  if err != nil {
    return errors.WithMessage(err, "failed creating atlas migration directory")
  }

  opts := []schema.MigrateOption{
    schema.WithDir(dir),                         // provide migration directory
    schema.WithMigrationMode(schema.ModeReplay), // provide migration mode
    schema.WithDialect(dialect.MySQL),           // Ent dialect to use
    schema.WithForeignKeys(false),               // planetscale uses https://vitess.io/ that requires foreign keys off
    schema.WithDropColumn(true),
  }

  // Generate migrations using Atlas support for MySQL (note the Ent dialect option passed above).
  return migrate.NamedDiff(context.TODO(), "mysql://root:pass@localhost:3306/deploy-test", name, opts...)
}

func mysqlConnectAndMigrate(dsn string, migrate bool) (*ent.Client, error) {
  pDSN, err := sqlmysql.ParseDSN(dsn)
  if err != nil {
    return nil, err
  }

  pDSN.ParseTime = true
  pDSN.Loc = time.Local

  if pDSN.Params == nil {
    pDSN.Params = map[string]string{}
  }

  pDSN.Params["tls"] = "true"
  pDSN.Params["charset"] = "utf8mb4"
  if migrate {
    pDSN.Params["multiStatements"] = "true"
  }

  dsn = pDSN.FormatDSN()

  db, err := sql.Open(dialect.MySQL, dsn)
  if err != nil {
    return nil, err
  }

  if migrate {
    d, err := migrations.MigrationFS()
    if err != nil {
      return nil, errors.WithMessage(err, "iofs.New")
    }

    m, err := gomigrate.NewWithSourceInstance("iofs", d, "mysql://"+dsn)
    if err != nil {
      return nil, errors.WithMessage(err, "NewWithSourceInstance")
    }

    if err := m.Up(); err != nil {
      if !errors.Is(err, gomigrate.ErrNoChange) {
        return nil, errors.WithMessage(err, "db migrations update")
      }
    }
  }

  return ent.NewClient(
    ent.Driver(entsql.NewDriver(
      dialect.MySQL,
      entsql.Conn{ExecQuerier: db})),
    ent.Log(logrus.Info)), nil
}

Generate the new migration

mkdir -p ent/migrate/migrations
go run . migration create add-users

This will create the following migrations

diff --git a/ent/migrate/migrations/20221012050944_add-users.down.sql b/ent/migrate/migrations/20221012050944_add-users.down.sql
new file mode 100644
index 0000000..6a8c12c
--- /dev/null
+++ b/ent/migrate/migrations/20221012050944_add-users.down.sql
@@ -0,0 +1,2 @@
+-- reverse: create "users" table
+DROP TABLE `users`;
diff --git a/ent/migrate/migrations/20221012050944_add-users.up.sql b/ent/migrate/migrations/20221012050944_add-users.up.sql
new file mode 100644
index 0000000..ea87419
--- /dev/null
+++ b/ent/migrate/migrations/20221012050944_add-users.up.sql
@@ -0,0 +1,2 @@
+-- create "users" table
+CREATE TABLE `users` (`id` bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) CHARSET utf8mb4 COLLATE utf8mb4_bin;
diff --git a/ent/migrate/migrations/atlas.sum b/ent/migrate/migrations/atlas.sum
new file mode 100644
index 0000000..8d400fc
--- /dev/null
+++ b/ent/migrate/migrations/atlas.sum
@@ -0,0 +1,3 @@
+h1:gMofK6wbvoWIZX3MHz8m96y9UpeW9JopKvXkof65qII=
+20221012050944_add-users.down.sql h1:xM7q8EP/VvWoWKEZEX6DLmTjGwK1B1pImDjbXqXNI+s=
+20221012050944_add-users.up.sql h1:2mXXnpykKV7RIs8kYK0ZM9Y8HtryKRAFcndW0f/6EEY=

Add some more fields to the User object

Edit ent/schema/user.go

func (User) Fields() []ent.Field {
-  return nil
+  return []ent.Field{
+    field.String("name"),
+    field.String("email"),
+  }
 }

go generate ./...
go run . migration create user-name-email

Setup a PlanetScale database

Taken from: https://planetscale.com/docs/tutorials/connect-go-gorm-app

pscale auth login
pscale database create <DATABASE_NAME> --region <REGION_SLUG>
pscale password create <DATABASE_NAME> <BRANCH_NAME> <PASSWORD_NAME>

Take note of the values returned to you, as you won't be able to see this password again. Export an environment variable "DSN" with the value from above. Below is the value for the local MySQL.

export DSN="root:pass@tcp(localhost:3306)/test"

Create an example app

var (
  db *ent.Client

  userName      string
  email         string
  userID        int
  userCreateCmd = &cobra.Command{
    Use:   "create",
    Short: "create a user in the DB",
    RunE: func(cmd *cobra.Command, args []string) error {
      return db.User.Create().
        SetName(userName).
        SetEmail(email).
        Exec(context.TODO())
    },
  }
  userListCmd = &cobra.Command{
    Use:   "list",
    Short: "list the users in the DB",
    RunE: func(cmd *cobra.Command, args []string) error {
      users, err := db.User.Query().All(context.TODO())
      if err != nil {
        return err
      }
      for _, u := range users {
        fmt.Println(u.String())
      }

      return nil
    },
  }
  userDeleteCmd = &cobra.Command{
    Use:   "delete",
    Short: "delete the user from the DB",
    RunE: func(cmd *cobra.Command, args []string) error {
      _, err := db.User.Delete().Where(user.IDEQ(userID)).Exec(context.TODO())
      return err
    },
  }
  userCmd = &cobra.Command{
    Use:   "user",
    Short: "user DB CRUD commands",
    PersistentPreRunE: func(cmd *cobra.Command, args []string) error {
      var err error
      db, err = mysqlConnectAndMigrate(os.Getenv("DSN"), false)
      return err
    },
    PersistentPostRunE: func(cmd *cobra.Command, args []string) error {
      return db.Close()
    },
  }
)

func init() {
  // user CRUD commands
  rootCmd.AddCommand(userCmd)
  userCmd.AddCommand(userCreateCmd)
  userCreateCmd.Flags().StringVarP(&userName, "name", "n", "", "-n John Deer")
  userCreateCmd.Flags().StringVarP(&email, "email", "e", "", "-e dearjohn@gmail.com")
  userCmd.AddCommand(userListCmd)
  userCmd.AddCommand(userDeleteCmd)
  userDeleteCmd.Flags().IntVarP(&userID, "id", "i", 0, "-i 4")
}

func main() {
  err := rootCmd.Execute()
  if err != nil {
    os.Exit(1)
  }
}

Run the app

go run . migration execute # run the migration

go run . user list
go run . user create -n "John Deer" -e "dearjohn@example.com"
go run . user list
User(id=1, name=John Deer, email=dearjohn@example.com)
go run . user delete -i 1
go run . user list