Unifying programming and database query

Question

Consider a common tutorial for object-oriented programming languages like C++ or Java: create a simple order processing system with objects representing accounts, orders, items etc (or something more or less equivalent). Makes perfect intuitive sense, but the elephant at the dining table is that it's not real because these are in-memory objects; in a real system, accounts, orders etc don't actually live in memory in the first place, they live in a database, with the memory representation only a short-lived mirror thereof.

You could write a lot of code yourself to read and write from the database, but that's so tedious and error-prone that nobody actually does it.

Everyone ends up using an ORM, but those are so problematic in their own right that a famous paper calls them 'the Vietnam of our industry'.

I don't think that's a mismatch between object and relational so much as a mismatch between the programming language and the database being separate things that don't know about each other. Conjecture: the solution is to have a single language that is both the programming and database query language, which in turn would require that the language runtime also be the database, and the JIT compiler also be the query optimizer.

So that's the summary of the problems I see it. My question is, has anyone yet either,

1. Actually built such a unified system

2. Tried but failed to build such a unified system

3. Written anything substantial on the topic of how you would go about building such, or why or why not

4. Come up with an alternative way to solve the problem?

Answer 1

This is my opinion. While I do see where are you coming from, I just can't see it happening from design perspective.

Data persistence is extremely complex subject. And so are programming languages. Combining the two would result in complexity explosion. It would take lots of effort to actually make both good enough for people to actually want to use it. I think already mentioned MUMPS is good example. Or you could look at various SQL variants that have full language bolted on top of them. They might be usable, but I don't think people would willingly use them.

So separating them is clear way how to solve this complexity. Also, by not tying them together, it allows both to be changed and evolved over time. For example, SQL is old and didn't change much since it was created. But languages used to run applications changed drastically over same period. And now, opposite is happening. Languages remain mostly same while databases are being changed and evolved.

Runtime deployment is another problem. Combining the two would mean both database and the application or web server would have to run in a same process. This is extremely limiting, both from maintenance perspective and from ability to run them on separate computers or in many-to-one relationships.

Splitting the two into separate modules with clear API between them is best way to keep the complexity down and give you flexibility in what technologies you want to use and how the final pieces are deployed.

Answer 2

It seems like you're making some major assumptions. For example, you're assuming that everyone is writing to relational databases. That's simply not the case, there are lots of examples of databases of other flavors (object dbs, document dbs, etc) that use a native programming language to write all the code and manage persistence.

For example, Db4O is compatible with both Java and C#, ObjectDB in Java, VelocityDB in a variety of languages. MongoDB's drivers all end up having you write stuff in your native programming language (bonus if you're doing JavaScript, since that means the shell uses the same syntax too), and so on.

There is a LOT of discussion in various places about which DB engines are better in what contexts, and why, far too much for the scope of this answer, to include a closed question on this very site. The upshot is that they're each optimized for different things, and until recently SQL was considered a sort of 'lowest common denominator' for business applications because you get a lot for free in terms of ACID and performance (though both of those are changing recently as architectures and requirements change).

It's also worth noting that there actually were a lot of "all in one" kinds of approaches before. Mainframe languages often had their own persistence logic built in, and there are languages like Smalltalk that don't differentiate between code and data at all. Again, they are often good for some use cases but not all.

Answer 3

1. Yes (not me). It was called MUMPS.

2. According to this this former SE.SE question, or this article, MUMPs was not very well designed. But is was indeed used in the health industry (and I guess there are still existing systems using it), so not a total failure.

3. You will surely find information about it now you know for what to search. Start with the Wikipedia link above.

4. Search for Object-Oriented databases, many of them are language-specific. They tried to address the object-relational mismatch in simpler ways than ORMs.

Answer 4

There are indeed multiple systems which unify database and programming language into a single environments.

Smalltalk is probably the closest to what you describe. Object in memory are persisted in an "image", so the language environment can be used an an (object) database out of the box. And most modern language have some kind of built-in persistence mechanism which means objects in the language environment can be persisted and queried using the language itself.

