Spring Boot - Java GraphQL - extended scalar types . . .

This is my first te(a)ch note on GraphQL. I had hit a couple road blocks in a few of days of my hands on journey with it. Unlike good-old-days when books were the primary source of learning that had everything documented, there is no single place to find all details these days.

Environment: Java 21, Spring Boot 3.2.3, PostgreSQL 15.3, maven 3.9.6 on macOS Catalina 10.15.7

Extended or Custom Scalar types

GraphQL specifies very limited set of well-defined built-in scalar data types (primitive data types): Int, Float, String, Boolean and ID. GraphQL systems must support these as described in the specification. Everything else is an extended or custom scalar data type.

That, obviously is a very limited set supported. All other data types need custom scalar implementations which basically require coercing values at run-time and converting those to Java run-time representation. Luckily, the Java ecosystem is so huge that you almost don't need to break the ground in doing so. You will always find few open-source libraries that have tackled it already for you. GraphQL Java Scalars is one such in this Java GraphQL for extended scalar data types.

The primitive data type set supported is just not enough. You at least need support for few other data types used in any Java application like: Long, UUID, DateTime etc. They all need special considerations in your application's GraphQL schema. The DateTime takes a very special seat. In fact, anything around Dates in Java always scares me. To humans, Date and Time are the most obvious types in day-to-day life, but non in Software systems. Date is the most abused type than any other data type. Just recollect how many billions of dollars of money was wasted on this one data type in 1998 and 1999 around the globe. After 23 years of learning the mistake, the Date is still not dealt easily; it is still a complex data type to deal with ;).

To use custom scalar types other than that limited primitive set, you have to look for writing code that handles serialization, parsing and literal parsing for each additional data type. The graphql-java-extended-scalars library provides implementation for many other data types.

With a maven dependency added for this library, all you need to do is to register a scalar data type  with RuntimeWiringConfigurer as described in the README. If you need to register multiple types, it's a builder, so you can just chain those like:

@Configuration @Slf4j public class GraphQLConfiguration { /** * Custom scalar support for UUID, Long, and DateTime. Registers extended scalar types used GraphQL query schema. */ @Bean public RuntimeWiringConfigurer runtimeWiringConfigurer() { log.info("Registering extended GraphQL scalar types for UUID, Long, DateTime..."); return wiringBuilder -> wiringBuilder.scalar(ExtendedScalars.UUID) .scalar(ExtendedScalars.GraphQLLong) .scalar(ExtendedScalars.DateTime); } }

In addition to this, specify these scalar types in your application's schema.graphqls schema specification like:

"Extended scalar types" scalar UUID @specifiedBy(url: "https://tools.ietf.org/html/rfc4122") scalar Long @specifiedBy(url: "https://ibm.github.io/graphql-specs/custom-scalars/long.html") scalar DateTime @specifiedBy(url: "https://scalars.graphql.org/andimarek/date-time.html") ...

You are good to go.

Note that the extended scalar type for Long is named as GraphQLLong by this library. But, you should use Long in your your schema when you specify it as shown above. The directive @sprifiedBy is recommended to be used by GraphQL specification and is also a good practice to follow. Never ignore good practices ;)

Gotcha

Java JPA -  Instant vs. OffsetDateTime

If you are dealing with DateTime, make sure that whatever the Java type used in your code, it complies with GraphQL specification that requires date time offset.

I initially used Instant type in my JPA BaseEntity class for two properties: createdOn and updatedOn that are mapped by Hibernate provided @CreationTimestamp and @UpdateTimestamp mapped to PostgreSQL column type TIMESTAMP WITH TIME ZONE.  I switched to OffsetDateTime type because Instant is not supported and will never be by this library due to it not complying with the specification for DateTime. Java's Instant, Date and LocalDateTime do not include offset.

OffsetDateTime is an immutable representation of a date-time with an offset. This class stores all date and time fields, to a precision of nanoseconds, as well as the offset from UTC/Greenwich.

TIP

PostgreSQL offers two date time types: timestamp, timestamptz (is abbreviation of timestamp with time zone).

The following query results tell the date time story on this day light savings day of this year (Sun Mar 10, 2024). I ran it on my local PostgreSQL 15.3 running in Docker container.

-- Ran the query on Mar 10, 2024 day light savings day at EST 5:13:13 PM, EDT: 17:13:13 select version(); -- PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit show time zone; -- UTC SELECT now(), -- 2024-03-10 21:13:13.956877 +00:00 (timestamp with time zone UTC) now() AT TIME ZONE 'EST' AS est, -- 2024-03-10 16:13:13.956877 (??) now() AT TIME ZONE 'EDT' AS edt, -- 2024-03-10 17:13:13.956877 (right) now() AT TIME ZONE 'CST' AS cst, -- 2024-03-10 15:13:13.956877 (??) now() AT TIME ZONE 'CDT' AS cdt, -- 2024-03-10 16:13:13.956877 (right) now()::timestamp AT TIME ZONE 'EDT' AS timestamp_without_tz, -- 2024-03-11 01:13:13.956877 +00:00 (wrong) now()::timestamptz AT TIME ZONE 'EDT' AS timestamptz; -- 2024-03-10 17:13:13.956877 (right)

