The Crystal Programming Language Forum

Help with CRUD macro

Hi all,

I’ve a large Ruby codebase that uses Sequel gem which I’d like to move to Crystal for obvious reasons. I’d be content with just getting the basic CRUD functions that looks like Sequel’s. I understand a macro can help here but I don’t have enough expertise to make this work. Can someone please help? Thanks in advance!

macro def_crud
  def self.create({{@type.instance_vars}})
    #Db.query("insert into {{@type.class_name}} values () returning *")
  end

  def initialize({{@type.instance_vars}})
  end

  def update({{@type.instance_vars}})
    #Db.exec("update {{@type.class_name}}...")
  end

  def [](id)
   #Db.query("select * from {{@type.class_name}} where id=? limit 1")
  end

  def destroy
   #Db.exec("delete from ...")
  end
end

class Product
  def_crud
  property title : String
  property category_id : Int32
end


prod = Product.create title: "Foo", category_id: 2
prod.update title: "Bar"
prod.destroy

It’s unfortunately not very simple. Since information like instance variables is only available inside methods (not in the method signature) because that’s actually part of the type itself, generating methods based on it requires some indirection.

However, as a super rough pass (it’s late here, so I didn’t clean this up at all), this is what I’ve got (implemented for Postgres, because that’s what I’m most familiar with, and assumes UUID primary keys):

require "db"
require "pg"

Db = DB.open("postgres:///")
Db.exec "DROP TABLE IF EXISTS products"
Db.exec "CREATE TABLE products(id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title TEXT, category_id INTEGER)"

Log.setup :debug

