views:

1150

answers:

24

I'm not quite sure stackoverflow is a place for such a general question, but let's give it a try.

Being exposed to the need of storing application data somewhere, I've always used MySQL or sqlite, just because it's always done like that. As it seems like the whole world is using these databases (most of all software products, frameworks, etc), it is rather hard for a beginning developer like me to start thinking about whether this is a good solution or not.

Ok, say we have some object-oriented logic in our application, and objects are related to each other somehow. We need to map this logic to the storage logic, so relations between database objects are required too. This leads us to using relational database, and I'm ok with that - to put it simple, our database table rows sometimes will need to have references to other tables' rows. But why use SQL language for interaction with such a database?

SQL query is a text message. I can understand this is cool for actually understanding what it does, but isn't it silly to use text table and column names for a part of application that no one ever seen after deploynment? If you had to write a data storage from scratch, you would have never used this kind of solution. Personally, I would have used some 'compiled db query' bytecode, that would be assembled once inside a client application and passed to the database. And it surely would name tables and colons by id numbers, not ascii-strings. In the case of changes in table structure those byte queries could be recompiled according to new db schema, stored in XML or something like that.

What are the problems of my idea? Is there any reason for me not to write it myself and to use SQL database instead?

EDIT To make my question more clear. Most of answers claim that SQL, being a text query, helps developers better understand the query itself and debug it more easily. Personally, I haven't seen people writing SQL queries by hand for a while. Everyone I know, including me, is using ORM. This situation, in which we build up a new level of abstraction to hide SQL, leads to thinking if we need SQL or not. I would be very grateful if you could give some examples in which SQL is used without ORM purposely, and why.

EDIT2 SQL is an interface between a human and a database. The question is why do we have to use it for application/database interaction? I still ask for examples of human beings writing/debugging SQL.

+7  A: 

Yes, it is annoying to have to write SQL statements to store and retrieve objects.

That's why Microsoft have added things like LINQ (language integrated query) into C# and VB.NET to make it possible to query databases using objects and methods instead of strings.

Most other languages have something similar with varying levels of success depending on the abilities of that language.

On the other hand, it is useful to know how SQL works and I think it is a mistake to shield yourself entirely from it. If you use the database without thinking you can write extremely inefficient queries and index the database incorrectly. But once you understand how to use SQL correctly and have tuned your database, you have a very powerful tried-and-tested tool available for finding exactly the data you need extremely quickly.

Mark Byers
It seems to me that LINQ statements are further translated to SQL, and the whole interaction with db is done like it's always done. This is no doubt a cool method not to use lame SQL, but it does not eliminate the whole problem with database interface I was writing about.
martinthenext
@martinthenext: With LINQ to SQL, if the database changes (for example a column name changes) you can adjust your DBML file. It is not necessary to change the code.
Mark Byers
A: 

There are a lot of non relational database systems. Here are just a few: Memcached Tokyo Cabinet

Jay
But I need my database to be relational! I just don't want it to use SQL.
martinthenext
You can certainly use a non relational database but structure your data in any way you like. It won't have any automated integrity or cascading update/delete features though.
Jay
+1  A: 

SQL was created to provide an interface to make ad hoc queries against a relational database.

Generally, most relational databases understand some form of SQL.

Object-oriented databases exist, and (presumably) use objects to do their querying... but as I understand it, OO databases have a lot more overheard, and relational databases work just fine.

