Introduction

Recently, my team and I were building an application that monitored the flow rate of liquid through pipes and remotely controlled valves to enable or disable these flows.

I work for the Internet of Things company Blues which specializes in getting IoT data from devices in the real world into the cloud via cellular, and we were demonstrating how our tech could be used by facilities managers to monitor liquid and gas transfers and remotely toggle valves throughout a piping system via a web app.

Without going into too much detail, the way the project works is: the device sends the current flow rate data and valve state to the cloud, the cloud then forwards that information to our web application, and the application saves that data into a PostgreSQL database and displays it in the browser for the user. From the UI, the user can monitor the flow rate, checking for anomalies, and open or close a valve at will.

If you're interested to learn more about our flow rate monitoring project, you can see the full tutorial for building your own here.

The web app dashboard looks like this:

For the web app, we were using Next.js to display our IoT data dashboard, and Prisma (an object relational mapper or ORM) to interact with our PostgreSQL database. Because of the project requirements, which I'll get into shortly, we ended up doing some pretty interesting things with Prisma including upsert transactions, querying for the most recent record in a table, fetching related data from multiple tables in one query, handling raw JSON data, and more.

Today, I'm going to share with you some of the more advanced and complex solutions I learned to solve with Prisma.io in hopes of making it easier when you run into similar scenarios in your own application development.

NOTE : This article is not an introduction to working with Prisma. If you're new to it, I highly recommend checking out their getting started documentation first to get familiar.

Prisma primer

Rarely do web developers today write raw SQL queries in their applications. Instead, we tend to rely on object relational mapping libraries (ORMs) like Mongoose, Sequelize, Knex, or Prisma.

ORMs provide a host of benefits like:

• Time saved writing raw queries,
• Compatibility with multiple types of databases,
• Schema definitions,
• Type-safety,
• And more.

Prisma, in particular, has become the latest, open source favorite when it comes to ORMs because it provides all the benefits I mentioned above plus:

• VS Code integration,
• TypeScript type-safety,
• Pagination and transactions,
• Serverless support,
• And a visual database browser, to name a just few.

It is really slick to work with and makes interacting with a database in a JavaScript application feel much more intuitive and natural and much less like two different languages being mashed together.

But even with all this going for it, there are still some things that are just plain complex to do, so let's cover some of them now so they're less daunting when you run into them in your next project.

Define implicit many-to-many data relationships

The majority of the tips I want to share have to do with tables of related data, so the first thing we'll cover is how to define those type of relationships.

For our project, we had two Prisma models that were related to one another:

• Device - each individual flow rate and valve control device,
• Fleet - a particular group of these devices.

NOTE: For any of the code snippets listed below, click the titles of the snippets to see the working code in GitHub repo.

The Device model in Prisma schema looks like this:

Device model

model Device {
  id Int @id @default(autoincrement())

  // unique device uid in notehub
  deviceID String @unique @map("device_id") @db.VarChar(64)

  // device serial number (cached)
  name String? @db.VarChar(80)
  lastSeenAt DateTime? @map("last_seen_at") // the last time an event was heard from this device

  // the project the device belongs to
  project Project @relation(fields: [projectID], references: [projectID])
  projectID String

  @@map("device")
}

The Fleet model in the Prisma schema looks like this:

Fleet model

model Fleet {
  id Int @id @default(autoincrement())

  // unique fleet id
  fleeID String @unique

  // the project the fleet belongs to
  project Project @relation(fields: [projectID], references: [projectID])
  projectID String

  @@map("fleet")
}

Each device can belong to no fleets or multiple fleets, and each fleet can contain no devices or many devices. This is a classic example of a many-to-many (m:n) relationship.

Prisma offers two ways to handle these sorts of relationships:

• Explicit many-to-many relationships - in explicit m:n relationships, the relation table is represented as its own model in the Prisma schema and can be used in queries. Explicit many-to-many relations define three models: Two models that have a many-to-many relation: Device and Fleet, and one model that represents the relation table: DevicesInFleets (sometimes called the JOIN, link or pivot table) in the underlying database.
• Implicit many-to-many relationships - in implicit m:n relationships, the table exists in the underlying database, but it is managed by Prisma and does not manifest in the Prisma schema (i.e. you don't have to define the DevicesInFleets model and table yourself, Prisma just knows it's a thing).

