My 14-Year Journey Away from ORMs - a Series of Insights Leading to Creation of a SQL-First Code Generator
fazalmajid
I learned very early in my career to distrust ORMs, when a project using Sybase and a Sybase-approved ORM malfunctioned spectacularly in production because of adverse interactions between the ORM and Sybase's page-level (not row-level) locking. It took 6 painful months seared into my memory to fix the mess.
While OP's take that you should treat the database as a microservice is sound, the key point is the database is responsble for the integrity of the data. Thus I would go one step further and say the queries are an implmentation detail of the interface, which should be implemented along functional line, i.e. using stored procedures.
This also has security benefits: instead of giving the app server full access to the users table and its hashed passwords, you could create a login() function that takes login and password. In PostgreSQL at least you can define stored procedures (functions, really) SECURITY DEFINER, which like setuid gives the function rights the caller doesn't have. The login function would be able to check passwords but if the app server were compromised, it would not be able to enumerate users or extract password hashes for GPU cracking, but at most try dictionary attacks on passwords (and the login function could of course be stateful and reject after N failed attempts).
Sadly stored procedures are not well known among developers. Part of it is a dearth of tooling like debuggers, profilers and code coverage analysis tools, but there is also a failure of education.
nikita-volkov
I agree with your take regarding stored procedures. They can be used to provide a clear API for the database. With applications being prohibited to access the tables directly they can also be used to make the migrations backward-compatible. If pushed further they can also be used to implement a form of CQRS. Even further, the business logic can be moved to the database as well. Various extensions can be used to program in the language of your liking in the DB (Python, Haskell). And with tools like PostgREST it can be turned into a REST API. No need for an application server at all. But as it always is with engineering there are trade-offs involved.
Stored procedures become a problem when you get to scaling horizontally. They break the shared-nothing architecture required for sharding. They increase write lock contention, are hard to parallelize, and complicate deployment. It probably is more of a tooling problem than a conceptual one, but AFAIK there is no solution to it today.
If you still have an application server that integrates with the database, you still have to deal with the integration layer within your app. It must be validated to call to procedures that exist and follow their contract. You will likely be happy to have that integration code be type-safe and synced with the DB automatically. That's where instruments like pGenie will still be useful.
gerikson
I think stored pocedures failed to continue to attrace mindshare for multiple reasons:
developers moved away from complex opaque database systems like Oracle
SPs used to have to be written in outdated language dialects (although Oracle did bring in Java as a language). This increased the communication distance between "front- and middle-end developers" and the "DB gurus".
they were "embedded" in the DB in a way that was inconvenient to integrate into source control and testing
they could have weird side effects (triggers were a big cause of this)
That said MS SQL, having a decent developer-centric front-end, was better at this than Oracle, in my experience.
vrolfs
The post feels unfortunately very AI-written to me. If it is not, I apologize. But the paragraph "Why This Matters Now" sounds like exactly how Chat GPT talks.
nikita-volkov
Thanks for sharing that! I guess in this age the authors need to put in an extra effort to sound authentic. I do use LLMs to help me polish the content but it's only formal. The experience and the ideas expressed are still mine.
tomhukins
Please read this community's self-promotion guidelines and ask questions if you do not understand them.
mrnosuch
Yeah, seems a wee bit over the 25% line, doesn’t it?
carlana
This is why I like sqlc with Go.
fazalmajid
I learned very early in my career to distrust ORMs, when a project using Sybase and a Sybase-approved ORM malfunctioned spectacularly in production because of adverse interactions between the ORM and Sybase's page-level (not row-level) locking. It took 6 painful months seared into my memory to fix the mess.
While OP's take that you should treat the database as a microservice is sound, the key point is the database is responsble for the integrity of the data. Thus I would go one step further and say the queries are an implmentation detail of the interface, which should be implemented along functional line, i.e. using stored procedures.
This also has security benefits: instead of giving the app server full access to the users table and its hashed passwords, you could create a login() function that takes login and password. In PostgreSQL at least you can define stored procedures (functions, really) SECURITY DEFINER, which like setuid gives the function rights the caller doesn't have. The login function would be able to check passwords but if the app server were compromised, it would not be able to enumerate users or extract password hashes for GPU cracking, but at most try dictionary attacks on passwords (and the login function could of course be stateful and reject after N failed attempts).
Sadly stored procedures are not well known among developers. Part of it is a dearth of tooling like debuggers, profilers and code coverage analysis tools, but there is also a failure of education.
nikita-volkov
I agree with your take regarding stored procedures. They can be used to provide a clear API for the database. With applications being prohibited to access the tables directly they can also be used to make the migrations backward-compatible. If pushed further they can also be used to implement a form of CQRS. Even further, the business logic can be moved to the database as well. Various extensions can be used to program in the language of your liking in the DB (Python, Haskell). And with tools like PostgREST it can be turned into a REST API. No need for an application server at all. But as it always is with engineering there are trade-offs involved.
Stored procedures become a problem when you get to scaling horizontally. They break the shared-nothing architecture required for sharding. They increase write lock contention, are hard to parallelize, and complicate deployment. It probably is more of a tooling problem than a conceptual one, but AFAIK there is no solution to it today.
If you still have an application server that integrates with the database, you still have to deal with the integration layer within your app. It must be validated to call to procedures that exist and follow their contract. You will likely be happy to have that integration code be type-safe and synced with the DB automatically. That's where instruments like pGenie will still be useful.
gerikson
I think stored pocedures failed to continue to attrace mindshare for multiple reasons:
developers moved away from complex opaque database systems like Oracle
SPs used to have to be written in outdated language dialects (although Oracle did bring in Java as a language). This increased the communication distance between "front- and middle-end developers" and the "DB gurus".
they were "embedded" in the DB in a way that was inconvenient to integrate into source control and testing
they could have weird side effects (triggers were a big cause of this)
That said MS SQL, having a decent developer-centric front-end, was better at this than Oracle, in my experience.
vrolfs
The post feels unfortunately very AI-written to me. If it is not, I apologize. But the paragraph "Why This Matters Now" sounds like exactly how Chat GPT talks.
nikita-volkov
Thanks for sharing that! I guess in this age the authors need to put in an extra effort to sound authentic. I do use LLMs to help me polish the content but it's only formal. The experience and the ideas expressed are still mine.
tomhukins
Please read this community's self-promotion guidelines and ask questions if you do not understand them.
mrnosuch
Yeah, seems a wee bit over the 25% line, doesn’t it?
I learned very early in my career to distrust ORMs, when a project using Sybase and a Sybase-approved ORM malfunctioned spectacularly in production because of adverse interactions between the ORM and Sybase's page-level (not row-level) locking. It took 6 painful months seared into my memory to fix the mess.
While OP's take that you should treat the database as a microservice is sound, the key point is the database is responsble for the integrity of the data. Thus I would go one step further and say the queries are an implmentation detail of the interface, which should be implemented along functional line, i.e. using stored procedures.
This also has security benefits: instead of giving the app server full access to the users table and its hashed passwords, you could create a login() function that takes login and password. In PostgreSQL at least you can define stored procedures (functions, really) SECURITY DEFINER, which like setuid gives the function rights the caller doesn't have. The login function would be able to check passwords but if the app server were compromised, it would not be able to enumerate users or extract password hashes for GPU cracking, but at most try dictionary attacks on passwords (and the login function could of course be stateful and reject after N failed attempts).
Sadly stored procedures are not well known among developers. Part of it is a dearth of tooling like debuggers, profilers and code coverage analysis tools, but there is also a failure of education.
I agree with your take regarding stored procedures. They can be used to provide a clear API for the database. With applications being prohibited to access the tables directly they can also be used to make the migrations backward-compatible. If pushed further they can also be used to implement a form of CQRS. Even further, the business logic can be moved to the database as well. Various extensions can be used to program in the language of your liking in the DB (Python, Haskell). And with tools like PostgREST it can be turned into a REST API. No need for an application server at all. But as it always is with engineering there are trade-offs involved.
Stored procedures become a problem when you get to scaling horizontally. They break the shared-nothing architecture required for sharding. They increase write lock contention, are hard to parallelize, and complicate deployment. It probably is more of a tooling problem than a conceptual one, but AFAIK there is no solution to it today.
If you still have an application server that integrates with the database, you still have to deal with the integration layer within your app. It must be validated to call to procedures that exist and follow their contract. You will likely be happy to have that integration code be type-safe and synced with the DB automatically. That's where instruments like pGenie will still be useful.
I think stored pocedures failed to continue to attrace mindshare for multiple reasons:
That said MS SQL, having a decent developer-centric front-end, was better at this than Oracle, in my experience.
The post feels unfortunately very AI-written to me. If it is not, I apologize. But the paragraph "Why This Matters Now" sounds like exactly how Chat GPT talks.
Thanks for sharing that! I guess in this age the authors need to put in an extra effort to sound authentic. I do use LLMs to help me polish the content but it's only formal. The experience and the ideas expressed are still mine.
Please read this community's self-promotion guidelines and ask questions if you do not understand them.
Yeah, seems a wee bit over the 25% line, doesn’t it?
This is why I like sqlc with Go.
I learned very early in my career to distrust ORMs, when a project using Sybase and a Sybase-approved ORM malfunctioned spectacularly in production because of adverse interactions between the ORM and Sybase's page-level (not row-level) locking. It took 6 painful months seared into my memory to fix the mess.
While OP's take that you should treat the database as a microservice is sound, the key point is the database is responsble for the integrity of the data. Thus I would go one step further and say the queries are an implmentation detail of the interface, which should be implemented along functional line, i.e. using stored procedures.
This also has security benefits: instead of giving the app server full access to the users table and its hashed passwords, you could create a login() function that takes login and password. In PostgreSQL at least you can define stored procedures (functions, really) SECURITY DEFINER, which like setuid gives the function rights the caller doesn't have. The login function would be able to check passwords but if the app server were compromised, it would not be able to enumerate users or extract password hashes for GPU cracking, but at most try dictionary attacks on passwords (and the login function could of course be stateful and reject after N failed attempts).
Sadly stored procedures are not well known among developers. Part of it is a dearth of tooling like debuggers, profilers and code coverage analysis tools, but there is also a failure of education.
I agree with your take regarding stored procedures. They can be used to provide a clear API for the database. With applications being prohibited to access the tables directly they can also be used to make the migrations backward-compatible. If pushed further they can also be used to implement a form of CQRS. Even further, the business logic can be moved to the database as well. Various extensions can be used to program in the language of your liking in the DB (Python, Haskell). And with tools like PostgREST it can be turned into a REST API. No need for an application server at all. But as it always is with engineering there are trade-offs involved.
Stored procedures become a problem when you get to scaling horizontally. They break the shared-nothing architecture required for sharding. They increase write lock contention, are hard to parallelize, and complicate deployment. It probably is more of a tooling problem than a conceptual one, but AFAIK there is no solution to it today.
If you still have an application server that integrates with the database, you still have to deal with the integration layer within your app. It must be validated to call to procedures that exist and follow their contract. You will likely be happy to have that integration code be type-safe and synced with the DB automatically. That's where instruments like pGenie will still be useful.
I think stored pocedures failed to continue to attrace mindshare for multiple reasons:
That said MS SQL, having a decent developer-centric front-end, was better at this than Oracle, in my experience.
The post feels unfortunately very AI-written to me. If it is not, I apologize. But the paragraph "Why This Matters Now" sounds like exactly how Chat GPT talks.
Thanks for sharing that! I guess in this age the authors need to put in an extra effort to sound authentic. I do use LLMs to help me polish the content but it's only formal. The experience and the ideas expressed are still mine.
Please read this community's self-promotion guidelines and ask questions if you do not understand them.
Yeah, seems a wee bit over the 25% line, doesn’t it?
This is why I like sqlc with Go.