Kagi DB Practices

The following is a quick summary of techniques that we use to program our database layer, that has allowed us to scale our DB code with minimal issues as our app has grown in complexity and user base size.

Tooling

Cloud

We run on GCP, using Postgres under Cloud SQL, using one primary instance and many zonal read-replicas.

Libraries

We write in raw SQL using crystal-db and crystal-pg.

Migrations

We use micrate. It has some rough edges, but it's a great & simple tool.

psql

We make heavy use of psql for quick development. Particularly it becomes very nice to work with using \e to open $EDITOR to edit queries.

Once the query works as we expect it, because we do not use any ORM, it is mostly just a matter of pasting the query into our code and writing a datatype (if relevant).

I can also recommend some lines from my .psqlrc:

\set QUIET 1
\x auto
\timing
\set COMP_KEYWORD_CASE upper
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
\set PROMPT2 '[more] %R > '
\set VERBOSITY verbose
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\pset null '(null)'
\pset pager off
\pset linestyle unicode
\unset QUIET

With options like \timing, we get an immediate sense if something could be slow, and also have the chance to run the query planner to do analysis before even integrating the query into code.

It also means use the same tool locally as we do for remote debugging on staging or production database instances - it's a great tool to become familiar with.

API Design

All queries are exposed as static methods in our DB module:

module DB
  struct ReturnType
    include ::DB::Serializable
    include ::DB::Serializable::NonStrict

    getter col1 : Int32
    getter col2 : Bool
  end

  def self.table_query_name(...) : ReturnType
    # ..
  end
end

Queries are NOT written for arbitrary CRUD updates to the backing table. Sometimes we do end up writing a plain CRUD layer, but that is not how we approach writing the API.

Each query is written to serve a specific function that our app needs. In many cases, there is no singular "backing table" - any query may read a subset of a table,or join multiple tables, or even return virtual columns that do not exist on disk. In a sense, our DB layer is "feature oriented" rather than any attempt at a generic "object relational" mapping.

Treating our DB code as a sort of FFI layer, this decoupling brings many benefits.

This means if we alter or remove a query, we have a list of all app features that are affected via compiler errors.

We can also change the query underneath without affecting app code.

We can tune the performance of each query for its specific use case, rather than attempting to preemptively optimize arbitrary table access.

Cloud SQL metrics will show you the raw SQL submitted to the database, and the queries load on the database. Since we use raw SQL in our code, it is very easy to trace back the under-performing query in our code base with a grep.

Naming

Query methods are usually written in "big endian" name after the principle table that they operate on - usually whatever table the query is FROM, even if there are JOINs involved - followed by a description of the action they perform.

This keeps names mostly predictable, and editors still autocomplete this just fine, even without LSP.

Heredocs

Almost all queries are written using Crystal's heredoc syntax:

def self.table_action(param1, param2, *, db = SQL)
  db.exec(<<-SQL, param1, param2)
  UPDATE
    table
  SET
    param1 = $1,
    param2 = $2
  SQL
end

This allows formatting queries in a legible and unobtrusive way, without having to deal with any escapes. It will also trim leading whitespace using the last SQL as a guide, which makes logs easier to read as well. Finally, editors and sites like GitHub will actually recognize this and enable SQL syntax highlighting in that section.

Almost all queries use a static string. A select few build an SQL string incrementally: in most cases, this is simply tacking on an extra WHERE clause.

Parameters

Objects vs IDs

API parameters accept only what they need. Generally what this means, if a query operates on a user ID, it does not accept User, just user_id.

This gives a lot more flexibility by not forcing code to produce an entire User object when that particular operation does not need any field other than the user's ID.

We do not make overloads that accept User. It just adds noise / more code than just typing user.id.

DB Parameter

Every query binding accepts an untyped, named db parameter. It has a default value of a connection pool instance. (DB::Database)

We have two connection pools in global constants:

• SQL, a read-write primary
• SQL_RR, a zonal read-only replica

Weird naming, but SQL was a legacy decision when we started the codebase because we couldn't pick a better one. And SQL_RR was added later. You can probably pick better names.

This has a few properties:

• At a glance, you can confirm or search for which queries go to our read replica, and which need to perform writes.
• Switching a query between RW primary and RO replica means just changing SQL -> SQL_RR, or vice versa.

# targets primary
def self.table_create(..., *, db = SQL)
  # ..
end

# targets read-replica
def self.table_get(..., *, db = SQL_RR) : ReturnType
  # ..
end

The *, db enforces that db must be used with an explicit db: argument by callers.

