August 2018 Summaries

TimescaleDB is an open-source time-series database that supports full SQL and is deployed across various industries for powering applications in DevOps, IoT, SaaS, and Machine Learning. It can be packaged as a Postgres extension. Hasura GraphQL engine provides instant GraphQL APIs on any Postgres database, including TimescaleDB. In this guide, we will set up TimescaleDB with Hasura GraphQL on a Digital Ocean VM. Key points include: 1. Creating a Digital Ocean droplet and installing Docker and docker-compose. 2. Running TimescaleDB with Hasura using docker-compose. 3. Understanding hypertables, which are an abstraction over standard SQL tables for efficient management and querying of time-series data in TimescaleDB. 4. Using GraphQL queries on hypertables and subscriptions to get real-time data. 5. Setting up the TimescaleDB extension using psql. 6. Creating a view, tracking it, and making GraphQL queries on the view. 7. Real-time data with TimescaleDB & Hasura GraphQL through Python script and subscriptions.

Postgres triggers can be used to perform backend operations like validation and inserting/updating related data whenever inserts or updates occur on tables. These triggers are associated with a table or view and are fired when an event such as INSERT, DELETE, UPDATE, or TRUNCATE takes place. Triggers can be invoked before, after, or instead of the operation. They can be used for server-side validation and inserting related data in a single transaction. For example, in a banking application, triggers can ensure that deposit amounts are valid and greater than zero, and in a note-taking app, they can create revisions whenever a note is updated.

Schema stitching is a process that creates a single GraphQL schema from multiple underlying GraphQL APIs. It can be used to customize existing GraphQL API(s) and has various use cases such as merging two remote schemas, adding new queries and mutations, overriding existing queries and mutations, renaming root fields and types, extending a remote schema with custom types, and more. The graphql-tools library provides functions like makeExecutableSchema, introspectSchema, makeRemoteExecutableSchema, and mergeSchemas to aid in schema stitching.

Hasura GraphQL Engine supports Postgres UUID type implicitly, allowing UUID values to be provided as strings. A primary key is essential for every table in a database and is typically an auto-incrementing integer called a serial id. However, a more robust alternative is the UUID (Universally Unique Identifier), which is a 128-bit number that is nearly impossible to duplicate, making it ideal for use as primary keys. The Postgres UUID data type stores UUIDs as defined by RFC 4122 and has several advantages such as preventing information leaks, independent generation, random distribution, and more. However, there are also some disadvantages like size constraints, difficulty in debugging, lack of easy ordering, and potential performance issues. Hasura GraphQL Engine supports UUID types implicitly, allowing for simple queries and insertions with UUIDs.

Postgres supports a variety of date and time types, offering flexibility in storing time/date data. This blog post discusses using these date-time types in GraphQL with the Hasura GraphQL Engine (HGE). HGE supports implicit timestamp types as strings in queries or custom scalars like Timetz and Timestamptz for properly formatted ISO-8601 values. It also allows creating columns with default timestamp values at creation. The timestamptz field stores the normalized UTC time, making it timezone independent and accurate across different locations and time zones. Querying date/time types involves representing them as ISO-8601 formatted strings. Inserting into Timestamptz fields is straightforward with timestamps as strings or just dates or timestamps without time zones.

The text discusses deploying Hasura GraphQL Engine on Amazon RDS to create instant, high-speed GraphQL APIs for a Postgres database. It provides step-by-step instructions on creating an RDS instance or using an existing one, deploying Hasura GraphQL on Heroku or EC2+docker, and accessing the GraphQL engine console. The text also covers troubleshooting common issues such as connection failures and inaccessible public IPs.

The Hasura GraphQL Engine allows for modeling a graph using a relational database system like Postgres. It supports creating relationships between tables (nodes) using single-column foreign keys, multi-column foreign keys, or even without foreign keys. In the case of single-column foreign keys, Hasura's console suggests relationships over these columns and derives a GraphQL schema containing relation names for querying both tables in a single query. Relationships can also be created manually without a constraint, allowing for more flexibility when defining relations between tables. This enables GraphQL queries over extra information not necessarily constrained by foreign keys.

