ORM is Bad, But is There an Alternative?

It’s a well-known fact, that although the database performance is great, and each query is executed in milliseconds, the overall application response time may be slow, making the users wait for a response for extended periods of time. The problem is not the database, but the way the application developers communicate with the database - using Object-Relational Mappers (ORM).

The only way to change this behavior is to provide application developers with a tool, which is as easy to use, as an ORM, but which will allow escaping the common ORM pitfalls. That’s why we developed NORM – No-ORM Framework. To facilitate the adoption of the No-ORM approach, we developed a set of generators of user-defined types and functions. The input data is presented in a form of annotated JSON schema, which makes them easy to use by both database and application developers.

1. ORM is Bad - But is There an Alternative? Henrietta Dombrovskaya Database Architect DRW (YOUR LOGO)

2. Hettie Dombrovskaya ( She/Her ) Database Architect DRW Working with databases for over 40 years (people do not live that long!) I love working with app developers! Local organizer of Chicago PostgreSQL User Group Author of PostgreSQL Query Optimization There is no “work-life balance” - it’s called “work-life integration”!

3. ORM Quizz What is ORM? Object Relational Mapper Why developers use ORM? To abstract from database specifics Why ORM is good? Rapid development Why ORM is bad? Inefficient data access patterns

4. Application Model Database Model ORM Flat Tables JDBC (Object-Relational) (Object-Oriented) 2023 hDBn

5. The result: World Wide Wait!

6. Why Waiting Is Bad? Amazon: 0.1 sec increase response time - 1% sales loss. 50 % visitors abandon the site, which is not loaded within 3 sec 79% visitors will never return again

7. Introducing NORM The goal of is to reduce the number of round trips

8. NORM – No ORM Transfer Mapping Mapping Database Model (Object-Relational) JDBC Tex t Text Transfer model (JSON) Transfer Application Model (Object-Oriented)

9. CONTRACT Both sides (an application and a database) convert internal representations into complex hierarchical object Contract establishes object structure implemented on both sides Now, for any application endpoint it takes one database call to transfer data to and from database App DB

10. Are You Kidding Me?!

11. We focused on: Formalizing contracts (mapping) Generating all database-level objects: Types Search functions (reads) DML functions (writes)

12. http://github.com/hettie-d/NORM

13. DB Schema

14. Hierarchy

15. Transfer Model: JSON Schema " booking": { "type": "object", "properties": { "booking_id": { "type": "number" }, "passengers": { "type": "array", "items": { "$ref": "#/definitions/passenger" } }, "booking_legs": { "type": "array", "items": { "$ref": "#/definitions/booking_leg" } }, "booking_reference": { "type": "string" } } }

16. Transfer Object in JSON Format

17. Generated UDT create type norm.bh_passenger_record as ( last_name text, account_id int4, first_name text, passenger_id int4, passenger_no int4 ); create type norm.bh_flight_record as ( flight_no text, arrival_city text, departure_city text, scheduled_arrival timestamptz, scheduled_departure timestamptz, arrival_airport_code bpchar, arrival_airport_name text, departure_airport_code bpchar, departure_airport_name text ); create type norm.bh_booking_leg_record as ( flight_id int4, flight norm.bh_flight_record, leg_num int4, booking_leg_id int4 ); create type norm.bh_booking_record as ( booking_id int8, passengers norm.bh_passenger_record[], booking_legs norm.bh_booking_leg_record[], booking_reference text );

18. Generated DML Functions Insert booking: { "passengers": [ { "last_name": "Jones", "account_id": 238648, "first_name": "Lucy" } ], "booking_legs": [ { "leg_num": 1, "flight_id": 558238, }, { "leg_num": 2, "flight_id": 563410 } ], "booking_reference": "IYZI42" } Delete booking: { "booking_id": 556470, "cmd": "DELETE" }

19. Generated DML functions - components update { "booking_id": 3974917, "passengers": [ { "cmd": "DELETE", "passenger_id": 11479596 }, { "last_name": "SCOTT", "first_name": "MILES", "passenger_id": 11479599 } ], "booking_legs": [ { "flight_id": 432724, "booking_leg_id": 11453272 }, { "flight_id": 427273, "booking_leg_id": 11453273 } ] }

20. MongoDB-style Search { "booking_hierarchy":{ "departure_airport_code":"ORD", "arrival_city":{"$like":"NEW%"}, "last_name":"Smith"} } SQL: booking_id IN ( select booking_id from postgres_air.booking_leg where flight_id IN ( select flight_id from postgres_air.flight where arrival_airport IN ( select airport_code from postgres_air.airport where city LIKE ('NEW%'::text) ) AND departure_airport = ('ORD'::bpchar) ) ) AND booking_id IN ( select booking_id from postgres_air.passenger where last_name = (Smith::text) )

21. Why not storing JSON? Single hierarchy The search is slow Storage overhead No strong type check

22. Additional Considerations How this is different from other ORMs? “Thinking sets” Mapping complex objects Doing DB work inside DB On the application side: Details may vary (no need for converting to text, etc.) ActiveRecord requires additional work, because it reads the DB catalog directly

23. Documented Tested Ready to use

24. Hettie Dombrovskaya [email protected] @HettieDombr [email protected] Thank you! Let’s connect.