8 ms·

The biggest concern that engineering teams seem have with stored procs is maintainability. The consistency guarantees are indeed very attractive, but perhaps no

by wyc 8y ago

The biggest concern that engineering teams seem have with stored procs is maintainability. The consistency guarantees are indeed very attractive, but perhaps not at the expense of keeping business logic in separate places, weak version control/deploy solutions, and a new programming language to the stack. If there were a compiler that could take business logic in the project's programming language and manage mappings to stored procs (reminiscent of LINQ's mapping to queries), maybe we could have the best of both worlds.

stuartaxelowen 8y ago
Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.
- jerrysievert 8y ago
  that's part of the reason I built equinox: https://github.com/jerrysievert/equinox https://github.com/jerrysievert/equinox if you're moving logic into your database, it's very important to be able to test and understand your use cases.
- hinkley 8y ago
  This sort of sentence is uttered by someone who is so far down a hole that all they can see is stars. It’s a big hole that a lot of people are in but it’s the consequence of a nasty trade off. All of this is trivial if your team decides that a shared database for dev work is bad for repeatability and thus bad for scaling the team. You should be able to spool up a local database with good sample data in it. To do that your schema, indexes and triggers would be under version control, and a data dump is somewhere people can get it. Once you have this your CI system runs Postgres locally or in a container, runs the drop create scripts, and then runs your integration and end to end tests on the canned data. In addition to getting a CI solution for next to free you get rid of the concurrent access Wild West and this particularly painful conversation: Why did this break and why didn’t you notice it before you pushed? Oh I saw that problem the other day but I thought someone else was changing data (and not my code being broken). If the data is on your machine and it gets broken, then it is only your machine that could have broken it. You can’t delude yourself into thinking it was someone else mucking around. The problem is either in your code or in your latest pull from master. You are responsible for determining the cause, not me, not the release manager, not QA. You.
  - sp527 8y ago
    Who hurt you?
  - manquer 8y ago
    Not all bugs are logical. Many times CI workflow does not catch poorly written but logically correct queries because sample db has few records in it, emulating large data sets with data patterns like production to get the same kind of query plans is hard.
    - hobs 8y ago
      I would say this is the first objection that is on point in this thread, not that figuring out deployment isn't hard, but the blog posts on how to solve it for your platform are multitudinous. Getting useful data loads to test queries is much more difficult but you have a few options (at least coming from a SQL Server approach): * Query hints to emulate larger sets of data (so you can see what type of IO you would get, of course multiplication is your friend) * Check if your database offers something along the lines of DBCC CLONEDATABASE https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/database-console-... (gets you a copy of statistics from prod without the data) * Building a data masking process so that the data is somewhat representative (in volume, mocking it such that the histogram of values is the same in your database is WAY harder) * Building an isolated load test environment with something like distributed replay https://docs.microsoft.com/en-us/sql/tools/distributed-replay/sql-server-distributed-replay?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/tools/distributed-repla... (record workload, replay, measure, make your change, replay, measure, yes - it is tedious but that's a performance regression test for you)
