Re: Are stored procedures/triggers common in your industry
Agree on the lock in comment, however, can't we say that of anything one is dependent on in the tech stack, whether that be at the java vs javascript vs python, or now aws vs azure vs gcp? Have always wondered that lock in concern seems to be only mentioned in light of dbs, but not any other piece of the tech stack. On Wed, Apr 20, 2022 at 3:31 PM Ravi Krishna wrote: > >I've really only ever worked in web development. 90+% of web > >developers regard doing anything at all clever in the database with > suspicion. > > One common argument they use is that if you write your business logic in > stored procedure, you are locked to that database since stored procedure > languages are pretty much vendor locked. > > TBH when one sees tens of thousands of Oracle PL/SQL code, there is some > truth in this. >
Re: Are stored procedures/triggers common in your industry
Agree with the comment on python, et al. I meant lock in within the context of Oracle PL/SQL. Actually, the point of Postgres support for all the languages you mentioned is interesting in that such mitigates the lockin argument if the DB used is. Postgres . Another reason to use Postgres, among all the other reasons we know. Architecturally speaking, using stored procs is a an elegant solution that provides proper abstraction for the Data Layer API, effectively separating the data access and security layers (stored proc design considerations) from the data model and storage considerations. This API type of layer provided by procs is more closely aligned with how one thinks about microservices. Secondly, it will generally be runtime faster as it avoids the critical network round trips that take up those precious milliseconds. On Wed, Apr 20, 2022 at 3:54 PM Guyren Howe wrote: > On Apr 20, 2022, at 13:43 , Alex Aquino wrote: > > > Agree on the lock in comment, however, can't we say that of anything one > is dependent on in the tech stack, whether that be at the java vs > javascript vs python, or now aws vs azure vs gcp? > > Have always wondered that lock in concern seems to be only mentioned in > light of dbs, but not any other piece of the tech stack. > > On Wed, Apr 20, 2022 at 3:31 PM Ravi Krishna > wrote: > >> >I've really only ever worked in web development. 90+% of web >> >developers regard doing anything at all clever in the database with >> suspicion. >> >> One common argument they use is that if you write your business logic in >> stored procedure, you are locked to that database since stored procedure >> languages are pretty much vendor locked. >> >> TBH when one sees tens of thousands of Oracle PL/SQL code, there is some >> truth in this. >> > You can write your stored procedures and triggers in: > - python > - perl > - Java > - R > - Javascrpt > - Rust > - C > - … others (scheme, …) > > How is this lock-in, again? >
Re: Are stored procedures/triggers common in your industry
You mentioned testing, and reminds me of another benefit. Way faster, more reliable, cheaper to test on the DB side. Testing logic in SPs or SQL is much easier, especially when testing requires a sequence of calls for a use case. It is easier because of the DBs support for transactions. With transactions and state management built into the DB, a testing process can always revert to a reliable starting point and end point, thereby facilitating more dependable, automated test harnesses. The alternative done mostly now is testing via UIs or APIs where there is no inherent transaction management, so a lot of work goes into preparing the test bed to be a known state and introspecting the results to verify. This is usually done with some mix of manual and automated processes. On Thu, Apr 21, 2022 at 12:31 AM raf wrote: > On Wed, Apr 20, 2022 at 12:18:23PM -0700, Guyren Howe > wrote: > > > I’ve really only ever worked in web development. 90+% of web > > developers regard doing anything at all clever in the database with > > suspicion. > > > > I’m considering working on a book about implementing business logic in > > Postgres, and I’m curious about how common that actually is. > > I'm used to putting all business logic in the database > (after choosing a great FLOSS database that you'll > never want to migrate away from - like Postgres). And > I've never regretted it (in decades of doing it). > > One of the main reasons is speed. I once had a job > where a program selected data out of a database, > dragged it over a network, effectively grouped it into > summaries, sent the summaries back over the network, > and inserted them back into the database one at a > time(!). Replacing it with a stored procedure changed > it from taking 2-3 hours to 2 minutes. And that was a > place that already made heavy use of stored procedures, > so I don't know what went wrong there. The point is > that whenever a lot of data activity is needed, it's > much faster when it's done where the data lives. > > The other main reason is security. The database can > provide an effective "firewall" between the data and > the client. I never liked the idea of trusting > arbitrary SQL sent from the client. It means you have > to trust every single client application and every > single user (even the ones with good intentions that > produce bad queries in some reporting software and > throwing it at the database and bringing it to its > knees) and every single developer (who might not know > SQL and relies on ORMs that trick them into thinking > they don't need to). But when the clients are only > permitted to execute security defining stored > procedures that have been loaded by the privileged > database owner, you know exactly what code can run > inside the database. SQL injections become impossible > no matter how many bugs and flaws there are in the > client software or its supply chain. > > Another good but less critical reason is that when you > use multiple languages, or you migrate partially or > completely from the old cool language to the new cool > language, you don't have to replicate the business > logic in the new language, and you can eliminate the > risk of introducing bugs into mission critical code. > The existing business logic and its test suite can stay > stable while all the bells and whistles on the outside > change however they like. > > There are other nice benefits but that's enough. > > I think it's safe to disregard the suspicions of the > 90+% of web developers you mentioned. The requirements > that they have for a database might be quite > undemanding. Most individual actions on a website > probably don't result in a lot of data activity (or > rather activity that involves a lot of data). The CRUD > model is probably all they need. So their views are > understandable, but they are based on limited > requirements. However, I still use stored procedures > for everything on websites for security reasons. > > Everyone's mileage varies. We're all in different places. > > cheers, > raf > > > >
Re: PostgreSQL on Amazon RDS
Jeremy Schneider - Thanks for that psqlrc file. Pretty informative. :-) On Wed, May 8, 2019 at 11:55 AM Jeremy Schneider wrote: > On 5/6/19 23:27, Rashmi V Bharadwaj wrote: > > Is there a SQL query or a database parameter setting that I can use from > > an external application to determine if the PostgreSQL database is on > > cloud (like on Amazon RDS or IBM cloud) or on a non-cloud on-prem > > environment? > > Here's my psqlrc file which has pretty solid logic for detecting between > Community PostgreSQL, RDS PostgreSQL and Aurora PostgreSQL. Note that > it just assumes "community/oss" as a default if it doesn't detect the > other two. Should be easy enough to add detection of other hosted > environments into the query with the "priority" column (as long as > there's a reliable way to detect). > > https://gist.github.com/ardentperf/52bd418e44b1be26d7b63af21331cece > > This psqlrc also detects read-write versus read-only (e.g. hot standby), > and the usual username/database/superuser info and builds everything > into the prompt in a way that suits me. > > -Jeremy > > -- > http://about.me/jeremy_schneider > > >
