Are there any programming languages that have a built-in database as a first-class language feature rather than connecting to an external SQL (or other) database? What would be the drawbacks and benefits to such a feature? What would such a feature look like and how would it change the way we program?

.NET has LINQ for SQL, which I think is the right approach to a general problem. You should not lock into a particular database, and you cannot make something that is general enough, and yet implement every feature of everything that is out there. LINQ is still awesome, just as I am.
–
JobDec 3 '10 at 18:06

3

and unfortunately, Linq2EF has some nasty microsoft-only extensions, which means you get locked into SQLServer with it if you do anything complex.
–
gbjbaanbDec 30 '11 at 12:37

Can you expand on why it's an evolutionary dead end? I'm not thinking of implementing one, I'm just curious.
–
VirtuosiMediaDec 3 '10 at 6:39

1

there are orders of magnitude difference in call time local vs. remote API, add more if a network interface exists. There are still distinct and real advantages to using languages in a DB stack.
–
XepochDec 4 '10 at 1:18

1

@David, re-read the question, pray tell what language would this ever be that would not be for vendor lock?
–
XepochDec 8 '10 at 5:41

Languages are "small" and databases are "big"; so whenever the two are combined, it's not a language with the database as a features, but a database with the language as a feature. Many databases have some proprietary languages tacked to them, e.g. PL/SQL, T-SQL.

Rei: You haven't taken a closer look at PL/SQL, have you? BTW, Oracle includes a JVM in the RDBMS, too.
–
user281377Feb 25 '11 at 12:31

2

Rei: Actually, many people use PL/SQL to write applications, at least the business logic part. As you might know, PL/SQL is also the language used in Oracle Forms, so you could basically write and run a PL/SQL program that never touches a database. In practice, PL/SQL is used in conjunction with an Oracle RDBMS, though.
–
user281377Feb 25 '11 at 13:22

1

Well damn, never would have thought.
–
Rei MiyasakaFeb 25 '11 at 21:03

I don't necessarily think the right question is "why aren't there?" but "why should there be?". What would be gained from having databases be a feature of the language? Remember, the language is at the very bottom of the programming stack. Making a language bloated affects everything. Thus, language designers need to be slow to add new features, especially ones that would involve such an investment.

Because you'd want the benefits of type checking and even simple name checking, when you are working across the boundaries of the query language and the programming language.
–
MacneilDec 3 '10 at 6:01

4

@Macneil, ORM tools do that now. What should it be bolted into the language when APIs can do it?
–
salDec 3 '10 at 14:51

Pick and MUMPS were developed years before the first academic paper on relational databases (which was about a decade before the first commercial SQL based database system made it to the market - from a company we now call Oracle; IBM's first attempt at a product fizzled and a successful SQL based system was later). You might find them still in use (our local public transport system used Pick until recently for the trip planning system). You want nothing to do with either Pick or MUMPS, and the best advice I can give is "step away from the keyboard with your hands in the air!" If you do have anything to do with them, the phrase "you'll be sorry" should be ringing in your ears.

Microsoft Access gets severely mocked and criticized in IT circles as it is quite easy for a non-developer to make a critical business app out of Access and have it mutate into something that the company quite literally cannot live without. It is also quite likely that quite a few developers got their start in developing via MS Access and as things kept getting bogged down they learned how to fix them (the first step is traditionally learning visual basic and rewriting the Access app first in VB, then in something "better"). It is possible to make a well behaved Access app that runs distributed with a huge amount of data - I've seen it done - but there are easier ways to do things, and it takes far less skill to make (and maintain) a well behaved app out of VB and SQL Server.

Since SQL Server 2005, Microsoft has introduced the capability to put CLR into stored procedures and functions. And if you want to be tricky about it, you could make datatypes that you could then use as columns in the database. I think Oracle has had something similar with Java.

That being said, I don't think there is anything stopping you from creating one, or hypothesizing about them. Pick and MUMPS are older than most coders here and reflect a very COBOLy way of looking at the world.

My personal advice is to keep things separate. Use a language that is good at manipulating the data your project needs (with the caveat that sometimes the "best" language is one that you can easily find programmers who can read/write the code). Use a database system that is good at keeping the data your project needs.

Access isn't a programming language, its more of an integrated development and runtime environment. The language it uses, VBA is the same language used for macro programming in the other office products and isn't Access specific. DB access is still done through the various providers to the JET drivers.
–
JeremyDec 3 '10 at 14:29

1

In addition to the three you mentioned, there are several 4GL environments: Oracle Forms, CA OpenROAD (nee Ingres Windows4GL), and Unify's Accell (just to name the ones I've worked with).
–
TMNDec 8 '10 at 15:08

Adding database into a programming language might cater only to a very narrow set of users. What if they want to use some non RDBMS features? Or don't want to use a database at all? The compiler will unnecessarily be bloated for such use cases.

Well, first, you're asking why the framework the language operates in doesn't provide a database. A language is simply a means of expressing something that you want done in set grammers; it doesn't really provide services like that. :)