- halostatue 8y ago
  This is where [sqitch](http://sqitch.org http://sqitch.org) and [pgtap](http://pgtap.org http://pgtap.org) are useful. We aren’t currently using stored procedures, but we have a fairly substantial pgtap set for our schema definitions (where we have a number of complex constraints).
- axelfontaine 8y ago
  Flyway repeatable migrations make this trivial. Keep your stored procs under version control and Flyway will only recreate the ones that changed. See https://flywaydb.org/documentation/migrations#repeatable-migrations https://flywaydb.org/documentation/migrations#repeatable-mig...
zmmmmm 8y ago
It's not the whole answer, but Postgres is rather remarkable for its support of using first class programming languages in stored procs - you can even write them in Java if you want: https://tada.github.io/pljava/ https://tada.github.io/pljava/
- brongondwana 8y ago
  I once embedded our entire Perl business logic (closed source, previous job) inside Postgres with its DB routines changed to the Postgres bridge internal stuff, and creating a single interface where you could message it with an XML blob and get back an XML blob of response. It was actually pretty usable, but the speed benefit wasn't enough to justify such a brittle rube goldberg machine!
btilly 8y ago
That is my biggest concern. My second biggest concern is performance overhead. Sure, they are individually fast. However the one thing that is hardest to scale is the database. Loading the database up front with overhead means that you'll hit that limit sooner rather than later.
- yen223 8y ago
  Agreed. I like thinking of the database as a dumb data store, optimised for data integrity, fast reads and writes, and nothing more.
protomyth 8y ago
> weak version control/deploy solutions, and a new programming language to the stack Are people not using the same version control for stored procedures as their "normal" programs? I thought deployment was pretty much a solved issue with DBAs.
- RussianCow 8y ago
  Based on the experience of co-workers (and my own limited dabbling), you are right that version control is (and should be) used for stored procedures just as it is with application code. The problem is that there isn't a lot of tooling that isn't language- or platform-specific to help maintain these things in the long run. So DBAs tend to write and maintain their own collection of homegrown scripts for deploying changes to the DB (or worse, do it by hand). I'm sure some companies have this figured out, though, so YMMV.
  - nathanaldensr 8y ago
    I certainly figured it out at several jobs: - Scripts are numbered for quick order determination by humans (e.g., 0001-initial-schema.sql, 0002-create-foo-table.sql) - A scripts.json file containing an array of string filenames declares the exact order of scripts, just in case numbers are shared for some reason (say, two branches being merged at the same time with a new script in them) - A custom PowerShell module knows how to read scripts.json and the files and execute the changes - When possible, all scripts are executed transactionally by the CI tool (some commands in some RDBMSes can't be executed in transactions, however) - Optionally, backups/restores may be performed to handle failures
  - combatentropy 8y ago
    I just make files of pure SQL to define the functions, keeping them in Git. I feed them into the psql command-line tool.
  - zzzzzzzza 8y ago
    isn't this what sqitch does, manages deploying the changes to the db? Anyone have experience with sqitch?
hinkley 8y ago
There’s nothing stopping you from putting a script to create all of your triggers under version control. With your table and index creation scripts...
- megy 8y ago
  How easy is it to deploy those changes then?
  - jerrysievert 8y ago
    a lot of languages have migrations available, that help manage the state of the database. in addition, postgres allows for you to ALTER or REPLACE functions and triggers, just like you'd ALTER a table. these in combination make it very easy to manage and deploy.
  - pgwhalen 8y ago
    Not as easy as application code, but easy enough with a good migration tool.
  - ta5676543244 8y ago
    Liquibase is one example of a tool that integrates with your project and can deploy or rollback version controlled scripts. Bit similar to Rails migrations too from memory.
  - magicalhippo 8y ago
    We have the DB schema in a custom XML file, and have written a tool to compare the actual schema with that of the XML file. The tool then applies the necessary changes if they're non-destructive (extending column, adding index etc), or fails and lets the user know what needs to be done if run interactively. By making sure we do as few potentially "destructive" changes to the schema as possible, this tool automatically upgrades our customers database when we release a new version with high reliability. Adding or altering triggers, stored procs and views are considered "non-destructive" in this context, but of course that requires some discipline from us. One aspect of that is that we try to keep the number of triggers to an absolute minimum. Removing triggers and similar is considered "destructive", however we have a way in the XML to explicitly delete the object if needed. The XML file lives in our version control repository alongside the source code, and we have triggers which updates test databases automatically when an update is committed etc. It's a fairly simple idea but has worked quite well for us.
dahdum 8y ago
For a large node app, we wrote most of the business logic in JS and ran with PLV8. All the procedures were called from a single PLV8 library in shared memory, and we created functions stubs so you could still call with straight SQL (mostly used for ETL scripts).
- jerrysievert 8y ago
  can you tell me a little more about the methods you used with plv8?
lostapathy 8y ago
The biggest problem I've had with triggers is that your application tier tells the database to do an update, it says it did it, and then the app shows the user the result. But in the background, the trigger somehow overrode your changes, so the next time the user comes back they see different data than the app just told them was there. Granted, this is arguably the wrong way to use triggers, but once it's become an invasive problem in your codebase it's incredibly hard to deal with. You can't remove the triggers for fear of what still relies on the side effects, and you don't want to query back the data for every update either. It ends up being like the polar opposite of functional programming - side effects everywhere.
- CuriouslyC 8y ago
  In postgres you can handle this using the "returning" syntax for updates; then as long as the trigger runs before rather than after the update you don't have to lie about anything.
  - lostapathy 8y ago
    I'm not really sure that would help in the case we had. Most of our code ran through an ORM, and the ORM assumes the DB either did what it asked, or the query fails. I don't want to have to abandon my ORM because I can't trust my "DBA" to not sabotage my queries.
    - matte_black 8y ago
      This is just a limitation of ORMs. After a certain complexity you really should just be making direct queries to your database and not relying on ORMs.
      - lostapathy 8y ago
        That’s the thing -what the app tier was doing wasn’t to complex for an ORM. Not by a long ahot. The triggers that caused us problems generally came about to cover unrelated dba laziness and needed removed for sanity reasons anyway. I am not arguing there cannot be a good case for triggers, just that when abused they cause a certain level of hell that is not worth it.
brightball 8y ago
The best implementation I've seen of this was actually in a Rails library, where rather than keep the functions and triggers in migrations, it kept them in individual files so they'd be version controlled just like the rest of the code...and then reloaded them with migrations if they had changed. I don't know if the library is maintained anymore, but I need to find it. EDIT: Found it and it's not maintained. Solid approach to the problem though. https://github.com/norman/squirm_rails https://github.com/norman/squirm_rails
- bobwaycott 8y ago
  Funny. I was just having a conversation with my partner about this very issue and this was the approach I was thinking of. I’ve found keeping database functions, triggers, etc. painful as part of standard migrations—finding the most recent version is particularly annoying. They’re the kind of thing it’s best to treat like the rest of a project’s code—version controlled outside of migrations.
  - mozumder 8y ago
    Isn't that what you'd do anyways? How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control? Or are you actually typing in triggers manually in the Postgres command line?
    - paulryanrogers 8y ago
      Not the OP but I did use triggers as suggested in the past. IME the issue is that DDL like triggers are transactional, so when you deploy them executing queries could be using a mix of old and new. Granted, this is an issue for any system comprised of distributed components. Though I think the pain is more acute when it's the source of truth. One way around is downtime or going all in on stored procedures for all work, or at least all writes. The other wrinkle is that the trigger has to be rolled back when reverting code. And some shops are not diligent about maintaining down procedures. There is also the possiblity of rolling back migrations out of the order they were run, so you might not get back to a consistent state.
gumballhead 8y ago
I've often wanted to write a compiler that would accept a schema definition like protobufs or graphql as input and generate the required SQL to update the database if a schema change was committed. It would also be read by the application layer so its model definitions were kept up to date. As you say, there's a tremendous advantage in using triggers to remove data invariants, but then there's also the issue of the schema's state, and it can be prone to error depending on the complexity of the trigger. It's definitely recommended to use SQL's schema inspection to verify and test it. Perhaps even based on schema definitions from protobufs or whatever.
- PeterisP 8y ago
  It's feasible to make such a tool to update the database schema so that it would match a changed definition, but I don't think that it's feasible for it to update the actual database. To do that, you need knowledge about how and why the schema was altered, and schema definitions like protobufs or graphql don't contain that. For example, how would you distinguish between renamed column (where you need to keep the data) and deletion of a column plus adding a different one? There's a reason why migrations are the standard level of abstraction, packaging changes to schema with scripts ensuring consistency of data throughout the whole version history.
- _mischief 8y ago
  I understand the appeal and you're not the only person to want it: I've seen people and companies implement db schema to graphql, db schema to models, and models to db schema. Based on personal experience, this is a bad idea though. You do not want a 1:1 correspondence between your database schema, your backend models, and your graphql schema, because the way you organise information in each layer should be different. Database schema needs to be performant for expected queries. That means de/normalisation decisions; sometimes the same data will be stored in multiple locations. Backend models need to express the domain, because this is where your business logic is. (There's a reason people bitch about ORMs: when you get to complex enough usecases they're not flexible enough in either direction and you need extra models wrapping THAT.) Graphql schema is a view of your backend; sometimes several fields will be fulfilled using the same model, sometimes your model should not have a reflection in graphql schema (because you do not want to expose this data to frontend/the world), and sometimes your graphql schema will be full of deprecated fields because client apps have not been updated (see Facebook policy of never removing anything.)
kbenson 8y ago
> and a new programming language to the stack. This was actually one of the interesting directions Drizzle SQL was exploring back in the day. It was a fork of MySQL that focused on removing old cruft, including lots of older platform support, modernizing the source, and adding modularity in a lot more places, including the use of different languages for SQL programming. Other languages like Perl, Python, Ruby, etc.
- hboon 8y ago
  Oracle DB supports Java stored procedures. I wrote a couple back in the day. I'm not sure we want to go there :P
- krylon 8y ago
  Microsoft SQL Server allows one to write server-side code (triggers, sprocs, functions) in any language that compiles to .Net IL. There are some serious restrictions, though, as most of the .Net Framework becomes unavailable[1]. More recent version also provide for server-side R, but our SQL Server is 2014, so I have not had the option to play with that (plus, I don't know R). [1] I was looking into writing a stored procedure for use with the Service Broker in C#, but the fact that most of the .Net Framework is unavailable (not to speak of third-party libraries) quickly put an end to my investigation.
begriffs 8y ago
> but perhaps not at the expense of keeping business logic in separate places Perhaps the answer is to consolidate business logic in the database with constraints. This also protects data from manipulation outside of an app, like with scripts or ETL. Putting more logic in the db often results in terse error messages though, so the app needs to deal with that and make them nicer for the end user. I wrote about one possible approach here - https://begriffs.com/posts/2017-10-21-sql-domain-integrity.html#improved-error-messages https://begriffs.com/posts/2017-10-21-sql-domain-integrity.h... > weak version control/deploy solutions There are solutions that allow you to store migrations in version control, apply them to different database targets, and run consistency checks. For instance http://sqitch.org/ http://sqitch.org/ but maybe you're familiar with it already and regard it as one of the weak tools. Thought I'd point it out though. > a new programming language to the stack plpgsql is somewhat gnarly, but it is well adapted for the database. You know what it's going to do, compared with managing mappings from some other language. I never tried LINQ though so maybe the mapping would be more pleasant than I'm imagining.
- arkh 8y ago
  > plpgsql is somewhat gnarly, but it is well adapted for the database. You can use python or perl to define your postgres functions. https://www.postgresql.org/docs/10/static/plpython-funcs.html https://www.postgresql.org/docs/10/static/plpython-funcs.htm...
  - rleigh 8y ago
    They are slower (by orders of magnitude) than writing them in C or C++, or even PL/pgSQL. Calling into the interpreter for millions of trigger events can quickly become a major bottleneck. When I originally implemented https://pgxn.org/dist/debversion/ https://pgxn.org/dist/debversion/ for version numbering, I originally implemented it in Perl, then Python. The implementations were clean, but the performance of both was abysmal. After reimplementing it in C++ with a C interface, it runs like greased lightning. While this is a custom datatype with operators implemented as C functions, the same concerns apply to triggers which are invoked on every affected row.
- 8y ago
ashearer 8y ago
One approach that works very well is to keep stored functions in separate .sql files in a directory (I use "fixtures"), and execute them all on each deployment. This should happen after triggering migrations, so that table and column dependencies are guaranteed to be present. The .sql files use CREATE OR REPLACE FUNCTION so that their execution is idempotent. This keeps the stored functions version-controlled along with the source code, and avoids any need to hunt through migration files to find the latest definition. Adding a stored function or modifying its function body just works. The less common operations of deleting a function or modifying its argument list do require an explicit line in a migration file, but those situations are rare (and potentially backward-incompatible, requiring extra caution regardless). One subtlety is that a migration that adds a new table with a trigger should define an empty stub function as the trigger. This avoids duplicating code. The real function body will be loaded from the fixture immediately afterwards.
juliangoldsmith 8y ago
I've worked on a pretty large, very stored proc-heavy app based on SQL Server for several years, and separation of business logic has never been much of an issue. Keeping logic in stored procedures is, in practice, not much different from putting it in a separate function. If your app is data-driven, like the one I work with, most of your business logic can be done in stored procedures. Most any sort of data transform is likely better done in SQL than another language, unless your data is a poor fit for a relational database. We have had issues with version control in the past. I'm currently working on implementing a CI and Git workflow using Jenkins and Red Gate tools, which I expect will make keeping our database in source control trivial. To be fair, though, I'm still working out the last of the kinks, so it may end up being more difficult than I thought. As far as having to learn another language, I've always found that to be an incredibly weak excuse to use JavaScript everywhere. Learning another programming language isn't difficult, and having the right tool for the job is indispensable. Given that JavaScript these days seems to go by "flavor of the week", I don't that think not being able to learn is a problem. I don't find the argument that it's difficult to switch between languages to be convincing, either. Switching between two programming languages that you know isn't any more difficult than switching between a programming language and a spoken one.

by wyc 8y ago

stuartaxelowen 8y ago

Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.

jerrysievert 8y ago
that's part of the reason I built equinox: https://github.com/jerrysievert/equinox https://github.com/jerrysievert/equinox if you're moving logic into your database, it's very important to be able to test and understand your use cases.
hinkley 8y ago
This sort of sentence is uttered by someone who is so far down a hole that all they can see is stars. It’s a big hole that a lot of people are in but it’s the consequence of a nasty trade off. All of this is trivial if your team decides that a shared database for dev work is bad for repeatability and thus bad for scaling the team. You should be able to spool up a local database with good sample data in it. To do that your schema, indexes and triggers would be under version control, and a data dump is somewhere people can get it. Once you have this your CI system runs Postgres locally or in a container, runs the drop create scripts, and then runs your integration and end to end tests on the canned data. In addition to getting a CI solution for next to free you get rid of the concurrent access Wild West and this particularly painful conversation: Why did this break and why didn’t you notice it before you pushed? Oh I saw that problem the other day but I thought someone else was changing data (and not my code being broken). If the data is on your machine and it gets broken, then it is only your machine that could have broken it. You can’t delude yourself into thinking it was someone else mucking around. The problem is either in your code or in your latest pull from master. You are responsible for determining the cause, not me, not the release manager, not QA. You.
- sp527 8y ago
  Who hurt you?
- manquer 8y ago
  Not all bugs are logical. Many times CI workflow does not catch poorly written but logically correct queries because sample db has few records in it, emulating large data sets with data patterns like production to get the same kind of query plans is hard.
  - hobs 8y ago
    I would say this is the first objection that is on point in this thread, not that figuring out deployment isn't hard, but the blog posts on how to solve it for your platform are multitudinous. Getting useful data loads to test queries is much more difficult but you have a few options (at least coming from a SQL Server approach): * Query hints to emulate larger sets of data (so you can see what type of IO you would get, of course multiplication is your friend) * Check if your database offers something along the lines of DBCC CLONEDATABASE https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/database-console-... (gets you a copy of statistics from prod without the data) * Building a data masking process so that the data is somewhat representative (in volume, mocking it such that the histogram of values is the same in your database is WAY harder) * Building an isolated load test environment with something like distributed replay https://docs.microsoft.com/en-us/sql/tools/distributed-replay/sql-server-distributed-replay?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/tools/distributed-repla... (record workload, replay, measure, make your change, replay, measure, yes - it is tedious but that's a performance regression test for you)
halostatue 8y ago
This is where [sqitch](http://sqitch.org http://sqitch.org) and [pgtap](http://pgtap.org http://pgtap.org) are useful. We aren’t currently using stored procedures, but we have a fairly substantial pgtap set for our schema definitions (where we have a number of complex constraints).
axelfontaine 8y ago
Flyway repeatable migrations make this trivial. Keep your stored procs under version control and Flyway will only recreate the ones that changed. See https://flywaydb.org/documentation/migrations#repeatable-migrations https://flywaydb.org/documentation/migrations#repeatable-mig...

zmmmmm 8y ago

It's not the whole answer, but Postgres is rather remarkable for its support of using first class programming languages in stored procs - you can even write them in Java if you want: https://tada.github.io/pljava/ https://tada.github.io/pljava/

brongondwana 8y ago
I once embedded our entire Perl business logic (closed source, previous job) inside Postgres with its DB routines changed to the Postgres bridge internal stuff, and creating a single interface where you could message it with an XML blob and get back an XML blob of response. It was actually pretty usable, but the speed benefit wasn't enough to justify such a brittle rube goldberg machine!

btilly 8y ago

That is my biggest concern. My second biggest concern is performance overhead. Sure, they are individually fast. However the one thing that is hardest to scale is the database. Loading the database up front with overhead means that you'll hit that limit sooner rather than later.

yen223 8y ago
Agreed. I like thinking of the database as a dumb data store, optimised for data integrity, fast reads and writes, and nothing more.

protomyth 8y ago

> weak version control/deploy solutions, and a new programming language to the stack Are people not using the same version control for stored procedures as their "normal" programs? I thought deployment was pretty much a solved issue with DBAs.

RussianCow 8y ago
Based on the experience of co-workers (and my own limited dabbling), you are right that version control is (and should be) used for stored procedures just as it is with application code. The problem is that there isn't a lot of tooling that isn't language- or platform-specific to help maintain these things in the long run. So DBAs tend to write and maintain their own collection of homegrown scripts for deploying changes to the DB (or worse, do it by hand). I'm sure some companies have this figured out, though, so YMMV.
- nathanaldensr 8y ago
  I certainly figured it out at several jobs: - Scripts are numbered for quick order determination by humans (e.g., 0001-initial-schema.sql, 0002-create-foo-table.sql) - A scripts.json file containing an array of string filenames declares the exact order of scripts, just in case numbers are shared for some reason (say, two branches being merged at the same time with a new script in them) - A custom PowerShell module knows how to read scripts.json and the files and execute the changes - When possible, all scripts are executed transactionally by the CI tool (some commands in some RDBMSes can't be executed in transactions, however) - Optionally, backups/restores may be performed to handle failures
- combatentropy 8y ago
  I just make files of pure SQL to define the functions, keeping them in Git. I feed them into the psql command-line tool.
- zzzzzzzza 8y ago
  isn't this what sqitch does, manages deploying the changes to the db? Anyone have experience with sqitch?

hinkley 8y ago

There’s nothing stopping you from putting a script to create all of your triggers under version control. With your table and index creation scripts...

megy 8y ago
How easy is it to deploy those changes then?
- jerrysievert 8y ago
  a lot of languages have migrations available, that help manage the state of the database. in addition, postgres allows for you to ALTER or REPLACE functions and triggers, just like you'd ALTER a table. these in combination make it very easy to manage and deploy.
- pgwhalen 8y ago
  Not as easy as application code, but easy enough with a good migration tool.
- ta5676543244 8y ago
  Liquibase is one example of a tool that integrates with your project and can deploy or rollback version controlled scripts. Bit similar to Rails migrations too from memory.
- magicalhippo 8y ago
  We have the DB schema in a custom XML file, and have written a tool to compare the actual schema with that of the XML file. The tool then applies the necessary changes if they're non-destructive (extending column, adding index etc), or fails and lets the user know what needs to be done if run interactively. By making sure we do as few potentially "destructive" changes to the schema as possible, this tool automatically upgrades our customers database when we release a new version with high reliability. Adding or altering triggers, stored procs and views are considered "non-destructive" in this context, but of course that requires some discipline from us. One aspect of that is that we try to keep the number of triggers to an absolute minimum. Removing triggers and similar is considered "destructive", however we have a way in the XML to explicitly delete the object if needed. The XML file lives in our version control repository alongside the source code, and we have triggers which updates test databases automatically when an update is committed etc. It's a fairly simple idea but has worked quite well for us.

dahdum 8y ago

For a large node app, we wrote most of the business logic in JS and ran with PLV8. All the procedures were called from a single PLV8 library in shared memory, and we created functions stubs so you could still call with straight SQL (mostly used for ETL scripts).

jerrysievert 8y ago
can you tell me a little more about the methods you used with plv8?

lostapathy 8y ago

The biggest problem I've had with triggers is that your application tier tells the database to do an update, it says it did it, and then the app shows the user the result. But in the background, the trigger somehow overrode your changes, so the next time the user comes back they see different data than the app just told them was there. Granted, this is arguably the wrong way to use triggers, but once it's become an invasive problem in your codebase it's incredibly hard to deal with. You can't remove the triggers for fear of what still relies on the side effects, and you don't want to query back the data for every update either. It ends up being like the polar opposite of functional programming - side effects everywhere.

CuriouslyC 8y ago
In postgres you can handle this using the "returning" syntax for updates; then as long as the trigger runs before rather than after the update you don't have to lie about anything.
- lostapathy 8y ago
  I'm not really sure that would help in the case we had. Most of our code ran through an ORM, and the ORM assumes the DB either did what it asked, or the query fails. I don't want to have to abandon my ORM because I can't trust my "DBA" to not sabotage my queries.
  - matte_black 8y ago
    This is just a limitation of ORMs. After a certain complexity you really should just be making direct queries to your database and not relying on ORMs.
    - lostapathy 8y ago
      That’s the thing -what the app tier was doing wasn’t to complex for an ORM. Not by a long ahot. The triggers that caused us problems generally came about to cover unrelated dba laziness and needed removed for sanity reasons anyway. I am not arguing there cannot be a good case for triggers, just that when abused they cause a certain level of hell that is not worth it.

brightball 8y ago

The best implementation I've seen of this was actually in a Rails library, where rather than keep the functions and triggers in migrations, it kept them in individual files so they'd be version controlled just like the rest of the code...and then reloaded them with migrations if they had changed. I don't know if the library is maintained anymore, but I need to find it. EDIT: Found it and it's not maintained. Solid approach to the problem though. https://github.com/norman/squirm_rails https://github.com/norman/squirm_rails

bobwaycott 8y ago
Funny. I was just having a conversation with my partner about this very issue and this was the approach I was thinking of. I’ve found keeping database functions, triggers, etc. painful as part of standard migrations—finding the most recent version is particularly annoying. They’re the kind of thing it’s best to treat like the rest of a project’s code—version controlled outside of migrations.
- mozumder 8y ago
  Isn't that what you'd do anyways? How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control? Or are you actually typing in triggers manually in the Postgres command line?
  - paulryanrogers 8y ago
    Not the OP but I did use triggers as suggested in the past. IME the issue is that DDL like triggers are transactional, so when you deploy them executing queries could be using a mix of old and new. Granted, this is an issue for any system comprised of distributed components. Though I think the pain is more acute when it's the source of truth. One way around is downtime or going all in on stored procedures for all work, or at least all writes. The other wrinkle is that the trigger has to be rolled back when reverting code. And some shops are not diligent about maintaining down procedures. There is also the possiblity of rolling back migrations out of the order they were run, so you might not get back to a consistent state.

gumballhead 8y ago

I've often wanted to write a compiler that would accept a schema definition like protobufs or graphql as input and generate the required SQL to update the database if a schema change was committed. It would also be read by the application layer so its model definitions were kept up to date. As you say, there's a tremendous advantage in using triggers to remove data invariants, but then there's also the issue of the schema's state, and it can be prone to error depending on the complexity of the trigger. It's definitely recommended to use SQL's schema inspection to verify and test it. Perhaps even based on schema definitions from protobufs or whatever.

PeterisP 8y ago
It's feasible to make such a tool to update the database schema so that it would match a changed definition, but I don't think that it's feasible for it to update the actual database. To do that, you need knowledge about how and why the schema was altered, and schema definitions like protobufs or graphql don't contain that. For example, how would you distinguish between renamed column (where you need to keep the data) and deletion of a column plus adding a different one? There's a reason why migrations are the standard level of abstraction, packaging changes to schema with scripts ensuring consistency of data throughout the whole version history.
_mischief 8y ago
I understand the appeal and you're not the only person to want it: I've seen people and companies implement db schema to graphql, db schema to models, and models to db schema. Based on personal experience, this is a bad idea though. You do not want a 1:1 correspondence between your database schema, your backend models, and your graphql schema, because the way you organise information in each layer should be different. Database schema needs to be performant for expected queries. That means de/normalisation decisions; sometimes the same data will be stored in multiple locations. Backend models need to express the domain, because this is where your business logic is. (There's a reason people bitch about ORMs: when you get to complex enough usecases they're not flexible enough in either direction and you need extra models wrapping THAT.) Graphql schema is a view of your backend; sometimes several fields will be fulfilled using the same model, sometimes your model should not have a reflection in graphql schema (because you do not want to expose this data to frontend/the world), and sometimes your graphql schema will be full of deprecated fields because client apps have not been updated (see Facebook policy of never removing anything.)

kbenson 8y ago

> and a new programming language to the stack. This was actually one of the interesting directions Drizzle SQL was exploring back in the day. It was a fork of MySQL that focused on removing old cruft, including lots of older platform support, modernizing the source, and adding modularity in a lot more places, including the use of different languages for SQL programming. Other languages like Perl, Python, Ruby, etc.

hboon 8y ago
Oracle DB supports Java stored procedures. I wrote a couple back in the day. I'm not sure we want to go there :P
krylon 8y ago
Microsoft SQL Server allows one to write server-side code (triggers, sprocs, functions) in any language that compiles to .Net IL. There are some serious restrictions, though, as most of the .Net Framework becomes unavailable[1]. More recent version also provide for server-side R, but our SQL Server is 2014, so I have not had the option to play with that (plus, I don't know R). [1] I was looking into writing a stored procedure for use with the Service Broker in C#, but the fact that most of the .Net Framework is unavailable (not to speak of third-party libraries) quickly put an end to my investigation.

begriffs 8y ago

> but perhaps not at the expense of keeping business logic in separate places Perhaps the answer is to consolidate business logic in the database with constraints. This also protects data from manipulation outside of an app, like with scripts or ETL. Putting more logic in the db often results in terse error messages though, so the app needs to deal with that and make them nicer for the end user. I wrote about one possible approach here - https://begriffs.com/posts/2017-10-21-sql-domain-integrity.html#improved-error-messages https://begriffs.com/posts/2017-10-21-sql-domain-integrity.h... > weak version control/deploy solutions There are solutions that allow you to store migrations in version control, apply them to different database targets, and run consistency checks. For instance http://sqitch.org/ http://sqitch.org/ but maybe you're familiar with it already and regard it as one of the weak tools. Thought I'd point it out though. > a new programming language to the stack plpgsql is somewhat gnarly, but it is well adapted for the database. You know what it's going to do, compared with managing mappings from some other language. I never tried LINQ though so maybe the mapping would be more pleasant than I'm imagining.

arkh 8y ago
> plpgsql is somewhat gnarly, but it is well adapted for the database. You can use python or perl to define your postgres functions. https://www.postgresql.org/docs/10/static/plpython-funcs.html https://www.postgresql.org/docs/10/static/plpython-funcs.htm...
- rleigh 8y ago
  They are slower (by orders of magnitude) than writing them in C or C++, or even PL/pgSQL. Calling into the interpreter for millions of trigger events can quickly become a major bottleneck. When I originally implemented https://pgxn.org/dist/debversion/ https://pgxn.org/dist/debversion/ for version numbering, I originally implemented it in Perl, then Python. The implementations were clean, but the performance of both was abysmal. After reimplementing it in C++ with a C interface, it runs like greased lightning. While this is a custom datatype with operators implemented as C functions, the same concerns apply to triggers which are invoked on every affected row.
8y ago

ashearer 8y ago

One approach that works very well is to keep stored functions in separate .sql files in a directory (I use "fixtures"), and execute them all on each deployment. This should happen after triggering migrations, so that table and column dependencies are guaranteed to be present. The .sql files use CREATE OR REPLACE FUNCTION so that their execution is idempotent. This keeps the stored functions version-controlled along with the source code, and avoids any need to hunt through migration files to find the latest definition. Adding a stored function or modifying its function body just works. The less common operations of deleting a function or modifying its argument list do require an explicit line in a migration file, but those situations are rare (and potentially backward-incompatible, requiring extra caution regardless). One subtlety is that a migration that adds a new table with a trigger should define an empty stub function as the trigger. This avoids duplicating code. The real function body will be loaded from the fixture immediately afterwards.

juliangoldsmith 8y ago

I've worked on a pretty large, very stored proc-heavy app based on SQL Server for several years, and separation of business logic has never been much of an issue. Keeping logic in stored procedures is, in practice, not much different from putting it in a separate function. If your app is data-driven, like the one I work with, most of your business logic can be done in stored procedures. Most any sort of data transform is likely better done in SQL than another language, unless your data is a poor fit for a relational database. We have had issues with version control in the past. I'm currently working on implementing a CI and Git workflow using Jenkins and Red Gate tools, which I expect will make keeping our database in source control trivial. To be fair, though, I'm still working out the last of the kinks, so it may end up being more difficult than I thought. As far as having to learn another language, I've always found that to be an incredibly weak excuse to use JavaScript everywhere. Learning another programming language isn't difficult, and having the right tool for the job is indispensable. Given that JavaScript these days seems to go by "flavor of the week", I don't that think not being able to learn is a problem. I don't find the argument that it's difficult to switch between languages to be convincing, either. Switching between two programming languages that you know isn't any more difficult than switching between a programming language and a spoken one.