PHP, Python, Ruby application with multiple RDBMS
I start to feel old fashioned when I see all these SQL structures for database abstraction and all these ORMs, although I am far from being old. I understand their need, but their use extends to locations where they usually don't belong.
I strongly believe that using database abstraction layers to generate SQL is the wrong way to write database applications that need to run on multiple database engines, especially when you ditch really expensive databases like Oracle. And this is more or less global, it does not only apply to a few languages.
Simple example: using pagination and insertion: when using Oracle, you can use the FIRST_ROWS and APPEND hints (if needed). Moving on to advanced examples, I might mention nesting many stored procedures / packages in the database where it makes sense. And this is different for each DBMS.
By using only a limited set of functions usually available for many RDBMSs, you cannot take advantage of the capabilities offered by these expensive and advanced RDBMSs.
So back to the heart of the question: how do you develop PHP, Python, Ruby, etc. applications that need to run on multiple database engines?
I am especially interested in how you decouple / use queries that are especially written to run on a single RDBMS. Let's say you have a statement that needs to work on 3 RDBMSs: Oracle, DB2 and Sql Server, and for each of them, you write a separate SQL statement to take advantage of all the functionality that RDBMS has to offer. How do you do it?
Putting that aside, what do you think along the way? Is it worth it in your experience? What for? Why not?
source to share
You cannot eat cake and have it, choose one of the following options.
- Use your database abstraction layer whenever you can, and on the rare occasion that you need a manual query (e.g. performance considerations), stick to the lowest common denominator and don't use stored procedures or any proprietary extensions your database might suggest. data. In this case, deploying the application to another RDBMS should be trivial.
- Harness the power of your dear RDBMS, but keep in mind that your application will not be easy to port. When the need arises, you have to spend considerable effort on porting and maintenance. Of course, a decent layered design that encapsulates all the differences in a single module or class will help with this.
In other words, you need to think about how likely it is that your application will be deployed across multiple RDBMSs and make an informed choice.
source to share
If you want to use the bells and whistles of various RDBMSs, you can do so. Just apply the standard OO principles. Find out what type of API you need to support your persistence layer.
As a result, you will create a set of isomorphic persistence adapter classes. From the point of view of your model code (which will call adapter methods to load and store data), these classes are identical. Writing good test coverage should be easy, and good tests will make life a lot easier. Determining how much abstraction is provided by persistence adapters is the hardest part and is highly application dependent.
As for whether it's worth the problem: it depends. This is a good exercise if you've never done it before. It might be premature if you really don't know exactly what your target databases are.
Implementing two persistent startup adapters can be a good strategy. Let's say you expect MySQL to be the most common backend. Implement one adapter configured for MySQL. Implement a second one that uses the database abstraction library of your choice and uses only standard and widely available SQL functions. You now have support for many back ends (it all depends on the abstraction library you choose), plus custom mySQL support. If you decide you want to provide an optimized adapter from Oracle, you can implement it at your leisure and you will learn that your application can support replaceable standby databases.
source to share