No. You should really wind up with a DAL. Define some stored procedures for accessing and working on the data and use only stored procedures.
No need for ORM, and no inline sql logic in your application code.
No need for ORM, and no inline sql logic in your application code.
If you use stored procedures, all you've done is move part of the model into the database, so you have to update the stored procedures as part of a deployment. You still need to have the SQL code written out somewhere, and you still need to have something in the application code that knows which procedures exist and how to use the data they return in business logic.
you can deploy schema changes independently
you can change everything and the app should not notice
I'm not really sure what you mean by "you can deploy schema changes independently" and "you can change everything and the app should not notice". The stored procedures are basically just an extension of the apps logic right? So you can deploy them at any time, sure, but that isn't different from an app that doesn't use stored procedures, because you could also deploy changes to any part of that app at any time.
I do think stored procedures can be more efficient, because you have a lot more control. But it's not like they are clearly superior from an organizational standpoint. If you write an ORM using stored procedures, it's still an ORM.
Why pretend your SQL database is about objects? It is not... (it is about data)
A stored procedure can act like a view or a query, or use procedural logic. Point is: your app can call it and get a concistent result, no matter what refactoring has been going on.
A direct query needs to know too much about the database (orm generated or otherwise) which prevent refactoring and couples app to database harder...
You can rename or merge tables, views functions in the database but the interface the app use (stored procedures/DAL) will stay the same and work the same way.
As for app logic... I prefer bussiness logic in the database, not the app when the data is important. Application logic stay in your application, data dependent bussiness logic stay with the data.
On the other hand, having well factored microservices (out of process) or in process modules have worked out really well with modern devops and software engineering principals - easy push button deployments and rollbacks, unit testing, A/B deployments, etc.
It probably depends on the domain/problems.
My experience is with transaction heavy financial systems or similar, with web frontends, microservices sprinkled around in different languages...
The web app or java worker should be allowed to focus in its problems, the bussiness logic needs to live in one central place, which happens to be in the database accessed through thightly controled interface in the form of stored procedures.
A rest api... how and why should it be responsible for your data? It solved a different problem.
You might not even need a database I guess, and then anything goes.
I need and like my database, and have suffered trying to get along with different ORMs. SQL is so good at what it is designed to do if you just let it.
(And why just one rest api? How about 100 restapis, some microservices, some web apps, some background workers, many different languages. One database. No ORM)
One db can be a problem, or a strenght depending on the domain; And I really dislike religious design, esp microservices.
I have less problems by avoiding ORMs (and religios microservice arch, or fundamentalist interpretations of rest)
Database handles the shared state in a heterogenous environment. We need it to be centralized to keep track of money, the apps can't do that, two independent databases cant do that either. It must be one system that guarantees concistency.
It works great, there is no downtime. The interfaces are defined, the database stands alone, updates are deployed separately.
Why can't apps "keep track of money"? I'm assuming you're referring to transactions. Apps can create transactions and you can share transactions across apps using distributed transaction (I'm not saying distributed transaction is a good idea).
And you lose all of the benefits of microservices if there is still a tight coupling between unrelated (from a domain perspective) to tables.
You dont need an ORM for testing your code...
But I think this varies from project to project. How many different applications, in different languages are using your db and do you tolerate downtime?
All of the manual sign off steps are integrated with the automated release pipeline. As soon as the required approvals sign off, the next step of the pipeline is done.
Rolling back is just redeploying the previous released version. Branching, source control, etc is also a lot easier when all of your business logic is in code and you don't have to sync up the "right" version of your source control with the right version of your stored procedures.
Of course this is even easier when you're using a NoSql solution where your schema is also defined by your class models. But that's another discussion.....
Of course this doesn't have to just apply to code. With things like Packer and Terraform you can do the same with infrastructure. Automated infrastructure deployment is not my expertise...yet
You can then start combining stored procedures. Great way to build a slow mess quickly.
Not really, you just mock the database calls in the code you're unit testing.
And I realize that being able to test queries without database dependencies, only really applies to a few languages that treat queries as a first class citizen in the language like C# and Linq where you can mock out your actual Linq provider - replace the EF context with in memory List<T> - and still test your Linq queries.
Depends on what you want to test. Can either write unit tests for the stored procedures or unit tests for the code that makes use of those stored procedures.
Qué?
tSQLt is used by the commercial SQL Test product from Red Gate if you wanted a more polished user experience:
https://www.red-gate.com/products/sql-development/sql-test/i...
For PostgreSQL and MySQL you can bring up the DBMS in Docker. That is not a built-in fixture obviously but easy enough to do locally as well as in CI/CD systems like Travis. You'll need to load SQL into the DBMS as a prerequisite to testing. That has the benefit of testing your load/upgrade sequence.
The beauty of Linq and Expression Trees.
In the real world:
Linq (c# code) -> compiler -> expression tree -> run time Linq provider -> destination language (sql, Mongo Query etc.)
When you are unit testing you switch out the Linq provider for in memory Linq to objects provider.
https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....
And would you not have your IDE on one monitor and your SQL IDE in another so you could look at both sets of code.
Rolling back is a simple matter of installing the previous archive. That doesn't just apply to code anymore. You can treat "infrastructure as code" also.
You can do A/B upgrades, rollbacks, etc. There is so much better tooling around regular code than sql/stored procedures. How many times have you seen stored procedures with hundreds of lines, duplicated stored procedures with V1,V2, etc appended to it, commented out logic etc?
I've had to wade through some hairy code to but at least with a code, I can do some automated guaranteed safe refactoring, find dependencies, keep the interfaces backwards compatible, etc.
Avoiding SQL in favour of an ORM or inline sql is 95% of the time a sign of laziness.