Prisma1: Scalar Types

[sorenbs]

As we introduce support for multiple databases we need to consider how the underlying types of each database are exposed. This document outlines all types supported by each database as well as the build in scalars in the GraphQL spec.

Overview

GraphQL | MySQL | Elastic Search | MongoDB | PostgreSQL
-- | -- | -- | -- | --
Int | TINYINT, SMALLINT, MEDIUMINT, INT, YEAR | Integer, short, byte, token_count | Int | Smallint, integer, bigint
Float | FLOAT, DOUBLE | float, double, half_float, scaled_float | Double | float4, float8
String | CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT | text, keyword, ip, completion | String | Char, varcher, text
Boolean | TINYINT | Boolean | Boolean | Boolean
ID | CHAR | text | ObjectId | char
Long | BIGINT | long | long | bigint
BigInt |   |   |   |  
BigDecimal | Decimal |   | decimal | Decimal, numeric
DateTime | DateTime, TimeStamp | Date | date | Timestamp
Date | Date | date | date | Date
Json | LONGTEXT, Json | Object, Nested | Object / Array | Text, Json
Binary | Binary, VarBinary, Blob | Binary | binData | bytea
  |   |   |   |  
  |   |   |   |  
Other types | BIT, Time | geo_point, geo_shape, ip, range_x | regex | Money, time, interval

GraphQL

spec

Int

32‐bit numeric non‐fractional value

Float

64-bit signed double‐precision fractional values as specified by IEEE 754

String

textual data, represented as UTF‐8 character sequences

Boolean

true or false

ID

unique identifier serialized as a string. In Prisma the ID type is only allowed for the id field and is mapped to an appropriate native id type when available.

Prisma specific GraphQL Scalars

Spec

note: Long, BigInt, BigDecimal are currently not implemented by Prisma. http://sangria-graphql.org/learn/#scalar-types

Long

64‐bit numeric non‐fractional value

BigInt

Arbitrarily large numeric non-fractional value

BigDecimal

Arbitrarily large fractional value

DateTime

ISO 8601 format

Json

parsed Json object/array

MySQL

Spec

Integer (Exact Value)

Type | Storage | Minimum Value | Maximum Value
-- | -- | -- | --
TINYINT | 1 | -128 | 127
  |   | 0 | 255
SMALLINT | 2 | -32768 | 32767
  |   | 0 | 65535
MEDIUMINT | 3 | -8388608 | 8388607
  |   | 0 | 16777215
INT | 4 | -2147483648 | 2147483647
  |   | 0 | 4294967295
BIGINT | 8 | -9223372036854775808 | 9223372036854775807
  |   | 0 | 18446744073709551615

Fixed Point (Exact Value)

Decimal and Numeric types are equivalent

DECIMAL(precision, scale)

precision is the total number of digits and scale is the number of digits following decimal point

DECIMAL(5,2) ranges from -999.99 to 999.99

Floating Point (Approximate Value)

• Float is represented as 4 byte
• Double is represented as 8 byte

Bit

A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64

Date

values with a date part but no time part. Retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

DateTime

values that contain both date and time parts. Displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
can include a trailing fractional seconds part in up to microseconds (6 digits) precision ranging from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

TimeStamp

values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
can include a trailing fractional seconds part in up to microseconds (6 digits) precision ranging from '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

Time

retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). Range from '-838:59:59' to '838:59:59'. With the fractional part included, the range for TIME values is '-838:59:59.000000' to '838:59:59.000000'.

Year

displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000

Char

Fixed length in the range 0 to 255. Values are right padded with spaces

VarChar

Variable length strings ranging from 0 to 65,535

Binary and VarBinary

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings

Blob and Text

Blobs are binary. TINYTEXT (2^8 bytes), TEXT (2^16 bytes), MEDIUMTEXT (2^24 bytes), and LONGTEXT (2^32 bytes)

Elastic Search

Spec

Text

For full text search. Passed through an analyzer to convert the string into a list of individual terms before being indexed. Multiple field parameters including analyzer, boost

Keyword

For structured data such as email address or tags. Typically used for filtering, sorting, aggregations. Multiple field parameters

long

signed 64-bit integer with a minimum value of -2^63 and a maximum value of 2^63-1.

integer

signed 32-bit integer with a minimum value of -2^31 and a maximum value of 2^31-1

short

signed 16-bit integer with a minimum value of -32,768 and a maximum value of 32,767.

byte

signed 8-bit integer with a minimum value of -128 and a maximum value of 127

double

double-precision 64-bit IEEE 754 floating point number

float

single-precision 32-bit IEEE 754 floating point number

half_float

half-precision 16-bit IEEE 754 floating point number

scaled_float

finite floating point number that is backed by a long, scaled by a fixed double scaling factor

Date

stored as a long number representing milliseconds-since-the-epoch. Accepts multiple input formats including string, int, long

Boolean

false, "false", true, "true"

Binary

Base64 encoded string

integer_range

range of signed 32-bit integers with a minimum value of -2^31 and maximum of 2^31-1

float_range

range of single-precision 32-bit IEEE 754 floating point values

long_range

