search

prisma / database-schema-examples

Database Schema Examples we strive to support in Prisma
207 stars 49 forks source link

Reference: Types #87

Open janpio opened 4 years ago

janpio commented 4 years ago

We need databases that have all the types possible in MySQL, Postgres and SQLite. This PR adds a new database reference that has a types table that has an @id (ID int(11) auto increment as Primary Key) and one column per possible type.

Source for types:

For MySQL the columns are roughly named and sorted by the structure of the reference documentation, so the name is enough to recognize the original type.

The type "parameter" (e.g. 255 for VARCHAR) is e.g. the default, if you create the column without setting a value or a reasonable enough manually chosen value.

MySQL types table in phpMyAdmin: image

matthewmueller commented 4 years ago

Here's a crazy table for Postgres. Surprisingly, introspection works, lots of columns end up as strings, but at least my computer didn't blow up in my face, so that's a plus

The alphabetical order issue becomes more noticeable here.

create table crazy (
  c1 bigint,
  c2 int8,
  c3 bigserial,
  c4 serial8,
  c5 bit,
  c6 bit(1),
  c7 bit(10),
  c8 bit varying,
  c9 bit varying(1),
  c10 bit varying(10),
  c11 varbit,
  c12 varbit(1),
  c13 varbit(10),
  c14 boolean,
  c15 bool,
  c16 box,
  c17 bytea,
  c18 character,
  c19 character(1),
  c20 character(10),
  c21 char,
  c22 char(1),
  c23 char(10),
  c24 character varying,
  c25 character varying(1),
  c26 character varying(110),
  c27 varchar,
  c28 varchar(1),
  c29 varchar(110),
  c30 cidr,
  c31 circle,
  c32 date,
  c33 double precision,
  c34 float8,
  c35 inet,
  c36 integer,
  c37 int,
  c38 int4,
  c39 interval,
  c40 interval year,
  c41 interval day to hour,
  c42 interval day to second,
  c43 interval day to second(0),
  c44 interval day to second(6),
  c45 interval(0),
  c46 interval(6),
  c47 json,
  c48 jsonb,
  c49 line,
  c50 lseg,
  c51 macaddr,
  c52 money,
  c53 numeric(7,5),
  c54 numeric(10,5),
  c55 decimal,
  c56 decimal(7,5),
  c57 decimal(10,5),
  c63 path,
  c64 pg_lsn,
  c65 point,
  c66 polygon,
  c67 real,
  c68 float4,
  c69 smallint,
  c70 int2,
  c71 smallserial,
  c72 serial2,
  c73 serial,
  c74 serial4,
  c75 text,
  c76 time,
  c77 time(10),
  c78 time(1),
  c79 time without time zone,
  c80 time (10) without time zone,
  c81 time (1) without time zone,
  c82 time with time zone,
  c83 time (10) with time zone,
  c84 time (1) with time zone,
  c85 timestamp without time zone,
  c86 timestamp (10) without time zone,
  c87 timestamp (1) without time zone,
  c88 timestamp with time zone,
  c89 timestamp (10) with time zone,
  c90 timestamp (1) with time zone,
  c91 tsquery,
  c92 tsvector,
  c93 txid_snapshot,
  c94 uuid,
  c95 xml,
  c96 bigint[],
  c97 int8[],
  c100 bit[],
  c101 bit(1)[],
  c102 bit(10)[],
  c103 bit varying[],
  c104 bit varying(1)[],
  c105 bit varying(10)[],
  c106 varbit[],
  c107 varbit(1)[],
  c108 varbit(10)[],
  c109 boolean[],
  c110 bool[],
  c111 box[],
  c112 bytea[],
  c113 character[],
  c114 character(1)[],
  c115 character(10)[],
  c116 char[],
  c117 char(1)[],
  c118 char(10)[],
  c119 character varying[],
  c120 character varying(1)[],
  c121 character varying(110)[],
  c122 varchar[],
  c123 varchar(1)[],
  c124 varchar(110)[],
  c125 cidr[],
  c126 circle[],
  c127 date[],
  c128 double precision[],
  c129 float8[],
  c130 inet[],
  c131 integer[],
  c132 int[],
  c133 int4[],
  c134 interval[],
  c135 interval year[],
  c136 interval day to hour[],
  c137 interval day to second[],
  c138 interval day to second(0)[],
  c139 interval day to second(6)[],
  c140 interval(0)[],
  c141 interval(6)[],
  c142 json[],
  c143 jsonb[],
  c144 line[],
  c145 lseg[],
  c146 macaddr[],
  c147 money[],
  c148 numeric(7,5)[],
  c149 numeric(10,5)[],
  c150 decimal[],
  c151 decimal(7,5)[],
  c152 decimal(10,5)[],
  c158 path[],
  c159 pg_lsn[],
  c160 point[],
  c161 polygon[],
  c162 real[],
  c163 float4[],
  c164 smallint[],
  c165 int2[],
  c170 text[],
  c171 time[],
  c172 time(10)[],
  c173 time(1)[],
  c174 time without time zone[],
  c175 time (10) without time zone[],
  c176 time (1) without time zone[],
  c177 time with time zone[],
  c178 time (10) with time zone[],
  c179 time (1) with time zone[],
  c180 timestamp without time zone[],
  c181 timestamp (10) without time zone[],
  c182 timestamp (1) without time zone[],
  c183 timestamp with time zone[],
  c184 timestamp (10) with time zone[],
  c185 timestamp (1) with time zone[],
  c186 tsquery[],
  c187 tsvector[],
  c188 txid_snapshot[],
  c189 uuid[],
  c190 xml[]
);
model crazy {
  c1   Int?
  c10  String?
  c100 String[]
  c101 String[]
  c102 String[]
  c103 String[]
  c104 String[]
  c105 String[]
  c106 String[]
  c107 String[]
  c108 String[]
  c109 Boolean[]
  c11  String?
  c110 Boolean[]
  c111 String[]
  c112 String[]
  c113 String[]
  c114 String[]
  c115 String[]
  c116 String[]
  c117 String[]
  c118 String[]
  c119 String[]
  c12  String?
  c120 String[]
  c121 String[]
  c122 String[]
  c123 String[]
  c124 String[]
  c125 String[]
  c126 String[]
  c127 DateTime[]
  c128 Float[]
  c129 Float[]
  c13  String?
  c130 String[]
  c131 Int[]
  c132 Int[]
  c133 Int[]
  c134 DateTime[]
  c135 DateTime[]
  c136 DateTime[]
  c137 DateTime[]
  c138 DateTime[]
  c139 DateTime[]
  c14  Boolean?
  c140 DateTime[]
  c141 DateTime[]
  c142 String[]
  c143 String[]
  c144 String[]
  c145 String[]
  c146 String[]
  c147 String[]
  c148 Float[]
  c149 Float[]
  c15  Boolean?
  c150 Float[]
  c151 Float[]
  c152 Float[]
  c158 String[]
  c159 String[]
  c16  String?
  c160 String[]
  c161 String[]
  c162 Float[]
  c163 Float[]
  c164 Int[]
  c165 Int[]
  c17  String?
  c170 String[]
  c171 DateTime[]
  c172 DateTime[]
  c173 DateTime[]
  c174 DateTime[]
  c175 DateTime[]
  c176 DateTime[]
  c177 DateTime[]
  c178 DateTime[]
  c179 DateTime[]
  c18  String?
  c180 DateTime[]
  c181 DateTime[]
  c182 DateTime[]
  c183 DateTime[]
  c184 DateTime[]
  c185 DateTime[]
  c186 String[]
  c187 String[]
  c188 String[]
  c189 String[]
  c19  String?
  c190 String[]
  c2   Int?
  c20  String?
  c21  String?
  c22  String?
  c23  String?
  c24  String?
  c25  String?
  c26  String?
  c27  String?
  c28  String?
  c29  String?
  c3   Int
  c30  String?
  c31  String?
  c32  DateTime?
  c33  Float?
  c34  Float?
  c35  String?
  c36  Int?
  c37  Int?
  c38  Int?
  c39  DateTime?
  c4   Int
  c40  DateTime?
  c41  DateTime?
  c42  DateTime?
  c43  DateTime?
  c44  DateTime?
  c45  DateTime?
  c46  DateTime?
  c47  String?
  c48  String?
  c49  String?
  c5   String?
  c50  String?
  c51  String?
  c52  String?
  c53  Float?
  c54  Float?
  c55  Float?
  c56  Float?
  c57  Float?
  c6   String?
  c63  String?
  c64  String?
  c65  String?
  c66  String?
  c67  Float?
  c68  Float?
  c69  Int?
  c7   String?
  c70  Int?
  c71  Int
  c72  Int
  c73  Int
  c74  Int
  c75  String?
  c76  DateTime?
  c77  DateTime?
  c78  DateTime?
  c79  DateTime?
  c8   String?
  c80  DateTime?
  c81  DateTime?
  c82  DateTime?
  c83  DateTime?
  c84  DateTime?
  c85  DateTime?
  c86  DateTime?
  c87  DateTime?
  c88  DateTime?
  c89  DateTime?
  c9   String?
  c90  DateTime?
  c91  String?
  c92  String?
  c93  String?
  c94  String?
  c95  String?
  c96  Int[]
  c97  Int[]
}
janpio commented 4 years ago

