README.md
1# Postgrex
2
3[](https://travis-ci.org/elixir-ecto/postgrex)
4
5PostgreSQL driver for Elixir.
6
7Documentation: http://hexdocs.pm/postgrex/
8
9## Example
10
11```iex
12iex> {:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "postgres")
13{:ok, #PID<0.69.0>}
14iex> Postgrex.query!(pid, "SELECT user_id, text FROM comments", [])
15%Postgrex.Result{command: :select, empty?: false, columns: ["user_id", "text"], rows: [[3,"hey"],[4,"there"]], size: 2}}
16iex> Postgrex.query!(pid, "INSERT INTO comments (user_id, text) VALUES (10, 'heya')", [])
17%Postgrex.Result{command: :insert, columns: nil, rows: nil, num_rows: 1}}
18```
19
20## Features
21
22 * Automatic decoding and encoding of Elixir values to and from PostgreSQL's binary format
23 * User defined extensions for encoding and decoding any PostgreSQL type
24 * Supports transactions, prepared queries and multiple pools via [DBConnection](https://github.com/elixir-ecto/db_connection)
25 * Supports PostgreSQL 8.4 and 9.0-9.6 (hstore is not supported on 8.4)
26
27## Data representation
28
29 PostgreSQL Elixir
30 ---------- ------
31 NULL nil
32 bool true | false
33 char "é"
34 int 42
35 float 42.0
36 text "eric"
37 bytea <<42>>
38 numeric #Decimal<42.0> *
39 date %Postgrex.Date{year: 2013, month: 10, day: 12}
40 time(tz) %Postgrex.Time{hour: 0, min: 37, sec: 14, usec: 0} **
41 timestamp(tz) %Postgrex.Timestamp{year: 2013 month: 10, day: 12, hour: 0, min: 37, sec: 14, usec: 0} **
42 interval %Postgrex.Interval{months: 14, days: 40, secs: 10920}
43 array [1, 2, 3]
44 composite type {42, "title", "content"}
45 range %Postgrex.Range{lower: 1, upper: 5}
46 uuid <<160,238,188,153,156,11,78,248,187,109,107,185,189,56,10,17>>
47 hstore %{"foo" => "bar"}
48 oid types 42
49 enum "ok" ***
50 bit << 1::1, 0::1 >>
51 varbit << 1::1, 0::1 >>
52 tsvector [%Postgrex.Lexeme{positions: [{1, :A}], word: "a"}]
53
54\* [Decimal](http://github.com/ericmj/decimal)
55
56\*\* Timezones will always be normalized to UTC or assumed to be UTC when no information is available, either by PostgreSQL or Postgrex
57
58\*\*\* Enumerated types (enum) are custom named database types with strings as values.
59
60Postgrex does not automatically cast between types. For example, you can't pass a string where a date is expected. To add type casting, support new types, or change how any of the types above are encoded/decoded, you can use extensions.
61
62## Extensions
63
64Extensions are used to extend Postgrex' built-in type encoding/decoding.
65
66Here is a [JSON extension](https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/extensions/json.ex) that supports encoding/decoding Elixir maps to the Postgres' JSON type.
67
68Extensions can be specified and configured when building custom type modules. For example, if you want to different a JSON encoder/decode, you can define a new type module as below.
69
70```elixir
71# Postgrex.Types.define(module_name, extra_extensions, options)
72Postgrex.Types.define(MyApp.PostgrexTypes, [], json: AnotherJSONLib)
73```
74
75`Postgrex.Types.define/3` must be called on its own file, outside of any module and function, as it only needs to be defined once during compilation.
76
77Once a type module is defined, you must specify it on `start_link`:
78
79```elixir
80Postgrex.start_link(types: MyApp.PostgrexTypes)
81```
82
83## OID type encoding
84
85PostgreSQL's wire protocol supports encoding types either as text or as binary. Unlike most client libraries Postgrex uses the binary protocol, not the text protocol. This allows for efficient encoding of types (e.g. 4-byte integers are encoded as 4 bytes, not as a string of digits) and automatic support for arrays and composite types.
86
87Unfortunately the PostgreSQL binary protocol transports [OID types](http://www.postgresql.org/docs/current/static/datatype-oid.html#DATATYPE-OID-TABLE) as integers while the text protocol transports them as string of their name, if one exists, and otherwise as integer.
88
89This means you either need to supply oid types as integers or perform an explicit cast (which would be automatic when using the text protocol) in the query.
90
91```elixir
92# Fails since $1 is regclass not text.
93query("select nextval($1)", ["some_sequence"])
94
95# Perform an explicit cast, this would happen automatically when using a
96# client library that uses the text protocol.
97query("select nextval($1::text::regclass)", ["some_sequence"])
98
99# Determine the oid once and store it for later usage. This is the most
100# efficient way, since PostgreSQL only has to perform the lookup once. Client
101# libraries using the text protocol do not support this.
102%{rows: [{sequence_oid}]} = query("select $1::text::regclass", ["some_sequence"])
103query("select nextval($1)", [sequence_oid])
104```
105
106## PgBouncer
107
108When using PgBouncer with transaction or statement pooling named prepared
109queries can not be used because the bouncer may route requests from the same
110postgrex connection to different PostgreSQL backend processes and discards named
111queries after the transactions closes. To force unnamed prepared queries:
112
113```elixir
114Postgrex.start_link(prepare: :unnamed)
115```
116
117## Contributing
118
119To contribute you need to compile Postgrex from source and test it:
120
121```
122$ git clone https://github.com/elixir-ecto/postgrex.git
123$ cd postgrex
124$ mix test
125```
126
127The tests requires some modifications to your [hba file](http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html). The path to it can be found by running `$ psql -U postgres -c "SHOW hba_file"` in your shell. Put the following above all other configurations (so that they override):
128
129```
130local all all trust
131host all postgrex_md5_pw 127.0.0.1/32 md5
132host all postgrex_cleartext_pw 127.0.0.1/32 password
133```
134
135The server needs to be restarted for the changes to take effect. Additionally you need to setup a Postgres user with the same username as the local user and give it trust or ident in your hba file. Or you can export $PGUSER and $PGPASSWORD before running tests.
136
137### Testing hstore on 9.0
138
139Postgres versions 9.0 does not have the `CREATE EXTENSION` commands. This means we have to locate the postgres installation and run the `hstore.sql` in `contrib` to install `hstore`. Below is an example command to test 9.0 on OS X with homebrew installed postgres:
140
141```
142$ PGVERSION=9.0 PGPATH=/usr/local/share/postgresql9/ mix test
143```
144
145## License
146
147Copyright 2013 Eric Meadows-Jönsson
148
149Licensed under the Apache License, Version 2.0 (the "License");
150you may not use this file except in compliance with the License.
151You may obtain a copy of the License at
152
153 http://www.apache.org/licenses/LICENSE-2.0
154
155Unless required by applicable law or agreed to in writing, software
156distributed under the License is distributed on an "AS IS" BASIS,
157WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
158See the License for the specific language governing permissions and
159limitations under the License.
160
){kind=link}