diff options
Diffstat (limited to 'guides/source/active_record_postgresql.md')
| -rw-r--r-- | guides/source/active_record_postgresql.md | 517 |
1 files changed, 517 insertions, 0 deletions
diff --git a/guides/source/active_record_postgresql.md b/guides/source/active_record_postgresql.md new file mode 100644 index 0000000000..58c61f0864 --- /dev/null +++ b/guides/source/active_record_postgresql.md @@ -0,0 +1,517 @@ +**DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON http://guides.rubyonrails.org.** + +Active Record and PostgreSQL +============================ + +This guide covers PostgreSQL specific usage of Active Record. + +After reading this guide, you will know: + +* How to use PostgreSQL's datatypes. +* How to use UUID primary keys. +* How to implement full text search with PostgreSQL. +* How to back your Active Record models with database views. + +-------------------------------------------------------------------------------- + +In order to use the PostgreSQL adapter you need to have at least version 9.1 +installed. Older versions are not supported. + +To get started with PostgreSQL have a look at the +[configuring Rails guide](configuring.html#configuring-a-postgresql-database). +It describes how to properly setup Active Record for PostgreSQL. + +Datatypes +--------- + +PostgreSQL offers a number of specific datatypes. Following is a list of types, +that are supported by the PostgreSQL adapter. + +### Bytea + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-binary.html) +* [functions and operators](https://www.postgresql.org/docs/current/static/functions-binarystring.html) + +```ruby +# db/migrate/20140207133952_create_documents.rb +create_table :documents do |t| + t.binary 'payload' +end + +# app/models/document.rb +class Document < ApplicationRecord +end + +# Usage +data = File.read(Rails.root + "tmp/output.pdf") +Document.create payload: data +``` + +### Array + +* [type definition](https://www.postgresql.org/docs/current/static/arrays.html) +* [functions and operators](https://www.postgresql.org/docs/current/static/functions-array.html) + +```ruby +# db/migrate/20140207133952_create_books.rb +create_table :books do |t| + t.string 'title' + t.string 'tags', array: true + t.integer 'ratings', array: true +end +add_index :books, :tags, using: 'gin' +add_index :books, :ratings, using: 'gin' + +# app/models/book.rb +class Book < ApplicationRecord +end + +# Usage +Book.create title: "Brave New World", + tags: ["fantasy", "fiction"], + ratings: [4, 5] + +## Books for a single tag +Book.where("'fantasy' = ANY (tags)") + +## Books for multiple tags +Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"]) + +## Books with 3 or more ratings +Book.where("array_length(ratings, 1) >= 3") +``` + +### Hstore + +* [type definition](https://www.postgresql.org/docs/current/static/hstore.html) +* [functions and operators](https://www.postgresql.org/docs/current/static/hstore.html#AEN179902) + +NOTE: You need to enable the `hstore` extension to use hstore. + +```ruby +# db/migrate/20131009135255_create_profiles.rb +ActiveRecord::Schema.define do + enable_extension 'hstore' unless extension_enabled?('hstore') + create_table :profiles do |t| + t.hstore 'settings' + end +end + +# app/models/profile.rb +class Profile < ApplicationRecord +end + +# Usage +Profile.create(settings: { "color" => "blue", "resolution" => "800x600" }) + +profile = Profile.first +profile.settings # => {"color"=>"blue", "resolution"=>"800x600"} + +profile.settings = {"color" => "yellow", "resolution" => "1280x1024"} +profile.save! + +Profile.where("settings->'color' = ?", "yellow") +# => #<ActiveRecord::Relation [#<Profile id: 1, settings: {"color"=>"yellow", "resolution"=>"1280x1024"}>]> +``` + +### JSON and JSONB + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-json.html) +* [functions and operators](https://www.postgresql.org/docs/current/static/functions-json.html) + +```ruby +# db/migrate/20131220144913_create_events.rb +# ... for json datatype: +create_table :events do |t| + t.json 'payload' +end +# ... or for jsonb datatype: +create_table :events do |t| + t.jsonb 'payload' +end + +# app/models/event.rb +class Event < ApplicationRecord +end + +# Usage +Event.create(payload: { kind: "user_renamed", change: ["jack", "john"]}) + +event = Event.first +event.payload # => {"kind"=>"user_renamed", "change"=>["jack", "john"]} + +## Query based on JSON document +# The -> operator returns the original JSON type (which might be an object), whereas ->> returns text +Event.where("payload->>'kind' = ?", "user_renamed") +``` + +### Range Types + +* [type definition](https://www.postgresql.org/docs/current/static/rangetypes.html) +* [functions and operators](https://www.postgresql.org/docs/current/static/functions-range.html) + +This type is mapped to Ruby [`Range`](http://www.ruby-doc.org/core-2.2.2/Range.html) objects. + +```ruby +# db/migrate/20130923065404_create_events.rb +create_table :events do |t| + t.daterange 'duration' +end + +# app/models/event.rb +class Event < ApplicationRecord +end + +# Usage +Event.create(duration: Date.new(2014, 2, 11)..Date.new(2014, 2, 12)) + +event = Event.first +event.duration # => Tue, 11 Feb 2014...Thu, 13 Feb 2014 + +## All Events on a given date +Event.where("duration @> ?::date", Date.new(2014, 2, 12)) + +## Working with range bounds +event = Event. + select("lower(duration) AS starts_at"). + select("upper(duration) AS ends_at").first + +event.starts_at # => Tue, 11 Feb 2014 +event.ends_at # => Thu, 13 Feb 2014 +``` + +### Composite Types + +* [type definition](https://www.postgresql.org/docs/current/static/rowtypes.html) + +Currently there is no special support for composite types. They are mapped to +normal text columns: + +```sql +CREATE TYPE full_address AS +( + city VARCHAR(90), + street VARCHAR(90) +); +``` + +```ruby +# db/migrate/20140207133952_create_contacts.rb +execute <<-SQL + CREATE TYPE full_address AS + ( + city VARCHAR(90), + street VARCHAR(90) + ); +SQL +create_table :contacts do |t| + t.column :address, :full_address +end + +# app/models/contact.rb +class Contact < ApplicationRecord +end + +# Usage +Contact.create address: "(Paris,Champs-Élysées)" +contact = Contact.first +contact.address # => "(Paris,Champs-Élysées)" +contact.address = "(Paris,Rue Basse)" +contact.save! +``` + +### Enumerated Types + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-enum.html) + +Currently there is no special support for enumerated types. They are mapped as +normal text columns: + +```ruby +# db/migrate/20131220144913_create_articles.rb +def up + execute <<-SQL + CREATE TYPE article_status AS ENUM ('draft', 'published'); + SQL + create_table :articles do |t| + t.column :status, :article_status + end +end + +# NOTE: It's important to drop table before dropping enum. +def down + drop_table :articles + + execute <<-SQL + DROP TYPE article_status; + SQL +end + +# app/models/article.rb +class Article < ApplicationRecord +end + +# Usage +Article.create status: "draft" +article = Article.first +article.status # => "draft" + +article.status = "published" +article.save! +``` + +To add a new value before/after existing one you should use [ALTER TYPE](https://www.postgresql.org/docs/current/static/sql-altertype.html): + +```ruby +# db/migrate/20150720144913_add_new_state_to_articles.rb +# NOTE: ALTER TYPE ... ADD VALUE cannot be executed inside of a transaction block so here we are using disable_ddl_transaction! +disable_ddl_transaction! + +def up + execute <<-SQL + ALTER TYPE article_status ADD VALUE IF NOT EXISTS 'archived' AFTER 'published'; + SQL +end +``` + +NOTE: ENUM values can't be dropped currently. You can read why [here](https://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com). + +Hint: to show all the values of the all enums you have, you should call this query in `bin/rails db` or `psql` console: + +```sql +SELECT n.nspname AS enum_schema, + t.typname AS enum_name, + e.enumlabel AS enum_value + FROM pg_type t + JOIN pg_enum e ON t.oid = e.enumtypid + JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace +``` + +### UUID + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-uuid.html) +* [pgcrypto generator function](https://www.postgresql.org/docs/current/static/pgcrypto.html#AEN182570) +* [uuid-ossp generator functions](https://www.postgresql.org/docs/current/static/uuid-ossp.html) + +NOTE: You need to enable the `pgcrypto` (only PostgreSQL >= 9.4) or `uuid-ossp` +extension to use uuid. + +```ruby +# db/migrate/20131220144913_create_revisions.rb +create_table :revisions do |t| + t.uuid :identifier +end + +# app/models/revision.rb +class Revision < ApplicationRecord +end + +# Usage +Revision.create identifier: "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11" + +revision = Revision.first +revision.identifier # => "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11" +``` + +You can use `uuid` type to define references in migrations: + +```ruby +# db/migrate/20150418012400_create_blog.rb +enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto') +create_table :posts, id: :uuid, default: 'gen_random_uuid()' + +create_table :comments, id: :uuid, default: 'gen_random_uuid()' do |t| + # t.belongs_to :post, type: :uuid + t.references :post, type: :uuid +end + +# app/models/post.rb +class Post < ApplicationRecord + has_many :comments +end + +# app/models/comment.rb +class Comment < ApplicationRecord + belongs_to :post +end +``` + +See [this section](#uuid-primary-keys) for more details on using UUIDs as primary key. + +### Bit String Types + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-bit.html) +* [functions and operators](https://www.postgresql.org/docs/current/static/functions-bitstring.html) + +```ruby +# db/migrate/20131220144913_create_users.rb +create_table :users, force: true do |t| + t.column :settings, "bit(8)" +end + +# app/models/device.rb +class User < ApplicationRecord +end + +# Usage +User.create settings: "01010011" +user = User.first +user.settings # => "01010011" +user.settings = "0xAF" +user.settings # => 10101111 +user.save! +``` + +### Network Address Types + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-net-types.html) + +The types `inet` and `cidr` are mapped to Ruby +[`IPAddr`](http://www.ruby-doc.org/stdlib-2.2.2/libdoc/ipaddr/rdoc/IPAddr.html) +objects. The `macaddr` type is mapped to normal text. + +```ruby +# db/migrate/20140508144913_create_devices.rb +create_table(:devices, force: true) do |t| + t.inet 'ip' + t.cidr 'network' + t.macaddr 'address' +end + +# app/models/device.rb +class Device < ApplicationRecord +end + +# Usage +macbook = Device.create(ip: "192.168.1.12", + network: "192.168.2.0/24", + address: "32:01:16:6d:05:ef") + +macbook.ip +# => #<IPAddr: IPv4:192.168.1.12/255.255.255.255> + +macbook.network +# => #<IPAddr: IPv4:192.168.2.0/255.255.255.0> + +macbook.address +# => "32:01:16:6d:05:ef" +``` + +### Geometric Types + +* [type definition](https://www.postgresql.org/docs/current/static/datatype-geometric.html) + +All geometric types, with the exception of `points` are mapped to normal text. +A point is casted to an array containing `x` and `y` coordinates. + + +UUID Primary Keys +----------------- + +NOTE: You need to enable the `pgcrypto` (only PostgreSQL >= 9.4) or `uuid-ossp` +extension to generate random UUIDs. + +```ruby +# db/migrate/20131220144913_create_devices.rb +enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto') +create_table :devices, id: :uuid, default: 'gen_random_uuid()' do |t| + t.string :kind +end + +# app/models/device.rb +class Device < ApplicationRecord +end + +# Usage +device = Device.create +device.id # => "814865cd-5a1d-4771-9306-4268f188fe9e" +``` + +NOTE: `gen_random_uuid()` (from `pgcrypto`) is assumed if no `:default` option was +passed to `create_table`. + +Full Text Search +---------------- + +```ruby +# db/migrate/20131220144913_create_documents.rb +create_table :documents do |t| + t.string 'title' + t.string 'body' +end + +add_index :documents, "to_tsvector('english', title || ' ' || body)", using: :gin, name: 'documents_idx' + +# app/models/document.rb +class Document < ApplicationRecord +end + +# Usage +Document.create(title: "Cats and Dogs", body: "are nice!") + +## all documents matching 'cat & dog' +Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)", + "cat & dog") +``` + +Database Views +-------------- + +* [view creation](https://www.postgresql.org/docs/current/static/sql-createview.html) + +Imagine you need to work with a legacy database containing the following table: + +``` +rails_pg_guide=# \d "TBL_ART" + Table "public.TBL_ART" + Column | Type | Modifiers +------------+-----------------------------+------------------------------------------------------------ + INT_ID | integer | not null default nextval('"TBL_ART_INT_ID_seq"'::regclass) + STR_TITLE | character varying | + STR_STAT | character varying | default 'draft'::character varying + DT_PUBL_AT | timestamp without time zone | + BL_ARCH | boolean | default false +Indexes: + "TBL_ART_pkey" PRIMARY KEY, btree ("INT_ID") +``` + +This table does not follow the Rails conventions at all. +Because simple PostgreSQL views are updateable by default, +we can wrap it as follows: + +```ruby +# db/migrate/20131220144913_create_articles_view.rb +execute <<-SQL +CREATE VIEW articles AS + SELECT "INT_ID" AS id, + "STR_TITLE" AS title, + "STR_STAT" AS status, + "DT_PUBL_AT" AS published_at, + "BL_ARCH" AS archived + FROM "TBL_ART" + WHERE "BL_ARCH" = 'f' + SQL + +# app/models/article.rb +class Article < ApplicationRecord + self.primary_key = "id" + def archive! + update_attribute :archived, true + end +end + +# Usage +first = Article.create! title: "Winter is coming", + status: "published", + published_at: 1.year.ago +second = Article.create! title: "Brace yourself", + status: "draft", + published_at: 1.month.ago + +Article.count # => 2 +first.archive! +Article.count # => 1 +``` + +NOTE: This application only cares about non-archived `Articles`. A view also +allows for conditions so we can exclude the archived `Articles` directly. |