A more elaborate version of what Matt built, with all columns named by the type they have:

create table crazy2 (
    numeric_integer_smallint smallint,
    numeric_integer_integer integer,
    numeric_integer_bigint bigint,
    numeric_integer_decimal decimal,
    numeric_arbitrary_numeric numeric,
    numeric_floating_real real,
    numeric_floating_double_precision double precision,
    numeric_serial_smallserial smallserial,
    numeric_serial_serial serial,
    numeric_serial_bigserial bigserial,
    monetary_money money,
    character_character_varying character varying,
    character_character character,
    character_varchar varchar,
    character_char char,
    character_text text,
    binary_bytea bytea,
    datetime_timestamp timestamp,
    datetime_timestamp_without_time_zone timestamp without time zone,
    datetime_timestamp_with_time_zone timestamp with time zone,
    datetime_timestamptz timestamptz,
    datetime_date date,
    datetime_time time,
    datetime_time_without_time_zone time without time zone,
    datetime_time_with_time_zone time with time zone,
    datetime_interval interval,
    datetime_interval_year interval year,
    datetime_interval_month interval month,
    datetime_interval_day interval day,
    datetime_interval_hour interval hour,
    datetime_interval_minute interval minute,
    datetime_interval_second interval second,
    datetime_interval_year_to_month interval year to month,
    datetime_interval_day_to_hour interval day to hour,
    datetime_interval_day_to_minute interval day to minute,
    datetime_interval_day_to_second interval day to second,
    datetime_interval_hour_to_minute interval hour to minute,
    datetime_interval_hour_to_second interval hour to second,
    datetime_interval_minute_to_second interval minute to second,
    boolean_boolean boolean,
    geometric_point point,
    geometric_line line,
    geometric_lseg lseg,
    geometric_box box,
    geometric_path path,
    geometric_polygon polygon,
    geometric_circle circle,
    network_cidr cidr,
    network_inet inet,
    network_macaddr macaddr,
    network_macaddr8 macaddr8,
    bitstring_bit bit,
    bitstring_bit_varying bit varying,
    textsearch_tsvector tsvector,
    textsearch_tsquery tsquery,
    uuid_uuid uuid,
    xml_xml xml,
    json_json json,
    json_jsonb jsonb,
    range_int4range int4range,
    range_int8range int8range,
    range_numrange numrange,
    range_tsrange tsrange,
    range_tstzrange tstzrange,
    range_daterange daterange,
    other_pg_lsn pg_lsn,
    other_txid_snapshot txid_snapshot
)
create table crazy3 (
    numeric_integer_smallint smallint[],
    numeric_integer_integer integer[],
    numeric_integer_bigint bigint[],
    numeric_integer_decimal decimal[],
    numeric_arbitrary_numeric numeric[],
    numeric_floating_real real[],
    numeric_floating_double_precision double precision[],
    monetary_money money[],
    character_character_varying character varying[],
    character_character character[],
    character_varchar varchar[],
    character_char char[],
    character_text text[],
    binary_bytea bytea[],
    datetime_timestamp timestamp[],
    datetime_timestamp_without_time_zone timestamp without time zone[],
    datetime_timestamp_with_time_zone timestamp with time zone[],
    datetime_timestamptz timestamptz[],
    datetime_date date[],
    datetime_time time[],
    datetime_time_without_time_zone time without time zone[],
    datetime_time_with_time_zone time with time zone[],
    datetime_interval interval[],
    datetime_interval_year interval year[],
    datetime_interval_month interval month[],
    datetime_interval_day interval day[],
    datetime_interval_hour interval hour[],
    datetime_interval_minute interval minute[],
    datetime_interval_second interval second[],
    datetime_interval_year_to_month interval year to month[],
    datetime_interval_day_to_hour interval day to hour[],
    datetime_interval_day_to_minute interval day to minute[],
    datetime_interval_day_to_second interval day to second[],
    datetime_interval_hour_to_minute interval hour to minute[],
    datetime_interval_hour_to_second interval hour to second[],
    datetime_interval_minute_to_second interval minute to second[],
    boolean_boolean boolean[],
    geometric_point point[],
    geometric_line line[],
    geometric_lseg lseg[],
    geometric_box box[],
    geometric_path path[],
    geometric_polygon polygon[],
    geometric_circle circle[],
    network_cidr cidr[],
    network_inet inet[],
    network_macaddr macaddr[],
    network_macaddr8 macaddr8[],
    bitstring_bit bit[],
    bitstring_bit_varying bit varying[],
    textsearch_tsvector tsvector[],
    textsearch_tsquery tsquery[],
    uuid_uuid uuid[],
    xml_xml xml[],
    json_json json[],
    json_jsonb jsonb[],
    range_int4range int4range[],
    range_int8range int8range[],
    range_numrange numrange[],
    range_tsrange tsrange[],
    range_tstzrange tstzrange[],
    range_daterange daterange[],
    other_pg_lsn pg_lsn[],
    other_txid_snapshot txid_snapshot[]
)
matthewmueller commented 4 years ago

