Geometry/custom data types

[duckontheweb]

Thanks for putting this tool together, it's been very helpful so far!

I'm modeling a database that's going to include the PostGIS extension with geometry/geography data types. It looks like those data types are not currently supported. Is there any mechanism for defining custom data types within the schema that could then be used as data types in the column definitions?

[JiawenKor]

Hi @duckontheweb, thanks for your post! You may define any data type, just as long as it is is a single word (remove all spaces in the data type). For instance, geography, JSON, decimal(1,2), custom_1, etc. The data type will still be reflected in your dbdiagram.io column definitions.

Just curious, what caused you to have the impression that the data type was unsupported? Are there other ways you were expecting it to be supported?

[raschan]

Hi @JiawenKor,
I have a similar request. I understand, that I can use any string to describe datatype. Is there any way to define the object shape of such datatype, similarly to Enum?
If not, is it planned?

[nvquanghuy]

Hi @JiawenKor,
I have a similar request. I understand, that I can use any string to describe datatype. Is there any way to define the object shape of such datatype, similarly to Enum?
If not, is it planned?

Hi Raschan I'm not sure what you mean by defining object shape of such data type. Can you elaborate more pls?

[raschan]

Hi @nvquanghuy, certainly.

In our use case, we store a few JSON object in our db, as they can go a few levels deep, and they are mostly configurations, and they are a lot easier to manage as objects.

For example one of the configs:

{
    "timeFrame": { "stop": "11:00", "start": "00:00" },
    "amplifications": [
      {
        "to": 49.6,
        "from": 49.5,
        "coefficient": { "additionValue": 123 },
        "socThreshold": { "maximum": 33, "minimum": 22 }
      },
      {
        "to": 50.5,
        "from": 49.7,
        "coefficient": { "additionValue": 321 },
        "socThreshold": { "maximum": 33, "minimum": 22 }
      }
    ],
    "batteryAdjustment": {
      "deltaStop": 11,
      "deltaStart": 11,
      "socPowerScale": 11
    },
    "unavailabilityMode": { "type": "backcharge", "lower": 40, "upper": 50 }
  }

What I would like to use to describe the shape of the object is something like this:

JSON period_config {
  "timeFrame" time_frame
  "amplifications" amplification [array]
  "batteryAdjustment" battery_adjustment
  "unavailabilityMode" unavailability_mode
}

JSON time_frame { 
  "stop" string [note: 'format: HH:mm']
  "start" string [note: 'format: HH:mm']
}

JSON amplification {
  "to" double
  "from" double
  "coefficient" coefficient
  "socThreshold" soc_threshold
}

JSON coefficient {...}
JSON soc_threshold {...}
JSON battery_adjustment {...}
JSON unavailability_mode {...}

[mkaatman]

@raschan at first look I like your idea, but what's the end result. Is this just for easier definition within the tool? The export is the same right? As far as I know, most databases don't allow you to define the JSON structure beyond saying that the column is JSON data.

[PWrzeszczSTH]

@mkaatman you can define JSON structure in your application DB models. The end result for custom type describing JSON fields would be a schema readability.

Is there any info about this feature?

[dan-wu-open]

Would also like this feature for our NoSQL database

[abyrd]

Hi, thanks for all your work on this project. I love having a simple language to express entity relationships and generate diagrams and documents.

I did notice and appreciate that DBML accepts arbitrary type names. I think the detail that people are missing is that you can't attach notes or other characteristics to the types and reuse them succinctly in many places. If you've got a table like this:

Table stops {
  stop_id id [pk]
  stop_name string [null]
  stop_lat float [not null, note: 'range -90 to 90']
  stop_lon float [not null, note: 'range -180 to 180']
}

We could instead write:

Type id [note: 'alphanumeric, underscores allowed but no other characters']
Type latitude [note: 'floating point, range -90 to 90']
Type longitude [note: 'floating point, range -180 to 180']

Table stops {
  stop_id id [pk]
  stop_name string [null]
  stop_lat latitude [not null]
  stop_lon longitude [not null]
}

or even

Type id [note: 'alphanumeric, underscores allowed but no other characters']
Type latitude [note: 'floating point, range -90 to 90']
Type longitude [note: 'floating point, range -180 to 180']
Type lat_lon {
  lat latitude
  lon longitude
}
Table stops {
  stop_id id [pk]
  stop_name string [null]
  stop_lat_lon lat_lon [not null]
}

This serves no purpose when there's only one reference to the custom type, but if you have 10 tables that all contain latitudes and longitudes and IDs following certain rules, this could factor out tons of notes describing ranges and constraints or structures.