~sivers/sive.rs

ref: 89db8a0db3b15ad1a131b443ccfe2a0d5f08170a sive.rs/site/pg -rw-r--r-- 12.0 KiB
89db8a0d — Derek Sivers formatting 4 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<meta charset="utf-8">
<title>Simplify: move code into database functions | Derek Sivers</title>
<meta name="description" content="If you are a web or API developer, programming code that uses an SQL database, this is for you.">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" type="text/css" href="/style.css">
<link rel="alternate" type="application/atom+xml" title="Derek Sivers" href="/en.atom">
<link rel="alternate" type="application/rss+xml" title="Derek Sivers podcast" href="/podcast.rss">
<link rel="prev" href="https://sive.rs/bad">
<link rel="next" href="https://sive.rs/2do">
</head>
<body id="article">
<section id="masthead">
<h1><a href="/" title="Derek Sivers">Derek Sivers</a></h1>
</section>
<div id="content">

<article>
<header>
<div class="blogparent"><a href="/blog">Articles</a>:</div>
<h1>Simplify: move code into database functions</h1>
<small>2015-05-04</small>
</header>

<p>
	If you are a web or API developer, programming code that uses an SQL database, this is for you.
</p><p>
	I’ve found a very different and useful way to structure code.
	It’s made such a big difference for me that I had to share it here.
</p>
<h3>
	How things are
</h3>
<p>
	Most web development — whether custom or using frameworks like Rails, Django, Laravel, Sinatra, Flask, and Symfony — tends to work the same way:
</p>
<ul><li>
	At the core is a <strong>database</strong>, which is just the storage of data.
</li><li>
	<strong>All intelligence</strong> is in Ruby/Python/PHP/JavaScript classes.
</li></ul>
<h3>
	Why that’s bad
</h3>
<p>
	These norms have some dangerous implications:
</p>
<ul><li>
	<strong>Everything</strong> must go through these Ruby/Python/PHP/JavaScript classes — including shell scripts and other things not part of this website.
</li><li>
	<strong>Nothing</strong> else may access the database directly, since doing so may break the rules defined by these surrounding classes.
</li><li>
	The <strong>database is treated as dumb storage</strong>, even though the database is smart enough to have most of this logic built-in.
</li><li>
	But if you add business rules into the database itself, it’s now <strong>duplicated</strong>, requiring changing in multiple places if the rules change.
</li><li>
	These two systems — the database and its surrounding code — are <strong>coupled and dependent</strong> on each other.
</li><li>
	If it’s ever advantageous to <strong>switch languages</strong> (say from Ruby to JavaScript, or Python to Elixir), you’re going to have to <strong>re-write absolutely everything</strong>.
</li></ul>
<h3>
	Simple vs complex
</h3>
<p>
	Please go watch this amazing 35-minute talk as soon as possible:
<strong><a href="https://www.youtube.com/watch?v=rI8tNMsozo0">Simplicity Matters by Rich Hickey</a></strong>.
</p><p>
	Here are his important points for this article:
</p><ul><li>
	“<strong>Complex</strong>” is objective.
	It means <strong>many things tied together</strong>.
</li><li>
	“<strong>Simple</strong>” is objective.
	It means <strong>one ingredient</strong> — the opposite of complex.
</li><li>
	These are unrelated to “easy”.
	It is easy to install and bind yourself to something very complex (like <a href="https://en.wikipedia.org/wiki/Object-relational_mapping">ORM</a>), and can be hard to build something simple.
</li><li>
	Classes, models, and methods (<a href="https://en.wikipedia.org/wiki/Object-oriented_programming">OOP</a>) are an <strong>unnecessary complication</strong>.
</li><li>
	Information is simple, so <strong>don’t hide it</strong> behind a micro-language.
</li><li>
	<strong>Work with values directly</strong>: hash/map of strings.
</li><li>
	Since a <strong>JSON API</strong> — a hash/map of strings — is often the eventual interface, it’s even more reason to skip the abstractions and work with values directly.
</li></ul>
<h3>
	Why this hit home for me
</h3>
<p>
	I’ve been using the same SQL database since 1997: same data, values, and SQL tables.
	But the code around it has changed so many times.
