Alternatives to Sequel Pro

Recently we were looking at a few robust and cost-effective ways of replicating the data that resides in our production MongoDB to a PostgreSQL database for data warehousing and business intelligence.

We set ourselves the following criteria for the optimal tool that would do this job: - The data replication must be near real-time, yet it should NOT impact the production database - The data replication must be horizontally scalable (based on the load), asynchronous & crash-resilient

Based on the above criteria, we selected the following tools to perform the end to end data replication:

We chose MongoDB Stitch for picking up the changes in the source database. It is the serverless platform from MongoDB. One of the services offered by MongoDB Stitch is Stitch Triggers. Using stitch triggers, you can execute a serverless function (in Node.js) in real time in response to changes in the database. When there are a lot of database changes, Stitch automatically "feeds forward" these changes through an asynchronous queue.

We chose Amazon SQS as the pipe / message backbone for communicating the changes from MongoDB to our own replication service. Interestingly enough, MongoDB stitch offers integration with AWS services.

In the Node.js function, we wrote minimal functionality to communicate the database changes (insert / update / delete / replace) to Amazon SQS.

Next we wrote a minimal micro-service in Python to listen to the message events on SQS, pickup the data payload & mirror the DB changes on to the target Data warehouse. We implemented source data to target data translation by modelling target table structures through SQLAlchemy . We deployed this micro-service as AWS Lambda with Zappa. With Zappa, deploying your services as event-driven & horizontally scalable Lambda service is dumb-easy.

In the end, we got to implement a highly scalable near realtime Change Data Replication service that "works" and deployed to production in a matter of few days!

We use MongoDB as our primary #datastore. Mongo's approach to replica sets enables some fantastic patterns for operations like maintenance, backups, and #ETL.

As we pull #microservices from our #monolith, we are taking the opportunity to build them with their own datastores using PostgreSQL. We also use Redis to cache data we’d never store permanently, and to rate-limit our requests to partners’ APIs (like GitHub).

When we’re dealing with large blobs of immutable data (logs, artifacts, and test results), we store them in Amazon S3. We handle any side-effects of S3’s eventual consistency model within our own code. This ensures that we deal with user requests correctly while writes are in process.

Flyway vs Liquibase #Migration #Backwards-compatible

We were looking for a tool to help us integrating the migration scripts as part of our Deployment. At first sight both tools look very alike, are well integrated with Spring, have a fairly frequent development activity and short release cycles.

Liquibase puts a lot of emphasis on independence with the DB, allowing you to create the scripts on formats like JSON and YML, abstracting away from SQL, which it's also supported. Since we only work with one DB type across services we wouldn't take much advantage of this feature.

Flyway on the other hand has the advantage on being actively working on the integration with PostgreSQL 11, for it's upcoming version 6. Provides a more extensive set of properties that allow us to define what's allowed on what's not on each different environment.

Instead of looking for a tool that will allow us to rollback our DB changes automatically, we decided to implement backwards-compatible DB changes, for example adding a new column instead of renaming an existing one, postponing the deletion of the deprecated column until the release has been successfully installed.

Flyway vs Liquibase #Migration #Backwards-compatible

We were looking for a tool to help us integrating the migration scripts as part of our Deployment. At first sight both tools look very alike, are well integrated with Spring, have a fairly frequent development activity and short release cycles.

Liquibase puts a lot of emphasis on independence with the DB, allowing you to create the scripts on formats like JSON and YML, abstracting away from SQL, which it's also supported. Since we only work with one DB type across services we wouldn't take much advantage of this feature.

Flyway on the other hand has the advantage on being actively working on the integration with PostgreSQL 11, for it's upcoming version 6. Provides a more extensive set of properties that allow us to define what's allowed on what's not on each different environment.

Instead of looking for a tool that will allow us to rollback our DB changes automatically, we decided to implement backwards-compatible DB changes, for example adding a new column instead of renaming an existing one, postponing the deletion of the deprecated column until the release has been successfully installed.

Heroku Docker GitHub Node.js hapi Vue.js AWS Lambda Amazon S3 PostgreSQL Knex.js Checkly is a fairly young company and we're still working hard to find the correct mix of product features, price and audience.

We are focussed on tech B2B, but I always wanted to serve solo developers too. So I decided to make a $7 plan.

Why $7? Simply put, it seems to be a sweet spot for tech companies: Heroku, Docker, Github, Appoptics (Librato) all offer $7 plans. They must have done a ton of research into this, so why not piggy back that and try it out.

Enough biz talk, onto tech. The challenges were:

• Slice of a portion of the functionality so a $7 plan is still profitable. We call this the "plan limits"
• Update API and back end services to handle and enforce plan limits.
• Update the UI to kindly state plan limits are in effect on some part of the UI.
• Update the pricing page to reflect all changes.
• Keep the actual processing backend, storage and API's as untouched as possible.

In essence, we went from strictly volume based pricing to value based pricing. Here come the technical steps & decisions we made to get there.

1. We updated our PostgreSQL schema so plans now have an array of "features". These are string constants that represent feature toggles.
2. The Vue.js frontend reads these from the vuex store on login.
3. Based on these values, the UI has simple v-if statements to either just show the feature or show a friendly "please upgrade" button.
4. The hapi API has a hook on each relevant API endpoint that checks whether a user's plan has the feature enabled, or not.

Side note: We offer 10 SMS messages per month on the developer plan. However, we were not actually counting how many people were sending. We had to update our alerting daemon (that runs on Heroku and triggers SMS messages via AWS SNS) to actually bump a counter.

What we build is basically feature-toggling based on plan features. It is very extensible for future additions. Our scheduling and storage backend that actually runs users' monitoring requests (AWS Lambda) and stores the results (S3 and Postgres) has no knowledge of all of this and remained unchanged.

Hope this helps anyone building out their SaaS and is in a similar situation.

PostgreSQL Heroku Heroku Postgres Node.js Knex.js

Last week we rolled out a simple patch that decimated the response time of a Postgres query crucial to Checkly. It quite literally went from an average of ~100ms with peaks to 1 second to a steady 1ms to 10ms.

However, that patch was just the last step of a longer journey:

1. I looked at what API endpoints were using which queries and how their response time grew over time. Specifically the customer facing API endpoints that are directly responsible for rendering the first dashboard page of the product are crucial.

2. I looked at the Heroku metrics such as those reported by heroku pg:outlier and cross references that with "slowest response time" statistics.

3. I reproduced the production situation as best as possible on a local development machine and test my hypothesis that an composite index on a uuid field and a timestampz field would reduce response times.

This method secured the victory and we rolled out a new index last week. Response times plummeted. Read the full story in the blog post.

Postman is a nice desktop #REST #API client that allows you to save requests for later use. But it does not really support GraphQL, which I use everyday at work. So it was time to look for something else.

GraphiQL is a nice toy that has a desktop client, but you cannot save requests in any organized way. Most other clients I tried were either sluggish, didn't save requests, or didn't support cookies. Lack of cookie support is a no-no for work because we use session-based authentication in our internal API.

Then I stumbled upon Insomnia REST Client, and it clicked! Cookies work, GraphQL support is pretty good, UI looks nice and goes straight to the point. The only thing it lacks is a schema explorer, but I can always use GraphiQL if I ever need one, which is almost never.

Overall, I am very happy with it, and would recommend it to anyone seriously working with GraphQL. Insomnia is a godsend!