News for hackers. Stuff that matters.
Re:Python: Just Write SQL by fmajid on Monday August 14, 2023 @09:44AM (#37120935)
I'd take it a step further and move all SQL into stored procedures and call those using a functional interface. That's because of PostgreSQL's excellent stored procedure support, it might be harder with, e.g. MySQL.

One major benefit of stored procedures, in addition to separation of concerns, is that you can declare them SECURITY DEFINER and give them access to tables the Python process doesn't (in a way reminiscent of setuid), thus improving the security posture dramatically.

One example: you could have a procedure authenticate(login, password) that has access to the table of users and (hashed) passwords, but if the Python app server is compromised it doesn't have access to even the hashed passwords or even the ability to enumerate users of the system.

Re:Python: Just Write SQL by baq on Monday August 14, 2023 @09:47AM (#37120955)
Last project we've explicitly decided to not have any stored procedures ever since you basically can't test nor deploy them in any sane way. I'm all ears how you make it work.
Re:Python: Just Write SQL by felipetrz on Monday August 14, 2023 @10:15AM (#37121277)
You can test them very easily by using database containers.
Re:Python: Just Write SQL by tracker1 on Monday August 14, 2023 @01:07PM (#37123544)
The setup/teardown, and even working with schema migrations can get complex, and potentially excessively so for the benefit of doing everything in SPs. Also, the developer experience and discoverability are definitely less than ideal.
Re:Python: Just Write SQL by claytonjy on Monday August 14, 2023 @11:01AM (#37121828)
I used sqitch to have very explicit tests for procedures, written in PL/pgSQL. Worked great though writing that code was a little weird due to lack of IDE support I'm used to in any other language. Could take it even further with pgTAP.
Re:Python: Just Write SQL by gregw2 on Monday August 14, 2023 @03:52PM (#37125976)
I had a boss once with a similar viewpoint, so I learned from him how they automated database deployments for Java/Scala apps using Liquibase+Maven and found I could apply and integrate the same principles to testing and deploying a data-layer stored procedure engine I had previously built into a joint product we were building together.

For that project I was able to put SQL DDL+DML+stored procedures in version control, create/run stored procedure (TDD even) unit/integration tests on mock data against other stored procedures, had pass-fail testing/deployment in my CICD tool right alongside native app code, and did some rollback support (although that was more trouble than I think it was worth), all using Liquibase change sets (+git+Jenkins). Flyway could also have worked but we were using Liquibase.

It did require some creative thinking to apply changesets and preconditions and post conditions and ability to have stored procedures execute and read results of other stored procedures.

Last I checked, the system had been used over many years to process/evaluate $50 billion in order transactions.

Re:Python: Just Write SQL by fmajid on Monday August 14, 2023 @04:28PM (#37126387)
SQL scripts with anonymous PL/pgSQL DO blocks to do unit tests, with a library of utility functions to do things like assertions, just as you would in any imperative language. Granted, the PL/pgSQL debugger tooling is not ideal but it works, and we wrote our line-by-line own profiler to identify hotspots.
Re:Python: Just Write SQL by pjmlp on Tuesday August 15, 2023 @03:07AM (#37131096)
From the point of view from Oracle and SQL Server, it looks like a consequence of not being aware of proper tooling.
Re:Python: Just Write SQL by pjmlp on Tuesday August 15, 2023 @03:06AM (#37131089)
That is my favourite approach, when I am allowed to have the last work how the DB layer should be like.