</p><p>
	In 1997, I started in Perl.
	In 1998, I switched to PHP.
	In 2004, a rewrite in Rails.
	In 2007, <a href="/rails2php">back</a> to PHP.
	In 2009, minimalist Ruby.
	In 2012, client-side JavaScript.
</p><p>
	<strong>Each time I’d have to re-write all of the logic around the database</strong>:
	how to add a new person into the database,
	how to verify an invoice is correct,
	how to mark an order as paid, etc.
</p><p>
	But <strong>that whole time, my trusty PostgreSQL database stayed the same</strong>.
</p><p>
	Since most of this is <a href="https://rob.conery.io/2015/02/21/its-time-to-get-over-that-stored-procedure-aversion-you-have/">data logic, not business logic</a>, it should be in the database.
</p><p>
	So I’m putting this data logic directly into <a href="https://www.postgresql.org/about/">PostgreSQL</a>, since I plan to stay with it for many more years, but plan to keep experimenting with programming languages.
(<a href="https://nim-lang.org/">Nim</a>, <a href="https://elixir-lang.org/">Elixir</a>, <a href="https://racket-lang.org/">Racket</a>, <a href="https://www.lua.org/about.html">Lua</a>, whatever.)
</p>
<h3>
	How things could be
</h3>
<p>
	Web developers have been treating the database as dumb storage, but it’s actually <a href="https://www.postgresql.org/docs/12/static/server-programming.html">quite smart</a>.
</p><p>
	It’s <strong>simple</strong> to have all of this intelligence <strong>in the database itself</strong>.
</p><p>
	It’s <strong>complex</strong> to have it tied to surrounding outside code.
</p><p>
	Once you put all of the intelligence directly into the database, then the outside code disappears!
</p><p>
	Then <strong>the database is self-contained, and not tied to anything</strong>.
</p><p>
	Your outside interface can <strong>switch</strong> to JavaScript, Haskell, Elixir or anything else with ease, because your core intelligence is all inside the database.
</p>
<h3>
	How to do it
</h3>
<h4>
	Table constraints
</h4>
<p>
	The easiest place to start is <a href="https://www.postgresql.org/docs/12/static/ddl-constraints.html">constraints</a>:
</p>
<pre><code>
create table people (
  id serial primary key,
  name text not null constraint no_name check (length(name) > 0),
  email text unique constraint valid_email check (email ~ '\A\S+@\S+\.\S+\Z')
);
create table tags (
  person_id integer not null references people(id) on delete cascade,
  tag varchar(16) constraint tag_format check (tag ~ '\A[a-z0-9._-]+\Z')
);
</code></pre>
<p>
	Define what is considered valid/invalid data here.
</p><p>
	In my people example above, it says name can’t be empty, email must match that pattern with “@” and “.” and no whitespace.
	Then it says tags.person_id has to exist in the people table, but if the person is deleted then delete the tags, too.
	And the tag has to fit that regexp pattern of lowercase letters, numbers, dot, underscore, dash.
</p><p>
	It helps to name your constraints for later use in error catching.
</p>
<h4>
	Triggers
</h4>
<p>
	For things that happen before or after you alter data, use <a href="https://www.postgresql.org/docs/12/static/trigger-definition.html">triggers</a>:
</p>
<pre><code>
create function clean() returns trigger as $$
begin
  new.name = btrim(regexp_replace(new.name, '\s+', ' ', 'g'));
  new.email = lower(regexp_replace(new.email, '\s', '', 'g'));
end;
$$ language plpgsql;
create trigger clean before insert or update of name, email on people
  for each row execute procedure clean();
</code></pre>
<p>
	This example cleans the input before it’s put into the database, in case someone accidentally put a space in their email address, or a line-break in their name.
</p>
<h4>
	Functions
</h4>
<p>
	Make little re-usable functions for things you’ll use often inside your code.
</p>
<pre><code>
create function get_person(a_name text, a_email text) returns setof people as $$
begin
  if not exists (select 1 from people where email = a_email) then
    return query insert into people (name, email)
      values (a_name, a_email) returning people.*;
  else
    return query select * from people where email = a_email;
  end if;