Relational Databases also allow you to operate in a "disconnected" state. Once you have the information you asked for, you can close the database connection. With an OO database, you either need to return all objects related to the current one (and the ones they're related to... and the... etc...) or reopen the connection to retrieve new objects as they are accessed.

In addition to SQL, you also have ORMs (object-relational mappings) that map objects to SQL and back. There are quite a few of them, including LINQ (.NET), the MS Entity Framework (.NET), Hibernate (Java), SQLAlchemy (Python), ActiveRecord (Ruby), Class::DBI (Perl), etc...

R. Bemrose
+8  A: 
  • It's an ubiquitous standard. Pretty much every programming language out there has a way to access SQL databases. Try that with a proprietary binary protocol.
  • Everyone knows it. You can find experts easily, new developers will usually understand it to some degree without requiring training
  • SQL is very closely tied to the relational model, which has been thoroughly explored in regard to optimization and scalability. But it still frequently requires manual tweaking (index creation, query structure, etc.), which is relatively easy due to the textual interface.
Michael Borgwardt
1) I'm sorry if I got something wrong, but why are using the word `proprietary`? I was thinking about some open-source solution, for instance.2) 3) It appears that it's a good thing for developers to have textual queries because they are easy to understand. But what about those developers who spend a lot of time on query optimization? Is it worth it? I don't think that a good application interface to bytecode queries, like the one I've written about, would have been so hard to understand. Developers would like speed more than learning simplicity, I guess.
martinthenext
@martinthenext - The problem with this is interoperability. SQL is text and can be copied/pasted, reviewed, etc. easily. With a compiled version, you need an application. Do you integrate one into each database, create an external one that works in all databases? What about different operating systems or even feature changes between database systems and each version? SQL doesn't have any of these problems... until you get to SQL builders :)
Nelson
...it's probably the same reason why XML is used so much. XML is not very efficient, but text is easier to work with than some binary format. A corrupt XML file is easier to fix (at least by a human) than a binary file, etc.
Nelson
@martinthenext: that sounds like you believe query optimization is only necessary because it's a textual format. Nothing could be farther from the truth. Query (and DB structure) optimization concerns the semantic structure and content of the DB and the queries. A binary format would merely make that kind of optimization impossible without additional tools.
Michael Borgwardt
A: 

If the first part you seem to refer to what is usually called the Object - relational mapping impedance. There are already a lot of frameworks to alleviate that problem. There are tradeofs as well. Some things will be easier, others will get more complex, but in the general case they work well if you can afford the extra layer.

In the second part you seem to complain about SQL being text (it uses strings instead of ids, etc)... SQL is a query language. Any language (computer or otherwise) that is meant to be read or written by humans is text oriented for that matter. Assembly, C, PHP, you name it. Why? Because, well... it does make sense, doesn't it?

If you want precompiled queries, you already have stored procedures. Prepared statements are also compiled once on the fly, IIRC. Most (if not all) db drivers talk to the database server using a binary protocol anyway.

Juan Pablo Califano
I've wrote a big comment on your post and then posted it as an edit. I don't actually complain about SQL, I'm asking about why we need it - an extra language for interface between HUMAN and DATABASE, as in most cases we need an APPLICATION/DATABASE interaction.
martinthenext
+1  A: 

yes, text is a bit inefficient. But actually getting the data is a lot more costly, so the text based sql is reasonably insignificant.

Keith Nicholas
A: 

A database language is useful because it provides a logical model for your data independent of any applications that use it. SQL has a lot of shortcomings however, not the least being that its integration with other languages is poor, type support is about 30 years behind the rest of the industry and it has never been a truly relational language anyway.

SQL has survived mostly because the database market has been and remains dominated by the three mega-vendors who have a vested interest in protecting their investment. That's changing and SQL's days are probably numbered but the model that will finally replace it probably hasn't arrived yet - although there are plenty of contenders around these days.

dportas
A: 

As far as finding a relational database that doesn't use SQL as its primary interface, I think you won't find it. Reason: SQL is a great way to talk about relations. I can't figure out why that's a big deal to you: if you don't like SQL, put an abstraction over it (like an ORM) so you don't have to worry about it. Let the abstraction worry about it. It gets you to the same place.

However, the problem your'e really mentioning here is the object-relation disconnect - the problem is with the relation itself. Objects and relational-tuples don't always lend themselves to be a 1-1 relationship, which is the reason why a developer can frustrated with a database. The solution to that is to use a different database type.

sheepsimulator
I have to disagree with your 2nd sentence. SQL is a truly lousy way to "talk about relations"! In so many ways SQL totally fails to deliver what the relational model was supposed to.
dportas
@David - Suggestions on a better way?
sheepsimulator
+1  A: 

