DEV Community: Jacob Cohen

I Stopped Buying SaaS for RevOps. I Built What I Needed on Harper Instead.

Jacob Cohen — Mon, 02 Mar 2026 21:22:58 +0000

TL;DR: A non-engineer RevOps leader, replaced a manual, error-prone reporting process by using OpenAI Codex to build and deploy a fully automated, production-grade Growth Weekly Digest application on the Harper platform.

I run commercial operations at a startup. That means Salesforce administration, deal desk, forecasting, pipeline management, contract workflows, and a dozen other things that keep our revenue engine running. I am not a software engineer. My day job is operations.

Last month, I built and deployed a production internal application that pulls live data from Salesforce and Slack, generates AI-assisted commentary, routes it through a multi-role approval workflow, and publishes a weekly growth digest for our entire company. It has eight database tables, five user roles, three external integrations, a scheduler with leader-node election, retry logic, CI/CD with automated testing, and it runs on a scaled Harper Fabric deployment. I built it in a few days using OpenAI Codex as my coding partner. I didn't write a single line of code myself.

What does "built" mean when you don't write code? It means I spent a few days having a conversation. I described what I needed, Codex planned and executed, and we iterated together until it was right. I'd explain a workflow, review its approach, push back when something didn't fit, ask it what common RevOps patterns look like, and refine until I was satisfied with the plan. Then it wrote the code. I did the thinking. It did the typing. That's the entire method, and it's why this post isn't a tutorial. There's no code to walk through. There's a conversation that produced a production application.

This post is about how I did it, why I did it on Harper instead of buying another SaaS tool, and why I think this changes the math for every business operator who's currently stuck choosing between manual processes and overpriced software subscriptions.

The RevOps Problem Nobody Talks About

If you work in RevOps, you know this situation. You have a process that's valuable, but tedious. It requires pulling data from countless, ever-changing systems, assembling it into something digestible, adding your own analysis, and distributing it. It takes 30 minutes to an hour. It's not hard, it's just manual enough that it consistently loses priority against everything else you're doing.

For me, that process was our Growth Weekly Digest.

Every Friday for the better part of a year, I produced a report that tracked our weekly sales activity: closed won and closed lost deals, new pipeline created, pipeline by rep and channel, BDR metrics, and narrative commentary about what happened and what to watch. The audience started as our executive team, expanded to team leads, and eventually the whole company. Our product management team relies on it to keep a pulse on sales activity, which is a critical input for product prioritization and roadmap decisions. People across the company found it valuable because it gave them visibility into the growth engine without having to dig through Salesforce or chase down updates.

The process to create it was entirely manual.

I'd start by pulling up Salesforce reports and dashboards. I had some dashboards on Salesforce's subscribe feature to email me at 9AM as a trigger to get started. Then I'd extract the numbers and copy them into a Google Doc: deal counts, revenue, pipeline breakdowns; you know the drill. That copy-and-paste step alone took ten minutes minimum and was highly error-prone. One wrong cell and the whole week's narrative would be based on bad data. This happened more often than I'd like to admit. The whole process violated one of my favorite rules: never introduce human error when you don't have to. I knew that copying and pasting numbers between systems was a terrible workflow, but I did it anyway because there wasn't a better option.

Then came the commentary. The numbers alone don't tell the story. I needed to explain which deals advanced, which stalled, what happened in customer conversations, and what risks were emerging. To write that, I'd piece together what I remembered from the week, scan Slack conversations, check meeting notes, and sometimes use Slack AI to help me summarize activity. The whole thing took 30 to 45 minutes, depending on how much digging was required.

It doesn't sound like much. Here's the real cost: I stopped doing it.

When the year turned over, and we were finalizing 2026 quotas, I paused the digest. We were well into Q1 before I confronted the fact that I didn't want to go back to the manual grind, even though people found the report valuable. At a startup, everyone wears multiple hats. I do far more than RevOps in a given week. The digest was always the first thing to get deprioritized. (Whatever you do, don't tell my boss, even though she's probably already forwarded this post to everyone she knows.)

So for weeks, the report just didn't get written.

Why I Didn't Buy Another SaaS Tool

The obvious move would have been to buy software for this. Here's why I didn't.

Salesforce dashboards aren't robust enough for this kind of consolidated weekly reporting, and more importantly, they require paid Salesforce licenses for every viewer. If you've ever priced Salesforce seats, you know the pain. The per-user licensing costs add up fast, and we already carry more licenses than we'd like. We are a cost-conscious startup and we absolutely do not get our money's worth out of every seat we hold. There was no world in which we were going to buy CRM licenses for the entire company just so people could read a weekly summary.

Databox was something we used for other executive-level dashboards that provided similar, though not identical, value. It worked for a while, but we ran into issues with their Salesforce connector that broke our data pipeline, and even when it was working, it didn't do everything we needed it to. That was the end of Databox for us.

Tableau was so comically expensive for our use cases that it wasn't even worth a serious conversation, even though we could have used it for other reporting needs too.

This is the pattern every RevOps person knows. The SaaS options are either too limited, too expensive, too rigid, or some combination of all three. And every tool you add is another vendor, another login, another integration to maintain, another line item for Finance to question. The thing you actually need is always slightly different from what the tool provides, so you end up building workarounds on top of the tool, at which point you're doing custom work anyway, just on someone else's platform.

I realized I could spend a few days building exactly what I needed, with real integrations, real auth, real deployment, on Harper. And it would cost less ongoing effort than re-establishing the manual process I'd abandoned. No point solution. No per-seat reporting license. No compromises on what the report should contain or who can see it. Harper is a platform, not a tool. The weekly digest is one application on it. The next one I build runs on the same infrastructure, the same deployment model, the same data layer. That's a fundamentally different cost equation than buying a new SaaS product for every internal need.

What I Built

The Growth Weekly Digest is now a Harper application. It runs on Harper Fabric with its own database, API, web interface, and scheduled jobs, all in one deployable unit.

Note: All screenshots in this post use mock data. Our actual digest contains proprietary sales information.

Here's what it does each week:

Data collection. On a scheduled trigger (Friday mornings), the application queries Salesforce via JWT-authenticated API calls. It pulls Opportunity data: deal names, stages, forecast categories, amounts, ARR fields, close dates, lead sources, and rep assignments. It also queries OpportunityPartner objects to classify deals by channel (partner-sourced vs. direct). Separately, it reads recent activity from configured Slack channels to capture qualitative signals about deal movement and team conversations.

Metrics computation. The raw data is processed into structured, frozen metrics: closed won and closed lost counts and revenue broken out by channel, new pipeline created, pipeline by rep, and week-over-week comparisons. Each digest is a snapshot of that week's state.

Commentary generation. Metrics and Slack signals are sent to OpenAI's Responses API with a system prompt that enforces structured JSON output. The LLM produces commentary organized into wins, risks, and action items, each with citation links back to the original Slack messages so we can jump directly into a conversation if something needs follow-up. The AI commentary is never published automatically. It's a draft starting point.

