Ch.10 SML# feature: seamless SQL integration

§ 10.2. Integrating SQL in SML#

We observe that SQL SELECT command is an expression that construct a table from a set of tables specified in FROM clause. In practice, SQL commands are usually evaluated against a particular database connection. Since database connection is conceptually a set of tables, if we generalize SQL as a language that takes a database connection and returns a relation, an SQL command can be regarded as a function that takes a set of tables and returns a table. As we have already observe that SQL SELECT is an algebraic expression. Then SQL is a functional language on tables.

Since tables are set of records, table value functions should be typable using record polymorphism. According to our analysis, there is one subtle point in typing beyond record polymorphism [13]. For this reason, SML# introduced the following special syntax for SQL query functions.

_sql db => select as name, #P.age as age
      from #db.Persons as P
      where SQL.>(#P.salary, 10000)

_sql db => ... represent a function that takes a database connection through parameter db. SQL.> is SQL primitive for numerical comparison operation defined in the SQL module that implement various SQL specific primitives. #P.Name selects Name field from P. #db.Persons selects Persons table from db. These correspond to #Name P and #Persons db in SML# expression. In _sql x => expr expression, we choose these syntax that are closer to SQL SELECT commands.

For this expression, SML# infers the following polymorphic type.

val it = _
     : ['a#{Persons: 'b},
    'b#{age: 'f, name: 'd, Salary: int},
    'd::{int, word, char, string, real, 'e option},
    'e::{int, word, char, bool, string, real},
    'f::{int, word, char, string, real, 'g option},
    'g::{int, word, char, bool, string, real}.
      ('a, 'c) SMLSharp_SQL_Prim.db
        -> {age: 'd, name: 'f} SMLSharp_SQL_Prim.query]

This type indicates that this query is a function from a database connection of type ('a, 'c) db to a table of tuple {age: 'd, name: 'f}. 'a represents the structure of the input database. Type variable 'c is introduce to enforce database connection consistency.

This is indeed a most general polymorphic type of the above query, By the fact that SML# can infer a most general polymorphic type, we are guarantees that SQL can be used in SML# based on ML programming principle: “expressions are freely composed as far as they are type consistent”, i.e. SQL expressions can be freely combined with any other language construct of SML# as far as they are type consistent. This will open up flexible and type safe programming using databases.