support higher granularity of sql schema datatypes specified

[uladkasach]

for specifying the types of the generated entities / tables more granularly, we'll need to add supplemental info:

• e.g.,:
  • whether to use int or number
  • precision and scale on numeric
  • which kind to use for ints (big, small, etc)
  • whether to use text or varchar on strings
  • checks

note: none of the above are critical for mvp, since:

• we can manually update the code as needed

however, we need to have an idea of how we could handle this long term

options:

• typescript @decorators
  • cons:
  • its a nonstandard ts/js feature (stage 2 only, and experimental)
  • it will make the domain object definition more confusing
• typescript type aliases
  • e.g., export type integer<Type = 'integer' extends 'smallint' | 'int' | 'bigint'> = number;
  • e.g., export type numeric<Precision, Scale> = number;
  • cons:
  • it will make the domain object definition more confusing / more difficult to read
    • now we have to think about "persistance scale and precision"
• comment based
  • e.g., `longitude: number; // numeric(7,5)
• joi / yup schema based
  • e.g.,
  • Joi.number().integer() => bigint
  • Joi.number().precision(5).min(-90).max(90).required() => numeric precision 7, scale 5, check "min -90 max 90"
  • cons:
  • [mute] can't get precise about which type of integer to use... unless .max() is specified, in which case we can!!!
  • tied down to the way that the schema validation libraries express things
  • pros:
  • syncs runtime validation w/ db persistance validation
    • i.e., if it passes through runtime validation, it will pass through db validation
    • i.e., if you want db constraints, must have them at runtime too
  • does not add extra complexity to reading code
  • can extend Joi and Yup to add more specific ways to define these constraints too:
    • https://github.com/jquense/yup/blob/master/docs/typescript.md#extending-built-in-types
• alternative decoration data based
  • e.g.,:

    public static persistance = {
    longitude: { precision: 7, scale: 5, check: '...' },
    latitude: { precision: 7, scale: 5, check: '...' }
    }

  • cons:
  • not automatically defined, extra "Decoration" required
  • adds clutter to the domain object definition
  • pros:
  • not locked into extracting sql schema constraints from runtime schema constraints
  • not required to sync runtime schema constraints from sql schema constraints

---

this will not be done for MVP, but is something to keep in mind for the future.

goals are:

• readability
• maintainability
• simplicity

[uladkasach]

note: we will not be supporting this soon, since it is an extreme optimization:

• numeric() vs numeric(p,s) has no size difference
  • just like varchar(n) vs varchar, has no affect on storage size (the column is variable size anyway), it just specifies trimming/casting of values that get written to the column

    
    select pg_column_size('80.1234567'::numeric(7, 5)); -- 12
    select pg_column_size('80.1234567'::numeric); -- 12

select pg_column_size('80.1234'::numeric(7, 5)); -- 10 select pg_column_size('80.1234'::numeric); -- 10

- int vs numeric has huge size difference
    - but this will only be really helpful for _large_ tables
```sql
select pg_column_size('821'::numeric); -- 8
select pg_column_size('821'::int); -- 4

select pg_column_size('821721'::numeric); -- 10
select pg_column_size('821721'::int); -- 4

• for strings, varchar is even explicitly specified to be used instead of varchar(n) (since size is the same, and just limits what is put in the column)

  • just like numeric vs numeric(p,s)
  • so this one does not need any specification

• datetime -vs- timestamp -vs- timestamptz

  • this one is a standard choice as well, timestamptz is the recommended option by postgres

---

so from the above, it looks like the only cases we'd really need to change the type to optimize storage size is if its an integer.... that can be derived pretty easily from schema (i.e., Joi.number().integer().min().max() -> where .min().max() tell us whether its a bigint, medint, smallint, etc)

folks can manually make the changes in these cases in the meantime, since this is such an easy optimization to do manually and would be a lot of lift automatically (i.e., domain-objects-metadata would have to be able to introspect Joi/Yup schemas to figure this out)