Database Integration

SML# 0.60 or above has SQL extension. This extension seamlessly integrates SQL queries as first-class citizens, which can be freely combined with any other language constructs in Standard ML.

SML# 0.60 uses PostgreSQL as a database server with which the extension interacts. To enable this feature, it is required that PostgreSQL server is running and C interface library of PostgreSQL (libpq) is available on your system. If your libpq is not installed in standard library path, you need to specify the place or name of the library by LD_LIBRARY_PATH or SMLSHARP_LIBPQ environment variable, like as follows:

 $ env LD_LIBRARY_PATH=/usr/local/lib smlsharp
 $ env SMLSHARP_LIBPQ=/usr/local/lib/ smlsharp

The SQL extension consists of the following constructs:

  • _sqlserver expression
  • _sql db => ... expression
  • _sqleval expression
  • _sqlexec expression
  • SQL structure

To connect a database server, first you declare the server and the schema type of the database which the server manages by _sqlserver special form:

 _sqlserver [string] : <ty>

where the [string] is an optional string literal denoting a server specification according to PostgreSQL syntax. If it is omitted, an empty string is used by default. <ty> specifies a set of tables with their types in the form of record types.

After the declaration, call SQL.connect with the server declaration to connect the server.

 SQL.connect : 'a SQL.server -> 'a SQL.conn

SQL.connect checks whether the type declared at _sqlserver matches the actual schema information of the database. If this check succeed, it returns a connection, otherwise it raises a runtime exception.

SQL queries are written in SML# program in the following form:

 _sql db => <query>

where db is a bound variable that abstracts a database and <query> is an SQL command for the db. For example:

 # val query =
       _sql db => select as name, #t.age as age
                  from #db.employee as t
                  where SQL.> (#r.salary, 50000)

SML# compiler type-checks SQL queries and gives a polymorphic query types to them.

SML# 0.60 supports select, insert, update and delete command as query constructs. The syntax of SQL commands are almost same as original SQL syntax except the following:

  • SQL commands in SML# programs must be an "explicit" form such that any name are represented explicitly such as by using "AS" keyword.
  • Any ML expression may appear as a part of SQL queries if its type is compatible with SQL.

To execute SQL queries defined in the above way, use _sqleval and _sqlexec special form:

 _sqleval query conn
 _sqlexec query conn

Both of _sqleval and _sqlexec takes a query and a connection, sends the query to the database server through the connection, and returns a result of the execution of the query on the server. _sqleval is used for select query, which returns a relation as a result of query computation. _sqlexec is for any other SQL commands which perform side effects on a database server and does not return a relation.

SQL.fetch and SQL.fetchAll primitive function provides functionality to obtain a tuple from a relation of the result of select query.

 SQL.fetch : 'a SQL.rel -> ('a * 'a SQL.rel) option
 SQL.fetchAll : 'a SQL.rel -> 'a list

The following is an example program that interacts with a database by the SQL extension:

 (* declare a database server *)
 # val server = _sqlserver "dbname=sample" : {people: {name: string, age: int}};
 val server = "dbname=sample " : {people: {age: int, name: string}} _SQL.server

 (* connect to the server *)
 # val conn = SQL.connect server;
 val conn = <conn> : {people: {age: int, name: string}} _SQL.conn

 (* compose an SQL query *)
 # val q = _sql db => select as name
                      from #db.people as person
                      where SQL.> (#person.age, 25);
 val q = fn
     : ['a#{people: 'b},
        'b#{age: int, name: 'd},
        'd::{int, word, char, string, real, 'e option},
        'e::{int, word, char, bool, string, real}.
        ('a, 'c) _SQL.db -> {name: 'd} _SQL.query]

 (* execute the query on the server *)
 # val r = _sqleval q conn;
 val r = <rel> : {name: string} _SQL.rel

 (* retrive the result as ML records *)
 # val l = SQL.fetchAll r;
 val l = [{name = "Alice"}] : {name: string} list

There are some example programs using these database features in sample directory of the SML# 0.60 source distribution.

Last modified:2011/11/28 15:04:12
References:[Features of SML#]