Here is a typical JSON Device data event that might get routed to the web app.

Example event routed to the app

{
  "projectID": "app:a8beb5bd-622e-46a6-866a-ae4528c7c201",
  "deviceID": "dev:864622040363787",
  "eventID": "490006f7-80f8-4314-8b5e-5ff587f07fba",
  "lastSeenAt": "2023-01-23T18:29:16.000Z",
  "eventBody": { "flow_rate": 810, "valve_state": "open" },
  "fleetIDs": ["fleet:f660d491-8830-420f-be7a-c8f91c460813"],
  "name": "Pipe Section A"
}

For convenience, we chose to take advantage of Prisma's implicit many-to-many relationship option to associate devices and fleets with one another. In order to let Prisma know that devices and fleets are related , in the Device and Fleet models defined in the schema.prisma file, simply add each related model to the other's schema.

So the Device model has Fleet added to it:

Device model

model Device {
  id Int @id @default(autoincrement())

  // unique device uid in notehub
  deviceID String @unique @map("device_id") @db.VarChar(64)

  // extra device-specific data here

  // fleets a device belongs to (implicit many-to-many relationship)
  fleets Fleet[]

  @@map("device")
}

And the Fleet model has Device added to it:

Fleet model

model Fleet {
  id Int @id @default(autoincrement())

  // unique fleet id
  fleeID String @unique

  // more fleet-specific data

  // devices assigned to fleet (implicit many-to-many relationship)
  devices Device[]

  @@map("fleet")
}

And that's all you need to do to establish an implicit many-to-many relationship with Prisma. No explicit tables, no SQL queries, no extra work to handle a relatively complex, yet very commonplace, relationship. I can tell you, implicitly defining these sorts of relationships amongst a number of related tables in this project really saved us a ton of time during development.

Now that we've defined the related data, it's time to handle creating or updating related data in one function.

Accurately update related records across multiple tables with Prisma

Building off the previous example, we'll cover here how to correctly update related data across multiple tables at once.

As I said before, each device can belong to zero or more fleets, and each fleet can contain zero or more devices at any given time.

As new Device events flow into our PostgreS database from individual devices, each event includes a field with a list of fleet IDs for whichever fleets that device belongs to at the time (if any).

Example event routed to the app

{
  "projectID": "app:a8beb5bd-622e-46a6-866a-ae4528c7c201",
  "deviceID": "dev:864622040363787",
  "eventID": "490006f7-80f8-4314-8b5e-5ff587f07fba",
  "lastSeenAt": "2023-01-23T18:29:16.000Z",
  "eventBody": { "flow_rate": 810, "valve_state": "open" },
  "fleetIDs": ["fleet:f660d491-8830-420f-be7a-c8f91c460813"],
  "name": "Pipe Section A"
}

According to Prisma's docs, the connectOrCreate API function is the way to create a "related record" that may or may not exist, which is perfect for adding new devices to the Device table and new, related fleets to the Fleet table at the same time via a create() with nested write function. However, when attempting to update a device's fleets when a new event came in, I encountered a slight problem with the update() function.

When an event arrives for an existing device and the fleets are completely different, only having the connectOrCreate() function present in the update() failed to delete the old fleets and replace them with new fleets in the Fleet table. The new fleets were instead associated with the device along with the old fleets, and the device looked as if it was associated with more fleets than it truly was.

In order to accurately update both the Device table and the related records in the Fleet table in the same query, we have to do two things:

1. Disconnect all related fleet records using the set() method.
2. Call the connectOrCreate() function to insert the new fleet data into the Fleet table.

NOTE: Deleting specific related records with deleteMany() function does not work

Another option for altering related records that I tried was to delete all related records using the deleteMany() function. This didn't work correctly though because it deleted the fleet IDs from all the devices in the Device table, not just the deviceID the event specified.