I don't think most people are getting your question, though I think it's very clear. Unfortunately I don't have the "correct" answer. I would guess it's a combination of several things:

  1. Semi-arbitrary decisions when it was designed such as ease of use, not needing a SQL compiler (or IDE), portability, etc.
  2. It happened to catch on well (probably due to similar reasons)
  3. And now due to historical reasons (compatibility, well known, proven, etc.) continues to be used.
  4. I don't think most companies have bothered with another solution because it works well, isn't much of a bottleneck, it's a standard, blah, blah..
Nelson
+1  A: 

While I see your point, SQL's query language has a place, especially in large applications with a lot of data. And to point out the obvious, if the language wasn't there, you couldn't call it SQL (Structured Query Language). The benefit of having SQL over the method you described is SQL is generally very readable, though some really push the limits on their queries.

I whole heartly agree with Mark Byers, you should not shield yourself from SQL. Any developer can write SQL, but to really make your application perform well with SQL interaction, understanding the language is a must.

If everything was precompiled with bytecode as you described, I'd hate to be the one to have to debug the application after the original developer left (or even after not seeing the code for 6 months).

Jeff Schumacher
+6  A: 

Given the fact that you used MySQL and SQLite, I understand your point of view completely. Most DBMS have features that would require some of the programming from your side, while you get it from database for free:

  • Indexes - you can store large amounts of data and still be able to filter and search very quickly because of indexes. Of course, you could implement you own indexing, but why reinvent the wheel

  • data integrity - using database features like cascading foreign keys can ensure data integrity across the system. You only need to declare relationship between data, and system takes care of the rest. Of course, once more, you could implement constraints in code, but it's more work. Consider, for example, deletion, where you would have to write code in object's destructor to track all dependent objects and act accordingly

  • ability to have multiple applications written in different programming languages, working on different operating systems, some even distributed across the network - all using the same data stored in a common database

  • dead easy implementation of observer pattern via triggers. There are many cases where only some data depends on some other data and it does not affect UI aspect of application. Ensuring consistency can be very tricky or require a lot of programming. Of course, you could implement trigger-like behavior with objects but it requires more programming than simple SQL definition

Milan Babuškov
+8  A: 

But why use SQL language for interaction with such a database?

I think it's for the same reason that you use a human-readable (source code) language for interaction with the compiler.

Personally, I would have used some 'compiled db query' bytecode, that would be assembled once inside a client application and passed to the database.

This is an existing (optional) feature of databases, called "stored procedures".


Edit:

I would be very grateful if you could give some examples in which SQL is used without ORM purposely, and why

When I implemented my own ORM, I implemented the ORM framework using ADO.NET: and using ADO.NET includes using SQL statements in its implementation.

ChrisW
+2  A: 

If all you need to do is store some application data somewhere, then a general purpose RDBMS or even SQLite might be overkill. Serializing your objects and writing them to a file might be simpler in some cases. An advantage to SQLite is that if you have a lot of this kind of information, it is all contained in one file. A disadvantage is that it is more difficult to read it. For example, if you serialize you data to YAML, you can read the file with any text editor or shell.

Personally, I would have used some 'compiled db query' bytecode, that would be assembled once inside a client application and passed to the database.

This is how some database APIs work. Check out static SQL and prepared statements.

Is there any reason for me not to write it myself and to use SQL database instead?

If you need a lot of features, at some point it will be easier to use an existing RDMBS then to write your own database from scratch. If you don't need many features, a simpler solution may be wiser.

The whole point of database products is to avoid writing the database layer for every new program. Yes, a modern RDMBS might not always be a perfect fit for every project. This is because they were designed to be very general, so in practice, you will always get additional features you don't need. That doesn't mean it is better to have a custom solution. The glove doesn't always need to be a perfect fit.

UPDATE:

But why use SQL language for interaction with such a database?

Good question.

The answer to that may be found in the original paper describing the relational model A Relational Model of Data for Large Shared Data Banks, by E. F. Codd, published by IBM in 1970. This paper describes the problems with the existing database technologies of the time, and explains why the relational model is superior.