Human review and approval. This is the part that matters. The generated digest enters a review workflow. I review the data and commentary for accuracy. Kelli, our VP of Sales, does the same. Either of us can edit the AI-generated commentary before approving. The system tracks each approver independently. Both must approve before the digest can be published. This is a real workflow with real gates, not a rubber stamp.

Publication and notification. Once both approvals are in, the digest is published to a company Slack channel. Published digests are also accessible through the web UI, authenticated via Google OAuth restricted to Harper email addresses.

Access controls. Five roles: company_reader, growth_editor, revops_approver, growth_manager, and admin. Readers only see published digests. Unpublished runs return a 404 for readers. They don't exist until they're ready. Editors and approvers see drafts and can act on them. Role assignments are stored in a Harper table and enforced server-side on every request.

The Architecture: One Runtime, Not a Rube Goldberg Machine

This is where Harper's value as a platform becomes concrete.

The entire application is a single Harper application. The database, the API, the web UI, the scheduler: they all ship together and deploy together. Here's what's inside:

Eight tables define the data model: DigestRun, DigestMetrics, DigestCommentary, DigestNote, DigestApproval, DigestPublication, DigestRoleMapping, and DigestCommentaryRevision. These are declared in schema files. When the application deploys, the tables exist. No external database. No migration scripts. No connection strings. No ORM. Related tables are linked via indexed fields like runId, so queries across the data model are fast and consistent. The application code interacts with these tables through Harper's Resource class, a unified API where your database tables, business logic, and HTTP endpoints are all part of the same runtime. There's no separate database driver, no connection pool, no serialization layer between your code and your data. This matters for AI-generated code because the agent doesn't have to wire together disconnected services. It writes application logic against a single integrated platform, and that code is production-ready and performant immediately.

A TypeScript service layer handles the digest lifecycle: generation, metrics computation, commentary requests, approval state derivation, publish-blocker validation, and data quality checks. Business rules live here. For example, you cannot generate a new digest for a week that already has a published one.

Three external connectors: Salesforce (JWT OAuth with RS256 signing, SOQL queries against Opportunity and OpportunityPartner objects), Slack (Web API with channel discovery, bounded concurrency, and thread fetching), and OpenAI (Responses API with structured JSON schema enforcement, citation sanitization, and configurable retry budgets).

A scheduler with two jobs: a weekly cron trigger for Friday morning digest generation, and a periodic retry loop for any LLM commentary requests that failed due to timeouts or rate limits. The scheduler includes leader-node gating. In a replicated Fabric deployment across multiple nodes, only one instance runs the scheduler. No duplicate digests.

Server-rendered HTML for the web interface. A dashboard listing all digest runs, detail views for each, and action endpoints for generate, approve, publish, and edit operations. No frontend framework. No separate build step.

A CI/CD pipeline built entirely by Codex using GitHub Actions. It runs strict typechecking, tests, a regex-based secrets scan, and a guard against raw environment variable usage in code. Every time we merge to main, it automatically deploys to our production Harper Fabric cluster and runs a post-deploy health check against the live API. I didn't configure any of this. Codex built the whole pipeline, and now I don't have to think about DevOps either.

Here's how the data flows:

Now consider what this would look like if I'd let an AI coding tool pick a typical stack:

A Node.js or Python backend framework. A managed PostgreSQL instance. An ORM. A frontend framework with its own build pipeline. A reverse proxy. A hosting provider for the API. A different hosting solution for the frontend. A managed cron service or sidecar process for scheduled jobs. A secrets manager. Probably Redis for session management. Maybe a container orchestration layer to tie it all together. Each one of those is a configuration surface, a potential point of failure, and something I'd need to understand and maintain.

With Harper, the application is the whole thing. Database, server, scheduler, auth foundation, deployment target. It's one runtime. That's not a marketing claim. That's what made it possible for me to build this in a few days.

How I Built It: Vibe Coding for Business Operators

I used OpenAI Codex as my implementation partner. Let me be precise about what that means.

I did: Requirements definition. System design. Architecture decisions. Data model design. Integration specification: which Salesforce objects to query, which Slack channels to read, what the approval workflow should be, what the role model should look like. Acceptance testing. Best-practice review using Harper Agent.

Codex did: All code. Every line of TypeScript, every schema, every route handler, every connector, every CI workflow, every test.

I did not: Write code. Debug code. Configure deployment. Set up CI/CD.

The working pattern was iterative. I'd switch between Codex's planning mode and coding mode constantly. In planning mode, I'd describe what I wanted, sometimes high-level ("We need a Salesforce connector that uses JWT OAuth and pulls Opportunity and Partner data"), sometimes very specific ("Add a guard that prevents generating a new digest for a week that already has a published run. No new routes. No schema changes. Redirect with structured flash data for the dashboard."). Codex would implement it.

After each implementation pass, I'd feed the output to Harper Agent for best-practice review against Harper's application conventions. Harper Agent would flag issues: patterns that didn't align with how Harper resources should be structured, configuration that could be cleaner. I'd take that feedback and send Codex back in with specific corrections. Harper is currently building out Harper Agent to handle more of this end-to-end, but even today this feedback loop worked well.

This is the part of building software I've always done well: designing systems, defining requirements, making architecture decisions, evaluating tradeoffs. The part I don't want to do, and historically couldn't do without engineering support, is the implementation labor: the typing, the integration wiring, the debugging. Codex handled that. And because it was building against Harper's unified runtime, the output was deployable from day one. No glue code. No service orchestration. No separate infrastructure to configure.

Harper's npm create harper@latest scaffold was the starting point. That gives you a project structure with schema files, resource definitions, configuration, and importantly, a skills/ directory that grounds the AI agent in Harper's architecture patterns. Codex consumed those skills files as context, which meant it wasn't guessing at how to structure a Harper application. It had the conventions built into its working memory.

To give you a sense of what that first conversation looked like, here's a prompt similar to the one I used to kick off the project. I uploaded my existing Google Doc digest as a reference so Codex could see exactly what the output should look like, then described what I wanted to build:

I want to build a Harper application that replaces this manual Google Doc "Weekly Digest" report (attached). The app should pull data from Salesforce and Slack automatically, compute the same metrics I've been assembling by hand, and generate a weekly digest. Build it as a Harper Application (https://docs.harperdb.io/docs/developers/applications), ensuring you use the Harper Resource class (https://docs.harperdb.io/docs/reference/resources). Start with the create-harper template (https://www.npmjs.com/package/create-harper). The app needs role-based auth, where company-wide readers can only see published digests. An approval workflow requiring two approvers before publish. OpenAI integration for drafting commentary from the Slack and Salesforce data. Scheduled generation on Fridays plus a manual trigger. A dashboard, digest detail view, and ops page. Strong environment validation, secure cookies, structured logs. CI pipeline with secrets scanning, strict typechecking, and tests. Once we get a version of the app working locally, we will be deploying to Harper Fabric (https://docs.harperdb.io/fabric). Let's work together and build a plan to implement this solution. We will plan to work iteratively and add features as we go.

