Thomas Edgesmith

[email protected]

Connecting to PlanetScale with Phoenix (Elixir)

I’ve been starting to learn Phoenix recently and wanted to see how hard it was to get PlanetScale connected.

This is mostly a post for myself on the next project.

As an aside, PlanetScale is pretty sweet, working at Shopify for a few years I’ve come to love MySQL for what it is. Working with branches and easy schema changes is really nice.

Configuring Dev

I’ve opted to do something similar in dev like PlanetScale blogged about.

Here’s my workflow with PlanetScale:

1) Create a new branch & switch to it

# creating a new branch
pscale branch switch branch_name --database db_name --create

2) Connect via pscale

# I've opted to use port `3305`, since normally it'd just be `3306` locally.
pscale connect db_name --port=3305

Running migrations is a matter of doing:

PSCALE=1 mix ecto.migrate

To get this all working, I’ve had to tweak the normal dev.exs DB config:

# config/dev.exs

db_name = if System.get_env("PSCALE"), do: "db_name", else: "db_name_dev"
db_hostname = if System.get_env("PSCALE"), do: "127.0.0.1", else: "localhost"
db_port = if System.get_env("PSCALE"), do: 3305, else: 3306

# Configure your database
config :db_name, db_name.Repo,
  username: "root",
  password: "",
  database: db_name,
  hostname: db_hostname,
  port: db_port,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

Whenever I’m needing to interact or change production’s DB schema I’ll follow these steps. Otherwise for day to day coding, I’m just running a MySQL DB locally.

Configuring Production

PlanetScale will give you an elixir config like this:

hostname = "random-host-name.region.psdb.cloud"

{:ok, pid} = MyXQL.start_link(username: "random-username",
  database: "db_name",
  hostname: hostname,
  password: "************",
  ssl: true,
  ssl_opts: [
    verify: :verify_peer,
    cacertfile: CAStore.file_path(),
    server_name_indication: String.to_charlist(hostname),
    customize_hostname_check: [
      match_fun: :public_key.pkix_verify_hostname_match_fun(:https)
    ]
  ]
)

So you’ll need to adjust your runtime.exs config a bit:

database_name =
 System.get_env("DATABASE_NAME") ||
   raise """
   environment variable DATABASE_NAME is missing.
   For example: my_db_name (see planetscale connection guides)
   """

database_hostname =
 System.get_env("DATABASE_HOSTNAME") ||
   raise """
   environment variable DATABASE_HOSTNAME is missing.
   For example: random-hostname.region.psdb.cloud (see planetscale connection guides)
   """

database_username =
 System.get_env("DATABASE_USERNAME") ||
   raise """
   environment variable DATABASE_USERNAME is missing.
   For example: ie83n0neoerr (see planetscale connection guides)
   """

database_password =
 System.get_env("DATABASE_PASSWORD") ||
   raise """
   environment variable DATABASE_PASSWORD is missing.
   For example: pscale_pw_ceT0k5mVVzi4GWPPyOaFORbpf4yGsf (see planetscale connection guides)
   """

config :db_name, AppName.Repo,
 username: database_username,
 database: database_name,
 hostname: database_hostname,
 password: database_password,
 ssl: true,
 ssl_opts: [
   verify: :verify_peer,
   cacertfile: CAStore.file_path(),
   server_name_indication: String.to_charlist(database_hostname),
   customize_hostname_check: [
     match_fun: :public_key.pkix_verify_hostname_match_fun(:https)
   ]
 ]

I’ve opted to just use a env for almost all configuration, to make it a bit easier to copy/paste on the next app.

gist

December 4, 2021  @SourdoughDev