Functional Database Strategies
2 likes361 views
This document discusses functional database strategies, specifically focusing on immutable rows, window functions, event-sourcing, and database interactions from a functional perspective. The key points covered include: 1. Making database rows immutable by storing them as a log of changes rather than allowing in-place updates. This allows reconstructing the full history. 2. Using window functions to partition and aggregate over row groups to enable querying the latest or historical state. 3. Storing a log of events rather than the current application state, in line with event-sourcing principles. 4. Techniques for interacting with databases in a functional way, including restricting mutations and optimizing data structures.
More Related Content
Similar to Functional Database Strategies (20)
More from Jason Swartz (7)
Recently uploaded (20)
Functional Database Strategies
- 1. Functional Database Strategies Scal脱 by the Bay 2016
- 2. YesQL
- 6. Step One Buy A Functional Database
- 7. Silly! Databases Are mutable, not functional!
- 9. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 10. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 11. Lets Talk About Immutable Tables
- 15. GET /issues GET /issues/{id} POST /issues PUT /issues/{id} Issue Endpoints
- 16. How Do These Events Affect The Database?
- 17. 1. POST /issues title=Config ELB +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+
- 18. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+
- 19. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+
- 20. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+ Not Bad.
- 21. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+ Do You Know How We Got Here?
- 22. +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+ Do You Know How We Got Here?
- 23. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+ Do You Know How We Got Here?
- 24. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+ Why is assignee NULL?
- 25. Mutable Table Rows Lose History
- 26. Immutable Table Rows KeepTheir History
- 27. Lets Try To Lock Down Our State
- 28. 1. POST /issues title=Config ELB +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+
- 29. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+
- 30. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:19 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+
- 31. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:19 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+
- 32. 1. GET /issues/1 +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:19 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+
- 33. Tables Are Mutable, But Table Rows Should Be Immutable
- 34. In Other Words, Tables Should Be Append-Only
- 35. How Do You Make An Append-Only Table?
- 36. One: Dont Let Your DB User Make Changes
- 37. Grant select, insert on issues to my-db-user; -- tested on Postgresql
- 39. Two: Pick The Right Columns
- 40. 1. GET /issues/1 +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:19 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | NULL | true | +------+-------------+------------+----------+-------+
- 41. create table issues ( id serial, created timestamp default now(), issue_id int default nextval(iseq), title text, assignee int, done boolean default false )
- 42. 1. GET /issues/1 +------+-------------+------------+------------+----------+-------+ | id | created | issue_id | title | assignee | done | +------+-------------+------------+------------+----------+-------+ | 1 | 09-18 18:13 | 1 | Config ELB | NULL | false | +------+-------------+------------+------------+----------+-------+ | 2 | 09-18 18:16 | 1 | Config ELB | 10 | false | +------+-------------+------------+------------+----------+-------+ | 3 | 09-18 18:19 | 1 | Config ELB | NULL | false | +------+-------------+------------+------------+----------+-------+ +------+-------------+------------+------------+----------+-------+ | 4 | 09-18 18:24 | 1 | Config ELB | NULL | true | +------+-------------+------------+------------+----------+-------+
- 43. select * from issues where issue_id = :issue_id order by id desc limit 1;
- 44. Thats The Basics Of Immutability In Table Rows
- 45. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 46. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 47. SQL:2003 expands Groups into Windows
- 49. Aggregation functions, eg sum(), rank(), avg() Window definitions with over() Grouping with partition by, order Window Functions
- 50. +------+------------+------------+----------+-------+ | id | issue_id | title | assignee | done | +------+------------+------------+----------+-------+ | 1 | 1 | Config ELB | NULL | false | +------+------------+------------+----------+-------+ | 2 | 1 | Config ELB | 10 | false | +------+------------+------------+----------+-------+ | 3 | 2 | Bug to fix | 11 | false | +------+------------+------------+----------+-------+ | 4 | 2 | Bug to fix | 11 | true | +------+------------+------------+----------+-------+
- 51. +------+------------+------------+----------+-------+ | id | issue_id | title | assignee | done | +------+------------+------------+----------+-------+ | 1 | 1 | Config ELB | NULL | false | +------+------------+------------+----------+-------+ | 2 | 1 | Config ELB | 10 | false | +------+------------+------------+----------+-------+ | 3 | 2 | Bug to fix | 11 | false | +------+------------+------------+----------+-------+ | 4 | 2 | Bug to fix | 11 | true | +------+------------+------------+----------+-------+
- 52. with latest_issues as ( select *, row_number() over ( partition by issue_id order by id desc ) from issues where created > now() - interval '7 day' ) select * from latest_issues where row_number = 1
- 53. with latest_issues as ( select *, row_number() over ( partition by issue_id order by id desc ) from issues where created > now() - interval '7 day' ) select * from latest_issues where row_number = 1
- 54. with latest_issues as ( select *, row_number() over ( partition by issue_id order by id desc ) from issues where created > now() - interval '7 day' ) select * from latest_issues where row_number = 1
- 55. with latest_issues as ( select *, row_number() over ( partition by issue_id order by id desc ) from issues where created > now() - interval '7 day' ) select * from latest_issues where row_number = 1
- 56. +------+------------+------------+----------+-------+------------+ | id | issue_id | title | assignee | done | row_number | +------+------------+------------+----------+-------+------------+ | 1 | 1 | Config ELB | NULL | false | 2 | +------+------------+------------+----------+-------+------------+ | 2 | 1 | Config ELB | 10 | false | 1 | +------+------------+------------+----------+-------+------------+ | 3 | 2 | Bug to fix | 11 | false | 2 | +------+------------+------------+----------+-------+------------+ | 4 | 2 | Bug to fix | 11 | true | 1 | +------+------------+------------+----------+-------+------------+
- 57. +------+------------+------------+----------+-------+------------+ | id | issue_id | title | assignee | done | row_number | +------+------------+------------+----------+-------+------------+ | 1 | 1 | Config ELB | NULL | false | 2 | +------+------------+------------+----------+-------+------------+ | 2 | 1 | Config ELB | 10 | false | 1 | +------+------------+------------+----------+-------+------------+ | 3 | 2 | Bug to fix | 11 | false | 2 | +------+------------+------------+----------+-------+------------+ | 4 | 2 | Bug to fix | 11 | true | 1 | +------+------------+------------+----------+-------+------------+
- 58. +------+------------+------------+----------+-------+------------+ | id | issue_id | title | assignee | done | row_number | +------+------------+------------+----------+-------+------------+ | 1 | 1 | Config ELB | NULL | false | 2 | +------+------------+------------+----------+-------+------------+ | 2 | 1 | Config ELB | 10 | false | 1 | +------+------------+------------+----------+-------+------------+ | 3 | 2 | Bug to fix | 11 | false | 2 | +------+------------+------------+----------+-------+------------+ | 4 | 2 | Bug to fix | 11 | true | 1 | +------+------------+------------+----------+-------+------------+
- 60. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 61. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 62. You Know How To Maintain State
- 63. Do We Still Need State?
- 65. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | 10 | true | +------+-------------+------------+----------+-------+
- 66. 1. POST /issues title=Config ELB 2. PUT /issues/1 assignee=10 3. PUT /issues/1 done=true +------+-------------+------------+----------+-------+ | id | updated | title | assignee | done | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:13 | Config ELB | NULL | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:16 | Config ELB | 10 | false | +------+-------------+------------+----------+-------+ | 1 | 09-18 18:24 | Config ELB | 10 | true | +------+-------------+------------+----------+-------+ Events States
- 71. Now Were Storing Events, Not States
- 72. create table issue_events ( id serial, created timestamp default now(), issue_id int default nextval(iseq), originator text, payload text )
- 73. 1. POST /issue/1/event Originator: 4a48239-8a.. payload=<Update val=done=true> +----+-------------+----------+------------+---------+ | id | created | issue_id | originator | payload | +----+-------------+----------+------------+---------+ | 14 | 09-18 18:50 | 1 | 4a482... | <...> | +----+-------------+----------+------------+---------+
- 74. Create Events And Simulate The State
- 75. 1. Create-Issue Issue(Config ELB, null, false); Real Events Virtual States
- 76. 1. Create-Issue 2. Assign-Issue Issue(Config ELB, 10, false); Real Events Virtual States
- 77. 1. Create-Issue 2. Assign-Issue 3. Complete-Issue Issue(Config ELB, 10, true); Real Events Virtual States
- 79. 1. High Write Performance 2. Potential for Command/Query Separation 3. Auditable 4. Replayable 5. Undo-able 6. Monitorable Reasons For Event-Sourcing
- 80. Its Like Having Control Over The Versions Of Your State Changes
- 81. Its Like Having Control Over The Versions Of Your Data
- 82. Its Like Git For Your Data
- 83. 1. Frankly, Its Weird 2. Requires Events. No Events, No Event-Sourcing. 3. As Of November 2016, Its Still Non-Standard Reasons Against Event-Sourcing
- 85. Who Cares About Being Non-Standard?
- 86. That About Sums Up Event Sourcing
- 87. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 88. 1. Immutable Rows 2. Window Functions 3. Events, Not State 4. DB Interactions Agenda
- 89. Remember That Your Database is mutable.
- 91. Avoid shared mutable SESSIONS Avoid shared mutable CURSORS Mutating state? Cool! But MODEL IT FIRST Execute state changes at THE EDGE Safe Database Interactions
- 93. Are these steps? Or a Monad?
- 95. That About Sums Up Database Interactions
- 96. Okay, Actually Thats The Entire Talk Unless Theres More Time
- 97. Functional Database Strategies Scal脱 by the Bay 2016
- 100. Fin