Flexible Schemas with PostgreSQL and Elasticsearch

2018 Dec26

R
elational Databases typically make use of a rigid schema - predefined tables containing typed columns allowing for a rich set of functionality that would otherwise be impossible. It is both a major strength as well as a major weakness. On one hand strong typing allows databases to expose a rich set of operators, functions and functionality for each of the types. For postgres, this usually presents itself in the form of column types sql syntax to interact with them. On the other hand it means that all of the data in the table is uniform and deviations or alterations are rather difficult to do.

At the day job, I am in the process of migrating a number of applications

Read More
filed under:  postgres elasticsearch json

Build JSON API Responses With Postgres CTEs

2017 Apr30
P

agination is a recurring problem that developers have to deal with when implementing data access layers for APIs. It can be particularly tricky with the more traditional RDMS like [MySQL](https://www.mysql.com/) or [Postgresql](https://www.postgresql.org/). For example, let's say we had an API endpoint that allowed consumers to search a data base of moves. We could search by title, director, starring actors, etc. Our data base has millions of movies, and we know we don't want to return all all the potential matches for every search request. We only want to return the top 25 or so records and indicate in the response that there are more results to query for:

{
  meta: {
    total: 12000

Read More
filed under:  sql postgres node.js