That's it. That prompt, the attached Google Doc as a reference, and the skills files Harper provides for AI context were enough for Codex to scaffold the entire project structure and start building. From there, every feature was a conversation: "Add the Salesforce connector," "Wire up the approval gates," "Build the CI pipeline." Each one a sentence or two of intent, followed by Codex executing and me reviewing.

For context: this was my second Harper application built with Codex. The first was a personal project I created just to learn the workflow. The moment I saw how it worked, I realized I should be building our actual internal tooling this way, everything running on a single Harper backend that scales through Fabric without me thinking about infrastructure.

What Harper Made Possible

Let me be specific about what Harper provided versus what I would have had to solve myself on any other platform.

No database to provision. Schemas declared in files. Deploy the application, tables exist. Eight tables with indexes. No DBA, no managed instance, no connection pooling.

No application server to configure. Harper serves HTTP routes natively, both the web UI and the JSON API. No Express, no nginx, no port management.

No deployment pipeline to architect. I pointed Codex at the Fabric documentation and told it to handle deployment. It built a GitHub Actions pipeline that deploys to our production Harper Fabric cluster with rolling restart across replicated nodes, runs a post-deploy health check, and confirms the API is responding. Every merge to main triggers it automatically. I have never manually deployed this application.

No scaling to think about. Fabric handles replication. The only concession to multi-node was the leader-node scheduler gating to prevent duplicate scheduled jobs, and that was a straightforward pattern. I don't manage infrastructure. I don't think about infrastructure.

Auth handled by the platform. Harper has an OAuth plugin that handles OAuth 2.0 and OpenID Connect authentication out of the box, with support for Google, GitHub, Azure AD, and other providers. The plugin manages the OAuth flow, token refresh, session integration, and CSRF protection. On top of that, the application layer enforces role-based authorization checked server-side on every request. Having the database and the application in the same runtime made this simple. Role mappings are just another table, checked in the same process that serves the request.

Mobile-friendly because I asked for it. I told Codex I wanted the UI to work on phones. It built a responsive layout. That's it. No separate mobile project, no responsive framework to configure. Our executives do a lot of work on their phones, especially when traveling, and the old Google Doc was painful to read on a small screen. With Codex building on Harper, "make it mobile-friendly" was a sentence in a conversation, not a sprint.

The critical point: what I built locally is what runs in production. There is no gap. I run npm run dev locally, iterate with Codex, and when it's ready, the same application deploys to Fabric. One platform. One set of concerns. One thing to understand.

This is what makes it viable for a business operator to ship production software. Not because the coding is easier (Codex handles the coding), but because the operational surface area is small enough that a non-engineer can reason about the whole system. I don't need to understand Kubernetes, or Terraform, or how to wire a PostgreSQL connection pool. I need to understand Harper, and Harper is one thing.

What's Running Today

The application is live in production on Harper Fabric. It went live in mid-February 2026. The system now automatically produces a new digest every Friday. As of this writing, it has produced eight digests. That includes backfilled digests for the weeks I missed at the start of the year, which the system generated retroactively once it was live.

The weekly process now works like this: the app generates a digest on Friday morning. Kelli and I get a Slack notification. We review the metrics and commentary, make edits, and approve. When both approvals clear, the digest publishes to a company Slack channel and is available in the web UI for anyone to read. The review step takes a few minutes.

The time savings are real, at least 30 minutes of manual work eliminated every week, but that understates the actual impact. The real win is that the digest gets produced now. Every week. Automatically. Before this, the manual effort meant it was always the first thing I'd cut when the week got busy. That's not a time-savings story. That's a "the process actually works now" story.

I also plan to keep iterating. Our CEO has already discussed combining this with other internal tools the team is building on Harper into a single unified internal operations platform. In fact, the moment he saw this tool running, he asked me to write this blog post. (So here we are.) That convergence is natural because all these applications share the same data layer and deployment model. No integration work required. They're already on the same backend.

This is the part that I think gets missed in the "build vs. buy" conversation. When you buy a SaaS tool, you get one solution for one problem, and the next problem requires a new vendor. When you build on a platform like Harper, the first application is the hardest. The second one is easier because the platform is already there, the deployment model is already running, and your team already knows how it works. Harper is the only platform where coding agents can build and deploy enterprise-grade applications end to end, and once you have that foundation, every internal tool you need is just another application on the same infrastructure. The weekly digest was my first internal tool. It won't be my last.

The Math That Changed

Let me frame this for every RevOps operator, sales ops lead, or business systems person reading this.

You probably have a process right now that works like my old digest. Valuable output, tedious assembly, always at risk of being deprioritized. You've probably looked at SaaS tools to automate it and found them too expensive, too rigid, or too limited. You've probably thought about asking engineering to build something and decided it wasn't worth the political capital or the wait.

The math has changed. Here's why:

AI coding tools can now write production-quality code when given clear requirements and a well-structured platform to build against. The bottleneck was never the coding. It was the operational complexity of deploying and maintaining what the code produces. If your AI tool generates a great application but it requires you to manage a database, a web server, a cron service, a CDN, and a container orchestrator, you haven't saved yourself anything. You've just traded one kind of complexity for another.

Harper collapses that complexity. One runtime. One deployment target. One thing to maintain. Your database, your API, your frontend, your scheduled jobs, your auth: they're all the same application. When you deploy to Fabric, it scales. When you iterate locally, you're working against the same system that runs in production.

That's what makes it possible for someone like me, an operations person who understands systems but doesn't write code, to build and maintain production internal software. Not a prototype. Not a demo. A real application with enterprise integrations, approval workflows, role-based access control, CI/CD, and automated deployment.

The alternative is buying more SaaS. Another subscription. Another vendor. Another tool that does 70% of what you need and requires workarounds for the rest. Another integration to maintain. Another thing that breaks when the vendor pushes an update you didn't ask for.

Or you can build exactly what you need, on a platform you control, and ship it to production in days.

I know which one I'd pick. I just did.

Jake Cohen is Senior Director of Commercial Operations at Harper, where he has spent over eight years across Solutions Architecture, Product Management, Engineering Leadership, and Commercial Operations. He holds a B.S. in Computer Engineering from George Mason University. You can find him on LinkedIn.

To start building on Harper, visit harper.fast/vibe or run npm create harper@latest.

HarperDB is Collapsing the Stack: Introducing Custom Functions

Jacob Cohen — Thu, 09 Sep 2021 15:35:17 +0000

Introducing the newest innovation from HarperDB: HarperDB Custom Functions. With the release of HarperDB 3.1 users are able to define their own API endpoints within HarperDB. What does that mean for you? HarperDB grows from a distributed database to a distributed application development platform with integrated persistence - one that can serve as a single solution for all of your backend needs. We’re collapsing the stack!