range of signed 64-bit integers with a minimum value of -2^63 and maximum of 2^63-1

double_range

range of double-precision 64-bit IEEE 754 floating point values

date_range

range of date values represented as unsigned 64-bit integer milliseconds elapsed since system epoch

ip_range

range of ip values supporting either IPv4 or IPv6 (or mixed) addresses.

Array

In Elasticsearch, there is no dedicated array type. Any field can contain zero or more values by default, however, all values in the array must be of the same datatype. This design has its origin in lucene.

Object

JSON documents are hierarchical in nature: the document may contain inner objects which, in turn, may contain inner objects themselves.

Nested

The nested type is a specialised version of the object datatype that allows arrays of objects to be indexed and queried independently of each other. This is a workaround for the special way Arrays work in Elasticsearch.

geo_point

stores longitude and latitude

geo_shape

stores a list of geo_points. Many tuning options affecting performance of insert/query

ip

An ip field can index/store either IPv4 or IPv6 addresses. Enables ip range queries

completion

provides auto-complete/search-as-you-type functionality. Expensive, stored in memory

token_count

is really an integer field which accepts string values, analyzes them, then indexes the number of tokens in the string

MongoDB

Spec

Max document size is 16 MB

double

64-bit binary floating point

String

UTF-8 string

object

Embedded Document. Max size limited by parent document

array

list of any other type. Different types can be mixed. Stored internally as an object with increasing numeric keys

binData

array of bytes

objectId

values consist of 12 bytes. Can be represented as text

boolean

"true", "false"

date

a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This results in a representable date range of about 290 million years into the past and future.

null

null value

regex

represented by two strings: pattern and options

int

32 bit integer

long

64 bit integer

decimal

IEEE 754-2008 128-bit decimal floating point. supports 34 decimal digits of precision, a max value of approximately 10^6145, and min value of approximately -10^6145

PostgreSQL

Spec

Numeric values

Name | Storage Size | Description | Range
-- | -- | -- | --
smallint | 2 bytes | small-range integer | -32768 to +32767
integer | 4 bytes | typical choice for integer | -2147483648 to [phone]
bigint | 8 bytes | large-range integer | -9223372036854775808 to +9223372036854775807
decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real | 4 bytes | variable-precision, inexact | 6 decimal digits precision
double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision
smallserial | 2 bytes | small autoincrementing integer | 1 to 32767
serial | 4 bytes | autoincrementing integer | 1 to 2147483647
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807

char

fixed length character string

varchar

variable length character string

text

unlimited variable length character string. The max size is around 1GB

money

Fixed fractional precision. Range -92233720368547758.08 to +92233720368547758.07

bytea

Variable length binary string

Date/Time Types

Name | Storage Size | Description | Low Value | High Value | Resolution
-- | -- | -- | -- | -- | --
timestamp [ (p) ] [ without time zone ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond / 14 digits
timestamp [ (p) ] with time zone | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond / 14 digits
date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day
time [ (p) ] [ without time zone ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond / 14 digits
time [ (p) ] with time zone | 12 bytes | times of day only, with time zone | 00:00:00+1459 | 24:00:00-1459 | 1 microsecond / 14 digits
interval [ fields ] [ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond / 14 digits

Boolean

true, false

[naartjie]

Hi. Just some notes:

There is no Date or DateTime in GraphQL spec AFAIK. You would normally define those as scalars yourself (eg, using something like graphql-iso-date).

Prisma 1.18 defines a DateTime but not Date. I'm not sure if there is a Date in trunk.

PS I came across this issue because I'm trying to map a date YYYY-MM-DD in datamodel.prisma

[ellipticaldoor]

I will like to have Long so I will be able to to store any mac address as a decimal number on the database. Right now Im using Float but for this case Long will be perfect because mac addresses can't have decimals.

This will be the biggest mac address FF:FF:FF:FF:FF:FF which in decimal equals to 281474976710655

I could store this number as a string but I need it as number so I can sort them from greatest to smallest.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[THPubs]

I think we better have the ability to add big decimals with higher precision points. Having issues when storing latitude and longitude values. When we give 6.940043516608129 it stores 6.94004351660813
That's 100m difference 🙂

[nnn-gif]

@THPubs did you got any solution for big decimals with higher precision points

[THPubs]

@niktrix, For now, had to manually set the field to float8. But that's not a good solution. We use this in many places, If we forget to update the field manually the system will record wrong coordinates! Hope the guys fix it soon.

[sscotth]

@THPubs Are you sure that a 15 decimal (0.000000000000001) difference is 100 meters?

According to wikipedia, 8 decimal degrees about 1mm precision

https://en.wikipedia.org/wiki/Decimal_degrees

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.

[AaronBuxbaum]

Use-case for Long or BigInt -- Twitter API numeric id is greater than 52 bits. You can't use a string because ordering strings doesn't work the same as ordering numbers. You can't use Float, either, because the number is too large. Attempting to add a float value of 1154057692723519500 throws the following:

Float or Int value is too big to fit in double (line 1, column 11)

[impavidum]

sooo....

[michaeldv-pg]

@sorenbs Please support Long