Is there a SQL system or wrapper that supports the actual standard as an abstraction?

Question

I am pretty new to databases; I am currently taking an online course from Stanford about relational algebra, database design, and fundamental and advanced features of the SQL language.

I am going to be writing an application that uses databases extensively (like most applications nowadays), and of course, I would like to abstract away the specifics of the database itself. Ideally, I'm hoping to build a module of my program with a public function that takes SQL code as input and modifies or returns data from the database as specified by that code. I don't want any of the rest of my program to care what database system I'm using (MySQL, SQLite, etc.) underneath. This obviously makes the lack of agreement between the different dialects of SQL used by various vendors a problem.

From what I can tell, this is currently impossible. It seems that my best option would to be to simply use a system like PostgreSQL that strives to follow the standard as closely as possible, but even then, it doesn't seem that PostgreSQL supports the complete standard exactly. Worse, I am then tied to the implementation (read: performance characteristics) of that one SQL system.

So what should I do? Is there a tool that can, at least somewhat reliably, convert between SQL dialects? I'm guessing that that won't even be enough, when you take into account things like triggers that are persistent in the database itself, and therefore affect more than the data and the code in which they are specified. Would I then need a tool that sits on top of the database system and stores its own persistent data? Again, does such a tool even exist? Or should I just suck it up, pick a particular system, and have my application code generate SQL code that that particular system accepts?

If the latter, I am interested in the possibility of later developing such a tool. I realize that it would be an absolutely monstrous task, so where should I go to gauge interest in such a project?

Answer 1

There is no DBMS that implements the entire SQL standard.

Depending in what programming language you are developing, there always are interfaces for communicating with a DBMS through SQL commands (e.g. OLEDB, ODBC).

The most important thing to keep in mind is that the "dialects" you are referring to are almost identical. That is, the difference between them is either very small or some don't implement a very unusual case and structure that is part of SQL standard.

Just because something is a part of the standard does not mean that you will ever need it. Unless you have very specific intentions and your applications needs exactly this one thing, but then your decision would already be obvious as you pick the one implementing it.

The performance difference is indeed an important part since SQL is declarative and the actual quality of the DBMS shows on how fast or with how much load request are executed. Here again it depends on your usage. PostgreSQL is a good DBMS, but for very large projects IBM's and Oracle's solutions might be needed. But judging on you starting to develop an application even MySQL should do the job.

Defining Triggers would need to be done in the DBMS, almost all have a good GUI that lets you easily set things up. There is no need for an additional tool.

In the end you indeed need to pick a DBMS an develop for that specific Database you are using. Though you application does not "generate SQL code that that particular system accepts" you will and always should use prepared statements that your applications simply fills with data. When talking about SELECT, CREATE, DROP statements there are likely no differences between most DBMS anyway (with a few small exceptions).

I have the feeling that you are still very new to databases and are viewing things at a very theoretical point of view. I think you should take look at what a DBMS actually has to do behind the scenes. This is why you cannot just implement a tool to put on top of a DBMS to suddenly create functionality that the SQL standard has or another vendor has implemented.

If you take a look at Wikibooks you can see the differences are not big, if even existing.