Alright, alright, what’s the big deal? Up until 3.1, in order to power an application you would need to deploy and host your backend API code on additional servers, then have them call out to HarperDB for database needs. This is a pretty typical software stack, but at HarperDB, we’re far from typical. We’re constantly innovating and changing the game. Custom Functions enable developers to build their entire application backend in one place. Oh yeah, and it’s faster, significantly faster! Traditional architectures naturally introduce latency as data moves across multiple servers through a local network or potentially even the Internet. HarperDB is collapsing the stack onto a single server, which eliminates any and all network latency. This frees up headroom for achieving higher throughput from a single server. Capitalizing on HarperDB’s already powerful horizontal scalability, this means you can now distribute both your APIs and your database to the edge.

For those familiar with modern cloud architectures, Custom Functions are just like AWS Lambda functions. For those familiar with relational databases, they’re like Stored Procedures. You define your logic and choose when to execute it. At a high level it’s as simple as that! They’re low maintenance and easy to develop. You can develop HarperDB Custom Functions in the Studio or in your own IDE and Version Management System. HarperDB Custom Functions can be maintained like any other development project, in fact, the sample Custom Functions provided in the Studio are generated from our public GitHub repository. That means you can develop, maintain, and deploy your HarperDB Custom Functions code just like any other development project, so you don’t have to deviate from your existing development practices. That’s great news!

HarperDB Studio Functions Editor

What makes Custom Functions so powerful? They leverage the full power of Node.js and Fastify. HarperDB Custom Functions projects are effectively just Node.js projects, which means you can leverage the npm ecosystem, opening the doors to fast and efficient development. Fastify serves as the basis for the webserver, which means you can define and build a fully functional REST API with all the bells and whistles you’d expect. The key differentiator is that these Fastify routes have direct access to HarperDB core methods, bypassing the HarperDB API, and instead interacting directly with HarperDB on the same machine.

WebStorm IDE with HarperDB Functions Project

By collapsing the stack, we deliver unparalleled performance and efficiency out of the box. Let’s take a look at some different ways Custom Functions can be used. I’m not going to cover everything here, in fact, I’m sure there are plenty of options that I haven’t even thought of.

Integrate with third-party apps and APIs: Seamlessly connect third-party/external data with data stored in HarperDB within a single function.
Utilize third-party authentication: Tightly integrate with third-party application providers to validate user requests within your API.
Define your own database functionality: HarperDB is always adding features, but let’s say there’s a feature you need that’s missing. Build a HarperDB Custom Function to solve the problem. For example, if you need to enforce row-level security based on a user account, write a function!
Serve a website: Custom Functions can serve static content and serve as backend APIs, which means you can fully power a website or web app all with HarperDB.

These are just some of the ideas we’re kicking around with HarperDB Custom Functions. We’ll be hosting a livestream event next week (September 14th at 6pm MT) where you can watch a live product tour! We look forward to hearing what the HarperDB community can build. Please share any ideas you have in the comments, I’m eager to hear what the community has to say!

Since this will be the initial release of HarperDB Custom Functions, please let us know what else you’d like to see in future releases. You can submit your ideas to our feedback board here: feedback.harperdb.io.

Reducing Data Latency with Geographically Distributed Databases

Jacob Cohen — Tue, 02 Mar 2021 15:53:02 +0000

Do you ever have those moments where you know you’re thinking faster than the app you’re using? You click something and have time to think “what’s taking so long?” It’s frustrating to say the least, but it’s an all-too-common problem in modern applications. A driving factor of this delay is latency, caused by offloading processing from the app to an external server. More often than not, that external server is a monolithic database residing in a single cloud region. This article will dig into some of the existing architectures that cause this issue and provide solutions on how to resolve them.

Latency Defined

Before we get ahead of ourselves, let’s define “latency.” In a general sense, latency measures the duration between an action and a response. In user facing applications, that can be narrowed down to the delay between when a user makes a request and when the application responds to a request. As a user, I don’t really care what is causing the delay resulting in a poor user experience, I just want it to go away. In a typical cloud application architecture, latency is caused by the Internet and the time it takes to make requests back and forth from the user’s device and the cloud, referred to as Internet latency. There is also processing time to consider, the time it takes to actually execute the request, which is referred to as operational latency. This article will focus on Internet latency with a hint of operational latency. If you’re interested in other types of latency, TechTarget has a good deep dive into specifics of the term.

Modern applications have reached a point where end-user performance is critical. However, in practice, most application architectures have not fully caught up to support globally consumed applications. I’ve personally run into cases over and over again where I hear that the application has been distributed around the world, but the database is stuck back in a single cloud geography. We’ve reached a point in technology where static assets are easy to distribute, but persistent data storage is not. Content delivery networks (CDN) have effectively solved the problem of latency in delivering static content. Movies can be streamed across the globe effortlessly because they are static, they don’t change. I can stream Goldfinger (the best Bond movie, by the way), all over the world because it’s hosted across the globe via a CDN. That’s an incredible feat, but what about the metadata associated with that streaming app? What happens when the app needs to remember where I paused the film, when I rate the film (5/5 of course), or if I want to add it to my list of favorites? That data needs to be recorded in a database somewhere for future access. Based on my experience with modern application architecture, that database is most likely centralized in a single cloud region. Depending on where I am in the world, this can result in excess latency for simple application features like clicking around the streaming app. Metadata has to be queried and returned through the pipes of the Internet, potentially across the world, creating a poor user experience on basic application features even when my movie is streaming crystal clear.

The Problem: A Centralized Database

Why is data storage such a bottleneck? Databases are complex, they need to be able to process large amounts of data transactions and stay responsive at all times. The most popular and flexible databases on the market, both SQL and NoSQL, have proven to be incredibly difficult to distribute. As such, application architects typically choose to leave them in a single geographic region and scale vertically to handle increasing usage requirements. This works for a while, but as the application grows, so does the demand on the database, eventually causing ballooning costs and increased latency when not physically near the database’s region. I’ve seen standard Internet latency range from anywhere between 200 milliseconds to a few seconds.

Internet Latency with a Centralized Database

Real World Examples

Before jumping into the solution, let’s take a quick look at some latency-sensitive applications where high latency quickly leads to poor user experience.

Gaming

I don’t pretend to be an expert gamer, but I’ve dabbled in the past. Nothing was more infuriating than my character getting crashed/killed/destroyed because of lag. In my case it didn’t really matter, I’d yell and scream at my TV for a few seconds, like the mature high schooler that I was, and go about my day hoping for redemption (and no lag) in the next match. That said, gaming has turned into a major business with huge competitions, each with millions of dollars in prize money on the line. Imagine losing a million-dollar prize because of a latency issue. Talk about a poor user experience.

Home Internet of Things (IoT)

As soon as I moved into my new house I went a little crazy with the smart home things. I’ve got cameras, alarm sensors, smart plugs, smart speakers, a connected thermostat, and some more gadgets I’m forgetting. They’re pretty cool, but the one thing that drives me absolutely crazy is just how long it takes for the apps to respond. My smart alarm system is not quite as smart as I’d hoped when I installed it. Someone could break into my house and get to the second floor before the alarm detects the break in. Why? Because the processing takes place in the cloud, somewhere that’s far away from my house. Now, I understand that when it comes to speech processing for my smart speakers, but you’re telling me that a contact sensor needs to go all the way to a major data center just to tell me that the door is open? That’s a blatant latency issue. Keep in mind, the cloud is still an important factor here because that activity needs to be logged and recorded. However, reducing that latency from multiple seconds down to a few milliseconds would certainly give me more peace of mind.

