Working with fields

This section describes how to work with field types like Json and lists/arrays of scalars.

Working with Decimal

Decimal fields are represented by the Decimal.js library. The following example demonstrates how to import and use Prisma.Decimal:

import { PrismaClient, Prisma } from '@prisma/client'
const newTypes = await prisma.sample.create({
data: {
cost: new Prisma.Decimal(24.454545),
},
})

Working with BigInt

BigInt fields are represented by the BigInt type (Node.js 10.4.0+ required). The following example demonstrates how to use the BigInt type:

import { PrismaClient, Prisma } from '@prisma/client'
const newTypes = await prisma.sample.create({
data: {
revenue: BigInt(534543543534),
},
})

Working with Bytes

Bytes fields are represented by the Buffer type. The following example demonstrates how to use the Buffer type:

import { PrismaClient, Prisma } from '@prisma/client'
const newTypes = await prisma.sample.create({
data: {
myField: Buffer.from(['Hello', 'this is a string', 1, 9, 4]),
},
})

Working with Json

Use the Json field to read, write, and perform basic filtering on JSON types in the underlying database. In the following example, the User model has an optional Json field named extendedProfile:

model User {
email String @unique
id Int @default(autoincrement()) @id
name String?
posts Post[]
extendedProfile Json?
role Role @default(USER)
}

The following represents a sample extendedProfile field value:

{
"bio": [
{
"languages": "Danish, English"
},
{
"age": "87"
}
],
"pets": "none"
}

Note: The Json field is only supported if the underlying database has a corresponding JSON data type.

You can use a Json field if:

  • You need to store data that does not adhere to a consistent structure
  • You are importing data from another system and do not want to map that data to Prisma models

The Json field supports a few additional types, such as string and boolean. These additional types exist to match the types supported by JSON.parse():

export declare type JsonValue = string | number | boolean | null | JsonObject | JsonArray

Filtering Json fields

Prisma Client supports basic filtering for Json fields. Relevant types:

export type NullableJsonFilter = {
equals?: InputJsonValue | null
not?: InputJsonValue | null | NullableJsonFilter
}

The examples in this section are based on the following schema:

generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model Post {
authorId Int?
content String?
id Int @default(autoincrement()) @id
published Boolean @default(false)
title String
user User? @relation(fields: [authorId], references: [id])
@@index([authorId], name: "authorId")
}
model User {
email String @unique
id Int @default(autoincrement()) @id
name String?
posts Post[]
extendedProfile Json?
role Role @default(USER)
}
enum Role {
ADMIN
USER
MODERATOR
}

Write to a Json field

The following example writes a JSON object to the extendedProfile field:

var json = { pets: [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }] }
const createUser = await prisma.user.create({
data: {
email: 'birgitte@prisma.io',
extendedProfile: json,
},
})

Note: JavaScript objects (for example, { pets: "none"}) are automatically converted to JSON.

Filter on a Json field

var json = { pets: [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }] }
const get = await prisma.user.findMany({
where: {
extendedProfile: {
equals: json,
},
},
})

The following example omits the equals but produces the same result:

var json = { pets: [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }] }
const get = await prisma.user.findMany({
where: {
extendedProfile: json,
},
})

The following example returns all users where the extendedProfile field does not match json:

var json = { pets: [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }] }
const get = await prisma.user.findMany({
where: {
extendedProfile: {
not: json,
},
},
})

Working with scalar lists / scalar arrays

Setting the value of a scalar list

The following example demonstrates how to set the value of a scalar list (coinflips) when you create a model:

const createdUser = await prisma.user.create({
data: {
email: 'eloise@prisma.io',
coinflips: [true, true, true, false, true],
},
})

Adding items to a scalar list

In 2.20.0 and later (PostgreSQL only), you can use the atomic push method to add a single value to a scalar list:

const userUpdate = await prisma.user.update({
where: {
id: 9,
},
data: {
coinflips: {
push: true,
},
},
})

In earlier versions, you have to overwrite the entire value. The following example retrieves user, uses push() to add three new coin flips, and overwrites the coinflips field in an update:

const user = await prisma.user.findUnique({
where: {
email: 'eloise@prisma.io',
},
})
if (user) {
console.log(user.coinflips)
user.coinflips.push(true, true, false)
const updatedUser = await prisma.user.update({
where: {
email: 'eloise@prisma.io',
},
data: {
coinflips: user.coinflips,
},
})
console.log(updatedUser.coinflips)
}

Filtering scalar lists

In 2.15.0 and later (Postgres only), you can filter scalar lists - for example, a list of String or Enum types. The following example returns all posts where the tags list includes databases and typescript:

const posts = await prisma.post.findMany({
where: {
tags: {
hasEvery: ['databases', 'typescript'],
},
},
})

NULL values in arrays

Array fields with a NULL value are not considered by following conditions:

  • NOT (array does not contain X)
  • isEmpty (array is empty)

This means that records you might expect to see are not returned. Consider the following examples:

  • The following query returns all posts where the tags do not include databases:

    const posts = await prisma.post.findMany({
    where: {
    NOT: {
    tags: {
    has: 'databases',
    },
    },
    },
    })
    • ✔ Arrays that do not contain "databases", such as {"typescript", "graphql"}
    • ✔ Empty arrays, such as {}

    The query does not return:

    • NULL arrays, even though they do not contain "databases"

The following query returns all posts where tags is empty:

const posts = await prisma.post.findMany({
where: {
tags: {
isEmpty: true,
},
},
})

The query returns:

  • ✔ Empty arrays, such as {}

The query does not return:

  • NULL arrays, even though they could be considered empty

To work around this issue, you can set the default value of array fields to {} at a database level (Prisma schema does not currently support default values for arrays.)

Edit this page on GitHub