6 ms·
Code in Database vs. Code in Application
- perrygeo 4y agoAnother good reason for code in the database - keep compute near the data. Particularly for aggregate functions where you're asking for a small summary of a potentially large amount of data, you're better off implementing that logic in the database to avoid excess data transfer.
- skyde 4y agoproblem with code in database. 1- impossible to unit test 2- program only work with a specific brand and sometimes version of the database 3- impossible to put breakpoint or inspect variable with a debugger 4- database admin change code directly in production without first making change in version control and using deployment pipeline
- ttfkam 4y ago1. https://pgtap.org/ https://pgtap.org/ 2. As opposed to Python 2.7? 3. Different level of computing, SQL being a 4GL. (Also, EXPLAIN ANALYZE) 4. https://sqitch.org/ https://sqitch.org/
- skyde 4y agoTool like sqlitch are great and i used them myself when working for a startup. but in big company only DBA are allowed to make change to the db not the developers. And sadly most dba refuse to use those tool.
- VTimofeenko 4y agoWhile the points are true for manual CREATE OR REPLACE, same could be said of any ad-hoc editing stuff in production by hand. 1, 4 - can be achieved with tools such as DBT 2, 3 - certain databases come with proper Python environment with various tooling around that
- skyde 4y agowhile i admit it’s doable if using traditional SQL database it’s sadly not possible with newsql and noSQL databases. Also in my last 30 year working for different industries, it’s extremely rare that DBA write unit test or a proper deploy system to automatically rollback if anything break while changing database schema or stored procedure. the best DBA i have met use Liquibase or something similar to treat database code like app code. But this is the exception not the norm.
- pjmlp 4y ago1 - https://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_testing.htm#RPTUG45000 https://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_test... 2 - Fair enough, so is the same when using C extensions, nothing new when using standards with multiple implementations 3 - https://www.thatjeffsmith.com/archive/2014/02/how-to-start-the-plsql-debugger/ https://www.thatjeffsmith.com/archive/2014/02/how-to-start-t... (2014 tutorial on purpose) 4 - Just like a UNIX admin can do the same in a production server if the culture isn't there
- skyde 4y agoyou are assuming I am using Oracle SQL on a database server I control. What if instead we have to use Amazon Aurora or Azure cosmosdb … you can’t simply use C for your stored procedure and you can’t simply login in the vm to have a debugger.
- pjmlp 4y agoNope, I am assuming that you are using a proper RDMS with enterprise capabilities and not fad databases with lower quality tooling. Oracle was just an example of what the minimum bar should be. With Oracle you can also use C for the stored procedures if you are so much inclined, although I really wouldn't advocate for it. As for logging in, it is a matter of permissions and policies.
- yobbo 4y agoCommitment specific to language/vendor and debugging clunkiness are valid points. However, if having code outside the db is even an alternative for you, you are already far more deeply committed to some language/framework, or your app is trivial. Debugging databases has slightly different aims than debugging code, and it's much easier since you have the whole relational db available as a debugging tool. It's effortless to store states in temporary tables.
- jdougan 4y agolaughs in Gemstone/S His position is arguably an engineering limitation of the databases used, rather than an actual principle.
- skyde 4y agoThat’s the datagrid product similar to oracle coherence and Gigaspace right?
- jdougan 4y agoNo. The distributed cache product was GemFire, which was derived from the cacheing strategy used in Gemstone/S and Gemstone/J. There was a series of aquisitions and I think Pivotal owns it now. Gemstone/S is an ACID object database that uses a modified Smalltalk data model and a Smalltalk dialect as a the data language. If you are developing for it on one of the supported client Smalltalk platforms, the experience is magical. eg. if the GS Smalltalk throws an exception, it can be caught by the front end and the debugger call stack display (ST doesn't unwind the stack when searching for a handler) shows both like they were on the same machine. GemTalk Systems has a free trial that works with Squeak/Pharo as a client. https://gemtalksystems.com/small-business/gsdevkit/ https://gemtalksystems.com/small-business/gsdevkit/
- jdougan 4y agoOn further searching GemFire has been open sourced as Apache Geode. https://geode.apache.org/ https://geode.apache.org/
- gavinray 4y agoI'm interested in languages experimenting with integrating tables/dataframes and the relational model as native parts of the language With how much of the code I've written that's been around representing entities and doing CRUD on collections of them it seems a bit wild not to have native "Table" types. There's an HN user who has a neat language called "Empirical" based on this, and there's an active research language called "Flix" that has this as well. APL/K and other array langs feature a similar concept as I understand it. https://www.empirical-soft.com/ https://www.empirical-soft.com/ https://flix.dev/ https://flix.dev/ I think this also meshes with the idea of LINQ and it's predecessor QUEL.
- pjmlp 4y ago> You’ll be printf debugging at best, and you almost certainly won’t have access to powerful development tooling like code-completion through LSPs Yes, you stay away from enterprise level databases, you will get what free beer gets you. All big name databases have proper IDE tooling with ability to single step stored procedure code. > Deployment and versioning: Likewise, there are source control tooling for database schemas on enterprise databases. > And sure, you may be able to activate an extension for an alternative scripting language with better syntax, but do you really want something like a Python VM running inside of your database? No, I rather enjoy the advanced GC and JIT capabilities of JVM and CLR implementations.
- lesserknowndan 4y agoCan you please provide some examples (anything other than Oracle)? I do a lot of programming with stored procedures in MySQL and being able to single step stored procedure code is something I’d like to try.
- pjmlp 4y ago- MS SQL, https://www.sqlshack.com/debugging-stored-procedures-sql-server-management-studio-ssms/ https://www.sqlshack.com/debugging-stored-procedures-sql-ser... - DB 2 on z/OS, https://ibm.github.io/db2forzosdeveloperextension-about/docs/working-with-stored-procedures/debugging-stored-procedures https://ibm.github.io/db2forzosdeveloperextension-about/docs... - Informix, https://www.ibm.com/docs/en/informix-servers/12.10?topic=dsr-debugging-spl-procedures-database-add-ins-visual-studio https://www.ibm.com/docs/en/informix-servers/12.10?topic=dsr... Basically all enterprise level RDMS support debugging them for years. As for MySQL, this seems to do the trick for VS users, https://dev.mysql.com/doc/visual-studio/en/visual-studio-debugger.html https://dev.mysql.com/doc/visual-studio/en/visual-studio-deb... I never really used MySQL, so I can't be of much help there.
- swaranga 4y agoHow would you do one-box deployments with code in the database? Everything else could be solved with perhaps some or a lot of pain.
- Smeevy 4y agoIt's just scaffolding. You write scripts to deploy your stored procedures, DDL, and functions and you store them in version control. You run those scripts from a remote connection with the appropriate database client installed using DB credentials with the appropriate privileges. Alternately, you run the updates directly on the database server as part of an Ansible/Puppet/Chef script. My point is that you make the database deployment part of your pipeline just like everything else. I think that the underlying problem here is that most developers, and some DBAs, aren't keeping DDL under version control. Instead, they are just using GUI tools to alter tables and, on very special occasions, create procedures. They don't treat the database with the same reverence as their app code and then blame the database for being out of sync.
- swaranga 4y agoOnebox deployment typically deploys the new revision of the application code to a single host/box and leaves the rest of the fleet in the older revision and then you run your tests against the new revision running in that single box. You also might monitor the metrics from that single box and attempt to find regressions from the rest of the fleet. If the code is deployed to the database (version controlled or not) all application servers will start using the new code immediately which is not what we would want from a onebox deployment first strategy. Usually all the servers will share the same database and thus the code in the code in database model. What am I missing.
- anonymous23801 4y agoYou could easily create a new revision of the function you want to change, and have the first box to use the new revision while the rest keep using the old one. In postgres that would be trivial with revision schemas and a clever usage of the SEARCH_PATH.
- leo_lightmeter 4y agoInterestingly when one uses SQLite (or virtually any embedded/serverless[1] database engine), the code is always executed within the application process, regardless of being written in pure SQL of by the hosting language, making the dichotomy irrelevant. Not having support for stored procedures can be used by some as an argument against SQLite, but the fact is that store procedures conceptually make no sense in embedded databases if you can extend the query language with the host code. One big issue I've seen on SQLite is that if you add an index or view on an expression which uses code from the host process, no other application will be able to read data from the database file, unless you have such code in an extension that can be read by such applications. [1] https://www3.sqlite.org/matrix/serverless.html https://www3.sqlite.org/matrix/serverless.html
- xupybd 4y agoI disagree with the author on few points. SQL is not terrible when you get used to it. They conflate database functions with triggers. It's very easy to restrict your database code to pure functions. It is possible to keep database functions in git. It requires some discipline but it's not hard. Migration tools like Flyway help keep track of db function changes. At small single server scale db functions can be much faster for fetching data. Tools such as Postgrest prove that you can do much of your crud work in DB functions.
- idoubtit 4y ago> It is possible to keep database functions in git. It requires some discipline but it's not hard. My experience differs. How do you check that the stored procedures in the DB are exactly as defined in Git? I remember spending several hours on a bug that was caused by an altered stored procedure. One SQL function was not as it should have been. I don't know if someone had patched it live, or if there had been an unnoticed failure in a DB migration script. With a compiled executable, consistency is guaranteed. With interpreted code (Python, PHP), consistency check is done with a command like `git status`. But there is no standard way to compare the state of store procedures in the DB with their expected code in versioned files.
- xupybd 4y agoThe solution is that nothing gets to production unless it gets migrated in via the CI pipeline. Admittedly it's still a problem during development. But a tool like Flyway versions your DB. You know if you are out of date as the migration table is behind. It's not as good as compiled code but it works when you need it.
- yobbo 4y ago> How do you check that the stored procedures in the DB are exactly as defined in Git? How do you know that a binary is produced by some code in git? Because you built it. Same principle applies. But also, the db can list its code.
- Smeevy 4y ago> I remember spending several hours on a bug that was caused by an altered stored procedure. One SQL function was not as it should have been. I don't know if someone had patched it live, or if there had been an unnoticed failure in a DB migration script. If you have everything under version control you can just reapply that version's stored procedures off the database scripts. If someone or something is not handling failures in deployment scripts (not just database migrations) then you get that sort of problem.