Getting data from Postgres into a class before converting it to json

Help & Support
1

I am trying to get data from Postgres to a class and then hoping to convert it to json using .to_json method. However, I get the following error when I try to get the data from Postgres.

PG::ResultSet#read returned a String. A LangSpeed was expected.

This is what I tried. Assume that db is named testdb and the table is named testtable. There are three columns in the database: id, v1, v2.

This is how I defined the class:

class LangSpeed
    JSON.mapping(
        id: {type: String?},
        v1: {type: String?},
        v2: {type: String?}
    )
end

This is the function that tries to query the data:

def getrecs_as_class
    conn = DB.open DBURL
    begin
       res = conn.query_all("select id, v1 from testtable", as: LangSpeed) 
    rescue ex
        puts ex.message        
        return nil
    else
        puts "Result as class: #{res}"
        return res
    ensure
        conn.close
    end
end

When I do this I get the above-mentioned error:

PG::ResultSet#read returned a String. A LangSpeed was expected.

In the API documentation for crystal-db it says that there is a way to cast the queried data to a class.

Can someone please suggest how this can be done with a class? I can do it with NamedTuples; however that way I don’t have the structure of the NamedTuple in one place.

Thank you for reading.

2

DB::ResultSet#read(Class) returns a single column value converted into an object of that type, so you’d have to pull back the columns and pass them to a LangSpeed constructor:

conn
  .query_all("select id, v1 from testable", as: { String, String })
  .map do |(id, v1)|
    LangSpeed.new(id, v1)
  end

It might be useful if it allowed a call like DB::ResultSet#read(DB::Mappable.class) where it just requires the class to receive a DB::ResultSet to its constructor and the class can build itself out of whatever columns the row contains. I suspect the reason it hasn’t been done that way is because the API doesn’t yet provide abstractions to make that easy for arbitrary queries.

3

Check DB::Mapping, similar to JSON, it’s in crystal-db. Though using an ORM might be better.

4

Thank you @jgaskins and @asterite. The solution suggested by @jgaskins.

@asterite, I looked through the most recent crystal-db api documentation. There is nothing called DB::Mapping. It has DB::Mappable which is an empty module (according to these docs, to support DB::Mapping). And, it has DB:MappingException. Can you please link it?

5

EDIT: http://crystal-lang.github.io/crystal-db/api/0.5.1/DB.html#mapping(properties%2Cstrict%3Dtrue)-macro

Would be the API docs for it ^

6

Thanks @asterite and @Blacksmoke16. The following worked.
Although, after doing this it seems that using NamedTuples might just be easier (and perhaps more efficient).
Thanks everyone for all the help.

class LangSpeed
    DB.mapping(
        id: {type: String?},
        v1: {type: String?},
        v2: {type: String?}
    )
    JSON.mapping(
        id: {type: String?},
        v1: {type: String?},
        v2: {type: String?}
    )
end

def getrecs_as_class
    conn = DB.open DBURL
    begin
        res = conn.query("select id, v1 from testtable") 
        recs = LangSpeed.from_rs(res)
    rescue ex
        puts ex.message        
        return nil
    else
        puts "Result as classes: #{recs}"
        return recs
    ensure
        res.close
        conn.close
    end
end
7

Might be a good idea to read the source of https://github.com/amberframework/granite and see how it does it. I’ve found reading how one program did something and integrating ideas in my own code tends to work well for me.