4 ms·

problem 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

by skyde 4y ago

problem 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 ago
1. 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 ago
  Tool 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 ago
While 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 ago
  while 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 ago
1 - 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 ago
  you 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 ago
    Nope, 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.
    - somehnguy 4y ago
      If embedding C functions into my database is the ‘right way’, well I’ll stick to the ‘wrong way’.
      - pjmlp 4y ago
        Apparently we have some reading comprehension problem => "...although I really wouldn't advocate for it.".
        somehnguy 4y ago
yobbo 4y ago
Commitment 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.

by skyde 4y ago

ttfkam 4y ago

1. 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 ago
Tool 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 ago

While 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 ago
while 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 ago

1 - 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 ago
you 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 ago
  Nope, 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.
  - somehnguy 4y ago
    If embedding C functions into my database is the ‘right way’, well I’ll stick to the ‘wrong way’.
    - pjmlp 4y ago
      Apparently we have some reading comprehension problem => "...although I really wouldn't advocate for it.".
      - somehnguy 4y ago

yobbo 4y ago

Commitment 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.