SQL interop

SQL setup

Pipefish gives special privileges to SQL. In particular, it allows you to configure the hub so as to make all its services into clients, by default, of a given SQL database.

Here is the process as I just carried it out in my REPL.

→ hub config db                                                                                      
The following SQL drivers are available: 

  [0] Firebird SQL
  [1] MariaDB
  [2] MySQL
  [3] Oracle
  [4] Postgres
  [5] SQLite

Pick a number: 4
Host: localhost
Port: 5432
Database: postgres
Username for database access: tim
Password for database access: ▪▪▪▪▪                                                                  
ok
→ 

Having done that, the hub assumes by default that a service trying to talk to SQL is trying to talk to that database in particular.

SQL interop

An example is given in examples/sql.pf. The following definitions and commands are only excerpts from that file, which you should look at in full.

newtype

Person = struct(name varchar(32), age int) 

cmd 

init : 
    put SQL --- CREATE TABLE IF NOT EXISTS People |Person|

add (name string, age int) :
    put SQL ---
        INSERT INTO People
        VALUES |name, age|

show (name string) :
    get personList as Person from SQL ---
        SELECT * FROM People
        WHERE name=|name| 
    post personList[0] to Output()

Things to note:

• As with other forms of IO, we overload the get, put, post and delete commands. Note that Pipefish has no way of checking whether you are in fact putting, posting, or deleting; and so which of these terms to use is up to your judgement, just as when you use the same terms in HTTP. get, on the other hand, is compulsory if you want to get a value back.

• Pipefish values are inserted into SQL code using | as a delimiter. This doesn't just allow you to pass variables, you can also use Pipefish expressions.

• |<TypeName>| converts the name of a struct type to a SQL table definition. (This is why we have abstract types of the form varchar(n), so that we can have datatypes that correspond exactly to SQL datatypes.)