Simple DB Abstractions

entity_manager.cr

@[ADI::Register]
# Central access point to DB related actions
class Blog::Services::EntityManager
  @@connection : DB::Database = DB.open ENV["DATABASE_URL"]

  # Each entity has a Repository to store query methods
  # Longer term ofc this could be made a bit better but manual overload for each works just fine.
  #
  # Could prob use a macro loop over entity and add overload if the repo type exists
  def repository(entity_class : Blog::Entities::User.class) : Blog::Entities::User::Repository
    @@user_repository ||= Blog::Entities::User::Repository.new @@connection
  end

  def repository(entity_class : Blog::Entities::Article.class) : Blog::Entities::Article::Repository
    @@article_repository ||= Blog::Entities::Article::Repository.new @@connection
  end

  def remove(entity : DB::Serializable) : Nil
    entity.on_remove if entity.responds_to? :on_remove
    self.update entity
  end

  def persist(entity : DB::Serializable) : Nil
    entity.before_save if entity.responds_to? :before_save

    id = if entity.id?.nil?
           self.save entity
         else
           return self.update entity
         end

    entity.after_save id
  end

  private def save(entity : Blog::Entities::User) : Int64
    @@connection.scalar(
      %(INSERT INTO "users" ("first_name", "last_name", "email", "password", "created_at", "updated_at", "deleted_at") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id";),
      entity.first_name,
      entity.last_name,
      entity.email,
      entity.password,
      entity.created_at,
      entity.updated_at,
      entity.deleted_at,
    ).as Int64
  end

  private def save(entity : Blog::Entities::Article) : Int64
    @@connection.scalar(
      %(INSERT INTO "articles" ("author_id", "title", "body", "created_at", "updated_at", "deleted_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id";),
      entity.author_id,
      entity.title,
      entity.body,
      entity.created_at,
      entity.updated_at,
      entity.deleted_at,
    ).as Int64
  end

  private def update(entity : Blog::Entities::Article) : Nil
    @@connection.exec(
      %(UPDATE "articles" SET "title" = $1, "body" = $2, "updated_at" = $3, "deleted_at" = $4 WHERE "id" = $5;),
      entity.title,
      entity.body,
      entity.updated_at,
      entity.deleted_at,
      entity.id
    )
  end

  private def update(entity : Blog::Entities::User) : Nil
    @@connection.exec(
      %(UPDATE "users" SET "first_name" = $1, "last_name" = $2, "email" = $3, "password" = $4, "updated_at" = $5, "deleted_at" = $6 WHERE "id" = $7;),
      entity.first_name,
      entity.last_name,
      entity.email,
      entity.password,
      entity.updated_at,
      entity.deleted_at,
      entity.id
    )
  end
end

usage.cr

# Finder User by ID, raising if not found
@entity_manager.repository(Blog::Entities::User).find 123

# Save new entity
@entity_manager.persist user

# Remove entity
@ntity_manager.remove user

user.cr

# In larger/more serious apps, it may be better to _not_ use your entities
# as the object that is (de)serialized, but no need to overengineer things to start.
class Blog::Entities::User
  include DB::Serializable
  include AVD::Validatable
  include JSON::Serializable

  getter! id : Int64

  @[Assert::NotBlank]
  property first_name : String

  @[Assert::NotBlank]
  property last_name : String

  @[Assert::NotBlank]
  @[Assert::Email(:html5)]
  property email : String

  @[Assert::Size(8..25, min_message: "Your password is too short", max_message: "Your password is too long")]
  @[JSON::Field(ignore_serialize: true)]
  property password : String

  getter! created_at : Time
  getter! updated_at : Time

  getter deleted_at : Time?

  # Could prob use a module that defines some of the common logic as well as DB::Serializable
  protected def after_save(@id : Int64) : Nil; end

  protected def before_save : Nil
    if @id.nil?
      @created_at = Time.utc
      @password = Crypto::Bcrypt::Password.create(@password).to_s
    end

    @updated_at = Time.utc
  end

  protected def on_remove : Nil
    @deleted_at = Time.utc
  end
end

user_repository.cr

class Blog::Entities::User::Repository
  def initialize(@connection : DB::Database); end

  def find(id : Int64) : Blog::Entities::User
    @connection.query_one(%(SELECT * FROM "users" WHERE "id" = $1 AND "deleted_at" IS NULL;), id, as: Blog::Entities::User)
  end

  def find_by_username?(username : String) : Blog::Entities::User?
    @connection.query_one?(%(SELECT * FROM "users" WHERE "email" = $1 AND "deleted_at" IS NULL;), username, as: Blog::Entities::User)
  end
end