The untyped parameter allows for either a DB::Database or a DB::Connection to be passed. This allows for trivially composing multiple queries in a transaction with zero changes needed to the API:

# one-shot queries, no tx
table_update1(id, param1)
table_update2(id, param1)
table_update3(id, param1)

# wrap in a tx
DB.transaction do |tx|
  db = tx.connection
  table_update1(id, param1, db: db)
  table_update2(id, param1, db: db)
  table_update3(id, param1, db: db)
end

These transaction operations themselves will usually be wrapped in their own static method, that also takes a db arg at the top level, for consistency with everything else.

Return Types

Most often, each query is given its own Serializable type bound to the return type of that particular query. This way, each query is treated as its own independent unit of code that can be freely moved around and changed without concern of having a rippling effect across a lot of queries, i.e. adding a new column without a default.

This also gives us a lot of control over bandwidth and memory size on a query level - for example, some queries may only return a subset of the users table columns that are needed for that particular query.

Every type will include ::DB::Serializable::NonStrict. This is important for zero-downtime updates to our app - adding new columns should not crash any existing code.

We may place utility methods on these types, but they are always kept simple: performing some summary of data or state of the returned object, and never i.e. performing more queries.

Enums

We prefer to use ints in DB for crystal enums, with explicitly declared values. Using pg enums we felt was not worth the investment, nor did we prefer TEXT. Consider also that we have things other than Crystal interacting with our tables (Python, Go, …)

We use the following converters to override the default serialization, and everything has worked out fine.

module EnumInt(EnumT)
  def self.from_rs(rs)
    value = rs.read(Int32)
    EnumT.new(value)
  end
end

module EnumNullInt(EnumT)
  def self.from_rs(rs)
    value = rs.read(Int32?)
    return unless value
    EnumT.new(value)
  end
end

macro enum_str_lookup(value, enum_t)
  case value
    {% for member in EnumT.constants %}
      when {{member.stringify.underscore}}.to_slice
        EnumT::{{member}}
    {% end %}
  else
    {{yield}}
  end
end

module EnumStr(EnumT)
  def self.from_rs(rs)
    value = rs.read(Bytes)
    enum_str_lookup(value, EnumT) do
      raise "Unknown enum value for #{EnumT}: #{String.new(value)}"
    end
  end
end

module EnumNullStr(EnumT)
  def self.from_rs(rs)
    value = rs.read(Bytes?)
    return unless value
    enum_str_lookup(value, EnumT) do
      nil
    end
  end
end

The Str variants were used to transition some columns away from using strings.

Update Confirmation

Minimally, our UPDATE or DELETE bindings will return a bool to confirm if the query had any affect, in case the caller needs to know this. The majority operate directly on a PK, so there will always be 0 or 1 matches.

def self.table_update(id : Int64, param : String) : Bool
  r = db.exec(<<-SQL, id, param)
  UPDATE table SET param = $1
  SQL
  r.rows_affected == 1 # or > 0, if editing multiple
end

Errors

None of our DB bindings raise errors (besides those propagated from the db or pg shard itself). If the caller needs to handle some case, we return an enum or union, and the caller uses exhaustive case.

Exceptions are unnecessary overhead 99% of the time, where we always just need to inform the immediate caller of some logical state. Exceptions are left for actual exceptions (protocol errors, unavailability, …).

Addendums

Placement of Static Methods

Initially, we started by attaching static methods that do queries directly on the "model" that they primarily deal with.

For example:

module DB
  struct User
    def self.create(email : String) : User
      # ..
    end

    def self.get(email : String, full : Bool) : User?
      # ..
    end
  end
end

It felt fine to do this at first, but also stemmed from the fact that we initially started our project with an ORM before removing it.

However, as our codebase grew, it became apparent that this was not a great idea - more complex queries begged distracting questions as to which "model" they should be placed on, so you wind up with a mess of inconsistencies.

So I suggest: completely discard the idea of attaching the queries to any "model" type; it is not useful, and does not scale.

All new queries that we write go directly as static methods on our DB module, with the freedom to pick an expressive name, decoupled from congregating around any one particular datatype or table.

Today we would write it as:

module DB
  # Limited/essential data about a user (ID, email)
  struct UserLite
    # ..
  end

  # "All" user data (ID, email, + all settings)
  struct UserFull
    # ..
  end

  def self.user_create(email : String, *, db = SQL) : UserLite
    # ..
  end

  def self.user_get_lite(email : String) : UserLite?
    # ..
  end

  def self.user_get_full(email : String) : UserFull?
    # ..
  end
end