Modernizing from PostgreSQL to Serverless with Fauna (Part 2)

Introduction

Modelling many-to-many relationships in Fauna

Storing the many-to-many relation

CreateCollection({name: "category"})
Do(
Create(Collection("category"), {
data: {
"name": "Horror"
}
}),
Create(Collection("category"), {
data: {
"name": "Documentary"
}
})
)
CreateCollection({name: "film_category"})
Do(
Create(Collection("film_category"), {
data: {
category_ref: Ref(Collection("category"), "288805203878086151"),
film_ref: Ref(Collection("film"), "288801457307648519")
}
}),
Create(Collection("film_category"), {
data: {
category_ref: Ref(Collection("category"), "288805203878085127"),
film_ref: Ref(Collection("film"), "288801457307648519")
}
})
)

Keeping primary keys unique

CreateIndex({
name: "unique_film_category",
source: Collection("film_category"),
values: [
{field: ["data", "category_ref"]},
{field: ["data", "film_ref"]}
],
unique: true
})

Querying the relation

Map(
Paginate(Documents(Collection("film"))),
Lambda(["filmRef"],
Let({
film: Get(Var("filmRef")),
spokenLang: Get(Select(['data', 'language', 'spoken'], Var("film"))),
subLang: Get(Select(['data', 'language', 'subtitles'], Var("film")))
},
// return a JSON object
)
)
SELECT * FROM film 
JOIN "language" as spol ON spol.language_id = film.spoken_language_id
JOIN "language" as subl ON subl.language_id = film.subtitles_language_id
JOIN film_category ON film.film_id=film_category.film_id
JOIN category ON film_category.category_id=category.category_id
SELECT 
film.title,
film.description,
film.last_update,
jsonb_agg(to_jsonb(spol)) ->> 0 as spoken_language,
jsonb_agg(to_jsonb(subl)) ->> 0 as subtitle_language,
jsonb_agg(to_jsonb(category))
FROM film
JOIN "language" as spol ON spol.language_id = film.language_id
JOIN "language" as subl ON subl.language_id = film.language_id
JOIN film_category ON film.film_id=film_category.film_id
JOIN category ON film_category.category_id=category.category_id

GROUP BY film.film_id
HAVING COUNT(film_category.category_id) < 64
LIMIT 64
CreateIndex(
{
name: "category_by_film",
source: Collection("film_category"),
terms: [
{
field: ["data", "film_ref"]
}
],
values: [
{
field: ["data", "category_ref"]
}
]
}
)
Match(Index("category_by_film"), Var("ref"))
Paginate(Match(Index("category_by_film"), Var("ref")))
Map(
Paginate(Match(Index("category_by_film"), Var("ref"))),
Lambda("catRef", Get(Var("catRef")))
)
Map(
Paginate(Documents(Collection("film"))),
Lambda(["ref"],
Let({
film: Get(Var("ref")),
spokenLang: Get(Select(['data', 'language', 'spoken'], Var("film"))),
subLang: Get(Select(['data', 'language', 'subtitles'], Var("film"))),
categories: Map(
Paginate(Match(Index("category_by_film"), Var("ref"))),
Lambda("catRef", Get(Var("catRef")))
)
},
{
categories: Var('categories'),
film: Var('film'),
language: {
spoken: Var("spokenLang"),
subtitles: Var("subLang")
}
})
)
)

Joins vs Map/Get

Using FQL to write a Domain Specific Language

var SelectAllRefs = (name) =>
Paginate(Documents(Collection(name)))

var GetCatsFromFilmRef = (filmRef) => Map(
Paginate(Match(Index("category_by_film"), filmRef)),
Lambda("catRef", Get(Var("catRef")))
)

var GetLangFromFilm = (film, langType) => Get(
Select(['data', 'language', langType], Var("film"))
)

var GetFilmWithLangsAndCats = (filmRef) => Let(
{
film: Get(filmRef)
},
{
categories: GetCatsFromFilmRef(filmRef),
film: Var('film'),
language: {
spoken: GetLangFromFilm(Var("film"), 'spoken'),
subtitles: GetLangFromFilm(Var("film"), 'subtitles')
}
}
)

var SelectFilmsWithLangsAndCats = () => Map(
SelectAllRefs("film"),
Lambda(["ref"], GetFilmWithLangsAndCats(Var("ref")))
)

Extensibility

var GetFilmWithLangsCatsActors = (filmRef) => Let(
{
film: Get(filmRef)
},
{
categories: GetCatsFromFilmRef(filmRef),
film: Var('film'),
language: {
spoken: GetLangFromFilm(Var("film"), 'spoken'),
subtitles: GetLangFromFilm(Var("film"), 'subtitles')
},
actors: GetActorsFromFilmRef(filmRef),
}
)

Reusability

function UpdateFilm(filmRef, description) {
return Update(
filmRef,
{
data: {
description: description
}
}
)
}
function UpdateFilm(filmRef, description) {
return Do(
Update(
filmRef,
{
data: {
description: description
}
}
),
GetFilmWithLangsAndCats(filmRef)
)
}

Adaptability

var GetLangFromFilm = (film, langType) => Get(
Select(['data', 'language', langType], Var("film"))
)
var GetLangsFromFilm= (film, langType) => Let({
filmRef: Select(['ref'], film),
langIndex: If(
Equals(langType, "spoken"),
Index("spoken_language_by_film"),
Index("subtitles_language_by_film")
)
},
Paginate(Match(Var('langIndex')))
)

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Fauna Inc

Fauna is a distributed document-relational database delivered as a cloud API.