SQL utilities

module sqlUtils

-- Database access through contexts: a function that needs a connection says
-- so in its type, and the caller supplies one with `use`.

type SqlPrimitive = #sqlInt(int) | #sqlString(string)

type SqlError = #duplicateKey | #sqlError(string)

type PreparedStatement = (
  setInt: (int, int) -> ()
  setString: (int, string) -> ()
  executeUpdate: () -> #some(int) | #error(SqlError)
)

context Connection = (
  prepare: string -> PreparedStatement
)

context DataSource = (
  getConnection: () -> Connection
)

def PreparedStatement.setArgument: (PreparedStatement, int, SqlPrimitive) -> () = {
  | statement, position, #sqlInt(value) -> statement.setInt(position, value)
  | statement, position, #sqlString(value) -> statement.setString(position, value)
}

export def executeUpdate: [Connection] (string, list(SqlPrimitive)) -> #some(int) | #error(SqlError)
  = { query, args ->
    def statement = prepare(query)
    for args, 1.. | arg, position -> statement \setArgument(position, arg)
    statement.executeUpdate()
  }

-- Callers that treat a duplicate key as "nothing to do" narrow the error with `except`
export def insertIgnoringDuplicates
  : [Connection] (string, list(SqlPrimitive)) -> #some(int) | #error(#sqlError(string))
  = { query, args ->
    executeUpdate(query, args) except #error(#duplicateKey) -> #some(0)
  }

export def withConnection: [DataSource] ([Connection] () -> a) -> a = { action ->
  def connection = getConnection()
  use connection in action()
}

export def addUser: [DataSource] (id: int, name: string) -> #some(int) | #error(#sqlError(string))
  = { id, name ->
    withConnection {
      insertIgnoringDuplicates("insert into users (id, name) values (?, ?)", [#sqlInt(id), #sqlString(name)])
    }
  }

This program lives in the repository as examples/sql-utils.nyx.