Here is what the final upsertDevice() query looks like: it correctly updates the device data in the Device table, and the associated fleet IDs in the Fleet table without altering the fleets associated with any other device in the process.

upsertDevice() query

 /**
   * Insert or update the device based on the unique device ID.
   *
   * @param project
   * @param deviceID
   * @param name
   * @param lastSeenAt
   * @param fleetIDs
   * @param location
   * @returns
   */
  private upsertDevice(
    project: Project,
    deviceID: string,
    name: string | undefined,
    lastSeenAt: Date,
    fleetIDs: string[],
    location?: NotehubLocation
  ) {
    const args = arguments;

    const formatConnectedFleetData = fleetIDs.map((fleet) => ({
      create: {
        fleetID: fleet,
        projectID: project.projectID,
      },
      where: {
        fleetID: fleet,
      },
    }));

    return this.prisma.device
      .upsert({
        where: {
          deviceID,
        },
        create: {
          name,
          deviceID,
          locationName,
          fleets: {
            connectOrCreate: formatConnectedFleetData,
          },
          project: {
            connect: {
              id: project.id,
            },
          },
          lastSeenAt,
        },
        update: {
          name,
          locationName,
          fleets: {
            set: [],
            connectOrCreate: formatConnectedFleetData,
          },
          project: {
            connect: {
              id: project.id,
            },
          },
          lastSeenAt,
        },
      })
      .catch((cause) => {
        throw new ErrorWithCause(
          `error upserting device ${deviceID} ${JSON.stringify(args)}`,
          { cause }
        );
      });
  }

Be aware that the set() function does not actually delete the fleet ID from the Fleet table - it just disconnects it from the device, however, as long as you don't have large amounts of fleet ID records filling up your related table, this shouldn't pose any major performance problems for your app.

Unfortunately there's currently no single related record upsert() function that will both delete previously related records and create newly related records, but maybe one day Prisma will include this use case. Until then, I hope this helps you handle accurately updating your related records.

Ok, let's move on to the next complex Prisma tip: including relational data in a READ query.

Return relational data from another table in Prisma READ query

Just like updating related tables can be done in a single transaction with Prisma, querying multiple tables for related data can also be done in a single action.

Taking the web app we've been talking about, for display purposes in the UI, it's necessary to have both the individual device ID and the fleet IDs that each device belongs to. If you've been following along with this article, you'll know already that the device details reside in the Device table and the fleet details for fleets that device is part of reside in their own separate Fleet table.

When storing device and related fleet data in their proper tables, we can use the connectOrCreate API function.

To perform a "nested read" and fetch both the device details and the associated fleet details, we use Prisma's include API function.

To get all the fleet data associated with a particular device, the fetchDevice() query in our code base looks like this:

fetchDevice() function

async fetchDevice(deviceID: DeviceID) {
    const device = await this.prisma.device.findUnique({
      where: {
        deviceID: deviceID,
      },
      include: {
        fleets: true,
      },
    });
    return device;
  }

We use Prisma's standard findUnique API query on the Device table to find the specific device based on its device ID, and then inside of that findUnique() query, we add include: { fleets: true } to get all the fleet details that are related to that device.

Here is what the data that comes back from the query looks like:

Example device data with fleet info returned from query

{
  [
   {
      "id":1,
      "deviceID":"dev:864622040363787",
      "name":"Pipe Section A",
      "lastSeenAt":"2023-01-23T18:29:16.000Z",
      "projectUID":"app:a8beb5bd-622e-46a6-866a-ae4528c7c201",
      "fleets":[
         {
            "id":1,
            "fleetID":"fleet:f660d491-8830-420f-be7a-c8f91c460813",
            "projectID":"app:a8beb5bd-622e-46a6-866a-ae4528c7c201"
         }
      ]
   }
 ]
}

As you can see from the code above, all the relevant device info is provided along with the fleet data for its related fleet. Pretty sweet.

NOTE:

If you only wanted to return certain fields for related data, you could use the select API to choose a subset of related fields to return.

For example, if you only wanted related fleet IDs, you'd update the include query to be: include: { fleets: { select: { fleetID: true } } } }.