Not sure if this is intentional or not, but one thing to note here is that I think it's important to test types like numeric [ (p, s) ]. Your revised tables are missing valid variations.

matthewmueller commented 4 years ago
Data Type Alias Description
bigint int8 signed eight-byte integer
bigserial serial8 autoincrementing eight-byte integer
bit [ (n) ] fixed-length bit string
bit varying [ (n) ] varbit [ (n) ] variable-length bit string
boolean bool logical Boolean (true/false)
box rectangular box on a plane
bytea binary data ("byte array")
character [ (n) ] char [ (n) ] fixed-length character string
character varying [ (n) ] varchar [ (n) ] variable-length character string
cidr IPv4 or IPv6 network address
circle circle on a plane
date calendar date (year, month, day)
double precision float8 double precision floating-point number (8 bytes)
inet IPv4 or IPv6 host address
integer int, int4 signed four-byte integer
interval [ fields ][ (p) ] time span
json textual JSON data
jsonb binary JSON data, decomposed
line infinite line on a plane
lseg line segment on a plane
macaddr MAC (Media Access Control) address
money currency amount
numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision
path geometric path on a plane
pg_lsn PostgreSQL Log Sequence Number
point geometric point on a plane
polygon closed geometric path on a plane
real float4 single precision floating-point number (4 bytes)
smallint int2 signed two-byte integer
smallserial serial2 autoincrementing two-byte integer
serial serial4 autoincrementing four-byte integer
text variable-length character string
time [ (p) ][ without time zone ] time of day (no time zone)
time [ (p) ] with time zone timetz time of day, including time zone
timestamp [ (p) ][ without time zone ] date and time (no time zone)
timestamp [ (p) ] with time zone timestamptz date and time, including time zone
tsquery text search query
tsvector text search document
txid_snapshot user-level transaction ID snapshot
uuid universally unique identifier
xml XML data
janpio commented 4 years ago

Not sure if this is intentional or not, but one thing to note here is that I think it's important to test types like numeric [ (p, s) ]. Your revised tables are missing valid variations.

How so? numeric_arbitrary_numeric numeric is present and defaults to some value for (p, s) I assume?

matthewmueller commented 4 years ago

You probably want something like:

numeric_arbitrary_numeric numeric,
numeric_arbitrary_numeric2 numeric(7, 1),
numeric_arbitrary_numeric3 numeric(2, 7)

Some variety to ensure that introspection can also handle explicit parameters.