That said, there are several reasons.

Building an efficient database storage system is a hard problem, probably on the order of or greater than building the .NET Framework (for instance). If a team tried to include a database in their framework, that would be all they ended up working on.

A database that gets load should be on its own separate machine and not in the process of the code that's accessing it.

ORMs provide a lot of the type safety and compile time checking that would be the benefit of such an action, without actually having the framework try to be a database.

That said, I guess it would be neat to include some sort of SQLite implementation within the framework that applications with smaller needs for data access could operate against. I'm not sure it would be useful in non-trivial applications, however.

I think your actual question is "why aren't there any programming languages that come with database libraries".

General purpose languages treat all IO as one and the same, be it writing or reading to/from a disk, a webcam, the network, the screen, a location in memory -- it's all IO, and that's all that programming languages concern themselves with.

In fact, aside from reading/writing to the heap and stack, most programming languages don't even do any actual IO. Some languages provide native features for expressing IO operations (e.g. the printcommand in BASIC), but most languages just treat them as normal function calls (e.g. printf in C) and let the libraries handle the actual writing.

Some languages like C# offer language features for expressing queries, but even then, those are just expressions on the most basic data structure of lists (or IEnumerables, as they're called in .NET) that get translated into SQL operations by libraries -- the language itself is still just working with very abstract notions of IO.

As for why building a database package into a programming language's standard library isn't a good idea, it's most likely because nothing else in a standard library would normally depend on database functionality.

As written, the question is partly wrong, as some counterexamples above have shown.

So I'd first refine the question to read, "Why is a DBMS usually not integrated as a feature of a general-purpose, high-level programming languages?"

This is for the same reason that other software products such as operating systems, file systems, web servers, caching layers, etc. are usually not built-in. General purpose languages generally operate at a level of abstraction above that of such products . So it's reasonable for a programmer to implement a DBMS in a general-purpose language, and that DBMS might even expose aspects of its parent language or a DB-specific, declarative language for use by DB programmers. But there are too many design options in writing a DBMS for it to be wise to fix them in a general-purpose programming language. If you do fix them, you wind up with a case like MUMPS, where the entanglement of the two results in an entire industry mired in a chicken-and-egg problem, stuck with an obsolete DBMS and an obsolete programming language.

Yes. The languages on the AS/400 platform have native, first class support of databases.

This is because that the AS/400 platform has the database fully integrated everywhere and allows for a lot of very nice features, like ease of navigation through a resultset updating the values on the way through.

Erlang has Mnesia built in. Mnesia is a soft real time distributed database. Actually it is quite nice to use in a lot of ways. For one thing you can distribute it over several nodes with almost no effort. Secondly you don't need any form of ORM as it stores native Erlang data structures which is very handy.

You can also do things like list comprehensions over tables which is just really handy. Of course you can also use your favorite SQL or NoSQL database as well.

Depends on the language and the platform. For instance, it is quite trivial for me to use a variety of databases while working with C, I just use the appropriate library.

A language must maintain its standard implementation, and that typically means providing the minimum amount needed for someone to be able to build whatever they want to build. Everything else becomes a library, or perhaps an extension to the language that is maintained by others.

At least, this is the case for languages that follow standards established by organizations such as ISO.

Used to work in NonStop/SQL which was fully integrated into NonStop/C, NonStop/C++, NonStop/Cobol, NonStop/Fortran, and probably other languages as well as being fully integrated with NonStop/Guardian, the operating system on which the computers ran.

I think that's probably the closest integration you can get, where the database IS the filesystem of the operating system.
It's also a dead end, there's no way to decouple any of the components, the database, the operating system, the hardware, and any software written on it can never be used separately, ported to another environment.

The closest you're going to get on a PC would likely be MS Access, Embarcadero/Borland Delphi being a close second.

After that, you're looking at embedded databases in your application, which can have limited appeal to those creating standalone applications that need a set of hierarchical data that's not easily stored in a simple configuration file and/or needs regular updating as the application runs. Or for people wanting to have a portable version of an application that maintains a snapshot of part of a larger database and maybe synchronises that with the larger database at times when the application can make connection (handy for say a salesman who's often out of reach of the corporate network yet needs sales data for his group of customers, or a doctor in the field who wants patient records but can't connect to the hospital network because there's no network access where he has to go).

As former Visual Foxpro developer, I tough that is odd that no mainstream language define the relational model as part of the language.

Having the full database engine is not good idea, but having the "SQL" language instead could be VERY useful.

In OO, exist the impedance mismatch. This happened because objects and set not like each other. But if a language let me define TABLES, FIELDS, RELATIONS, CONSTRAINS, etc (without tie it to a particular storage) will be very powerful. Plus, making a ORM will be more 1-to-1 mapping.

True, but that's a part of the library, not a core part of the language. A lot of languages distribute SQLite as a part of the language's standard library, including Python and Ruby.
–
mipadiDec 7 '10 at 20:06