λ
Scala
JS
Mongo
Riak
Redis
Couchbase
CouchDB
ElasticSearch
Titan
Cassandra
Kafka
Hbase
Neo4J
Datomic
Postgres
relational algebra
relational thinking
Data consistency
SQL("""select student.student_id,
student.name,
student.birth_date""")
.as(parser.*)
val parser = get[UUID]("student_id") ~
str("name") ~
get[DateTime]("birth_date")
SQL(selectSQL[Student])
.as(parser[Student].*)
val q2 = for {
c <- coffees if c.price < 9.0
s <- suppliers if s.id === c.supID
} yield (c.name, s.name)
Result<Record3<String, String, String>> result =
create.select(
BOOK.TITLE,
AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
.where(BOOK.PUBLISHED_IN.equal(1948))
.fetch();
The key
The whole key
Nothing but the key
So help me Codd
Uniform naming for PKs & FKs
<table_name>_id
Uniform naming for PKs & FKs
select <fields> from
table_1
inner join table_2
on table_1.table_1_id =
table_2.table_2_id
Uniform naming for PKs & FKs
select <fields> from
table_1
inner join table_2
using (table_1_id)
Uniform naming for PKs & FKs
select <fields> from
table_1
natural join table_2
(or random IDs)
created_at
timestamp with time zone not null,
deleted_at
timestamp with time zone
Use enums
create type status
as enum('pending', 'validated');
Use enums even when a boolean could do
create type status
as enum('pending', 'validated');
varchar(15)
, right?Use precise types
inet (IP address)
timestamp with time zone
point (2D point)
tstzrange (time range)
interval (duration)
Create your own
create type my type
as (
field1 int,
field2 text
);
Rich types => powerful constraints
create table reservation(
reservation_id uuid primary key,
dates tstzrange not null,
exclude using gist (dates with &&)
);
Use arrays
select '{1,2,3}'::int[]
SELECT 'a=>1,a=>2'::hstore;
hstore
----------
"a"=>"1"
SELECT
('{ "a": "b"}'::jsonb)->>'a' as field;
field
----------
"b"
array_agg
select
article.*,
array_agg(comment.*) as comments
from
article
left join comment using(article_id)
select
row_to_json(article.*) as article,
row_to_json(author.*) as author
from
article
inner join author using(author_id)
select
row_to_json(article.*) as article,
array_to_json(
array_agg(
row_to_json(comment.*))) as comments
from article
inner join author using(author_id)
Common Table Expressions
with sub_request as (
select <fields> from table1
)
select <fields> from sub_request;
jDbT
status:
- Test
- Prod
jDbT
member:
name: text
email: text
status: status | 'Test'
__unique: [ name, email ]
jDbT
post:
member_id:
+?title: text
?content: text
jDbT
tag:
+name: text
__check: name <> 'forbidden'
jDbT
post_tag:
post_id:
tag_id:
__pk: [ tag_id, post_id ]
geecon15roxx