Here is the DbFiddle playground to play with the above query.

That's it in this te(a)ch note, more might come in as I walk forward along this GraphQL path.

Reference

• GraphQL Scalars - specification
• GraphQL Custom Scalar Specification
• GraphQL DateTime Scalar type
• IBM on Custom Scalars

• Scalars - GraphQL Java
• GraphQL Java Extended Scalars
• Why Instant is not included in extended scalar types

• Java OffsetDateTime
• Java time package

• Handling DST switch

Enums - all the way to persistence . . .

In any application there would be a need for pre-defined ordered set of constants. Enum is a data type good for such cases. Java has enum since 1.5. Databases do support enum type and PostgreSQL has a special support for this since release 8.3. JPA and Spring Data is a good match to use in modern Java applications for persistence, especially in Spring Boot applications. 

Environment: Java 17, Spring Boot 2.6,7 on macOS Catalina 10.15.7

Example Scenario - A persistable entity object in a Spring Boot micro-service application with JPA and PostgreSQL DB.

DDL Script

-- create enum type genders CREATE TYPE genders AS ENUM( 'MALE', 'FEMALE' ); -- create people table CREATE TABLE people( id VARCHAR(36) PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender genders NOT NULL ); -- Unique Constraints ALTER TABLE people ADD CONSTRAINT people_fname_lname_uk UNIQUE (first_name, last_name);

DB Fiddle

Maven dependencies: pom.xml

... <dependencyManagement> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.6.3</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <dependency> <groupId>com.vladmihalcea</groupId> <artifactId>hibernate-types-55</artifactId> <version>2.16.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.20</version> <scope>provided</scope> </dependency> ... </dependencies> ...

Enum: Gender.java

import lombok.AllArgsConstructor; @AllArgsConstructor public enum Gender { MALE("Male"), FEMALE("Female"); String genderName; }

Domain Object: Person.java

import com.vladmihalcea.hibernate.type.basic.PostgreSQLEnumType; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import org.hibernate.annotations.Type; import org.hibernate.annotations.TypeDef; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.EnumType; import javax.persistence.Enumerated; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import javax.persistence.UniqueConstraint; import javax.validation.constraints.NotNull; import java.util.UUID; @Entity @Table( name = "people", uniqueConstraints = { @UniqueConstraint( columnNames = {"firstName", "lastName"}, name = "people_fname_lname_uk" ) } ) @TypeDef( name = "pgsql_enum", typeClass = PostgreSQLEnumType.class ) @Data @Builder @NoArgsConstructor @AllArgsConstructor public class Person { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(length = 36, nullable = false, updatable = false) @Type(type="org.hibernate.type.UUIDCharType") private UUID id; @NotNull String firstName; @NotNull String lastName; @NotNull @Enumerated(EnumType.STRING) @Type(type = "pgsql_enum") Gender gender; }

JPA Repository: PersonRepository.java

import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import java.util.UUID; public interface PersonRepository extends JpaRepository<Person, UUID> { Person findByFirstNameAndLastNameAndGender(String firstName, String lastName, Gender gender); @Query(value = "SELECT * FROM people WHERE first_name = :firstName AND last_name = :lastName AND gender = CAST(:#{#gender.name()} as genders)", nativeQuery = true) Person findByFirstNameAndLastNameAndGenderNQ(String firstName, String lastName, Gender gender); }

JPA Repository Test: PersonRepositoryIT.java

