What are good alternatives to SQL (the language)?

I occasionally hear things about how SQL sucks and it's not a good language, but I never really hear much about alternatives to it. So, are other good languages that serve the same purpose (database access) and what makes them better than SQL? Are there any good databases that use this alternative language?

EDIT: I'm familiar with SQL and use it all the time. I don't have a problem with it, I'm just interested in any alternatives that might exist, and why people like them better.

I'm also not looking for alternative kinds of databases (the NoSQL movement), just different ways of accessing databases.

I certainly agree that SQL's syntax is difficult to work with, both from the standpoint of automatically generating it, and from the standpoint of parsing it, and it's not the style of language we would write today if we were designing SQL for the demands we place on it today. I don't think we'd find so many varied keywords if we designed the language today, I suspect join syntax would be different, functions like GROUP_CONCAT would have more regular syntax rather than sticking more keywords in the middle of the parentheses to control its behavior... create your own laundry list of inconsistencies and redundancies in SQL that you'd like/expect to see smoothed out if we redesigned the language today.

There aren't any alternatives to SQL for speaking to relational databases (i.e. SQL as a protocol), but there are many alternatives to writing SQL in your applications. These alternatives have been implemented in the form of frontends for working with relational databases. Some examples of a frontend include:

• SchemeQL and CLSQL, which are probably the most flexible, owing to their Lisp heritage, but they also look like a lot more like SQL than other frontends.
• LINQ (in .Net)
• ScalaQL and ScalaQuery (in Scala)
• SqlStatement, ActiveRecord and many others in Ruby,
• HaskellDB
• ...the list goes on for many other languages.

I think that the underlying theme today is that rather than replace SQL with one new query language, we are instead creating language-specific frontends to hide the SQL in our regular every-day programming languages, and treating SQL as the protocol for talking to relational databases.

http://en.wikipedia.org/wiki/SQL#Alternatives">Take a look at this list.

http://en.wikipedia.org/wiki/Hibernate_Query_Language">Hibernate Query Language is probably the most common. The advantage of Hibernate is that objects map very easily (nearly automatically) to the relational database, and the developer doesn't have to spend much time doing database design. Check out the http://www.hibernate.org/">Hibernate website for more info. I'm sure others will chime in with other interesting query languages...

Of course, there's plenty of NoSQL stuff out there, but you specifically mention that you're not interested in those.

> "I occasionally hear things about how SQL sucks and it's not a good language"

SQL is over thirty years old. Insights about "which features make something a 'good' language and which ones make it a 'bad' one" have evolved more rapidly than SQL itself.

Also, SQL is not a language that conforms to current standards of "what it takes to be relational", so, SQL just isn't a relational language to boot.

> "but I never really hear much about alternatives to it."

I invite you to ponder the possibility that you are trying to hear only in the wrong places (that is, the commercial DBMS industry exclusively).

> "So, are other good languages that serve the same purpose (database access) and what makes them better than SQL?"

Date&Darwen describe the features that a modern data manipulation language must conform to in their "Third Manifesto", the most recent version of which is laid down in their book "Databases, Types & the Relational Model".

> "Are there any good databases that use this alternative language?"

If by "good", you mean something like "industrial-strength", then no. The closest thing available would probably be Dataphor.

The Rel project offers an implementation for the Tutorial D language defined in "Databases, Types & The Relational Model", but the current prime goal of Rel is to be educational in nature.

My SIRA_PRISE project offers an implementation for "truly relational" data management, but I hesitate to also label it "an implementation of a language".

And of course, you might also look into some non-relational stuff, as some have proposed, but I personally dismiss non-relational data management as multiple decades of technological regression. Not worth considering, that is.

Oh, and by the way, a software system that is used to manage databases is not "a database", but "a DataBase Management System", "DBMS" for short. Just like a photograph is not the same thing as a camera, and if you are discussing cameras, and you want to avoid confusion, then you should be using the proper word "cameras" instead of "photograph".

Perhaps you're thinking of the criticism C. Date and his friends have uttered against existing relational databases and SQL; they say the systems and language aren't 100% relational, and should be. I don't really see any real problem here; as far as I can see you can have a 100% relational system, if you want, just by disciplining the way in which you use SQL.