module Model
  macro table(name)
    def self.table_name
      {{name}}
    end
  end

  macro included
    include DB::Serializable

    {% verbatim do %}
      def self.create(**kwargs)
        {% begin %}
          values = "{% for ivar, index in @type.instance_vars %}${{index + 1}}{% if index < @type.instance_vars.size - 1 %}, {% end %}{% end %}"
          sql = <<-SQL
            INSERT INTO #{table_name} ({{@type.instance_vars.map(&.name).join(", ").id}})
            VALUES (#{values})
            RETURNING {{@type.instance_vars.map(&.name).join(", ").id}}
          SQL

          Db.query_one sql, {% for ivar in @type.instance_vars %}kwargs.fetch(:{{ivar.name.id}}) { {{ivar.default_value}} }, {% end %} as: {{@type}}
        {% end %}
      end
    {% end %}
  end

  property id : UUID = UUID.random

  def update(**kwargs)
    sql = String.build do |str|
      str << "UPDATE " << self.class.table_name
      str << " SET "
      kwargs.each_with_index(1) do |key, value, index|
        str << key.to_s << " = " << '$' << index
        if index < kwargs.size
          str << ','
        end
      end
      str << " WHERE id = $" << kwargs.size + 1
    end

    args = kwargs.values.to_a + [id]
    Db.exec sql, args: args
  end

  def self.[](id)
    # Db.query("select * from #{self.table_name} where id=? limit 1")
  end

  def destroy
    # Db.exec("delete from ...")
  end
end

class Product
  include Model

  # Could be inferred, but would require an inflection macro. It's easier just
  # to make it explicit.
  table "products"

  property title : String
  property category_id : Int32
end

prod = Product.create title: "Foo", category_id: 2
prod.update title: "Bar"
prod.destroy

The .create and #update methods are implemented here, but are not fully type-safe. To achieve that, you could check out how Avram does it. ORMs are quite complicated, though, and if you want to go that far it’d likely be easier to wrap Avram calls in a Sequel-esque API.

1 Like

Wow, thank you so much for your guidance! It works beautifully well, I’m jealous of your macro writing skills! :slight_smile:

I’ve managed to extend your code a little but I’m stumped at a couple of other things – I was hoping you could help some more, please?! – I was hoping to update the schema by running CREATE TABLE and ALTER TABLE ADD COLUMN for all the properties in the model, the changes I’ve made in the verbatim doesn’t seem to run (the ALTER TABLE loop), also how do I update an instance variable through the macro (what I’m trying to do in the update method).

And finally, what do you mean by indirection, and is there a way to make all this type safe (by replacing kwargs with list of properties)? Thanks so much for all your help!


module Model
  macro table(name)
    def self.table_name
      {{name}}
    end
  end

  PG_TYPES = {Int32: "INTEGER", Int64: "BIGINT", Float32: "REAL", String: "VARCHAR"}

  macro included
    include DB::Serializable


    {% verbatim do %}
      # {% begin %}
      #   Db.exec "CREATE TABLE IF NOT EXISTS #{table_name}(id UUID PRIMARY KEY DEFAULT gen_random_uuid(), created_at TIMESTAMP DEFAULT NOW())"

      #   {% for ivar in @type.instance_vars %}
      #     Db.exec "ALTER TABLE #{table_name} ADD COLUMN IF NOT EXISTS #{ivar} #{PG_TYPES[ivar.type]}" #HELP PLEASE!
      #   {% end %}
      # {% end %}


      def self.create(**kwargs)
        {% begin %}
          values = "{% for ivar, index in @type.instance_vars %}${{index + 1}}{% if index < @type.instance_vars.size - 1 %}, {% end %}{% end %}"
          sql = <<-SQL
            INSERT INTO #{table_name} ({{@type.instance_vars.map(&.name).join(", ").id}})
            VALUES (#{values})
            RETURNING {{@type.instance_vars.map(&.name).join(", ").id}}
          SQL

          Db.query_one sql, {% for ivar in @type.instance_vars %}kwargs.fetch(:{{ivar.name.id}}) { {{ivar.default_value}} }, {% end %} as: {{@type}}
        {% end %}
      end

      def self.all
        {% begin %}
          Db.query_all "SELECT * FROM #{table_name}", as: {{@type}}
        {% end %}
      end

      def self.first
        {% begin %}
          Db.query_one "SELECT * FROM #{table_name} ORDER BY created_at LIMIT 1", as: {{@type}}
        {% end %}
      end

      def self.last
        {% begin %}
          Db.query_one "SELECT * FROM #{table_name} ORDER BY created_at DESC LIMIT 1", as: {{@type}}
        {% end %}
      end

      def self.with_sql(sql)
        {% begin %}
          Db.query_all sql, as: {{@type}}
        {% end %}
      end

      def self.[](id)
        {% begin %}
          Db.query_one "SELECT * FROM #{table_name} WHERE id = $1", id, as: {{@type}}
        {% end %}
      end
    {% end %}
  end

  property id : UUID = UUID.random

  def update(**kwargs)
    sql = String.build do |str|
      str << "UPDATE " << self.class.table_name
      str << " SET "
      kwargs.each_with_index(1) do |key, value, index|
        str << key.to_s << " = " << '$' << index
        if index < kwargs.size
          str << ','
        end
      end
      str << " WHERE id = $" << kwargs.size + 1
    end

    args = kwargs.values.to_a + [id]
    Db.exec sql, args: args

    kwargs.each_with_index(1) do |key, value, index|
      # instance_variable_set("@#{key}", value) # MAKE SURE WE UPDATE THE INSTANCE VARS IF NO EXCEPTION -- HELP PLEASE!
    end
  end

  def destroy
    Db.exec("DELETE FROM #{self.class.table_name} WHERE id = $1", id)
  end
end

I’m going to preface this with another reminder that ORMs are complicated af. This will be a long road if you choose to write your own.

This is what I meant about the instance variables not being available outside of the method. :slightly_smiling_face: @type.instance_vars is empty outside of a method for reasons that are a bit complex but boil down to: at the point in time where the macro is evaluated at the class level, it doesn’t yet have all the type information. Method bodies are evaluated later in the compilation process (if at all) but class/struct/module bodies, including their macros, are evaluated early enough that even the order in which they’re defined matters, similar to class bodies in Ruby.

I mean I think the implementation can’t live inside the class body. At its simplest, I think it will need to live inside a method that is invoked inside the class body:

module Model
  macro included
    define_columns!

    {% verbatim do %}
      def self.define_columns!
        {% begin %}
          {% for ivar in @type.instance_vars %}
            Db.exec "ALTER TABLE ADD COLUMN {{ivar.name}} ..."
          {% end %}
        {% end %}
      end
    {% end %}
  end
end

The more the code evolves, the more likely you’ll want to extract it to something else.

It looks like you’re trying to automatically provision the tables when the app starts. I’d be careful with this, because it can cause some weird issues in a production environment where you might start multiple instances of the app simultaneously. To be clear, the end result should still be what you’re looking for but there’s the potential for errors on startup (resulting in app crashes) in getting to that point if, for example, multiple instances of the app start the ALTER TABLE query at the same time.

It also blocks the app from listening for connections until those columns are added, which may be a nontrivial amount of time in a production environment and its impact on your system depends entirely on your deployment architecture. For example, if you’re deploying to Kubernetes without startup/readiness/liveness probes configured or to Heroku without preboot enabled you may get 502/503 errors.

In general, I’d recommend a separate migration step. It doesn’t eliminate the possibility of these collisions, but it does reduce the probability by multiple orders of magnitude.

There is, but you’ve gotta figure out what your source of truth is. For example, IIRC Avram uses the DB schema as it existed when the app was compiled, which seems to align with Sequel and ActiveRecord’s approach — they do it on DB connection/app boot, but Crystal can’t define methods at runtime. Interro uses methods you define on your query objects, pushing the responsibility of type safety to the app developer.

If you want your source of truth to be the model’s instance variables, you need to guarantee the schema matches them any time you interact with data. It seems that’s what you were trying to achieve by running DDL queries on startup, but again, be careful there.

A couple different ways I can think of to do this. One is to update them in-place like you’re doing:

def update(**kwargs)
  sql = ...
  args = ...

  Db.exec sql, args: args, as: self.class

  kwargs.each do |key, value|
    # We need to `begin` the macro here so the parser doesn't think
    # the macro code is just a single `when` clause, because that
    # isn't a valid Crystal expression on its own. We have to tell
    # it to encompass the full `case` expression.
    # See: https://crystal-lang.org/reference/1.2/syntax_and_semantics/macros/index.html#pitfalls
    {% begin %}
      case key
      {% for ivar in @type.instance_vars %}
        when :{{ivar.name}}
          @{{ivar.name}} = value
      {% end %}
      end
    {% end %}
  end
end

The other is to return the updated row from the query and assign all its ivars:

def update(**kwargs)
  sql = ...
  args = ...

  updated = Db.query_one sql, args: args, as: self.class

  {% for ivar in @type.instance_vars %}
    @{{ivar.name}} = updated.{{ivar.name}}
  {% end %}
end

Both of them are really doing the same thing but the second is arguably more flexible.

1 Like

Thank you for taking the time to explain in detail! Really appreciate it!

My intention was not build a comprehensive ORM but to build something that could be used quickly for prototyping purposes but I can see how this can easily get out of hand as with any other project! So, thank you for that warning!

Got it. Instance variables not available inside the class but available only in the method is a bit surprising to me but I understand Crystal is still evolving but given the fact that Crystal doesn’t have reflection, it would be great if everything that could be done via reflection could be done via macros! I’ve resorted to code generation before and I was hoping to avoid it with this approach. Oh well.

Thank you for helping me think long term about this – I’ve no idea of Kubernetes but this is good to know!

I really wanted to use Avram but have been put off but the lack of documentation. Perhaps things have changes recently, I’ll take a look again. And thanks for introducing me to Interro, I now see where you’re coming from and those warnings make even more sense now! :slight_smile:

I love this approach better than mine since I realise a trigger could’ve updated other columns during the update!

Thanks once again for all your help! I’ll explore this some more for learning sake but given the complexity and limitations involved it looks to me I may have to stick with code generation for a while! ;)

1 Like

Can you elaborate here what you mean? I’m not following what you’re looking to do.

Hi @mdwagner, I was looking for a way to mimic the Sequel gem (from Ruby land) in Crystal. I intended to do that by writing code that would generate methods that would help with the CRUD operations for any given class (ie a Sequel::Model) but also extending it further by automatically creating and keeping tables up-to-date using class properties (instance variables become columns in a table named after the class name). I imagined macros could help but but it looks like, because of the way Crystal works, the list of instance variables are not available at the method signature level and are only available in the body so for the class above we’d have to resort to using a catch all **kwargs in the create method instead of type safe list of method arguments matching the column names. I’ve a crude solution in place using code generation which I was hoping to ditch if this macro solution worked but turns out I can yet because of this limitation. Hope that makes sense.

Gotcha. I’ve never used Sequel (although I come from the Ruby side as well), but you could take a look at jennifer.cr, which does some interesting stuff with macros similar to what I think you’re looking for.

1 Like