Problem

I am quite tired of writing SQL that isn't modular or easy. There are no variables or ways to combine them like functions or variables. Is there no a SASS or LESS where writing CSS was a bit more modular or dynamic? I am not asking for a miracle replacement for SQL, merely a language or library that compiles to SQL which would make it easier for everyone to write it without too much pain.

Sketch

This is what I want to talk about, another language or library. My idea is to implement this in a functional language such as Haskell or implement it with the prototyping Python or Javascript or Common Lisp to make it easy for me. I do want this language to have a lot of editor support like Emacs, so in Emacs Lisp I thought to implement this. Ease of writing is one key point so choosing which language will serve as a prototype is important.

Of course, I can create a syntax or grammar for this but it would be better to see how it plays as a library first. So let's role with that without being grandeur. However, I do want to stress being functional in a sense where there is no side-effect or the fact that I am being obsessed with it.

So let's start with some sketches.

Snippets

Let's start with hypothetical use case: create two tables and join their there fields. Let say we Person and Relationship which is a table of people and their relationship to each other respectively. How about a quick schema to make this clearer.

  • person
    • personid
    • firstname
    • lastname
    • birthdate
  • relationship
    • personid
    • relatedpersonid

Please ignore the fact that I hate writing in all caps as SQL does. Let's assume the compiler also converts the naming convention to it's proper name. Moving on, let's create a pseudo syntax in creating these tables. I'll be sketching in Python because it is easy.

birth_date = Field('birth_date') # Short constructor, positional

person = Table.make( # Long constructor, keywords
    name = 'person',
    alias = 'p', # For later
    fields = [
        Field.make(
            name = 'person_id'
        ),
        Field('last_name'),
        Field('first_date'),
        birth_date
    ]
)

relationship = Table(
    name = 'person_relationship',
    alias = 'pr',
    fields = [
        Field.make('person_id'),
        Field.make('related_person_id')
    ]
)

I hope the snippet is readable enough and the ideas here are gotten across

  • Variables
  • Field and Table data type
  • Constructor function
  • Function keywords
  • Strings
  • Quick constructor with strings
  • Flatten list of lists(Wish list)

So our next step is to create the query for each one. Let's see.

print(Query.craft(person)) # Crafting from a normal table
# out >> select p.person_id, p.last_name, p.first_name, p.birth_date from person p

print(Query.craft(relationship)) # likewise
# out >> select pr.person_id, pr.related_person_id from person_relationship pr

all_person = Query.make(
    name = 'everyone', # Name functions as an id
    tables = [
        person
    ]
)

print(Query.craft(all_person)) # ditto

people_relationships = Query.make(
    name = 'everyelse',
    tables = [
        person,
        relationship
    ]
)

print(Query.craft(people_relationships)) # Outer join
# out >>
# select
# p.person_id, p.last_name, p.first_name, p.birth_date,
# pr.person_id, pr.related_person_id
# from
# person p, person_relationship pr

# Astute readers will realize that there are two person_ids here which will fail

person_relations = Link.make( # A way to refactor ON clauses
    name = 'p_and_pr',
    source = Table.getField('person_id', person), # Still experimental
    target = Table.getField('person_id', people_relationships)
)

relations_person = Link.make(
    name = 'pr_and_p',
    source = people_relationships,
    target = person
)

everyone = Query.make(
    name = 'everyone',
    table = [
        person,
        relationship,
        Table.setAlias('np', person)
    ],
    link = [
        person_relations,
        relations_person
    ]
)

print(Query.craft(everyone))
# out >>
# select
# *
# from
# person p
# inner join person_relations pr on p.person_ id = pr.person_id
# inner join person np on np.person_id = pr.related_person_id

I hope I got my point across with the following ideas.

  • Query and Link data type
  • .craft() function
  • Mutation creates another copy

Just a quaint idea to merge these two queries but I still have some problems specially with the Link type. I want the tables and the join clause to be separated. Ideally, you want to declare the tables only and the join clauses can automatically inferred but obviously it is more verbose at the moment and harder to join.

Assuming this works, the idea is now to declare the tables on the top, create a auto link mechanism, select the fields and make them queries. After that base is okay, having Emacs support them would be awesome.

Insights

Assuming this is works, the library should allow for extension and expansion. Given the base types, one can create new personalized types from it. With the vast majority of SQL vendor syntax, the language should allow not just to compile to SQL but to the vendor specific SQL and if willing compiles to an XML file of tables and automatic queries just for the MyBatis or for some third party dynamic SQL shiznit.

I may work on this on Haskell as my first project but I really want to stress the point that SQL should not be a pain to write. Anyway, I'm out of ideas anyway but this will haunt me until I implement this properly.