Get most recent data record from a table

Right, let's move on to reading the most recent data record from a table.

In the UI of the valve flow rate monitor, we need to get the most recently added flow-rate and valve state data for a device to correctly show the user what's happening.

Prisma provides a findFirst API query that returns the first created data record, but it doesn't provide a similarly named findLast API query.

Instead, there are two ways we can get the most recent record out of a table:

1. Use a combination of the findFirst API and the orderBy filter to sort the records by something like the record's date.
2. Use the findMany API and use the take function to grab just the last value from the query.

We'll cover both options:

findFirst and orderBy example

The first way to get the most recent event from a table is to use findFirst combined with orderBy where the records are sorted in descending order from newest to oldest. In the app, there are alarms that are triggered when a device's flow rate falls below or above thresholds set by the user; the alarm is displayed by highlighting the device's row in red in a table showing all the devices.

Since the device row can't be highlighted more than once, we don't need to know if the device has more than one alarm event for it - we only need to know the most recent alarm event, so we'll use the findFirst query and sort all the alarm events in the Notification table for a device in descending order by their lastSeenAt date.

Here's how the final query looks.

getLatestDeviceAlarm()

  async getLatestDeviceAlarm(deviceID: DeviceID): Promise<Notification> {
    const latestAlarmFromDevice = await this.prisma.notification.findFirst({
      where: {
        AND: {
          type: "alarm",
        },
        content: {
          path: ["deviceID"],
          equals: deviceID,
        },
      },
      orderBy: {
        lastSeenAt: "desc",
      },
    });

    return latestAlarmFromDevice || undefined;
  }

Notice in the query that in the end, the getLatestDeviceAlarm() function either returns the most recent alarm for a device or it returns undefined because there's a chance the device has not had an alarm go off. Something to keep in mind, which I'll cover in more detail later in this article.

Now let's look at the other option to get the most recent record from the Event table using findMany.

findMany and take example

getLatestDeviceEvent()

async getLatestDeviceEvent(deviceID: DeviceID, file: string): Promise<Event> {
    const latestDeviceEvent = await this.prisma.event.findMany({
      where: {
        AND: {
          deviceUID: deviceID,
        },
        eventName: file,
      },
      take: -1,
    });
    return latestDeviceEvent[0];
  }

For this function, we just want to get the latest regular event for a device: the latest flow rate and valve state reading - again, it's used to display the device's current status in the web app dashboard.

To get the most recent event with Prisma's findMany API, we use the deviceID to narrow down all the events and use take: -1 to grab the last record in the table. take: -1 was introduced to Prisma back in v2 to simplify paginating through lots of data, but it can also be used just to return one record as well as a list of records, just remember to extract the single record from the array when accessing the data.

Read and write JSON data to a table

All right, now we'll cover a very useful and flexible thing you might want to do: write JSON data into a table column or read it out again.

JSON fields are super useful when you need to store data that doesn't have a consistent structure (i.e. you're not sure what properties will be present or not), or you're importing data from another system and don't want to map that data to Prisma models.

For our project, we chose to use JSON fields for event objects because we weren't always certain what data an event might contain. Here's what an event routed to the app might look like, the eventBody property is what we want to store as raw JSON data because it's the field most subject to change.

Example event routed to the app

{
  "projectID": "app:a8beb5bd-622e-46a6-866a-ae4528c7c201",
  "deviceID": "dev:864622040363787",
  "eventID": "490006f7-80f8-4314-8b5e-5ff587f07fba",
  "lastSeenAt": "2023-01-23T18:29:16.000Z",
  "eventBody": { "flow_rate": 810, "valve_state": "open" },
  "fleetIDs": ["fleet:f660d491-8830-420f-be7a-c8f91c460813"],
  "name": "Pipe Section A"
}

To get our data table prepared to accept any old JSON data as a column, we just need to tell the model that's the case. So in our schema.prisma file where we define all our Prisma models, we set the properties for an Event like so:

Event model