Autonomous Vehicles

It wouldn’t be a proper blog by yours truly if I didn’t sneak in some sort of car reference. Autonomous vehicles are the future. Yes, I’m going to cling to the steering wheel as long as I can, but that’s because I enjoy driving. That said, if the car next to me is autonomous, I want it to be able to detect any mistakes I make as quickly as possible. This is why most of the processing is done on board the vehicle itself. Personally, I see a later phase of vehicles being connected and communicating with each other, which will require some sort of data orchestration between vehicles. This is a place where latency is not an option. Imagine a connected car needing a few seconds to alert another connected semi-truck behind it that there’s a traffic jam and it should exit first. Those few seconds may be the difference between the truck rerouting or being stuck in traffic. Another case where latency is the difference between success and failure.

Warehouse Robotics

Beyond cars, I’m also a big robot guy, so this is another fun example for me. There is all sorts of innovation going on within warehouse robotics and distribution facilities. In many of these cases control decisions are still made in the cloud, which makes sense, because you need serious computing power to control a swarm of robots. Latency can seriously affect production, or in this case, output. If a robot has to stop what it’s doing while waiting for a response from the cloud, even just for a second, that can lead to hours of lost productivity across the swarm of robots over the course a day. Sure, it’s automated, but you might as well have your robots operating as efficiently as possible.

The Solution: Decentralization

If you’ve made it this far, I’d imagine this issue resonates with you, so let’s get into the solution. To solve these latency challenges, you need two very important things: distributed data centers and a database technology that can be distributed. Effectively, decentralization. Some of you may have seen this coming based on the title… Let’s dig into each of them separately.

Distributed Data Centers

You can’t geographically distribute any software without the physical hardware to deploy it on. There are all sorts of options for geographically distributing software, I prefer edge data centers because they bring the computing power physically closer to the end user. That means a shorter distance for network traffic to travel and typically means fewer hops, resulting in faster response time based purely on physics. Alternatively, you could choose a multi-cloud approach using a combination of cloud providers and/or private data centers to achieve a more distributed solution free from single cloud provider lock-in (something that will certainly make your CFO happy). Realistically, I see a hybrid of both as the solution which allows you to capitalize on the best of both worlds.

Distributed Database

This is the tricky part. Like I said earlier, CDN technology is established, but distributing data effectively is a whole different beast. Enter HarperDB, a distributed database solution that can be installed anywhere while presenting a single interface across a range of clouds, with backend ability to keep data synchronized everywhere. What makes HarperDB unique from other, more traditional database solutions? Critically, it’s not a cloud exclusive product like DynamoDB (exclusive to AWS), Cosmos DB (exclusive to Azure), or Firebase (exclusive to GCP). These are all strong products, but they are exclusive to a single cloud provider and as a result can only be distributed in those respective data center locations. In practice, they’re also difficult to distribute in general, as I discussed earlier. That’s not the case with HarperDB. Instead, HarperDB is cloud agnostic, meaning it can run anywhere, whether you’re installing from npm, running a Docker container, or choosing a managed service. It’s so flexible that I have a HarperDB instance running on my laptop. Once installed, HarperDB’s clustering and replication can be configured to automatically synchronize data between nodes, regardless of where they’re installed, often times faster than an external request would execute. I should mention that HarperDB is fast on its own, which helps to reduce the operational latency that I explained above. Finally, HarperDB provides a simple and elegant single endpoint API, which provides a consistent interface for consumers.

Reduced Internet Latency with Geographically Distributed HarperDB

With the power of HarperDB, we can extend the concept of CDN to geographically distributed data centers, providing end users with a fast and consistent solution and an improved user experience. Gamers can stop throwing their controllers at the TV, autonomous vehicles can route more effectively, home security can be more responsive, and warehouse robots can be more time efficient. Many organizations have come to accept cloud latency as a given, but it doesn’t have to be. With the power of a geographically distributed database, we can empower innovators to create faster and smoother applications by reducing latency caused by long distance routing. Thus, latency is solved!

The Unbiased Guide to Choosing the Right BI Tool

Jacob Cohen — Mon, 08 Feb 2021 19:54:21 +0000

Business Intelligence (BI) is the ultimate end goal of digital transformation. The ability to make better, smarter, more efficient decisions based on data collected across the business is what drives technical investment. This makes choosing the right tool paramount for your organization. As a database instructor and mentor of mine used to say: data is worthless until it becomes information. Information is what happens when you use data to glean insights and conclusions. What better way to do that than with a tool designed for the job? Great news! There are a ton of options out there for you, potentially too many. In this blog, I’ll discuss strategies for selecting the right BI tool as well as some important things to keep in mind throughout the process.

Let’s start by defining what exactly makes up a BI tool. Don’t tell my English teachers, but I’m going to cite Wikipedia. According to them, “Business intelligence software is a type of application software designed to retrieve, analyze, transform and report data for business intelligence. The applications generally read data that has been previously stored…” I think that does a pretty good job of defining the broad terms, but that still leaves us with a wide variety of software choices. Spreadsheets, database management studios, dashboarding tools, data mining software, all fall under this umbrella. For the purposes of this blog, I’ll be operating under the assumption that we are referring to the more colloquial BI tools which focus on providing visualization capabilities to end users of any skill level. Please note that I’m agnostic here and neither I nor HarperDB are promoting certain tools over others. This blog is intended to share my experience and provide general guidelines for making the decision that’s right for you.

There are plenty of minor factors that come into play when choosing the right BI tool, but let’s take a look at some important ones. Here are what I consider to be effective criteria to determine the right BI tool for your organization:

Technical Compatibility
Does the tool connect to the existing datastores in your organization? If the tool can’t connect to your datastores or would require a large data integration project, it’s probably best to move on and find something that works a little closer to out-of-the-box. I’ve worked with quite a few organizations that have legacy data stored in legacy database technologies. Just because the tools are old doesn’t make the data any less valuable and it may be critical for true business intelligence understanding. If this is the case for your organization, you may have to skip some of the more modern, web-based solutions. For example, neither Looker nor Google Data Studio support old school JDBC or ODBC connections, which can be a limiting factor of legacy technology.

User Base/Ease of Use
What tools are your end-users already familiar with? Providing them with BI tools that have a similar user experience to tools they already use helps ease the transition to the new tool and drives adoption. If end users are intimidated by the complexity of the new solution they are far less likely to adopt it. For example, if most of the end-users are already Excel power users, selecting Microsoft PowerBI is going to have a far greater chance of success because PowerBI is basically Excel for analytics. Another example is if your organization is powered by G Suite, going with Google Data Studio might make the most sense because it fits into the already existing ecosystem.

