README.md (8180B)
1 # Postgrex 2 3 [](https://github.com/elixir-ecto/postgrex/actions) 4 5 PostgreSQL driver for Elixir. 6 7 Documentation: http://hexdocs.pm/postgrex/ 8 9 ## Examples 10 11 ```iex 12 iex> {:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "postgres") 13 {:ok, #PID<0.69.0>} 14 15 iex> Postgrex.query!(pid, "SELECT user_id, text FROM comments", []) 16 %Postgrex.Result{command: :select, empty?: false, columns: ["user_id", "text"], rows: [[3,"hey"],[4,"there"]], size: 2}} 17 18 iex> Postgrex.query!(pid, "INSERT INTO comments (user_id, text) VALUES (10, 'heya')", []) 19 %Postgrex.Result{command: :insert, columns: nil, rows: nil, num_rows: 1}} 20 ``` 21 22 ## Features 23 24 * Automatic decoding and encoding of Elixir values to and from PostgreSQL's binary format 25 * User defined extensions for encoding and decoding any PostgreSQL type 26 * Supports transactions, prepared queries and multiple pools via [DBConnection](https://github.com/elixir-ecto/db_connection) 27 * Supports PostgreSQL 8.4, 9.0-9.6, and later (hstore is not supported on 8.4) 28 29 ## Data representation 30 31 PostgreSQL Elixir 32 ---------- ------ 33 NULL nil 34 bool true | false 35 char "é" 36 int 42 37 float 42.0 38 text "eric" 39 bytea <<42>> 40 numeric #Decimal<42.0> * 41 date %Date{year: 2013, month: 10, day: 12} 42 time(tz) %Time{hour: 0, minute: 37, second: 14} ** 43 timestamp %NaiveDateTime{year: 2013, month: 10, day: 12, hour: 0, minute: 37, second: 14} 44 timestamptz %DateTime{year: 2013, month: 10, day: 12, hour: 0, minute: 37, second: 14, time_zone: "Etc/UTC"} ** 45 interval %Postgrex.Interval{months: 14, days: 40, secs: 10920, microsecs: 315} 46 array [1, 2, 3] 47 composite type {42, "title", "content"} 48 range %Postgrex.Range{lower: 1, upper: 5} 49 uuid <<160,238,188,153,156,11,78,248,187,109,107,185,189,56,10,17>> 50 hstore %{"foo" => "bar"} 51 oid types 42 52 enum "ok" *** 53 bit << 1::1, 0::1 >> 54 varbit << 1::1, 0::1 >> 55 tsvector [%Postgrex.Lexeme{positions: [{1, :A}], word: "a"}] 56 57 \* [Decimal](http://github.com/ericmj/decimal) 58 59 \*\* Timezones will always be normalized to UTC or assumed to be UTC when no information is available, either by PostgreSQL or Postgrex 60 61 \*\*\* Enumerated types (enum) are custom named database types with strings as values. 62 63 \*\*\*\* Anonymous composite types are decoded (read) as tuples but they cannot be encoded (written) to the database 64 65 Postgrex 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. 66 67 ## JSON support 68 69 Postgrex comes with JSON support out of the box via the [Jason](https://github.com/michalmuskala/jason) library. To use it, add :jason to your dependencies: 70 71 ```elixir 72 {:jason, "~> 1.0"} 73 ``` 74 75 You can customize it to use another library via the `:json_library` configuration: 76 77 ```elixir 78 config :postgrex, :json_library, SomeOtherLib 79 ``` 80 81 Once you change the value, you have to recompile Postgrex, which can be done by cleaning its current build: 82 83 ```sh 84 mix deps.clean postgrex --build 85 ``` 86 87 ## Extensions 88 89 Extensions are used to extend Postgrex' built-in type encoding/decoding. 90 91 The [extensions](https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/extensions/) directory in this project provides implementation for many Postgres' built-in data types. It is also a great example of how to implement your own extensions. For example, you can look at the [`Date`](https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/extensions/date.ex) extension as a starting point. 92 93 Once you defined your extensions, you should build custom type modules, passing all of your extensions as arguments: 94 95 ```elixir 96 Postgrex.Types.define(MyApp.PostgrexTypes, [MyApp.Postgis.Extensions], []) 97 ``` 98 99 `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. 100 101 Once a type module is defined, you must specify it on `start_link`: 102 103 ```elixir 104 Postgrex.start_link(types: MyApp.PostgrexTypes) 105 ``` 106 107 ## OID type encoding 108 109 PostgreSQL'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. 110 111 Unfortunately 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. 112 113 This 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. 114 115 ```elixir 116 # Fails since $1 is regclass not text. 117 query("select nextval($1)", ["some_sequence"]) 118 119 # Perform an explicit cast, this would happen automatically when using a 120 # client library that uses the text protocol. 121 query("select nextval($1::text::regclass)", ["some_sequence"]) 122 123 # Determine the oid once and store it for later usage. This is the most 124 # efficient way, since PostgreSQL only has to perform the lookup once. Client 125 # libraries using the text protocol do not support this. 126 %{rows: [{sequence_oid}]} = query("select $1::text::regclass", ["some_sequence"]) 127 query("select nextval($1)", [sequence_oid]) 128 ``` 129 130 ## PgBouncer 131 132 When using PgBouncer with transaction or statement pooling named prepared 133 queries can not be used because the bouncer may route requests from the same 134 postgrex connection to different PostgreSQL backend processes and discards named 135 queries after the transactions closes. To force unnamed prepared queries: 136 137 ```elixir 138 Postgrex.start_link(prepare: :unnamed) 139 ``` 140 141 ## Contributing 142 143 To contribute you need to compile Postgrex from source and test it: 144 145 ``` 146 $ git clone https://github.com/elixir-ecto/postgrex.git 147 $ cd postgrex 148 $ mix test 149 ``` 150 151 The 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): 152 153 ``` 154 local all all trust 155 host all postgrex_md5_pw 127.0.0.1/32 md5 156 host all postgrex_cleartext_pw 127.0.0.1/32 password 157 host all postgrex_scram_pw 127.0.0.1/32 scram-sha-256 158 ``` 159 160 The server needs to be restarted for the changes to take effect. Additionally you need to setup a PostgreSQL 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. 161 162 ### Testing hstore on 9.0 163 164 PostgreSQL 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: 165 166 ``` 167 $ PGVERSION=9.0 PGPATH=/usr/local/share/postgresql9/ mix test 168 ``` 169 170 ## License 171 172 Copyright 2013 Eric Meadows-Jönsson 173 174 Licensed under the Apache License, Version 2.0 (the "License"); 175 you may not use this file except in compliance with the License. 176 You may obtain a copy of the License at 177 178 http://www.apache.org/licenses/LICENSE-2.0 179 180 Unless required by applicable law or agreed to in writing, software 181 distributed under the License is distributed on an "AS IS" BASIS, 182 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 183 See the License for the specific language governing permissions and 184 limitations under the License.