Share
Embed
Transcript
24 slides
-
PostgresDoc Using Postgres as a Document Database Myles Braithwaite |
mylesb.ca | [email protected] | @mylesb 1 -
How I Learned to Stop Worrying and Love the SQL
Again Myles Braithwaite | mylesb.ca | [email protected] | @mylesb 2 -
Document Oriented Database { "_id": "b04b9746-77bc-11e6-a75a-34363bd0c9bc", "name": "Lot 9 Pilsner",
"brewer": "Creemore Springs", "rating": 4.2 } Myles Braithwaite | mylesb.ca | [email protected] | @mylesb 5 -
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 | [email protected] | @mylesb 6 -
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 | [email protected] | @mylesb 9 -
CREATE TABLE notes ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
notebook_id UUID REFERENCES notebooks (id), note JSONB ); Myles Braithwaite | mylesb.ca | [email protected] | @mylesb 12 -
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 | [email protected] | @mylesb 14 -
SELECT note->>'name' AS name FROM notes; name ---------------------------------------- PostgresDoc World
Domination Plans Superman < Batman > Supergirl = Batgirl (3 rows) Myles Braithwaite | mylesb.ca | [email protected] | @mylesb 16 -
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 | [email protected] | @mylesb 17 -
SELECT id, note FROM notes WHERE note->'tags' ? 'Postgres'; id
| note | -------------------------------------+------------------------ 675afef8-7863-11e6-8914-34363bd0c9bc | {"name": "PostgresDoc"} (1 row) Myles Braithwaite | mylesb.ca | [email protected] | @mylesb 18 -
SELECT count(*) FROM notes WHERE note ? 'checklist'; count -------
1 (1 row) Myles Braithwaite | mylesb.ca | [email protected] | @mylesb 19 -
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 | [email protected] | @mylesb 22 -
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 | [email protected] | @mylesb 23