Reporting Capabilities
Does the tool provide the proper reporting and analytics? At the end of the day, if you need to run specific analytics and produce distinctive charts, you need the right tool that can do it. The big players in the BI tool world most likely have everything you need, but it’s still important to verify ahead of time. I have seen cases where the leading tool is disqualified for not being able to produce a chart that a key stakeholder considered critical.

Collaborative Functionality
Does the tool feature collaborative functionality to build and share charts and dashboards across teams? We should be past the days of accessing an Excel file or, even worse, an Access database file from a shared drive. (Yet I keep seeing them, so don’t feel too bad if you have some left, but please digitally transform already). This means we should be choosing tools that are collaborative at their core. The ability to build a chart with your team, share, and distribute is critical in modern business. Especially now that I haven’t left my house in months.

Pricing
Is the functionality worth the cost? I’m not going to dig too much into this one, it’s pretty obvious. However, if Fred, our COO, were writing this blog this section would be the largest one, but that’s his job. That’s the point, the technical team may want the biggest and baddest tool, but if the business stakeholders don’t see the value in it then you’re not getting your fancy tool. Pricing for BI tools is wide-ranging, for example, Tableau is over $800 per user per year and Google Data Studio is free. Sure, Tableau has more features, but ask your COO which one they prefer once they hear that tidbit.

Optional: Mobile Friendly
Does the tool offer mobile-friendly dashboarding and reporting? Now, I say this is optional because for some organizations this just isn’t a priority or a necessity. Others could find immense value in giving its users a real-time dashboard in the palm of their hands. For example, anyone with physical assets could benefit from having status and management of everything with them at all times instead of relying on a laptop.

Ancillary Requirements
What else do we need? Here’s my catchall. Each organization operates its own way, I’m sure there are things I’ve missed here that are important to you. Maybe you only run Microsoft OS, so the tool needs to be able to run there as well. Perhaps email alerting is a requirement.

There are plenty of criteria and requirements that could sway the decision of a BI tool. Hopefully, this blog helps provide some unbiased opinions and thoughts of what to look for when getting started with your BI tool search.

Now for a bit of HarperDB bias. Our database technology was built to be powerful and easy to use for all aspects of data collection and analysis. In fact, HarperDB can easily serve as a data warehouse to coalesce data from disparate data stores, so you only need to hook your BI tool up to a single data source. We offer plenty of drivers, like ODBC and JDBC, out-of-the-box that work natively with client-based tools like Tableau and PowerBI. We also have an Excel Add-In that can be used for makeshift BI purposes. In fact, I’ve seen some of our partners build some incredibly powerful tools for users who just want to stay in Excel for everything. However, as a newer database, there are times when we run into BI tools that don’t support HarperDB out of the box. Fortunately, many of these tools allow the community to build their own connectors and integrations. We currently have someone working on a Google Data Studio connector through our bounty program which should be ready to go in the near future. I’m pretty excited for that one! Additionally, the HarperDB Studio has built in charting functionality, making it a free and powerful BI tool of its own. Did I convince you to give it a try? Try HarperDB Cloud for free today!

HarperDB Studio Charting

5 Ways to Use HarperDB in Your Next Project

Jacob Cohen — Tue, 05 Jan 2021 17:49:04 +0000

HarperDB strives to provide the simplest and most streamlined database solution for developers everywhere. That said, just because it’s simple doesn’t mean that it’s not powerful. HarperDB provides plenty of powerful tools to use in a diverse set of projects. In a year that has been unhinged, HarperDB has only added stability. Features and functionality have been hardened, and we’ve added some exciting new features like Upsert, JSON Search, and Token Authentication. As we look forward to next year, I wanted to hit on some of the things that I consider to be emerging and ongoing trends into 2021. Let’s take a look at some examples of how you can use HarperDB in your next project!

Metadata Management

Let’s start simple with metadata. As our ability to collect data improves, our data footprint also grows to the point where we are storing metadata, which literally means data about data. It’s data that informs us about other data, and that right there is how you know you need a powerful database solution.

Let’s dig into an example. I’m going to take one of the easier, more obvious examples: photos. Particularly photos from your phone. Both Android and iOS (and most modern digital cameras) store EXIF data, which is a standardized format for photo metadata that includes typical things like: timestamp, dimensions, and resolution. It also includes more detailed data like: device make and model, aperture value, focal length, ISO speed, F-number, and longitude and latitude. That implies that there are plenty of different ways to search for photos in an application.

Why is HarperDB better at this than the average NoSQL database? Enter my old friend SQL! Data can be ingested into HarperDB however you want, but the easiest way to build a crazy conditional query is with SQL. For example, I can find all photos that were taken on an Apple product with ISO speed between 25 and 200 with the following query.

SELECT *
FROM photos.metadata 
WHERE device_make = 'Apple' 
  AND iso_speed BETWEEN 25 AND 200

Yeah, I get it, that’s a weird thing to be looking for, but maybe that’s exactly what I need.

Geospatial Data Analysis

Heavily related to metadata, but more specific, geospatial data deals with the added complication of maps, and wow is it complex! So complex, in fact, that there are a bunch of competing geospatial data standards. I’m a big fan of JSON, so I tend to use GeoJSON when dealing with geospatial data. This certainly falls in the metadata category, but geospatial data is considered a subset because it requires specially tailored functions to effectively gain insights into the data.

I’m going to keep going with my photo example from above. We know every photo already comes with latitude and longitude coordinates, so I can easily write queries in HarperDB using the built-in geospatial functions. I grew up near Washington, DC, so I can easily run a query to count how many photos I’ve taken within a mile radius of the Washington Monument with this query:

SELECT COUNT(*)
FROM photos.metadata
WHERE geoNear([-77.035257,38.889571], geojson_point, 1, 'miles')

Because we’re using SQL, we can run all sorts of different filters and aggregations to narrow down to the exact data we need.

IoT Sensor Data Collection

Changing it up a little bit on this one. The Internet of Things (IoT) has taken off in the last few years and only has more room to grow. The thing about IoT (no pun intended) is that the data is incredibly unstructured. Sensors can return data in all sorts of different formats and you never really know what you’re going to get, especially when trying to configure a hodgepodge of different sensors for different manufacturers. You could use a NoSQL database to collect all of that data, but then you have to ask yourself: what happens once I have that data? If you’re going to do anything beyond archive it, you’re going to need to be able to query it effectively. That’s where HarperDB thrives, as I mentioned above, you can execute SQL on that data immediately. That is made possible by the HarperDB Dynamic Schema!

You might say: What’s a Dynamic Schema and why do I care? A dynamic schema adjusts to the data as it’s ingested. In the HarperDB case this means attributes are reflexively added to the schema as they come into the database. For example, if I add a new sensor with additional attributes and dump them into a sensor table, any new attributes just show up. This means you now automatically have metadata (Aha!) on your schema that you would not have in a standard NoSQL database.

