Db

Run db queries in Gren.

Initialization

type Connection

Connection to a ws4sql server.
init : Permission -> String -> Connection

Connect to a ws4sql server.

ws4sql uses HTTP as its protocol so you must grant permission with HttpClient.Permission

The String is the server url and by default is http://localhost:12321/[dbname] where [dbname] is the database name configured for your server, without the suffix. So for example, if you started a server with ws4sql --quick-db mydb.db, the url will be http://localhost:12321/mydb.
withAuth :
{ user : String
, password : String
}
-> Connection
-> Connection

Update a connection to use authentication.

Db.init httpPerm "http://localhost:12321"
    |> Db.withAuth { user = "myuser", password = "mypass" }
withLogFile : Permission -> Path -> Connection -> Connection

Update a connection to log requests.

Db.init httpPerm "http://localhost:12321"
    |> Db.LogFile (FileSystem.Path.fromPosixString "myLog.json")

Each sql request to ws4sql will be appended to the file as a line of json.

Queries

type alias Query a =
{ query : String
, parameters : Array Value
, decoder : Decoder a
}

A query for pulling one or more values from a database.

This is the record used for the get* functions below.

The query string can contain parameters prefixed with a colon. For example: "select * from user where id = :id". They will be replaced with Encoder values in the parameters array.

You should always use parameter encoding instead of direct string manipulation to avoid sql injection attacks.

The resulting data will be turned into a Gren value (or array of values) using the Decoder in the decoder field.
type alias Statement = { statement : String, parameters : Array Value }

A statement for executing sql that does not return any values.

Just like queries, the statement can contain parameters prefixed with a colon. For example: "insert into user (id, name) values (:id, :name)". They will be replaced with Encoder values in the parameters array.

You should always use parameter encoding instead of direct string manipulation to avoid sql injection attacks.
getOne : Connection -> Query a -> Task Error a

Run a query that should return exactly one result.

Queries with zero or more than one result will result in an error.

Db.getOne connection
    { query = "select * from users where id = :id"
    , parameters = [ Db.Encode.int "id" 1 ]
    , decoder = 
        Db.Decode.map2
            (Db.Decode.int "id")
            (Db.Decode.string "name")
            (\id name -> { id = id, name = name })
    }
getMaybeOne : Connection -> Query a -> Task Error (Maybe a)

Run a query that should have zero or one result.

The resolved value will be a Maybe holding Nothing or the result of the decoder.

Db.getMaybeOne connection
    { query = "select * from users where id = :id"
    , parameters = [ Db.Encode.int "id" 1 ]
    , decoder = 
        Db.Decode.map2
            (Db.Decode.int "id")
            (Db.Decode.string "name")
            (\id name -> { id = id, name = name })
    }
getAll : Connection -> Query a -> Task Error (Array a)

Run a query that should have zero or more results.

The resolved value will be an array of values, each derived from the decoder.

Db.getAll connection
    { query = "select * from users"
    , parameters = []
    , decoder = 
        Db.Decode.map2
            (Db.Decode.int "id")
            (Db.Decode.string "name")
            (\id name -> { id = id, name = name })
    }
execute : Connection -> Statement -> Task Error Int

Execute arbitrary sql.

The resulting Int represents the number of affected rows.

Db.execute connection
    { statement = "insert into users (id, name) values (:id, :name)"
    , parameters =
        [ Db.Encode.int "id" 1
        , Db.Encode.string "name" "Justin"
        ]
    }
transaction : Connection -> Array Statement -> Task Error (Array Int)

Execute an array of sql statements in a single transaction.

The resulting array of Ints represents the number of affected rows in each statement.

Db.transaction connection
    [ { statement = "insert into users (id, name) values (1, \"one\")"
      , parameters = []
      }
    , { statement = "insert into users (id, name) values (2, \"two\")"
      , parameters = []
      }
    ]

Errors

type Error
= Error String
| NoResultError
| MultipleResultsError Int