Handle PostgreSQL Custom Types for JSON Object Fields in Prisma Schema

Handle PostgreSQL Custom Types for JSON Object Fields in Prisma Schema
Recently, I had to deal with Prisma schemas to create PostgreSQL custom data types that are used to structure and validate JSON object columns. When it comes to handling PostgreSQL custom data types for JSON object fields within Prisma schemas, developers often encounter challenges due to the lack of native support. If you are reading this article, that means you also have found out that Prisma schema doesn't support PostgreSQL custom data types. Hence, this article is written to give you some approaches to handle that problem within your backend code and keep your JSON object columns as you planned in your ER diagram. Before going for the real work, let's give some briefs on some keywords in this article.

What is Prisma and Prisma Schema?

Traditional methods of interacting with databases through raw queries have been commonplace, but modern developers are increasingly turning to Object-Relational Mapping (ORM) tools like Prisma to streamline and enhance their database interactions. Prisma is an open-source database toolkit designed to simplify database access for developers. It supports multiple databases, including popular choices like PostgreSQL, MySQL, and SQLite. At its core, Prisma serves as an ORM, bridging the gap between the application code and the database by providing a type-safe and auto-generated query builder.

The core part of Prisma lies in its schema definition. The Prisma Schema is a declarative configuration file that describes the data model of an application, including the relationships between different entities and their properties. With the Prisma Schema, developers can define their data structures using a clear and simple syntax, enhancing the readability and maintainability of the code.

Why you should use an ORM like Prisma?

In the dynamic landscape of modern software development, utilizing efficient tools and frameworks is essential for building robust and scalable applications. One such indispensable tool in the realm of database interaction is Object-Relational Mapping (ORM), with Prisma emerging as a powerful and versatile choice. Prisma simplifies database access by providing a type-safe and intuitive interface, offering a plethora of advantages that make it a preferred ORM solution for developers.
  • Cross-Database Compatibility: Seamlessly integrates with various databases, including PostgreSQL, MySQL, and SQLite, offering developers the flexibility to work across different projects and adapt to diverse requirements effortlessly.
  • Type-Safe Query APIs: Generates type-safe and auto-completed query APIs, reducing the risk of runtime errors and enhancing overall code quality, thereby enabling developers to focus more on application logic and business requirements.
  • Modern Data Modeling: Utilizes a concise and readable Prisma schema for defining data structures, promoting maintainability, and reducing the likelihood of inconsistencies in the database, ensuring a streamlined and organized development process.
  • Efficient Migrations: Simplifies database schema evolution with efficient migration support, facilitating smooth transitions as the application evolves over time, contributing to long-term stability and scalability.
  • Declarative Data Access: Embraces declarative data access, enabling developers to express database queries in a clear and expressive manner, enhancing code readability, and facilitating a better understanding of the data layer within the application.
  • Asynchronous Operations: Supports asynchronous operations, aligning with modern development paradigms and enabling the creation of performant and scalable applications.
  • Reverse Lookup for Relationships: Simplifies navigation through relationships by providing a reverse lookup mechanism, enhancing the ease of querying related data and improving overall database interaction efficiency.
  • SQL Injection Prevention: Implements robust security measures by automatically preventing SQL injection, safeguarding the application against malicious attacks, and bolstering the overall security posture.

What are PostgreSQL Custom Types for JSON Fields

PostgreSQL has been widely adopted for its robustness, reliability, and extensibility. One of the features that make PostgreSQL so versatile is its support for custom types, which allows users to define their own data types beyond the built-in ones. This capability extends to JSON objects, enabling users to create custom types that represent complex JSON structures.

In PostgreSQL, JSON (JavaScript Object Notation) data types allow you to store and manipulate JSON data more efficiently. However, JSON data can sometimes be complex and nested, making it challenging to manage. By creating custom types for JSON objects, you can encapsulate complex data structures into reusable and manageable units.

Let's consider an example where you need to store the addresses of users in the format defined in SCIM 2.0 within a single column of the "Users" table. For this, you can define a custom data type for that column like below.

Once you have defined this custom type, you can use it as a column type in your PostgreSQL tables.

By using custom types for JSON objects, you can improve the readability, reusability, and manageability of your JSON data in PostgreSQL. Custom types allow you to define the structure of your JSON data and enforce constraints, making it easier to work with complex data structures.

How to Handle PostgreSQL Custom JSON Types in Prisma Schema

As I mentioned above, Prisma schema doesn't support custom data types, facilitated in PostgreSQL databases. Therefore, you have to define typescript interfaces for those fields and manually validate them before storing data. First of all, let's see how we can define the above mentioned "address" column of the "users" table in Prisma schema.

As you can see, we can not structure or validate the "address" field at the DB level because Prisma schemas don't support for writing custom JSON types that is native in PostgreSQL DBs. The only way to deal with this bottleneck without interrupting the Prisma DB migration process is to write a typescript interface and a custom validator for the "address" field.

In this "ValidatorService" class, we have the validateAddress() method that accepts an object as the input param and returns the validated address object. Inside the validateAddress() method, it checks whether the data types of the user-entered values are equal to the data types we need to store in the DB. Then it creates a new "address" object from the user-entered object to drop any unnecessary values from the user-entered object before storing data in the database.

Hope you will pass the code audit with this trick that allow you to validate user inputs in the backend level before storing custom JSON objects in your DB with Prisma ORM. Happy Coding!