This is very convenient for single-user applications. But the approach will not scale to multiple users, since they will need the share the same memory space, which obviously puts a limit on the amount of users. A scalable solution requires a separate database server which manages concurrency. Even then, there are multiple NoSql-databases which integrates with a specific language environment and allows you to persist and query objects in the language itself.

From the relational side of the things we have languages like T-SQL which is a full fledged programming language which is a superset of SQL, so querying and DML can be intermingled with arbitrary complex procedural logic. Complex business application have been built using T-SQL so this is certainly doable, but the current trend is to procedural business logic away from the database.

In these cases it is indeed very elegant and convenient to have the database integrated with the programming language and avoid the "impedance mismatch". So why does people still use relational databases seperate from the programming environment and try to bridge with some ORM-kludge?

It turns out there are a number of advantages to having data and querying separate from any specific programming language and environment.

• Data independence. In most organizations data is actually accessed by multiple applications. A shop might have a database used by the web frontend, by a customer support tool, a reporting engine and so on. The data itself is often long-lived, while applications come and go. Coupling the data to one specific programming environment would be lock-in to a specific programming environment. But programming languages come and go, while data lives forever.
• Ad hoc querying. It is extremely convenient to be able to open a database prompt and write a query. If querying were tightly coupled to the programming environment this would be a programming task and only developers would be able to do it.
• Avoid lock-in. Since SQL is a standard, multiple vendors may provide database management systems which are more or less interchangeable. This avoid vendor-lock in, and makes it easier to compare products.
• Loose coupling. Having a well-defined interface between application layer and database makes it possible to tune and optimize the database independently of application logic.
• Shared interface. Since the database interface is independent of application logic, off-the-shelf tools can be used for profiling, replication, analysis and so on.

Answer 5

It is pretty good question which I was processing in my head many times. One example of an existing solution solving your problem is a graph database ArangoDB in which you use JavaScript (running on internal engine) to write controllers which can generate whole web pages. Data is passed to/from storage using JSON so it's natively accessible in JavaScript and queries are done in an embedded query language. So this case is an example of extending JavaScript to be run in the database.

In practice such controllers should not be exposed to the public for security reasons as a flaw in the database configuration or a bug will result in exposing your precious data to the public.

In my personal opinion this is a good approach and considering if the database supports a kind of map/reduce feature which will update aggregated data / text indexes and other frequently queried data in real time, adding a thin security layer in between (call it load balancer) would make a functional application running in a distributed database.

Answer 6

1. Actually built such a unified system

Yes, I did that in Sciter.

Sciter's script is a "JavaScript++" that has built-in persistence:

var ndb = Storage.open(pathname);
ndb.root = { ... root object ... };

Where ndb.root is normal Object in terms of JS. All its properties and sub-objects accessible from it are persistable - stored and fetched in the DB when needed - transparently for the code:

ndb.root.accounts[2].firstName = "John";
var name = ndb.root.accounts[3].firstName;

Data is stored in DB either on GC cycle, when not enough memory, or on explicit ndb.commit() call.

Storage class is accompanied by Index class - persistable ordered object collections with unique/non-unique keys.

Feature set is similar to MongoDB or other NoSQL databases but id does not require separate ORM - db access is made by purely script means.

Answer 7

I'm absolutely for it and I have no idea where to start. SQL can be brilliant and I imagine it would be great to have all that power and the transactional guarantees right in your all-purpose programming language (instead of having to write queries as collections of strings or using, god forbid, an ORM).

The only system approaching your idea that I know of is called aquameta (tag line: "web dev stack built in PostgreSQL"; see https://github.com/aquametalabs/aquameta, http://aquameta.org). There are some intro videos that are not a bit less crazy than the idea itself (youtube.com/watch?v=jz74upW7TN0, youtube.com/watch?v=cWGm9eYUYUk&t=29s, youtube.com/watch?v=xRoUQGUmiMg), and when I say crazy, I mean that they implemented their own editor and their own version control system inside Postgres.

Answer 8

I think this was exactly the rationale for Microsoft's invention of LINQ. It has been in full scale production use for several years so it's easy to find literature about it and reflections from real world experiences, both positive and negative. (Most .net development shops embrace it.)

A good starting point on linq: https://docs.microsoft.com/en-us/dotnet/csharp/linq/