What I personally keep running into is the lack of expressive power SQL inherits from its theoretical basis, relational algebra. One issue is the lack of support for the use of domain ordering, which you run into when you work with data marked by dates, timestamps, etcetera. I once tried to do a reporting application entirely in plain SQL on a database full of timestamps and it just wasn't feasible. Another is the lack of support for path traversal: most of my data look like directed graphs that I need to traverse paths in, and SQL can't do it. (It lacks "transitive closure". SQL-1999 can do it with "recursive subqueries" but I haven't seen them in actual use yet. There are also various hacks to make SQL cope but they're ugly.) These problems are also discussed by some of Date's writings, by the way.

Recently I was pointed at .QL which appears to address the transitive closure issue nicely, but I don't know whether it can resolve the issue with ordered domains.

Take a look at LINQ to SQL...

Tried it out a couple months ago and never looked back....

Direct answer: I don't think there's any serious contender out there. DBase and its imitators (Foxpro, Codebase etc) was a contender for a while, but I think they basically lost the database query language war. There have been many other database products that had their own query language, like Progress and Paradox and several others I've used whose names I don't remember and surely many more that I never heard of. But I don't think any other contender even came close to getting a non-trivial share of the market.

As simple proof that there is a difference between a database format and a query language, the last version of DBase I used -- many years ago now -- offerred both the "traditional" DBase query language and SQL, both of which could be used to access the same data.

Side ramble: I wouldn't say that SQL sucks, but it has many flaws. With the benefit of the years of experience and hindsight we now have, I'm sure one could design a better query language. But creating a better query language, and convincing people to use it, are two very different things. Would it be enough better to convince people that it was worth the trouble of learning. People have invested many years of their lives learning to use SQL effectively. Even if your new language is easier to use, there would surely be a learning curve. And how would you migrate your existing systems from SQL to the new language? Etc. It can be done, of course, just like C++, C#, and Java have largely overthrown COBOL and FORTRAN. But it takes a combination of technical superiority and good marketing to pull it off.

Still, I get a chuckle out of people who rush forward to defend SQL anytime someone criticizes it, who insist that any problem you have with SQL must be your own ineptitude in using it and not any fault of SQL, that you must just not have reached the higher plane of thingking necessary to comprehend its perfection, etc. Calm down, take a deep breath: We are insulting a computer language, not your mother.

Back in the 1980's, ObjectStore provided transparent object access. It was kind of like an RDBMS plus an ORM, except without all those extra leaky abstraction layers: it stored objects directly in the database.

So this alternative was really "no language at all", or perhaps "the language you're already using". You'd write C++ code and create or traverse objects as if they were native objects, and the database took care of everything as needed. Kind of like ActiveRecord but it actually worked as well as the ActiveRecord marketing blitzes claim. :-)

(Of course, it didn't have Oracle's marketing muscle, and it didn't have MySQL's zero-cost, so everybody ignored it. And now we try to replicate that with RDBMSs and ORMs, and some people try to argue that tables actually make sense for storing objects, and that writing giant XML file to tell your computer how to map objects to tables is somehow a reasonable solution.)

The general movement these days is NoSQL; generally these technologies are:

• Distributed "hashtables" that store data as key/value pairs
• Document-oriented databases

Personally I think there is nothing wrong with SQL as long as it fits your needs. SQL is expressive and great for working with structured data.

I think you might be interested in looking at Dataphor, which is an open-source relational development environment with its own database server (which speaks D), and the ability to derive user interfaces from its query language.

Also, it appears Ingres still supports QUEL, and it's open source.

SQL works fine for the domain for which it was designed — interrelated tables of data. This is generally found in traditional business data processing. SQL doesn't work that well when trying to persist a complex network of objects.

If your needs are to store and process relatively traditional data, use some SQL-based DBMS.

In response to your edit:

If you're looking for alternatives to the SQL DML for retrieving data from relational data stores, I've never heard of any serious alternative to SQL.

The knocks SQL gets are not, I think, so much against the language as opposed to the underlying data storage principles on which the language is based. People often confuse the language SQL with the relational data model on which RDBMSes are built.

Relational Databases are not the only kind of databases around. I should say a word about Object-Databases as I havn't seen it in responses from others. I had some experience with the Zope python framework that use ZODB for objects persistency instead of RDBMS (well, it's theoretically possible to replace ZODB by another database within zope but the last time I checked I didn't succeed to have it working, so can't be positive about that).

