The Crystal Programming Language Forum

Inserting data into Postgres based on Class instance

Please see the example class below. JSON.mapping and DB.mapping are awesome. They make it easy to get data:

from class instance to json
from json to class instance
from db to class instance

Missing: from class instance to db

Is there an easy way to insert a new row into the database (postgres) when I have a class instance. One can assume that the set of property names matches with the column names in the DB table.

Thank you.

Example class:

class Dog

    JSON.mapping(
        name: String,
        age: Int32,
        color: String?
    )

    DB.mapping(
        name: String,
        age: Int32,
        color: String?
    )

    property name = "FatTire"
    property age = 10
    property color 

    def initialize()
    end
end

I think you can use Lucky for this, specifically Avram.

DB mapping was just an experiment. It doesn’t scale because it’s missing relationships and other things. So your best bet is using something other than DB.mapping

You’re best bet would be to use some ORM. Granite has built in support for JSON::Serializable. Is also a PR that refactors a bunch of stuff to simplify and make the DSL/UX of it better.

I.e. you could do stuff like

class Dog < Granite::Base
  adapter "pg"
  table_name "dogs"

  primary id : Int64
  field! name : String
  field age : Int32
  field color : String
end

Dog.create(name: "Fido", age: 10, color: "Black")

dog = Dog.first!
dog.id # => 1
dog.name # => "Fido"
dog.age # => 10
dog.color # => "Black"

dog.to_json # => {"name": "Fido", "age": 10, "color": "Black"}

dog = Dog.from_json %({"name": "Fido", "age": 10, "color": "Black"})
dog.save
dog.id # => 2
dog.name # => "Fido"
dog.age # => 10
dog.color # => "Black"

Granite also supports third party annotations if you wanted to use libraries such as CrSerializer.

This should be possible using {% for ivar, i in @type.instance_vars %} in macroland, no?

Right yes, but he’d have to build out the SQL/methods in order to insert a record. IMO he’s already defining the same properties three times. Don’t reinvent the wheel.

Thanks, all. Looks like I might have to bite the bullet and use an ORM. I have been trying to avoid that since I don’t want to bring in too many dependencies. I will look at the options suggested. There is also the clear library.

@girng, actually I was trying to figure out how to get all properties of a class, but was unsuccessful. Your example is not clear to me. Can you please elaborate?

Thanks again.

I agree with Blacksmoke, my idea is pretty redundant as well.

@curious2learn Here is an example on how to get the class properties. However, insert statements in SQL require the correct order (as you probably know).

Yea, there are a good amount of options out there. Just depends on what your needs are and which best fits those needs.