Custom SQL functions can now be directly queried in the GraphQL API with PostgreSQL, enhancing the ability to execute complex business logic at the database level rather than on individual clients or through multiple queries. This feature supports only certain types of PostgreSQL functions—those with behaviors marked as STABLE or IMMUTABLE, returning SETOF a table, and using IN argument modes. Access control permissions for the SETOF table apply to the function itself, maintaining security. Examples include a text-search function for an article database, and a PostGIS-based function for finding nearby landmarks to a user, which can be integrated into GraphQL queries. These functions allow for aggregations and various query arguments such as where, limit, and order_by. Future iterations promise expanded function support and integration with mutations. Feedback is encouraged via community channels.

This article discusses the use of Postgres views and materialized views with GraphQL. It explains that Postgres views are virtual tables that can be linked to regular tables using relationships, allowing for single nested queries to fetch related data. The Hasura GraphQL engine is an open-source tool that enables setting up a GraphQL server over Postgres in minutes, providing the ability to add views as relationships to tables or other views. An example use case of building a backend for a blog engine demonstrates how these concepts can be applied in practice.

This article discusses atomic set and increment operators for mutations in GraphQL using Hasura GraphQL Engine. It explains how to update multiple objects in one shot with a powerful whereargument, which allows the creation of a boolean filter to select objects to be updated. The article also covers the use of setoperator to set or replace an existing value, and incrementoperator to atomically increment numerical values by a given amount to prevent race conditions. Additionally, it highlights native JSON/JSONB operators that can modify attributes in a JSON/JSONB value without needing to set the full JSON value.

The Hasura GraphQL Engine provides efficient bulk insert mutations for creating new objects in a back-end database. It allows multiple objects of the same type to be inserted using a single SQL statement, and multiple objects of different types can be inserted using multiple insert mutation fields within the same mutation. If any mutation field fails, all mutation fields in that mutation will be rolled back, ensuring consistency. This feature is useful for applications requiring high performance and consistent database states during bulk inserts.

The article discusses how GraphQL can be used to improve internal application development in organizations. It highlights the challenges faced during the development process for internal apps such as budget and staffing constraints, compatibility with existing technology, and build versus buy decisions. The author then introduces GraphQL, a specification for a query language for APIs that offers features like fewer errors, flexibility, uniformity, and extensibility. The article further explains how GraphQL can transform internal app development by reducing the number of APIs needed, improving client app experience, automating API documentation, simplifying testing and deployment, and standardizing code and workflows. It also emphasizes that GraphQL is compatible with existing technology and can be used to stitch together multiple sources of data into a single endpoint. Several well-known organizations have adopted GraphQL as their primary API technology, and the author suggests that organizations should evaluate its potential benefits and consider adopting it for internal applications.

Postgres supports storing schema-less (NoSQL) data as JSON columns through dedicated hstore, JSON or JSONB column types. Even though type safety is a major push for moving to GraphQL, there are real-world use-cases where you’d still need some columns which can store data for which schema is not decided already. For example, an analytics system that logs events from various sources and an e-commerce website’s database where each product has a strict set of basic parameters, but a varying range of specifications (or specs). Hasura GraphQL Engine supports JSON/JSONB columns and also provides support for JSONB Boolean operators. It allows inserting arbitrary JSON into the table using mutations and supports Postgres JSONB operators like _append, _prepend, _delete_key, _delete_elem, and _delete_at_path. Additionally, views can be created to further inspect the contents of the column which can be queried through GraphQL as well.

This blog post discusses bulk update and delete mutations in GraphQL using Hasura GraphQL Engine. It explains how to safely update and delete data in a database over GraphQL while ensuring transactions for independent tasks without loss of data in case of failures. The author demonstrates examples of bulk mutations with the Hasura GraphQL Engine, which provides instant GraphQL APIs over Postgres without requiring any code. They also cover setup instructions for a GraphQL backend using Hasura Cloud and discuss scenarios involving bulk update, delete, and combined update-delete operations in various schemas.

Postgres supports storing schema-less (NoSQL) data as JSON columns through dedicated hstore, JSON or JSONB column types. Even though type safety is a major push for moving to GraphQL, there are real-world use-cases where you’d still need some columns which can store data for which schema is not decided already. Hasura GraphQL Engine supports JSON/JSONB columns and also provides support for JSONB Boolean operators. It allows inserting arbitrary JSON into the table using mutations and supports Postgres JSONB operators like _append, _prepend, _delete_key, _delete_elem, and _delete_at_path. Additionally, views can be created to query data stored in JSONB columns using known keys.