There are a couple of other fancy HarperDB features you might appreciate when working with sensor data. Take for example the HarperDB WebSocket SDK where you can create a publish/subscribe client which you can use to listen to data as it’s ingested and take immediate action. Additionally, you can use Clustering and Replication features to move data between instances, but I’m getting ahead of myself…

Distributed Data Systems

This is what I consider to be the most promising future trend! Truly distributed data, not simply a few data centers across the world, but points of presence physically near users. Sort of like 5G towers everywhere, but for your data. Distributing data on or near the edge is the most effective way to reduce latency for your users and reduce load on your servers, ultimately improving overall customer experience. Of course, this is not something that happens overnight, and most likely not on the initial launch of your project, but it’s important to consider scalability of a project.

At HarperDB, this is top of mind for us always. We call this clustering and replication, and we provide users with the granularity to define exactly what data is moving and where it’s going by configuring data to publish/subscribe at a table level. This means, in the IoT example above, we can configure our devices to publish their data to a primary server, but not receive (or subscribe) any data from anywhere else. In a fully distributed example where we want exact replicas across the globe, we would configure all tables to publish and subscribe. This flexibility enables you to define exactly how your data moves. We have some exciting distributed features on our roadmap for 2021, so be sure to keep an eye out for them!

Data Science

This is a broad category that involves the analysis of data, both structured and unstructured, to extract knowledge. Two of the most popular data science techniques are Machine Learning (ML) and Artificial Intelligence (AI). To call ML/AI a trend would be an understatement. Sometimes it seems like they’re all people can talk about, but for good reason- they’re powerful. I’ve always been a proponent of using a database to power these models, as it provides a great foundation of tools to aggregate and query data. My colleague Margo put together a great blog on using a database for Machine Learning, which you should absolutely check out if you’re interested in this sort of thing.

Closing Thoughts

This is a small subset of projects where HarperDB will provide solid underpinnings and is by no means a complete list. You can try out HarperDB for free with HarperDB Cloud. Give it shot and I think you’ll find that it’s incredibly easy to use and great for rapid and effective development. Have you used HarperDB in other types of projects? What other types of projects do you think HarperDB would be good for? Drop your thoughts in the comments below!

Hey CodeLand! We're HarperDB and we make data management easy throughout your coding journey!

Jacob Cohen — Thu, 23 Jul 2020 12:00:31 +0000

Hello! We're excited to be here and meet all of you during CodeLand and beyond. Collaborative developer communities like DEV and CodeNewbies have full support from the HarperDB team, and we love being a resource for people across the world on their coding journeys.

HarperDB was founded with the goal of delivering a simplified solution for developers without sacrificing scale or performance. HarperDB is a distributed database focused on making data management easy. With an intuitive REST API, and NoSQL & SQL operations including joins, users can be up and running in minutes.

Our Latest News

We're excited to announce that we recently released HarperDB Cloud, our fully managed and hosted Database-as-a-Service powered by AWS with the same code base and single easy to use API endpoint. It supports NoSQL & SQL including joins, has an intuitive Management Studio enabling you to install, design, cluster, and manage your databases without writing a line of code.

We would love for you to check it out. You can learn more here or watch our launch tour video:

Got Questions? Want to walkthrough a demo together? Let's Chat!

We'll be hanging out at our DEV Connect channel from 10:00am to 9:00pm ET today. We'd love to answer any questions you have about HarperDB, so please swing by!

If today doesn't work, you can schedule a call with us anytime: https://app.hubspot.com/meetings/margo8

or simply leave a comment down below :)

A special discount just for CodeLand attendees ✨

Try HarperDB Cloud for free, or use coupon code “CODELAND” for $250 off any larger instance to unlock features like clustering and full support.

SQL Queries to Complex JSON Objects

Jacob Cohen — Tue, 02 Jun 2020 18:44:53 +0000

How many times have you run into a situation where you wish you could do a SQL join without getting duplicate rows back? What if we could get a list "column" returned instead? HarperDB’s ARRAY() function enables just that. In this post we’re going to take a look at a basic example of people with addresses and phone numbers.

Use Cases for the ARRAY() Function

Most existing systems have trouble transforming relational data into hierarchical data. Typically large batch processes or ETL jobs exist to perform these data transformations. HarperDB can perform these transformations out-of-the-box with a single SQL query. This query effectively performs the job of an ORM without the need for bloated software. Don’t think this is possible? Keep reading.

How the ARRAY() Function Works

The HarperDB ARRAY() function, forthcoming in a future release, is an aggregate function, similar to COUNT, SUM, AVG. The difference is that while standard aggregate functions will return computation results, ARRAY() returns a list of data as a field. While this may not be intuitive to those, like myself, who have been using SQL for years, it does enable the developer to create complex JSON objects with a single query. Let’s take a look at an example use case…

Example Data

We’ll be working with People, Phone Numbers, and Addresses. Each Address and/or Phone Number links back to a single Person. We have 10 person records, each with one or more phone numbers and addresses for a total of 20 addresses and 24 phone numbers.

Array Example ERD

Connecting Person and Phone Number

Let’s say I want to get all of the phone numbers for a person with ID 1. That’s fairly simple, I just query the phone number table for that person. But what happens if I also want to get the person data? I have to execute two queries and connect the data in my application.

SELECT * FROM arr.person WHERE person_id = 1
SELECT * FROM arr.phone WHERE person_id = 1

Now what happens if I want to get all people and all of their phone numbers. While I’d like to do a simple join, I can’t, because I’d end up with duplicate person data.

SELECT * FROM arr.person LEFT JOIN arr.phone ON person.person_id = phone.person_id

So, again, I have to run two queries and aggregate the data together in my application.

In HarperDB, we have the ARRAY() aggregate function which allows us to return this data, with no duplicates, in a single query. Remember, because ARRAY() is an aggregate function that we need to have a GROUP BY clause specified. In this case, since we are selecting multiple person fields, we need to specify all of them in our GROUP BY clause. Since we included our hash, person_id, we will safely retrieve each person record.

SELECT
  person.person_id, 
  person.first_name,
  person.middle_name,
  person.last_name,
  person.saluation,
  person.dob, 
  ARRAY({
    type: addr.address_type,
    addressLine1: addr.address_line_1,
    addressLine2: addr.address_line_2,
    city: addr.city,
    state: addr.state,
    zip: addr.zip_code,
  }) as address 
FROM 
  arr.person AS person 
    LEFT JOIN arr.address AS addr 
      ON person.person_id = addr.person_id
GROUP BY 
  person.person_id,
  person.first_name,
  person.middle_name,
  person.last_name,
  person.saluation,
  person.dob

This returns a list of complex JSON objects where each Person object contains a list of Phone objects. For example, the complex object for person ID 1 would look like this:

{
  "person_id": 1,
  "first_name": "Doug",
  "middle_name": "James",
  "last_name": "Henley",
  "saluation": "Mr.",
  "dob": "8/15/57",
  "address": [
    {
      "type": "MAILING",
      "addressLine1": "94317 Roxbury Court",
      "addressLine2": "Apt 102",
      "city": "Tampa",
      "state": "FL",
      "zip": 33625
    },
    {
      "type": "MAILING",
      "addressLine1": "35 Elgar Court",
      "city": "Arvada",
      "state": "CO",
      "zip": 80005
    }
  ]
}

