Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
on January 24, 2023
There’s a lot of hype, concern, and fear regarding generative AI lately. Tools like ChatGPT are so good at generating groups of words that it feels like magic– however, generative AI doesn’t have the ability to understand or verify the language it generates. For example, it’s been used to write news stories for CNET – but sometimes included facts that are just plain wrong.
One positive use case of a generative AI tool like ChatGPT is to ask it to make a case for different perspectives than your own, and use this as a starting point to broaden your understanding – with the knowledge that we need to verify everything ChatGPT tells us. As an example, let’s chat to ChatGPT about Object Relational Mapping tools (ORMs).
Prompt: give arguments for and against using an ORM to generate database code.
As someone who generally sits closer to database code than to application code, I’ve often wondered, “Why do developers love ORMs so much?” I get the basics: it can make getting started a lot faster. But since I haven’t actually used ORMs hands-on, there’s plenty I can learn.
To start out, I asked ChatGPT to argue for and against using an ORM, specifically in the context of generating database code.
ChatGPT responded:
Arguments for using an ORM to generate database code:
Abstraction: ORMs provide an abstraction layer between the application and the database, making it easier for developers to interact with the database without needing to know the underlying SQL.
Productivity: ORMs can automatically generate common database operations, such as CRUD operations, which can save time and increase productivity for developers.
Portability: ORMs can allow developers to work with multiple databases by providing a consistent interface, making it easier to switch between different databases or to change the underlying database technology.
Security: ORMs can help prevent SQL injection attacks by automatically parameterizing queries and escaping user input.
Maintenance: ORMs can automatically handle database schema changes, making it easier to maintain the application over time.
Arguments against using an ORM to generate database code:
Performance: ORMs can add an additional layer of overhead and may negatively impact performance, especially for complex or large datasets.
Complexity: ORMs can add complexity to the codebase, making it harder to troubleshoot and maintain the application.
Flexibility: ORMs may not provide the same level of flexibility as writing raw SQL, making it harder to optimize performance or take advantage of specific features of a particular database.
Learning curve: ORMs can have a steep learning curve for developers who are not familiar with them, making it harder for new developers to understand the codebase.
Obscurity: ORMs can obscure the underlying database operations, making it harder for developers to understand how the application is interacting with the database.
I’m more familiar with the arguments against using ORMs to generate database code, but I was still pretty impressed with this as a general summary of two different perspectives.
Prompt: what are the challenges with combining an ORM with custom stored procedures?
Digging in a little, one thing that can be frustrating is hitting a performance problem when an ORM is generating a bad query. One can often rewrite the query and it will perform much better, but then you hit the question: how do you tell the ORM to use different syntax? One option is to combine the ORM with procedures.
A lot of folks say this isn’t always doable and introduces problems. I asked ChatGPT what those problems might be.
ChatGPT responded:
Integration: Integrating ORMs with custom stored procedures can be challenging, as the ORM may not be designed to work seamlessly with stored procedures, and the developer may need to write additional code to handle the integration.
Mapping: ORMs rely on a mapping between the application’s data model and the database schema, and stored procedures can make this mapping more complex, as the ORM may not be able to automatically map the output of a stored procedure to the application’s data model.
Performance: Stored procedures can provide better performance than dynamically generated SQL in some cases, but executing stored procedures through an ORM can add an additional layer of overhead and may negatively impact performance.
Debugging: Debugging issues that arise from the integration of ORMs and stored procedures can be challenging, as the ORM may not provide detailed information about the execution of stored procedures.
Maintenance: Stored procedures can make it harder to maintain the application over time, as changes to the stored procedures may require changes to the ORM’s codebase, and vice versa.
Portability: ORMs allow to work with multiple databases, but if your application relies on a lot of stored procedures, it may limit the ability to switch between different databases or to change the underlying database technology without rewriting the stored procedures.
Prompt: Are there tools or processes that can make it easier to combine ORMs with other programming models for database code?
Confession: I don’t think I’ve ever asked a human this. I probably should have, but for various reasons I never had time to fit it into a conversation. So I asked a language-generating web page.
ChatGPT responded:
Code Generation: Some ORMs include code generation tools that can automatically generate code for stored procedures, views, and other database objects. This can make it easier to integrate the ORM with existing database code.
Data Access Layers: Creating a data access layer (DAL) that sits between the ORM and the application code can make it easier to handle the integration of the ORM with other programming models. The DAL can handle the execution of stored procedures and other custom database code, and provide a consistent interface for the application code to interact with the ORM.
Stored Procedures Wrappers: Some ORM frameworks provide wrappers that allow developers to call stored procedures as if they were regular functions, this way the ORM can handle the integration and provide a consistent interface for the application.
ORM-agnostic libraries: Some libraries can be used to provide a consistent interface for working with databases, regardless of the ORM in use. This can make it easier to switch between ORMs or to work with multiple ORMs in the same codebase.
Third-party libraries: There are third-party libraries that can provide additional functionality to the ORM like stored procedures support, this way the ORM can provide the abstraction layer and the third-party library can provide the stored procedures support.
It’s important to note that these tools and processes can vary based on the specific ORM and programming models you are using, and some may be more effective than others depending on the requirements of your application.
Hmmm, it seems like there’s no easy answers, but these pointers are interesting things that I could explore in a real world problem solving situation.
Prompt: What are the major improvements that have happened in ORM tools over time, specifically related to database code?
Lastly, I considered that technology, including ORMs change fast, and improvements are often made. I asked ChatGPT how ORM tooling has improved over time when it comes to generating database code.
ChatGPT responded:
Over time, ORM tools have undergone various improvements related to database code, some of the major improvements include:
Performance: ORMs have become more efficient at generating and executing database code, and many now include features such as caching and lazy loading to improve performance.
Support for multiple databases: ORMs have become more flexible and now support a wide range of databases, this allows developers to work with multiple databases using the same ORM.
Support for stored procedures and views: ORMs have improved their support for stored procedures and views, allowing developers to use these database objects in a more seamless way.
Better support for complex queries: ORMs have improved their ability to handle complex queries and joins, allowing developers to write more sophisticated queries with the ORM.
Improved support for migrations: ORMs have improved their support for database migrations, allowing developers to easily make changes to the database schema over time.
Better support for NoSQL databases: ORMs have improved their support for NoSQL databases, allowing developers to use ORMs to work with NoSQL databases in a similar way to how they would work with traditional relational databases.
More advanced features: ORMs have added more advanced features like multi-tenancy, sharding, and other features that are specific to certain databases.
Better integration with other technologies: ORMs have improved their integration with other technologies like caching systems, search engines, and queues, allowing developers to leverage these technologies in a more seamless way.
Better support for testing: ORMs have improved their support for testing, allowing developers to create test data and perform testing in a more efficient way.
It’s important to note that these improvements have happened over time, and are not all present in all ORM tools, and some ORM may have more or less improvements in certain areas.
We need to check our facts and check our blind spots
Tools like ChatGPT (and tools like ORMs) don’t understand things like humans do. They don’t imagine things, they don’t lie, they just … make stuff up. Sometimes it can go very badly.
We need to use them carefully, and we need to work to improve them. But maybe, if we use them carefully, and if we focus on learning from humans, checking our facts, and looking for our blind spots, they can help us learn and grow.