General purpose RDBMS abstraction layer
Pull request Compare This branch is 48 commits behind chef:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.


This is the repository for sqerl, a database layer originally written for chef for interfacing with postgres.

Getting Started

So, you want to model a Postgres database table with Sqerl. Let's get to it.

Postgres I'm going to assume you know very little about Postgres,

since that's what I knew when I started writing this. You can safely skip this section if you know how to get that running.

You can get Postgres up and running on a Mac as easy as downloading You should probably learn more that the basics if you're going to run it anywhere more complex than that. starts Postgres on port 5432 of localhost. It'll also create a user and database for your OS X user's shortname. We'll use tyktorp for the examples below.

We should at least create a table so you have something to interact with in the following examples.

    description TEXT

Add It!

To add sqerl to your project (hereon known as sample_app), add it to your deps proplist in rebar.config

{sqerl, ".*",
 {git, "git://", {branch, "master"}}}

It's going to pull in these dependencies, just go with it:

Cloning into 'epgsql'...
Cloning into 'pooler'...
Cloning into 'envy'...

Configuring Sqerl

You actually need two sys.config application blocks. If only there were an application out there for abstracing configuration settings in to logical groupings, instead of imposing erlang's application behavior as a unit of scope.

 {sqerl, [
          %% Database connection parameters
          {db_host, "localhost" },
          {db_port, 5432 },
          {db_user, "tyktorp" },
          {db_pass, "" },
          {db_name, "tyktorp" },
          {idle_check, 10000},
          {column_transforms, []},
            {sqerl_rec, statements, [[{app, sample_app}]]}}

 {pooler, [
           {pools, [[{name, sqerl},
                     {max_count,  10 },
                     {init_count, 5 },
                     {start_mfa, {sqerl_client, start_link, []}}]]}

Model the table



-compile({parse_transform, sqerl_gobot}).


%% Must be the same as the module name
-record(thing, {
          id :: integer(),
          description :: binary()

'#insert_fields'() ->

'#update_fields'() ->

'#statements'() ->

%% Only needed if table name is different than module name
%'#table_name'() ->
%    "things".

NOTE: module name and record name MUST be the same.

Hopefully the sqerl_gobot parse transform doesn't do too much magic for you behind the scenes. Just the right amount of magic. They're not tricks, they're illusions. Here's what it gives you:


-behaviour(sqerl_rec) - Basically defines the required callbacks.


Some callbacks are generated by the parse transform, some you have to do yourself. Either way, the ones that start with # are meant to be called internally by sqerl_rec. The ones that don't are there for developer use. I suppose you can use them if you want. It's more of a guideline.


'#new'() - returns a new record of this type

'is'(Obj) - is Obj an "object" of this type

getval(Fieldname, Obj) - returns Fieldname of Object

setvals([{Fieldname, Value}|...]=Proplist, Obj) - returns a copy of Obj with each Fieldname's Value' from PropList modified.

fromlist([{Fieldname, Value}|...]=Proplist) - works just like setvals/2, but it's for new instances only.

fields() - returns a list of fields you provided in the -record of your module.

Write Yourself:

'#insert_fields'() -> [atom()] - the list of record fields to be inserted with generated insert statement.

'#update_fields'() -> [atom()] - the list of record fields to be updated with generated update statement.

'#statements'() -> [ default | {atom(), iolist()}] - a list of named statements. This is where your specific database code goes.


'#table_name'() -> atom. If your table name isn't the name of your module, set it here.


I skipped some steps here for now, but I'm assuming you can open up an erlang console with this application started.

%%% Make sure it's all configured right.
(sample_app@> sqerl_rec:statements([thing]).
[{thing_delete_by_id,<<"DELETE FROM things WHERE id = $1">>},
 {thing_fetch_by_id,<<"SELECT id, description FROM things WHERE id = $1">>},
 {thing_insert,<<"INSERT INTO things(description) VALUES ($1) RETURNING id, description">>},
 {thing_update,<<"UPDATE things SET description = $1 WHERE id = $2 RETURNING id, description">>}]

%% Add a thing?
(sample_app@> R = {thing, undefined, "Hi!"}.

(sample_app@> sqerl_rec:insert(R).

(sample_app@> sqerl_rec:fetch(thing, id, 1).

And look in the database!

tyktorp=# SELECT * FROM things;
 id | description
  1 | Hi!
(1 row)

Here we are now, it's a statement

Let's get everything!

(sample_app@> sqerl_rec:fetch_all(thing).
%% OOPS!

You have to specify a fetch_all query for thing, fortunately there's a convenience method for that. Change thing:#statements/0 to

'#statements'() ->
        {fetch_all, sqerl_rec:gen_fetch_all(thing, id)}


(sample_app@> sqerl_rec:fetch_all(thing).

You can make lots of custom statements as your usecase requires. Ours is pretty trivial though, I probably should have added more columns.


local postgres commands must be on $PATH to successfully make all






Copyright 2011-2014 Opscode, Inc. All Rights Reserved.

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.