model Event {
  id Int @id @default(autoincrement())

  eventName String @map("file")
  eventID String @map("event") @unique

// the device that produced the event
  device Device @relation(fields: [deviceID], references: [deviceID], onDelete: Cascade)
  deviceID String 

// when the event occurred
  when DateTime
  value Json

  @@map("event")
}

The property to pay special attention to is the value property - that's the field we're giving a type of Json to, which is all we need to do to make the model happy.

Write JSON into a Prisma field

After the model's defined, when a new event comes in (like the example above), a Prisma upsert() function can be called on the Event table.

upsertEvent() function

  /**
   * Insert or update the event
   *
   * @param deviceID
   * @param when
   * @param eventName
   * @param eventID
   * @param value
   * @returns
   */
  private upsertEvent(
    deviceID: string,
    when: Date,
    eventName: string,
    eventUID: string,
    value: object
  ) {
    const args = arguments;

    return this.prisma.event
      .upsert({
        where: {
          eventID,
        },
        create: {
          deviceID,
          when,
          eventName,
          eventID,
          value,
        },
        update: {
          // reading already exists
          // no-op
        },
      })
      .catch((cause) => {
        throw new ErrorWithCause(
          `error upserting event ${deviceUID} ${JSON.stringify(args)}`,
          { cause }
        );
      });
  }

If you're using TypeScript like we are in our project, just define the JSON field value as an object and then simply pass it in as one of the values in the Prisma upsert() function. Pretty straightforward.

Read JSON from a Prisma table

Reading the JSON field out with Prisma is pretty straightforward as well. If you recall in the previous tip we looked at the getLatestDeviceEvent() query, this query returns the data from the Event table, including the JSON field value. When the data is returned it looks something like this:

Example event data returned from getLatestDeviceEvent()

[
  {
    "id": 1,
    "eventName": "data.qo",
    "eventID": "490006f7-80f8-4314-8b5e-5ff587f07fbc",
    "deviceID": "dev:864622040363787",
    "when": "2023-01-23T18:29:16.000Z",
    "value": { "flow_rate": 810, "valve_state": "open" }
  }
]

From here, just like with any other field of data returned by a Prisma query, we can access the value field (and any data contained therein) and do with it what we want. So to get the flow_rate, you'd simply do something like const flowRate = data[0].value.flow_rate;.

Trust me, this ability to easily read and write any sort of JSON data into a field in a database via a Prisma model is really, really handy to have.

Handle undefined data

Last Prisma tip for this article: be prepared to handle undefined values returned from Prisma.

As I alluded to in the earlier tip where I showed the getLatestDeviceAlarm() query, there is a chance in some scenarios where there are no records that match the parameters in the Prisma query. When this happens, undefined will be returned by Prisma and if you're not prepared to handle it, it can throw you for a loop.

If you're calling a particular Prisma query for an array of items (like checking which devices have alarm events), be ready to filter out any returned alarm data that is actually undefined so it doesn't give you false positives.

And with that, we've covered the majority of the hard won knowledge I gained about Prisma while working with it.

Conclusion

Prisma.io is a very powerful, very popular, JavaScript-friendly ORM for interacting with all sorts of databases. Although the learning curve for it can be a little steep, once you start to get the hang of it, you realize just how fine-grained the control is and how much nicer it is to use when interacting with databases instead of raw SQL queries.

When my team and I were using it to help us build a valve and flow rate monitoring application, we learned a lot about using some of Prisma's more advanced features like relational data tables, handling raw JSON fields, and even filtering and sorting results. With Prisma, very quickly you can start to do some amazing stuff that used to be quite complex and required good SQL knowledge.

Check back in a few weeks — I’ll be writing more about JavaScript, React, IoT, or something else related to web development.

If you’d like to make sure you never miss an article I write, sign up for my newsletter here: https://paigeniedringhaus.substack.com

Thanks for reading. I hope you learned some new ways to use Prisma on top of your own database-driven web apps. Enjoy!

References & Further Resources

• Prisma.io ORM site
• Valve flow rate monitor Hackster tutorial
• Valve flow rate monitor GitHub repo
• Many-to-many (m:n) data relationship