The reason for using the relational model, and thus a logical query language like SQL, is data independence.

Data independence is defined in the paper as:

"... the independence of application programs and terminal activities from the growth in data types and changes in data representations."

Before the relational model, the dominate technology for databases was referred to as the network model. In this model, the programmer hand to know the on-disk structure of the data and traverse the tree or graph manually. The relational model allows one to write a query against the conceptual or logical scheme that is independent of the physical representation of the data on disk. This separation of logical scheme from the physical schema is why we use the relational model. For a more on this issue, here are some slides from a database class. In the relational model, we use logic based query languages like SQL to retrieve data. Codd's paper goes into more detail about the benefits of the relational model. Give it a read.

SQL is a query language that is easy to type into a computer in contrast with the query languages typically used in a research papers. Research papers generally use relation algebra or relational calculus to write queries.

In summary, we use SQL because we happen to use the relational model for our databases.

If you understand the relational model, it is not hard to see why SQL is the way it is. So basically, you need to study the relation model and database internals more in-depth to really understand why we use SQL. It may be a bit of a mystery otherwise.

UPDATE 2:

SQL is an interface between a human and a database. The question is why do we have to use it for application/database interaction? I still ask for examples of human beings writing/debugging SQL.

Because the database is a relational database, it only understands relational query languages. Internally it uses a relational algebra like language for specifying queries which it then turns into a query plan. So, we write our query in a form we can understand (SQL), the DB takes our SQL query and turns it into its internal query language. Then it takes the query and tries to find a "query plan" for executing the query. Then it executes the query plan and returns the result.

At some point, we must encode our query in a format that the database understands. The database only knows how to convert SQL to its internal representation, that is why there is always SQL at some point in the chain. It cannot be avoided.

When you use ORM, your just adding a layer on top of the SQL. The SQL is still there, its just hidden. If you have a higher-level layer for translating your request into SQL, then you don't need to write SQL directly which is beneficial in some cases. Some times we do not have such a layer that is capable of doing the kinds of queries we need, so we must use SQL.

Jay
Ultimately, he's asking that if SQL is designed for humans to access databases, why are computers using that and not a system better designed for computers?
RCIX
A: 

One of the Unix design principles can be said thusly, "Write programs to handle text streams, because that is a universal interface.".

And that, I believe, is why we typically use SQL instead of some 'byte-SQL' that only has a compilation interface. Even if we did have a byte-SQL, someone would write a "Text SQL", and the loop would be complete.

Also, MySQL and SQLite are less full-featured than, say, MSSQL and Oracle SQL. So you're still in the low end of the SQL pool.

Paul Nathan
+1  A: 

I think the premise of the question is incorrect. That SQL can be represented as text is immaterial. Most modern databases would only compile queries once and cache them anyway, so you already have effectively a 'compiled bytecode'. And there's no reason this couldn't happen client-wise though I'm not sure if anyone's done it.

You said SQL is a text message, well I think of him as a messenger, and, as we know, don't shoot the messenger. The real issue is that relations are not a good enough way of organising real world data. SQL is just lipstick on the pig.

CurtainDog
+25  A: 

Everyone I know, including me, is using ORM

Strange. Everyone I know, including me, still writes most of the SQL by hand. You typically end up with tighter, more high performance queries than you do with a generated solution. And, depending on your industry and application, this speed does matter. Sometimes a lot. yeah, I'll sometimes use LINQ for a quick-n-dirty where I don't really care what the resulting SQL looks like, but thus far nothing automated beats hand-tuned SQL for when performance against a large database in a high-load environment really matters.

