PostgresDoc: Using Postgres as a Document Oriented Database

Transcript

1. PostgresDoc Using Postgres as a Document Database Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 1
2. How I Learned to Stop Worrying and Love the SQL Again Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 2
3. Relational Database Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 3
4. Relational Database Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 4
5. Document Oriented Database { "_id": "b04b9746-77bc-11e6-a75a-34363bd0c9bc", "name": "Lot 9 Pilsner", "brewer": "Creemore Springs", "rating": 4.2 } Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 5
6. Document Oriented Database { "_id": "b04b9746-77bc-11e6-a75a-34363bd0c9bc", "date": "2016-09-11T16:35:33.773325-0400" "customer": { "name": "Myles Braithwaite", "telephone": "+1 (416) 555-1234", "address": "123 Street Ave." }, "order": [{ "name": "beer", "price_per_unit": 12, "quantity": 2, "total": 24 }], "total": 24, "payment": { "type": "VISA", "number": "12345", "expiry": "2001-04" } } Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 6
7. Document Oriented Database Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 7
8. Why not both? Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 8
9. Why not both? • Avoid complicated JOINs. • Ability to store complicated JSON API responses in the database. • Avoid transforming data before returning it via a JSON API. Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 9
10. Postgres JSON/JSONB Type Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 10
11. CREATE TABLE Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 11
12. CREATE TABLE notes ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), notebook_id UUID REFERENCES notebooks (id), note JSONB ); Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 12
13. INSERT Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 13
14. INSERT INTO notes VALUES ( '675afef8-7863-11e6-8914-34363bd0c9bc', '8921ad42-300e-447a-8417-ec92bb18e2df', '{ "name": "PostgresDoc", "tags": ["Postgres", "Document Oriented Database"], "body": "Hello, World" }' ) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 14
15. SELECT Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 15
16. SELECT note->>'name' AS name FROM notes; name ---------------------------------------- PostgresDoc World Domination Plans Superman < Batman > Supergirl = Batgirl (3 rows) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 16
17. SELECT jsonb_array_elements_text(note->'tags') AS tag FROM notes WHERE id = '675afef8-7863-11e6-8914-34363bd0c9bc'; tag --------------------------- Postgres Document Oriented Database (2 rows) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 17
18. SELECT id, note FROM notes WHERE note->'tags' ? 'Postgres'; id | note | -------------------------------------+------------------------ 675afef8-7863-11e6-8914-34363bd0c9bc | {"name": "PostgresDoc"} (1 row) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 18
19. SELECT count(*) FROM notes WHERE note ? 'checklist'; count ------- 1 (1 row) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 19
20. CREATE INDEX idx_checklist ON notes ((note->>'tags')) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 20
21. ORMs Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 21
22. SQLAlchemy note_table = Table('notes', metadata, Column('id', Integer, primary_key=True), Column('note', JSONB) ) with engine.connect() as conn: conn.execute( note_table.insert(), note = {"name": "Postgres", "tags": ["Postgres"]} ) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 22
23. Rails create_table :notes do |t| t.json 'note' end class Note < ApplicationRecord end Note.create(note: { name: "Postgres", tags: ["Postgres"]}) Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 23
24. https://github.com/myles/2016-09-13-postgresdoc Myles Braithwaite | mylesb.ca | me@mylesb.ca | @mylesb 24