end;
$$ language plpgsql;
</code></pre>
<p>
	That’s one I use often:
	Given someone’s name and email, if they’re not already in my database, add them.
	Then, either way, return the database info for this person.
</p>
<h4>
	Views for JSON
</h4>
<p>
	Instead of requiring outside code to convert your data into JSON, you can have the <a href="https://www.postgresql.org/docs/12/static/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE">database create JSON directly</a>.
</p><p>
	For this, use <a href="https://www.postgresql.org/docs/12/static/sql-createview.html">views</a> as JSON structure templates.
	Inside the view, use <a href="https://www.postgresql.org/docs/12/static/functions-aggregate.html">json_agg</a> for nested values.
</p>
<pre><code>
create view person_view as
  select *, (select json_agg(t) as tags from
    (select tag from tags where person_id=people.id) t)
  from people;
</code></pre>
<p>
	This will be used in the API functions, below:
</p>
<h4>
	API functions
</h4>
<p>
	These are the only functions your outside code will access.
</p><p>
	They return only JSON.
</p>
<pre><code>
create function update_password(p_id integer, nu_pass text, out js json) as $$
begin
  update people set password=crypt(nu_pass, gen_salt('bf', 8)) where id = p_id;
  js := row_to_json(r) from (select * from person_view where id = p_id) r;
end;
$$ language plpgsql;
create function people_with_tag(a_tag text, out js json) as $$
begin
  js := json_agg(r) from
    (select * from person_view where id in
      (select person_id from tags where tag = a_tag)) r;
end;
$$ language plpgsql;
</code></pre>
<p>
	No matter what you need to do with your database, the <a href="https://www.postgresql.org/docs/12/static/xplang.html">procedural languages built-in to PostgreSQL</a> can do it.
</p><p>
	<a href="https://www.postgresql.org/docs/12/static/plpgsql-overview.html">PL/pgSQL</a> is <strong>not the most beautiful language</strong>, but the <strong>simplicity</strong> of having everything in the database is worth it.
</p><p>
	If you like JavaScript, check out the promising <a href="https://plv8.github.io/">plv8</a>.
</p>
<h3>
	Now, if you need a REST API:
</h3>
<pre><code>
require 'pg'
require 'sinatra'
DB = PG::Connection.new(dbconfig)
def qry(sql, params=[])
  @res = DB.exec_params('select js from ' + sql, params)
end
after do
  content_type 'application/json'
  body @res[0]['js']
end
get '/people' do
  qry('get_people()')
end
get %r{/people/([0-9]+)} do |id|
  qry('get_person($1)', [id])
end
put %r{/people/([0-9]+)} do |id|
  qry('update_password($1, $2)', [id, params[:password]])
end
get '/people/tagged' do
  qry('people_with_tag($1)', [params[:tag]])
end
</code></pre>
<h3>
	Or if you need a client library:
</h3>
<pre><code>
require 'pg'
require 'json'
DB = PG::Connection.new(dbconfig)
def js(func, params=[])
  res = DB.exec_params('select js from ' + func, params)
  JSON.parse(res[0]['js'])
end
def people
  js('get_people()')
end
def person(id)
  js('get_person($1)', [id])
end
def update_password(id, newpass)
  js('update_password($1, $2)', [id, newpass])
end
def people_tagged(tag)
  js('people_with_tag($1)', [tag])
end
</code></pre>
<h3>
	That’s it!
</h3>
<p>
	Now whether a REST API or client library, <strong>all it really has to do is pass the arguments into the database functions, and return the JSON</strong>.
</p><p>
	I’m not trying to convince everyone to do things this way.
	But I hope you find it useful or at least interesting to consider.
</p>
<img src="/images/postgresql.png" alt="postgresql logo">

<footer>
© 2015 <a href="https://sive.rs/">Derek Sivers</a>.
(
  « <a href="/bad" accesskey="p" rel="prev">previous</a>
    ||
  <a href="/2do" accesskey="n" rel="next">next</a> »
)
<h1>
  Copy &amp; share:
  <span class="url"><a href="https://sive.rs/pg">sive.rs/pg</a></span>
</h1>
</footer>
</article>
<div id="comments"></div>
<script type="text/javascript" src="/js/comments.js"></script>

</div>
</body>
</html>