Donnie
+1 - and with a decent schema design, SQL gets easier to use.
Will
This doesn't really answer the question.
FogleBird
+1, I usually only use SQL tools for the initial database design. But I always rewrite/optimize the code I get from those tools. And every query is written by hand.
poke
@Fogle - it is specifically addressed to his two edits asking for examples of people still hand-writing SQL instead of using an ORM / automatically generated queries.
Donnie
@Donnie - I don't have any evidence but I would think *most* ORM queries would resolve to a simple index lookup; so the scope for hand optimisations is limited. Sure, there might be the odd slow query you have to tweak.
CurtainDog
This is a bit far fetch but people concerned with performance is still writing assembly too but its a dying race.
TT
@CurtainDog no offence, but you must not have ever looked at the sql generated by ORM's. ORM's are great for the basics, and they do an admirable job, but you're also ignoring what ORM's can't do; things like Full-Text queries, CTE's, using views. There are still plenty of occasions the wetware is just plain better at querying.
Robert Paulson
@RP - I think we're actually saying the same thing here. ORMs will do a lot of the basic stuff and the basic stuff is most of the stuff. During my time with Hibernate I had to pull apart maybe 1 or 2 queries, and, in hindsight, I should've scrapped the queries altogether and managed that bit of data (time-sensitive stuff) with an in memory prio queue.
CurtainDog
@CurtainDog - it depends on your industry, really. I've done a lot of work where real-time updates are of the utmost priority. These are the cases where you get the big-iron DBs, expensive 3-tier solutions, and hand crafted queries.
Donnie
+1: While ORM's make easy queries even easier, they seem (for me at least) to make hard queries even harder.
TokenMacGuy
+2  A: 

My biggest reason for SQL is Ad-hoc reporting. That report your business users want but don't know that they need it yet.

blissapp
+6  A: 

There are some good answers here. I'll attempt to add my two cents.

I like SQL, I can think in it pretty easily. The queries produced by layers on top of the DB (like ORM frameworks) are usually hideous. They'll select tons of extra stuff, join in things you don't need, etc.; all because they don't know that you only want a small part of the object in this code. When you need high performance, you'll often end up going in and using at least some custom SQL queries in an ORM system just to speed up a few bottlenecks.

Why SQL? As others have said, it's easy for humans. It makes a good lowest common denominator. Any language can make SQL and call command line clients if necessary, and they is pretty much always a good library.

Is parsing out the SQL inefficient? Somewhat. The grammar is pretty structured, so there aren't tons of ambiguities that would make the parser's job really hard. The real thing is that the overhead of parsing out SQL is basically nothing.

Let's say you run a query like "SELECT x FROM table WHERE id = 3", and then do it again with 4, then 5, over and over. In that case, the parsing overhead may exist. That's why you have prepared statements (as others have mentioned). The server parses the query once, and can swap in the 3 and 4 and 5 without having to reparse everything.

But that's the trivial case. In real life, your system may join 6 tables and have to pull hundreds of thousands of records (if not more). It may be a query that you let run on a database cluster for hours, because that's the best way to do things in your case. Even with a query that takes only a minute or two to execute, the time to parse the query is essentially free compared to pulling records off disk and doing sorting/aggregation/etc. The overhead of sending the ext "LEFT OUTER JOIN ON" is only a few bytes compared to sending special encoded byte 0x3F. But when your result set is 30 MB (let alone gigs+), those few extra bytes are worthless compared to not having to mess with some special query compiler object.

Many people use SQL on small databases. The biggest one I interact with is only a few dozen gigs. SQL is used on everything from tiny files (like little SQLite DBs may be) up to terabyte size Oracle clusters. Considering it's power, it's actually a surprisingly simple and small command set.

MBCook
+1  A: 

SQL is an interface between a human and a database. The question is why do we have to use it for application/database interaction? I still ask for examples of human beings writing/debugging SQL.

I use sqlite a lot right from the simplest of tasks (like logging my firewall logs directly to a sqlite database) to more complex analytic and debugging tasks in my day-to-day research. Laying out my data in tables and writing SQL queries to munge them in interesting ways seems to be the most natural thing to me in these situations.

On your point about why it is still used as an interface between application/database, this is my simple reasoning:

  1. There is about 3-4 decades of serious research in that area starting in 1970 with Codd's seminal paper on Relational Algebra. Relational Algebra forms the mathematical basis to SQL (and other QLs), although SQL does not completely follow the relational model.

  2. The "text" form of the language (aside from being easily understandable to humans) is also easily parsable by machines (say using a grammar parser like like lex) and is easily convertable to whatever "bytecode" using any number of optimizations.

  3. I am not sure if doing this in any other way would have yielded compelling benefits in the generic cases. Otherwise it would have been probably discovered and adopted in the 3 decades of research. SQL probably provides the best tradeoffs when bridging the divide between humans/databases and applications/databases.

