SQL-based programming language for web development

[johnaau]

Project description

As most web sites/applications are database-driven these days, I think there may be a place for a new programming language/environment, where web and database are one system.

Let’s look at some of the way we may be developing database-driven websites/applications now…

We write our SQL, put it in a string in PHP and send this request to MySQL which returns the data we need, which we then translate and present in HTML.

Or we have a model of our database in PHP and we use a query builder to create our query which gets translated to SQL which we then send to MySQL which we then translate into our model and then present in HTML.

Or maybe we are also using database stored procedures.

Could this be improved by producing our website directly in the database? And database-driven web development could require one less language to learn and master.

What I am thinking is a hybrid language combining the query functionality of SQL with the functionality of a web programming language like PHP.

The database structure would be known, so there would be no need to create models (although you might have custom views).

Even though the application and database code would be one, the system would of course need to allow for separation of concerns. It would also have to allow for multiple sites/applications using the same data source.

I’m not sure if this project would be best as an entry-level language (as PHP once was), possibly something like SQLlite combined with PHP. Or whether it would be best as a larger framework like MySQL combined with PHP, that can scale across multiple servers, and something that can take advantage of the likely performance benefits of a combined system.

I’ve got more ideas of how this could work if there’s any interest.

Relevant Technology

Completely open. Possibly base on an existing open source database and/or programming language.

Who is this for

Would require experienced developers.

[Thrilleratplay]

This sounds like a security nightmare trying to prevent SQL injections.

[johnaau]

@Thrilleratplay, I would say just the opposite. You would be writing in SQL so a parameter value could only be a parameter value, it couldn't contain anything else like SQL comment, closing delimiter, or other SQL statement

[ahopkins]

Interesting idea. I have some thoughts on how to run with something like this, but not sure if it is the same ideas as what you have.

Instead of creating a new database, what if we create a thin layer on top of the DB to translate JSON requests to SQL statements, and get a response. Essentially a very little API framework, leaving all the data manipulation for the client side. Basically something similar to what elasticsearch has for interacting with the data, except for relational DBs. And, with drivers for the major players (Postgres, mysql, sqlite).

This would achieve some of those concerns about allowing for multiple systems to tie into the same data.

[georoot]

isn't that what an ORM is ?

[ahopkins]

@georoot ORM (in my opinion) is an easier way to abstract and tie functionality to data. It does not inherently remove the middle layer. What I am understanding the idea to be is a sort of "do away" with the middle portion. A data store and a front end. The middle component therefore becoming a translation point instead of a logical driver.

[bobwhitelock]

@ahopkins: this sounds a bit like what you're describing: https://github.com/postgraphql/postgraphql.

[johnaau]

My original idea was to combine a web programming language with a database query language.

As most applications are database dependant it would make sense to me to combine these two together.

I appreciate that it is common practice to have separation between application and data storage but this has disadvantages…

We might write SQL as a string and send this request to the database server, which is easy to make errors, and not always easy to maintain. We also have to map the results to different variable types.

We might use a tool (ORM/query builder) to simplify our database queries. This might be less error prone (particularly in strongly typed languages). The queries may also not be optimal and they might be database server agnostic so not optimised for the particular server being used. We also have to maintain our ORM models when the database structure changes.

Then there is the performance issues relating to connecting and passing data between two systems.

My suggestion was to create a programming language where database query was an integrated part of the language. Maybe even the web and database servers would be one server too.

Maybe you remember the “old” days where we had application development environments like dBase where you could develop you whole application and database in one system?

I use C# and Linq to SQL a lot and I like the way it works but it has disadvantages such as you still need to maintain a model of your database structure, and there is translation from Linq queries to SQL queries, so to produce optimal queries you need to be an expert at Linq to SQL queries and also at SQL queries so you can ensure that the Linq-generated SQL is optimal.

I know that this goes against common practices and would mean no ability to switch database servers but that is not relevant to a lot of organisations and applications. Perhaps this system would not be targeted at large enterprise level applications where 18 levels of separation is expected.

I don’t know how well I have explain what’s in my mind. I’ve got more details and ideas if there’s any interest.

[georoot]

Okay so i am still unclear what the whole project is about but let me take a go at it. What you are trying is that somehow you have a language that acts as both api and datastore so you don't have to write controllers for each route. I am guessing there is a permission table similar to what sql offers for database.