Connecting Person, Phone Number, and Address

Now that we’ve shown how to aggregate list data from a single table let’s take a look at how we can retrieve multiple lists within our complex JSON objects. Ordinarily, if I wanted to pull data for person, phone, and address, then I would need three SQL queries.

SELECT * FROM person WHERE person_id = 1
SELECT * FROM phone WHERE person_id = 1
SELECT * FROM address WHERE person_id = 1

Now, if I were to put all three of those tables into a JOIN statement, I would receive a lot of duplicate data across all three tables. Take a look, here, at what is returned by the below SQL statement.

SELECT * 
FROM person 
  LEFT JOIN phone 
    ON person.person_id = phone.person_id 
  LEFT JOIN address
    ON person.person_id = address.person_id

Moving back to HarperDB we can query with the ARRAY() function to help us out with this. However, because we are joining across multiple tables we may still see some duplicate data in the phone and address lists. This is the inherent nature of SQL JOINS. In order to solve this problem, HarperDB created the DISTINCT_ARRAY() wrapper function. This function can be placed around a standard ARRAY() function call to ensure a distinct (deduplicated) results set is returned. Now to create our complex Person object with lists of both Phone and Address we can write a SQL statement like this:

SELECT 
  person.person_id,
  person.first_name,
  person.middle_name,
  person.last_name,
  person.saluation,
  person.dob,
  DISTINCT_ARRAY(ARRAY({
    type: addr.address_type,
    addressLine1: addr.address_line_1,
    addressLine2: addr.address_line_2,
    city: addr.city,
    state: addr.state,
    zip: addr.zip_code,
  })) as address,
  DISTINCT_ARRAY(ARRAY({
    type: phone.phone_type,
    num: phone.number,
    primaryFlag: phone.primary_flag,
  })) as phone
FROM arr.person AS person 
  LEFT JOIN arr.address AS addr
    ON person.person_id = addr.person_id
  LEFT JOIN arr.phone AS phone
    ON person.person_id = phone.person_id
GROUP BY
  person.person_id,
  person.first_name,
  person.middle_name,
  person.last_name,
  person.saluation,
  person.dob

The complex object for Person ID 1 returned from the above query looks like this

{
  "person_id": 1,
  "first_name": "Doug",
  "middle_name": "James",
  "last_name": "Henley",
  "saluation": "Mr.",
  "dob": "8/15/57",
  "address": [
    {
      "type": "MAILING",
      "line1": "94317 Roxbury Court",
      "line2": "Apt 102",
      "city": "Tampa",
      "state": "FL",
      "zip": 33625
    },
    {
      "type": "MAILING",
      "line1": "35 Elgar Court",
      "city": "Arvada",
      "state": "CO",
      "zip": 80005
    }
  ],
  "phone": [
    {
      "type": "REFERENCE",
      "num": "926-647-6907",
      "primaryFlag": 1
    },
    {
      "type": "HOME",
      "num": "737-377-6038",
      "primaryFlag": 0
    }
  ]
}

With a single query in HarperDB we were able to transform SQL data into a complex JSON object that can be used in your modern application!

Sample Data

Here are links to CSVs of each table used in the above example. You can also view the data below.
person.csv
address.csv
phone.csv

Person Table

person_id	first_name	middle_name	last_name	saluation	dob
1	Doug	James	Henley	Mr.	8/15/57
2	Megan		Creech		6/29/66
3	Michael	Samuel	Lang	Mr.	9/18/68
4	Charles	Jay	Cohen	Mr.	1/12/76
5	Gabby	Sarah	Hughes		9/30/82
6	Emily	Alexandra	Wood	Mrs.	1/18/64
7	Samantha	Grace	Choi	Mrs.	5/25/64
8	Hana		Smith	Ms.	3/12/72
9	Kent	Richard	Garrett		9/24/79
10	Kara	Caitlin	May	Ms.	9/17/90

Address Table

address_id	person_id	address_type	address_line_1	address_line_2	city	state	zip_code
1	1	MAILING	94317 Roxbury Court	Apt 102	Tampa	FL	33625
2	2	BILLING	9 Mayer Plaza	#277	Washington	DC	20430
3	3	MAILING	99 Cascade Crossing		Hartford	CT	6152
4	4	MAILING	39094 Hoard Center	#418	Flushing	NY	11388
5	5	MAILING	6 Waubesa Point		Aurora	CO	80045
6	6	BILLING	94209 Kinsman Place	#135	Atlanta	GA	30311
7	7	MAILING	526 Barnett Hill		Waco	TX	76711
8	8	BILLING	9 Luster Trail	#348	Nashville	TN	37240
9	9	BILLING	33553 Talmadge Hill		Bakersfield	CA	93386
10	10	MAILING	21900 Rusk Drive	Apt 8	Harrisburg	PA	17121
11	1	MAILING	35 Elgar Court		Arvada	CO	80005
12	3	BILLING	9 Tennessee Street		Trenton	NJ	8619
13	6	MAILING	0 Old Gate Alley	Apt 439	Wilkes Barre	PA	18768
14	7	BILLING	3918 Messerschmidt Way	Apt 234	Oklahoma City	OK	73173
15	9	BILLING	41778 Stephen Circle		Salt Lake City	UT	84145
16	10	BILLING	50 Tony Terrace		Sioux Falls	SD	57198
17	2	MAILING	6 Hanson Trail		Nashville	TN	37240
18	3	BILLING	0 Darwin Terrace	#144	Montpelier	VT	5609
19	5	MAILING	59265 Dakota Center		Pittsburgh	PA	15279
20	9	MAILING	369 Badeau Road		Miami	FL	33283

Phone Table

phone_id	person_id	phone_type	number	primary_flag
1	1	REFERENCE	926-647-6907	1
2	2	REFERENCE	864-324-2292	1
3	3	WORK	540-908-1691	1
4	4	HOME	253-590-9734	1
5	5	CELL	302-785-7313	1
6	6	REFERENCE	670-198-4073	1
7	7	CELL	923-662-5491	1
8	8	REFERENCE	176-225-5902	1
9	9	WORK	228-536-6858	1
10	10	REFERENCE	175-549-9915	1
11	1	HOME	737-377-6038	0
12	2	WORK	603-492-5375	0
13	4	WORK	192-656-9676	0
14	5	REFERENCE	537-446-7971	0
15	7	WORK	627-936-7236	0
16	8	WORK	762-324-7571	0
17	10	WORK	521-906-6326	0
18	2	REFERENCE	390-785-1962	0
19	4	CELL	787-954-6675	0
20	7	WORK	168-382-4627	0
21	8	WORK	199-264-7443	0
22	2	REFERENCE	212-508-4836	0
23	4	WORK	493-724-1771	0
24	8	CELL	156-617-7276	0