Better way to fetch data from DB and store in NamedTuple

Help & Support
1

Hi,
Is there a better way to fetch data and store it in NamedTuple?

  db= DB.open "postgres://XXX@x.x.x.x/somedb"
  result = db.query_one "select * from Z where xxxx='#{id}'", as: {Int32 , String, String, String,
   String, String, String, String, String}
   customer ={
    FirstName: result[1],
    LastName:  result[2],
    FatherName: result[3],
    Birthday:  result[4],
    RegisterNumber: result[5],
    PostalCode: result[6],
    NationalCode: result[7],
    Code:      result[8],
    }
  return customer.to_json

Thanks

2

Instead of a NamedTuple, Iā€™d recommend using a first-class object for this:

require "db"
require "pg"
require "json"

struct Customer
  include DB::Serializable

  JSON.mapping(
    id: Int32,
    first_name: String,
    last_name: String,
    father_name: String,
    birthday: String,
    register_number: String,
    postal_code: String,
    national_code: String,
    code: String,
  )
end

db = DB.open("postgres:///")

customer = db.query_one "SELECT 1 AS id, 'First' as first_name, 'Last' as last_name, 'FatherName' as father_name, 'Birthday' as birthday, 'RegisterNumber' as register_number, 'PostalCode' as postal_code, 'NationalCode' as national_code, 'Code' as code", as: Customer

pp customer

puts customer.to_json
1 Like
3

I agree with @jgaskins. Note thereā€™s no need to use JSON.mapping, for that, just regularly declaring the instance variables or using the record macros and then including DB::Serializable works as well.

That said there totally is an overload taking a named tuple: http://crystal-lang.github.io/crystal-db/api/0.8.0/DB/QueryMethods.html#query_one(query,*args_,args:Array?=nil,astypes:NamedTuple)-instance-method

I never tried it but I suspect it goes something like this

 customer = db.query_one("SELECT
  first_name,
  last_name,
  father_name,
  birthday,
  register_number,
  postal_code,
  national_code,
  code
 FROM custoemrs WHERE id = $1", {id}, as: {
  FirstName:      String,
  LastName:       String,
  FatherName:     String,
  Birthday:       String,
  RegisterNumber: String,
  PostalCode:     String,
  NationalCode:   String,
  Code:           String,
})
1 Like
4

I used it because of the very last line of @Geo-7ā€˜s example code. :slight_smile:

1 Like
5

This code doesnā€™t work
There was a problem expanding macro ā€˜macro_140019633894496ā€™

> Code in lib/db/src/db/result_set.cr:108:7
> 
>  108 | {% begin %}
>        ^
> Called macro defined in lib/db/src/db/result_set.cr:108:7
> 
>  108 | {% begin %}
> 
> Which expanded to:
> 
>  > 2 |         NamedTuple.new(
>  > 3 |           
>  > 4 |             FirstName: read(String),
>                             ^
> Error: expecting token ')', not ':'
6

Ah, looks like a small inconsistency in the language, while named tuples support keys starting with uppercase letters, keyword arguments do not. The macro does NamedTuple.new passing the keys of our named tuple literal as keyword arguments. So youā€™ll either have to go back to your original code, yet better use a value object like suggested above or give up those weird key names for ones that start with a lower case letter :slight_smile:

I opened an issue about this here: https://github.com/crystal-lang/crystal/issues/9221

1 Like
7

@jgaskins Your code works fine,Iā€™ve changed my code to first-class object Thanks.
@jhass I will give up on wired key names. :slight_smile: but do we have struct tag in crystal like go:
https://github.com/golang/go/wiki/Well-known-struct-tags
I want to return Unicode none English character in JSON keys.

1 Like
8

I think for this the best solution would be a value object solution based on JSON::Serializable:

struct Customer
  include JSON::Serializable
  include DB::Serializable
  
  @[JSON::Field(key: "mƶp")]
  @first_name : String
end
1 Like
9

Thank you.

10

Maybe a macro would work?

11

In a recent personal projet I developed, I use the following code :

...
OPERATION_RECORD = {id: Int32, quantity: Float64, price: Float64}
...
sql = "select * from operation"
DBCONN.query sql do |rs|
	rs.each do
        operation = rs.read(**OPERATION_RECORD)
        puts operation[:quantity]
	    puts operation[:price]
	end
end

or

sql = "select * from operation where id=(select min(id) from operation)"
first_operation = DBCONN.query_one sql, as: OPERATION_RECORD
puts first_operation[:quantity]
puts first_operation[:price]

I see it as an advantage as there is only one declaration for the operation type in the whole program.
Good practice or not ?

1 Like
12

This is what DB::Serializable is for :slight_smile:

struct OperationRecord
  include DB::Serializable
  getter id : Int32
  getter quantity : Float64
  getter price: Float64
end

operations = OperationRecord.from_rs(DBCON.query("select * from operation"))

(Side snark: Donā€™t store money as floating point, convert to cents (or whatever precision you need) and cast to integer as early as possible after data input, do operations and storage with that value and only convert back to float for display / data output).

1 Like
13

Ah, thanks.
Iā€™ll try that.