Now that being the case, when we deploy an application( take docker for instance) the same database is shared by multiple other apps. Also apps are unsecure and will crash ( practically speaking), thats why the concept of load balancing and having another container ready for failsafe. Now if you are combining the roles of datastorage and controller view the IDK , if an app fails the database also shuts down for other apps. There would be issues with locking,permissions and atomicity being a new project.

I can't say if its a nice idea but is definately a lot of work for something that already exists. Also if you want database to be a part of language, you can run sqlite directly from fs without a server thats pretty close to what i understand you are saying.

[johnaau]

I am not really clear on my own idea of a project either, haha. I have some ideas though which I was seeing if there was anyone with similar interests so that these ideas might evolve.

My main gripe with modern development environments is the separation of application and database coding. Given that the majority of applications, particularly web, are database-driven it seems that there should be less separation.

Although accepted as the norm, I consider tools like ORMs and query builders to be bandaid solutions to handle the separation (and I might be the only one who thinks this).

I am not talking about a revolutionary change here. Not something that can’t already be done. Just something that could be done a lot better.

Let’s look at how we might access our database now:

1. Write SQL queries in strings and send them to the database server. GOOD = queries can be as efficient as possible, BAD = error prone
2. Use an ORM and have it generate your simple CRUD queries. GOOD = okay for simple stuff, BAD = complex queries
3. Use a query builder/translation tool of some sort (possibly as part of ORM): GOOD = less error prone, BAD = generated SQL may not always be good quality, an extra layer of inefficiency

So what I am suggesting is a language where database query is “built-in”.

I’m not suggesting that my proposed system would not have solution for load balancing and redundancy. If it was aimed at the enterprise level then these features would be essential.

On the other hand, there’s plenty of non-enterprise level hosting where web and database servers are on the same piece of hardware with no load balancing redundancy and a failure of application or database means a failure of the whole system. This is very commonplace, for non-enterprise shared hosting and even VPS and dedicated hosting.

[ahopkins]

@BobWhitelock Sort of ... but not really.

@johnaau I sort of understand your thought, and it seems like there are two potential routes:

1. create an abstraction layer that operates sort of "invisibly" to generate the SQL; or
2. create a new database with this functionality.

I suggested option 1 because it allows for the use and reuse of existing databases without the need to reinvent that wheel. I think what you are suggesting is more like option 2. Yes, my idea sort of overlaps with ORMs, but my suggestion is different.

An ORM requires the developer to do something, and then expose it to an API. I am suggesting to make an API that can be called from a Javascript client (or anywhere for that matter) then create and execute the SQL. The developer would not need to do anything, and all "logic" would need to be handled in the client.

To begin, I would suggest keeping authentication separate. An MVP that simply translates the API CRUD calls to the appropriate SQL operations. And, to make it even easier for the developer, perhaps use POST/PUT/PATCH for various logic, something like this:

POST Runs a INSERT statement PUT Checks the fields in the payload against the DB schema, updates the DB schema (add fields, etc), then runs INSERT PATCH Runs UPDATE statement

This gives the developer some flexibility. They can push data to the DB knowing they will not break their schema and rely upon the enforcement rules. Or, they can push data to the DB knowing that what they push will be stored.

I agree that this is likely NOT a good enterprise solution. And, likely NOT the best choice for a lot of applications since you lose out that middle layer flexibility.

HOWEVER, as stated above, this has some benefits:

1. Easier for new developers to get up and running because there is less layers to learn in full stack development
2. Rapid prototyping and simple access to DB

I can already think of some small scale uses I could make for this.

[edsonmedina]

You might want to have a look at GraphQL

[cfcodefans]

I had same idea.

1. what program does? processing data with functions, sql select query is prefect and concise expression of functions

2. sql can be strong typed

3. dom tree on h5 html can also be operated by sql why did they name it J"QUERY"?

4. Streaming of data can be an infinite table, kafka also released new feature to use kafka sql to treat stream as table.

Most of programmers have to know some sql, but most never take sql as some serious programming language, therefore they invent(reinvent) ORM for every other programming language, so sql can be something more comfortable.

so can't we think in reversed way?

Personally I think the biggest obstacle is lack of IDE support

[paulcmal]

This reminds me of urweb, a crazy experimental language where SQL is embedded deep within so that you can't compile SQL injections and such.