import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase; import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest; import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager; import org.springframework.test.context.ActiveProfiles; import org.springframework.test.context.junit4.SpringRunner; import java.util.List; import static org.assertj.core.api.Assertions.assertThat; @ActiveProfiles("test") @RunWith(SpringRunner.class) @DataJpaTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) public class PersonRepositoryIT { @Autowired TestEntityManager testEntityManager; @Autowired PersonRepository personRepository; @Test public void findBy_returns_expected() { // given: data in DB List.of( Person.builder().firstName("Giri").lastName("Potte").gender(Gender.MALE), Person.builder().firstName("Boo").lastName("Potte").gender(Gender.MALE) ).forEach(testEntityManager::persist); testEntityManager.flush(); // expect: assertThat( personRepository.findByFirstNameAndLastNameAndGender("Giri", "Potte", Gender.MALE) ).isNotNull() .extracting("firstName", "lastName", "gender") .isEqualTo(List.of("Giri", "Potte", Gender.MALE)); // expect: assertThat( personRepository.findByFirstNameAndLastNameAndGenderNQ("Boo", "Potte", Gender.MALE) ).isNotNull() .extracting("firstName", "lastName", "gender") .isEqualTo(List.of("Boo", "Potte", Gender.MALE)); } }

TIP

From Database side, sometimes, it's handy to have some SQLs dealing with enum type to create, drop, list values, add a new value, modify existing value etc.

I was just fiddling with PostgreSQL enum type and the following is the link:

PostgreSQL - enum - DB Fiddle 

PostgreSQL - Find Max - DB Fiddle

References

• Persisting Enums in JPA
• Java enums, JPA, PostgreSQL enums - make them work together - Stackoverflow 
• Derived method queries in Spring Data JPA Repositories
• Hibernate Types
• SpEL support in Spring Data JPA @Quey Definitions
• Spring Data JSP @Query documentation

PostgreSQL - JSON - Gotchas . . .

These days, JSON is widely used for data-interchange mainly because it's light-weight, simple and readable. Many programming languages have a very good support for JSON, either a built-in support or through libraries.

Databases also added support for JSON in recent years. PostgreSQL is no exception in this space. However, each Database adds its own set of JSON functions and specific syntax for working with JSON data stored in its database. There is no standard and one has to become familiar with the database specific syntax and function to save and retrieve JSON data in and out of database. This post mainly focuses on gotchas dealing with JSON in PostgreSQL Database.

PostgreSQL database Version:12.x

Gotcha-1: Updating JSON data

PostgreSQL documentation lists all JSON functions. However, updating JSON data has a Gotcha.  It only works if the JSON column has JSON data. If the JSON column is NULL, then the function jsonb_set() doesn't work. It's bit frustrating as it doesn't fail with an error either. It misleads by returning the number of records updated.

Click on the DEMO to check it out.

Given below is the SQL from the above DEMO to try out:

-- check version SELECT version(); -- create test table CREATE TABLE test_json ( id varchar(36) NOT NULL, profile json NULL, -- age: generated column, value derived from profile json age smallint GENERATED ALWAYS AS ((profile ->> 'age')::smallint) STORED, CONSTRAINT pk_giri_test PRIMARY KEY (id) ); SELECT * FROM test_json; -- insert a record INSERT INTO test_json (id, profile) VALUES('2eab2d99-167d-41b7-8227-d89ae45d3801', '{"fname":"Giri", "lname":"Pottepalem", "age":30}'); -- check data SELECT * FROM test_json; -- insert another record with no profile INSERT INTO test_json (id) VALUES('2eab2d99-167d-41b7-8227-d89ae45d3802'); -- check data SELECT * FROM test_json; -- try to update profile using json_set UPDATE test_json SET profile = jsonb_set(profile::jsonb, '{fname}', '"boo"'::jsonb) WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that previous update did not work though it returned 1 rows affected SELECT * FROM test_json; -- lets update using regular UPDATE test_json SET profile = '{"fname":"boo", "lname":"pottepalem"}'::jsonb WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that previous update actually updated profile SELECT * FROM test_json; -- update one property using jsonb_set UPDATE test_json SET profile = jsonb_set(profile::jsonb, '{age}', '15'::jsonb) WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that jsonb_set now updated profile SELECT * FROM test_json; -- update multiple properties using jsonb_set UPDATE test_json SET profile = jsonb_set( jsonb_set( jsonb_set( profile::jsonb, '{age}', '18'::jsonb )::jsonb, '{fname}', '"Bhuvan"'::jsonb )::jsonb, '{lname}', '"Pottepalem"'::jsonb ) WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that jsonb_set now updated profile with multiple properties SELECT * FROM test_json; -- update json by concatening with ||, properties that exists get updated, properties that don't exist get added UPDATE test_json SET profile = profile::jsonb || '{"fname" : "boo", "lname" : "potte", "address" : "dummy address"}' WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that existing properties got updated and new properties got added SELECT * FROM test_json; -- remove address property from json UPDATE test_json SET profile = profile::jsonb - 'address' WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that address property is removed from the json SELECT * FROM test_json; -- update json by concatening with ||, properties that exists get updated, properties that do not exist get added, integer age UPDATE test_json SET profile = profile::jsonb || '{"fname" : "Bhuvan"}' || '{"lname" : "Pottepalem"}' || '{"age" : 19}' || '{"address" : "dummy address"}' WHERE id = '2eab2d99-167d-41b7-8227-d89ae45d3802'; -- check data, notice that existing properties got updated and new properties got added, including generated column age SELECT * FROM test_json; -- append another column data to a JSON column SELECT profile :: jsonb || jsonb_build_object('id', id) as profile_json_with_id_added FROM test_json;

References

• PostgreSQL JSON Functions
• PostgreSQL JSON Cheatsheet