The question that then becomes interesting to ask is, "What are the real benefits of doing SQL in any other "non-text" way?" Will google for this now:)

neoblitz
+1  A: 

Actually there are a few non-SQL database (like Objectivity, Oracle Berkeley DB, etc.) products came but non of them succeeded. In future if someone finds intuitive alternative for SQL, that will answer your question.

Venkat
+1  A: 

SQL is a common interface used by the DBMS platform - the entire point of the interface is that all database operations can be specified in SQL without needing supplementary API calls. This means that there is a common interface across all clients of the system - application software, reports and ad-hoc query tools.

Secondly, SQL gets more and more useful as queries get more complex. Try using LINQ to specify a 12-way join a with three conditions based on existential predicates and a condition based on an aggregate calculated in a subquery. This sort of thing is fairly comprehensible in SQL but unlikely to be possible in an ORM.

In many cases an ORM will do 95% of what you want - most of the queries issued by applications are simple CRUD operations that an ORM or other generic database interface mechanism can handle easily. Some operations are best done using custom SQL code.

However, ORMs are not the be-all and end-all of database interfacing. Fowler's Patterns of Enterprise Application Architecture has quite a good section on other types of database access strategy with some discussion of the merits of each.

There are often good reasons not to use an ORM as the primary database interface layer. An example of a good one is that platform database libraries like ADO.Net often do a good enough job and integrate nicely with the rest of the environment. You might find that the gain from using some other interface doesn't really outweigh the benefits from the integration.

However, the final reason that you can't really ignore SQL is that you are ultimately working with a database if you are doing a database application. There are many, many WTF stories about screw-ups in commercial application code done by people who didn't understand databases properly. Poorly thought-out database code can cause trouble in so many ways, and blithely thinking that you don't need to understand how the DBMS works is an act of Hubris that is bound to come and bite you some day. Worse yet, it will come and bite some other poor schmoe who inherits your code.

ConcernedOfTunbridgeWells
A: 

Because often, you cannot be sure that (citing you) "no one ever seen after deployment". Knowing that there is an easy interface for reporting and for dataset level querying is a good path for evolution of your app.
You're right, that there are other solutions that may be valid in some situations: XML, plain text files, OODB...
But having a set of common interfaces (like ODBC) is a huge plus for the life of data.

iDevlop
+4  A: 

After all the edits and comments, the main point of your question appears to be : why is the nature of SQL closer to being a human/database interface than to being an application/database interface ?

And the very simple answer to that question is : because that is exactly what it was originally intended to be.

The predecessors of SQL (QUEL being presumably the most important one) were intended to be exactly that : a QUERY language, i.e. one that didn't have any of INSERT, UPDATE, DELETE.

Moreover, it was intended to be a query language that could be used by any user, provided that user was aware of the logical structure of the database, and obviously knew how to express that logical structure in the query language he was using.

The original ideas behind QUEL/SQL were that a database was built using "just any mechanism conceivable", that the "real" database could be really just anything (e.g. one single gigantic XML file - allthough 'XML' was not considered a valid option at the time), and that there would be "some kind of machinery" that understood how to transform the actual structure of that 'just anything' into the logical relational structure as it was perceived by the SQL user.

The fact that in order to actually achieve that, the underlying structures are required to lend themselves to "viewing them relationally", was not understood as well in those days as it is now.

Erwin Smout
XML didn't exist at the time.
mikerobi
A: 

OK, i agreed with your point, that we don't need SQL as text interface in app-db interaction, but there is one thing: db management out of your code. SQL is universal, so you just use the same SQL queries you already know, both in your code and in management utility, that comes with almost every db.

For avoiding the ORM part, all NoSQL db's use direct bindings for different languages, you know.

artemy