Also worth noting, there is no distinction between client and server code in urweb. The same codebase is compiled to a binary server-side and a JS script client-side if i remember correctly.

[soaxelbrooke]

I think this is a fantastic idea.

Re: security - I think this can be very effectively handled by role-based access control, like PostgreSQL allows. This way, even if the end user is trying to access other's info, the rows simply don't show up to them.

[ahopkins]

I think dgraph is already pretty close.

https://github.com/dgraph-io/dgraph

The problem with GraphQL by itself is that it still requires you to create a middle layer that maps queries and mutations to your dB schema.

Dgraph had all the mapping internally and uses a GraphQL like syntax. It exposes an http endpoint so you don't even need a backend per se. I'm not enough of an expert with it yet to know the best way to handle authentication and security. But it seems a HUGE step in this direction.

[Pamblam]

I don't get it. If you want a language it might be helpful to post some possible example code. What would it's applications be? CMSes? SPAs? You say you want a language but as far as I can tell by your description you're thinking about an MVC.

My original idea was to combine a web programming language with a database query language.

Well, here's an old project I've been working on for the last few years, it's basically SQL implemented in Javascript for use in both node or a browser. The lexer and parser could easily be expanded to run functions but something like that would be so high level performance would probably not be ideal.

http://pamblam.github.io/jSQL/

[aquiandres]

Here is my two cents.

Just take a moment and think about GraphQL. It's an expressive query language for APIs and can be used as programming language -- if I correctly understood by your given description.

GraphQL is a query language for APIs and a runtime for fulfilling those queries with your existing data. GraphQL provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more, makes it easier to evolve APIs over time, and enables powerful developer tools.

GraphQL has built-in strong data types, and libraries written in many different programming languages allowing you to work on server- and client-side.

Then you have solutions built on top of GraphQL known as BaaS (Backend as a Service) -- a middleware between your database and your application. Of the many GraphQL BaaS out there, I've read quickly about Prisma it is open-source and I think it does a good job -- yet, I haven't had the time to play with it. This backend server allows you to work seamlessly with SQL and NoSQL database at the same time.

Prisma translates your GraphQL query to the corresponding query language of your database -- no matter whether it be a SQL or a NoSQL. Just take a look at this video on YouTube. Currently, at the time of writing this (8/8/18), Prisma officially supports MySQL and PostgreSQL with upcoming connectors to MongoDB and Elastic Search. However, the team is working to implement connectors in many flavours: relational, document, graph, key/value, search, time series, analytical, in-memory, and file-based.

How to GraphQL is a free and open-source tutorial if you want to learn all about GraphQL.

This may be a solution or at least a starting point to what you want to accomplish.

[sunk818]

@Pamblam nice work.

Does anyone remember ColdFusion from the 90s? It had its issues, but it was so easy to query the database and display it on a web page. There's an open source product for CFML called Lucee (was Railo)

[ahopkins]

@sunk818 I remember. I shudder at the thought ...

[ahopkins]

@aquiandres While I do not doubt that I think GraphQL is a step in the right direction here ... it is only sort of part way. It still requires a backend, and still requires you to create something.

Honestly, I am more excited by what the prospects of web assembly may bring in terms of network protocols that may be possible.

In the end I think the biggest obstacle to removing the application layer will be security.

[php-coder]

I'm glad that I'm not alone who came to idea that many apps are built around SQL queries and it might be beneficial to use them as a source of true.

I agree with @ahopkins that in 2020 we should only focus on backend side that exposes data through API:

I am suggesting to make an API that can be called from a Javascript client (or anywhere for that matter) then create and execute the SQL. The developer would not need to do anything, and all "logic" would need to be handled in the client.

To begin, I would suggest keeping authentication separate. An MVP that simply translates the API CRUD calls to the appropriate SQL operations.

It's turned out that the last month I worked on something similr: https://github.com/php-coder/query2app But I chose a little different approach: the tool generates code. It allows to start quickly while still have an ability to modify the generated code as needed. I have a plan to make templates customizable, so it will be possible to chose on what language an app is generated, what stack is used and so on.

[wmertens]

By exposing the SQL to the client, you can never change your database unless you have full control over your clients.

Also, business logic can be quite complex and you would have to implement it in the database itself. For example, a user can only upload an avatar for themselves, group admins can do it for users in their group.