The ZODB mindset is really different, more like object programming that would happen to be persistent.

ORM can be seen as a kind of language

In a way I believe the Object-database model is what ORM are about : accessing persistent data through your usual object model. It's a kind of language and it's gaining some market share, but for now we don't see it as a language but as an abstraction layer. However I believe it would be much more efficient to use an ORM over an Object-database than over SQL (in other words performance of ORMs I happened to use using some SQL database as base layers sucked).

SQL is de-facto.

Frameworks that try to shield developers from it have eventually created their own specific language (Hibernate HQL comes to mind).

SQL solves a problem fairly well. It is no more difficult to learn than a high level programming language. If you already know a functional language then it is a breeze to grasp SQL.

Considering the leading database vendors providing state of the art databases (Oracle and SQL Server) support SQL and have invested years into optimization engines, etc. and all leading data modelling software and change management software deals in SQL, I'd say it is the safest bet.

Also, there is more to a database than just queries. There is scalability, backup and recovery, data mining. The big vendors support a lot of things that even the new "cache" engines don't even consider.

There are many implementations of SQL (SQL Server, mysql, Oracle, etc.), but there is no other language that serves the same purpose in the sense of being a general purpose language designed for relational data storage and retrieval.

There are object databases such as db4o, and there are similar so-called noSQL databases that refer to just about any data storage mechanism that doesn't rely on SQL, but most commonly open-source products like Cassandra based loosely on Google's Bigtable concept.

There are also a number of special-purpose database products like CDF, but you probably don't need to worry about those - if you need one, you'll know.

None of these are equivalent to SQL.

That doesn't mean they're "better" or "worse" - they're just not the same. Dennis Forbes wrote a great post recently breaking down a number of the strange claims surfacing against SQL. He maintains (and I agree) that these complaints originate largely from people and shops who have either picked the wrong tool for the job in the first place, or aren't using their SQL DBMS properly (I'm not even surprised anymore when I see another SQL database where every column is a varchar(50) and there's not a single index or key, anywhere).

If you are implementing yet another social networking site and aren't too concerned with ACID principles, by all means start looking into products such as db4o. If you are developing a mission-critical business system, however, I highly highly recommend that you think twice before joining the "SQL sucks" chorus. Do the research first, find out what features the various products can and cannot support.

---

Edit - I was busy writing my answer and didn't get the question update from a few minutes. Having said that, SQL is essentially inseparable from the DBMS itself. If you run a SQL database product, then you access it with SQL, period.

Perhaps you are looking for abstractions over the syntax; Linq to SQL, Entity Framework, Hibernate/NHibernate, SubSonic, and a host of other ORM tools all provide their own SQL-like syntax that is not quite SQL. All of these "compile down" to SQL. If you run SQL Server, then you can also write CLR Functions/Procedures/Triggers, which allows you to write code in any .NET language that will run inside the database; however, this isn't really a substitute for SQL, more of an extension to it.

I'm not aware of any full "language" that you can layer on top of a SQL database; short of switching to a different database product, you're eventually going to see SQL on the pipe.

Problems with SQL have motivated me to cook up a draft query language called http://c2.com/cgi/wiki?TqlRoadmap">SMEQL</a> over at the http://c2.com/cgi/wiki?WelcomeVisitors">Portland Pattern Repository wiki. Comments Welcome. It borrows ideas from functional programming and IBM's experimental Business System 12 language. (I originally called it TQL, but found later that name was taken.)

Within the .NET world, while it still has a SQL-esque feel to it, LINQ-to-SQL will allow you to have a good mix of SQL and in-memory .NET processing of your data. It also simplifies a lot of the lower-level data plumbing that nobody really wants to do.

If you want to see a database type of a completely different mindset, take a look at CouchDB. "Better" is obviously a relative requirement and this sort of non-relation database is "Better" but only in certain scenarios.

SQL the language is very powerful, and relational database management systems have been and still are a huge success. But there is a class of application that requires very high scalability and availability, but not necessarily a high degree of data consistency (eventual consistency is what matters). A variety of systems get better performance and scaling than an RDBMS by relaxing the need for full ACID compliant transactions. These have been named "NoSQL", but as others point out, this is a misnomer: that perhaps they should be called NoACID databases.

Michael Stonebraker covers this in The "NoSQL" Discussion has Nothing to Do With SQL.