Support MySQL JSON data type

[Blacksmoke16]

Granite, as far as I can tell, does not support MySQL JSON data type.

class Item < Granite::ORM::Base
   adapter mysql
   table_name items

   primary type_id : Int64, auto: false
   field locations : JSON::Type | JSON::Any
 end

CREATE TABLE items
(
  type_id   BIGINT NOT NULL PRIMARY KEY,
  locations JSON                    NOT NULL
)
INSERT INTO items (type_id, locations) VALUES (4, '[60003760, 1022887354289, 1023736622218, 1022568876022, 1025064405048, 1026712174905, 1026960906556]');

  p Item.first

Missing hash key: 245_u8 (KeyError)
  from /usr/share/crystal/src/hash.cr:0:9 in 'fetch'
  from /usr/share/crystal/src/hash.cr:62:5 in '[]'
  from lib/mysql/src/mysql.cr:9:7 in 'column_type'
  from lib/mysql/src/mysql/result_set.cr:81:13 in 'read'
  from lib/db/src/db/result_set.cr:79:15 in 'read'
  from src/EveToolsApi/models/item.cr:4:3 in 'set_attributes'
  from src/EveToolsApi/models/item.cr:4:3 in 'from_sql'
  from lib/granite_orm/src/granite_orm/querying.cr:49:17 in 'raw_all'
  from lib/kemal/src/kemal/helpers/helpers.cr:0:3 in '~procProc(Array(EveToolsApi::Models::Item))'
  from lib/granite_orm/src/granite_orm/collection.cr:255:3 in 'collection'
  from lib/granite_orm/src/granite_orm/collection.cr:0:5 in 'Granite::ORM::Collection(EveToolsApi::Models::Item)'
  from lib/granite_orm/src/granite_orm/querying.cr:69:5 in 'first'
  from lib/granite_orm/src/granite_orm/querying.cr:68:3 in 'first'
  from src/EveToolsApi.cr:42:3 in '__crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:11:3 in '_crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:112:5 in 'main_user_code'
  from /usr/share/crystal/src/crystal/main.cr:101:7 in 'main'
  from /usr/share/crystal/src/crystal/main.cr:135:3 in 'main'
  from __libc_start_main
  from _start
  from ???

It will work if i set the column type as text and field type as a string, parse the string as json, but ideally granite should return JSON::Any already without that extra step.

[robacarp]

Postgres also has json and json-b datatypes, which should present the same interface as mysql+json

[Blacksmoke16]

Might also look into Pg Array type. As of now we only support it on read.

[ghost]

Is it currently possible to use the JSON data type with postgres?

[robacarp]

@astupidnerd currently Granite doesn't support implementing any custom data types