DEV Community: Camma Smith

Buy vs. Build: The Answer Has Changed

Camma Smith — Mon, 22 Jun 2026 14:30:52 +0000

Every company that uses software eventually faces a version of the same question: should we pay a vendor for this, or build it ourselves? For most of commercial software history, the answer leaned heavily toward buying. That answer is shifting, and the implications are uncomfortable for vendors and buyers alike.

Where the question came from

Before packaged software existed, every company that needed software built it. Payroll systems, inventory tracking, order management: all custom, all internal. Then, in the 1970s and 80s, the commercial software industry emerged. Companies like SAP and Oracle began selling enterprise software that handled entire business functions in one system.

SAP's ERP is the canonical example of why buying made sense. ERP, or Enterprise Resource Planning, is software that unifies a company's core operations: accounting, HR, procurement, inventory, manufacturing, supply chain. It is enormously complex. SAP spent years building their system, handling edge cases across dozens of industries and thousands of business scenarios. No individual company could justify that investment. But SAP could, because the cost spread across every customer they sold to.

The economics were clear: a vendor could invest far more in a specialized product than any single buyer could, and every buyer got access to the depth of that investment for a fraction of the cost. The conventional wisdom that emerged from this era was intuitive: buy for commodity functions, build only for competitive advantage. Don't build your own payroll system. Do build your recommendation engine if recommendations are your product.

The case for buy vs. build

The arguments for buying were strong. Vendors specialized deeply. They had teams dedicated to a single product, absorbing requirements and edge cases that no individual company would think to handle. Buying meant faster time to value, a predictable cost structure, and someone else responsible for maintenance. It also usually meant a breadth of features that internal teams could rarely match.

Building had real advantages too. Full control over the product, no vendor lock-in, the ability to customize to exact requirements, and ownership of the underlying logic and data. But those advantages came at a cost that was easy to underestimate: not just the initial build, but ongoing maintenance. Software doesn't sit still. Requirements change, infrastructure evolves, and bugs surface in production. The team that built the thing becomes responsible for it indefinitely. For anything that wasn't central to the business, that tradeoff rarely made sense.

What changed in the following decades

The 2000s introduced SaaS and made buying even easier. No installation, no on-premise infrastructure, subscription pricing, and tools that teams could adopt in days rather than months. The barrier to buying dropped further. Open source offered a middle path: free to use, but still requiring someone to run it, upgrade it, and handle whatever it didn't do out of the box. For most companies, the commercial option remained the practical choice.

Throughout this period, the underlying calculus stayed roughly constant. The expertise gap between a vendor and an internal team was wide. Vendors had specialists. Internal teams had generalists stretched across many priorities. Building anything serious required experienced developers, long timelines, and a maintenance commitment that compounded over time. The wisdom held: buy the commodity, build the moat.

AI changes everything

The cost of building has dropped substantially over the last few years, and it continues to drop. AI helps with scaffolding, boilerplate, integrations, debugging, and iteration. A developer who isn't a specialist in a domain can now produce credible software in that domain, because AI fills the knowledge gaps that used to require years of experience. Tasks that once took a team months can take a single developer weeks.

Things that once lived firmly in the "buy" column have crossed into "build." The long tail of software that companies bought because building was too expensive is now buildable, by smaller teams, faster, with less expertise required. Internal tools, custom integrations, specialized data pipelines: these were always better built than bought in principle, but the cost made buying the only practical option. That's no longer the case. The gap has closed, and it will keep closing. This doesn't mean building is free or easy. The threshold has moved, not disappeared.

The principle hasn't changed. Buy for commodity, build for advantage still holds. What AI has changed is where that line falls. More things that used to be "buy because you can't afford to build it" are becoming "build because you can now afford to." And the pace of that shift is accelerating.

What this means in practice

This is where the implications get uncomfortable.

If building is getting easier every year, the natural question for any buyer is: why pay for something I could build myself? A SaaS product that solves a narrow, well-defined problem is increasingly at risk of being replaced by something a team assembled in a few days with AI assistance. The economics that once made buying obvious are changing.

But there is a cost that the initial build effort consistently hides: ownership. Software isn't done when it ships. It needs to be updated when dependencies change, extended when requirements grow, debugged when production breaks, and understood by whoever inherits it. That burden accumulates. A tool built in a weekend can quietly become a recurring weekend every month. The question isn't how much effort it takes to build something. It's how much effort it takes to own it over time.

This is the real tension in the buy vs. build decision today. For a buyer: how much can we build for ourselves before the management and maintenance burden exceeds what a vendor would charge? The answer isn't the same for every team. A small team with limited engineering capacity has a lower threshold than a platform team with dedicated infrastructure engineers. A tool that rarely changes is cheaper to maintain than one that needs constant updates to stay current.

For a vendor, the question is harder. How do you build a product that a customer will value more than something they could build themselves? Price used to answer that question. If building cost ten times as much as buying, the math was simple. Now, for narrower tools, the gap has closed. A team can often replicate basic functionality faster than a sales cycle closes.

What remains is depth. A mature product has years of production exposure. It has encountered the edge cases, handled the bad inputs, survived the scale spikes, and integrated with everything the customer was already running. That's real value, and it's hard to reproduce quickly. But it's also invisible in a demo. Vendors who have been selling on surface-level features are the most exposed. Vendors who have been solving the hard problems, the ones that only surface after months of real-world use, are the ones whose products remain difficult to replicate.

The line between buy and build is not fixed. It is moving, and it is moving toward build. The question both sides are now trying to answer is the same one it has always been: is this worth doing yourself, or is someone else already doing it better? The difference is that "better" is a higher bar than it used to be.

Nobody is going to pay for software they can build with minimal effort. The products worth paying for are the ones where the effort isn't minimal, even with AI. Finding and communicating that clearly is, increasingly, what the buy vs. build question is really about.

Running Real SQL on DynamoDB: How It Actually Works

Camma Smith — Mon, 08 Jun 2026 14:49:19 +0000

DynamoDB is good at what it was designed for: fast, predictable reads and writes against known access patterns. It is not designed for ad-hoc queries. If you need to join two tables, aggregate by a column, or ask a question your schema didn't anticipate, the answer is application code: SDK calls, manual pagination, in-memory joins. A simple question might take 50 lines. A complex one takes more, and the logic ends up scattered across your codebase. As you can imagine, that gets frustrating very quickly. That's where DynamoSQL comes in.

DynamoSQL is a read-only SQL engine built on top of DynamoDB that closes that gap. You write standard ANSI SQL SELECT statements against your existing tables. On the surface that sounds simple. Under the hood, it involves a parser, a query planner, an optimizer that inspects your table's index structure, and a batched execution engine that coordinates multiple DynamoDB API calls to produce a result that DynamoDB couldn't produce on its own.

This article walks through all of it: how DynamoDB's access model works, why it makes certain queries hard, and exactly what DynamoSQL does to close that gap.

How DynamoDB stores and retrieves data

DynamoDB is a key-value and document store. Every item you store in a DynamoDB table has a partition key, which is the attribute DynamoDB uses to decide which physical server holds that item. If your table also has a sort key, items with the same partition key are stored together and sorted by that second attribute.

This design is intentional. By routing all reads and writes for a given partition key to a single location, DynamoDB can guarantee single-digit millisecond latency at any scale. There's no coordination between servers, no lock contention, no complex query planning. You give it a key, it gives you the data.

The tradeoff is that you have to tell DynamoDB your partition key to get any data efficiently. Without it, you're left with a Scan: DynamoDB reads every item in the table and filters the results afterward. On a large table, a Scan that returns 10 rows might read millions of items to find them. The cost, both in time and in read capacity, scales with the amount of data read, not the amount returned.

To support multiple access patterns without resorting to scans, DynamoDB offers Global Secondary Indexes (GSIs). A GSI is an additional index on a different partition key and optional sort key. DynamoDB maintains a copy of your data indexed by those keys, letting you query on attributes that aren't your table's primary key.

The catch is that you have to define your GSIs before you need them, and they need to match the exact access patterns you anticipate. If someone asks a question you didn't foresee at design time, you either build a new GSI and backfill, or you scan.

Designing tables for access patterns

The standard advice for DynamoDB table design is to work backward from your queries. Before you write a single item, you list every query your application needs to support, then design a partition and sort key structure that covers all of them.

To make this concrete, say you're building an e-commerce platform. You might have two tables:

Orders table

Partition key: customer_id
Sort key: order_date
Attributes: order_id, total, status, region

Customers table

Partition key: customer_id
Attributes: name, email, state

With this design, the query "get all orders for customer 123" is fast and cheap: DynamoDB queries the Orders partition for customer_id = "123" and returns results sorted by date. That access pattern is well-supported.

But the query "how much revenue did we generate per state last week" is a different story. That requires:

Reading all orders from the last week (there's no order_date GSI, so this is a scan of the entire Orders table)
Looking up the state for each customer (a separate read against the Customers table for each order)
Aggregating the totals by state

DynamoDB can't do any of that natively. There's no JOIN between Orders and Customers. There's no GROUP BY. There's no SUM. The application has to do all of it.

What the native API looks like

Here's what that query looks like in JavaScript using the AWS SDK directly:

const { DynamoDBClient } = require('@aws-sdk/client-dynamodb');
const { DynamoDBDocumentClient, ScanCommand, BatchGetCommand } = require('@aws-sdk/lib-dynamodb');

const client = new DynamoDBClient({});
const docClient = DynamoDBDocumentClient.from(client);

const oneWeekAgo = new Date();
oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);
const startDate = oneWeekAgo.toISOString();

// Step 1: Scan all orders from the last week
let orders = [];
let lastKey;
do {
    const response = await docClient.send(new ScanCommand({
        TableName: 'Orders',
        FilterExpression: 'order_date >= :start',
        ExpressionAttributeValues: { ':start': startDate },
        ExclusiveStartKey: lastKey,
    }));
    orders.push(...(response.Items || []));
    lastKey = response.LastEvaluatedKey;
} while (lastKey);

// Step 2: Batch-fetch the customer record for each order
const customerIds = [...new Set(orders.map(o => o.customer_id))];
const customerKeys = customerIds.map(id => ({ customer_id: id }));

const customerMap = {};
for (let i = 0; i < customerKeys.length; i += 100) {
    const batch = customerKeys.slice(i, i + 100);
    const response = await docClient.send(new BatchGetCommand({
        RequestItems: {
            Customers: { Keys: batch }
        }
    }));
    for (const customer of (response.Responses?.Customers || [])) {
        customerMap[customer.customer_id] = customer;
    }
}

// Step 3: Join and aggregate in application code
const revenueByState = {};
for (const order of orders) {
    const customer = customerMap[order.customer_id];
    if (!customer) continue;
    const state = customer.state;
    revenueByState[state] = (revenueByState[state] || 0) + order.total;
}

// Step 4: Sort results
const result = Object.entries(revenueByState)
    .map(([state, revenue]) => ({ state, revenue }))
    .sort((a, b) => b.revenue - a.revenue);

That's about 50 lines of code. It performs a full table scan of Orders (reading every item regardless of how many match), makes a separate BatchGet call to Customers, handles pagination manually, and aggregates the result in application code. The cost of the scan grows with your table size, not with your result size.

The same query in DynamoSQL

This is where DynamoSQL comes in. Instead of writing and maintaining that 50-line SDK routine, you hand the problem to DynamoSQL and let it figure out how to fetch the data efficiently. Here's the same query written in SQL:

SELECT c.state, SUM(o.total) AS revenue
FROM myschema.orders o
JOIN myschema.customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE() - 7
GROUP BY c.state
ORDER BY revenue DESC

That's it. DynamoSQL handles everything the 50-line version does manually, and it does it more efficiently.

What DynamoSQL does with that query

When you send this SQL to DynamoSQL, it goes through five stages: parsing, planning, optimizing, fetching, and aggregating.

1. Parsing

The SQL string is parsed into an Abstract Syntax Tree (AST): a structured representation of what the query is asking for. The parser identifies the SELECT columns, the FROM clause with its JOIN condition, the WHERE filter, the GROUP BY clause, and the ORDER BY. At this stage it is just structure. No data has been touched.

2. Planning

The AST is converted into an execution plan, which is a tree of iterator nodes. Each node represents a step in the query: scan a table, apply a filter, execute a join, group and aggregate rows, sort. If you were to draw this plan out, it would look like a tree of operations, where each node feeds rows up to the node above it.

At this point the plan is correct but not yet optimized. A naive execution would scan the entire Orders table, scan the entire Customers table, perform a nested-loop join, then filter, group, and sort.

3. Optimizing

This is where DynamoSQL earns its value. The optimizer inspects your DynamoDB table metadata: the partition keys, sort keys, and GSI definitions, and rewrites the plan to use the most efficient access path available.

For the query above, the optimizer reasons through the following:

For the Orders table: the WHERE clause filters on order_date. The Orders table has a sort key of order_date. If the optimizer also has a partition key value to anchor the query, it can use DynamoDB's Query API directly. But here there's no specific customer_id filter, so no partition key is available for Orders. The optimizer notes this.

For the JOIN on customer_id: the Customers table has customer_id as its partition key. This is important. Once the optimizer knows which customer_id values come back from Orders, it can fetch matching Customers rows using BatchGet, DynamoDB's API for fetching up to 100 items by exact key in a single request. This is far cheaper than scanning Customers.

Execution order: the optimizer determines that Orders should be fetched first (as a scan, since there's no key filter), and then for each batch of Orders rows, it fires BatchGet requests against Customers using the customer_id values from those rows. This avoids scanning Customers entirely.

If a matching GSI had existed on Orders (for example, a GSI with region as the partition key and order_date as the sort key), the optimizer would have used a Query against that GSI instead of a scan. The scan is always the fallback of last resort.

A note on the scan in this example: the scan on Orders is unavoidable given the table design above. The Orders table has customer_id as its partition key and order_date as its sort key. Because DynamoDB's Query API always requires a partition key value, a date range filter alone can't drive a Query. You'd need to also specify a customer_id, which we don't have. The sort key on order_date only helps once the partition is already known.

This is a real limitation of the table design, not a limitation of DynamoSQL. If you wanted to make date range queries efficient, you'd add a GSI. One common pattern is to use a synthetic partition key, such as a fixed value like "orders" or a date bucket like "2026-06", used as the GSI partition key, with order_date as the GSI sort key. DynamoSQL would then detect that GSI during optimization and use a Query instead of a Scan, drastically reducing read costs.

The key point is that DynamoSQL's optimizer works with whatever indexes your table already has. It doesn't require you to redesign your schema. But if your schema has indexes that can serve a query efficiently, it will find and use them automatically. The more indexes you have that match your query patterns, the less DynamoSQL has to scan.

4. Fetching

Execution runs through a chain of batch collectors. The engine works in batches of 100 rows at a time.

The first collector fetches a batch of rows from Orders via Scan. It passes those rows to the next collector, which extracts the customer_id values and fires a BatchGet against Customers, fetching up to 100 customer records in a single DynamoDB API call. The two sets of rows are then joined on customer_id by matching the keys.

This continues in a loop: fetch 100 Orders rows, BatchGet the corresponding Customers rows, join them, pass the joined rows up the chain. No full scan of Customers ever happens. Each customer lookup is a direct key-based fetch.

The pagination that the 50-line version handles manually is managed internally. If Orders has 50,000 items, the engine pages through them in batches of 100, firing BatchGet calls against Customers for each batch, until all matching rows have been retrieved.

5. Aggregating

Once joined rows flow up through the execution tree, the GROUP BY node collects them by state, the SUM aggregate accumulates the totals, and the ORDER BY node sorts the final result. These operations run in the DynamoSQL execution engine, not in DynamoDB. DynamoDB doesn't support them natively.

The result comes back as a JSON array of rows, exactly what you'd get from an SQL database.

Why avoiding a full scan matters

DynamoDB bills for read capacity proportional to the amount of data read, not returned. A scan that reads 1 million items to return 500 costs the same as reading 1 million items with no filter at all.

DynamoSQL avoids scans wherever possible by inspecting your index metadata before execution. If your WHERE clause matches a GSI partition key, it uses a Query. If a JOIN condition ties to another table's primary key, it uses BatchGet. A scan only happens when no other path is available, and when it does happen, the engine still processes results in batches rather than loading everything into memory at once.

What this means in practice

The 50-line SDK example and the 6-line SQL query produce the same result. The difference is that DynamoSQL's execution is:

Cheaper: BatchGet is a direct key lookup. Scanning Customers never happens.
Faster: BatchGet requests fire in batches as Orders rows arrive, rather than waiting for all Orders to load first.
Simpler: The pagination, batching, joining, and aggregation logic is inside the engine, not scattered through your application code.

The SQL you write tells DynamoSQL what you want. The optimizer and execution engine figure out the most efficient way to get it from DynamoDB.

Why LLM Agents Still Can't Query NoSQL Databases

Camma Smith — Thu, 04 Jun 2026 15:01:07 +0000

When it comes to SQL databases, LLMs are great at writing SQL. It's precise, expressive, and unambiguous. LLMs write it well. Connect an MCP server to Postgres and the agent can write queries directly and efficiently. It's a lot harder for agents to work with NoSQL databases, and given how much production data lives in them, I'm surprised there isn't more discussion about it.

Why SQL works so well for LLMs

The core reason SQL is a natural interface for language models is specificity. A SQL query says exactly what data you want, in exactly what shape, with exactly what conditions. There's no ambiguity for the model to navigate.

SQL is also one of the most well-represented languages in LLM training data. Decades of documentation, Stack Overflow answers, textbooks, and open source code have produced an enormous corpus for models to learn from. The syntax is consistent. The semantics are well-defined. When an LLM writes SQL, it's drawing on a deep, reliable foundation.

It also maps cleanly to tool calls. An agent calls run_sql with a query string and gets back rows. The interface is simple, inputs and outputs are typed, and errors are catchable. It's a well-defined contract between the agent and the data layer.

The NoSQL problem

The same isn't true for NoSQL, and the root cause isn't just syntax. It's that there's no shared interface. Each major NoSQL database has its own query model, its own access patterns, and its own constraints. For an LLM to query any of them effectively, it needs to understand not just the syntax but the underlying data model.

MongoDB uses a document query syntax built around JSON filters and aggregation pipelines. DynamoDB uses an API-style expression language built around partition keys and index access patterns. Cassandra has CQL, which looks like SQL but has strict limitations on what queries are allowed based on how tables are partitioned. Redis exposes commands rather than queries. Neo4j uses Cypher, a graph query language with its own pattern-matching syntax.

These aren't variations on the same problem. Each one requires the agent to internalize a different mental model for how data is stored and retrieved. There's no training data equivalent to decades of SQL documentation to draw from. And because developers' attention is split across all of them, the tooling and ecosystem around each is thinner than what SQL databases enjoy.

A concrete DynamoDB example

Take DynamoDB specifically. It requires a partition key for efficient queries. Without one, you're doing a full table scan, which reads every item in the table and applies filters afterward. It doesn't support JOINs, native aggregates, or GROUP BY.

Say an agent needs to answer: "Find how many orders were placed by region this week."

In SQL, that's straightforward:

SELECT region, COUNT(*) AS order_count
FROM orders
WHERE created_at >= CURRENT_DATE() - 7
GROUP BY region
ORDER BY order_count DESC

In DynamoDB, the agent can't write that query. It has to write code:

const results = {};
let lastKey;

do {
  const response = await dynamodb.scan({
    TableName: 'orders',
    FilterExpression: 'created_at >= :start',
    ExpressionAttributeValues: { ':start': startDate }
  });

  for (const item of response.Items) {
    results[item.region] = (results[item.region] || 0) + 1;
  }

  lastKey = response.LastEvaluatedKey;
} while (lastKey);

This is more code, harder to audit, and requires the agent to understand DynamoDB's pagination model. It also runs a full table scan rather than hitting an index, which is expensive on large tables. And this is a simple question. Anything involving a JOIN across two tables gets significantly more complex.

LLMs can do so much already. It's frustrating that they still can't reliably handle something this straightforward against a database as widely used as DynamoDB.

What teams do about it

Before reaching for a purpose-built solution, most teams working with DynamoDB work through a sequence of workarounds.

The first option is PartiQL, AWS's own SQL-like syntax for DynamoDB. It's built into the SDK and the console, so there's no setup required. The agent can write queries directly against DynamoDB without any additional infrastructure. The limitation is that PartiQL is a syntax layer, not a query engine. It translates SQL-style statements into native DynamoDB API calls, which means it inherits all of DynamoDB's query constraints. The agent still needs to supply a partition key for efficient queries. There are no JOINs, no GROUP BY, and no native aggregates. The syntax looks like SQL, but the access model is unchanged.

The second is exporting to S3 and querying with Athena. Engineers set up a DynamoDB export pipeline to S3 and configure Athena to query the exported files. Once that infrastructure is in place, the agent can write SQL against Athena directly. The tradeoff is freshness: the export isn't real-time, so the agent's results are only as current as the last export. Engineers also have to maintain the pipeline and monitor it for failures.

The third is streaming to a secondary store. Engineers pipe DynamoDB changes to Redshift, OpenSearch, or a relational database, and the agent queries the replica. Once the infrastructure exists, the agent gets a proper query interface. But it's querying a copy of the data, not the source. Engineers have to maintain the pipeline, and when it lags or fails, the agent's results are wrong without any obvious signal that something is off.

None of these are unreasonable choices. PartiQL is the lowest-friction starting point, but it hits a ceiling fast. The infrastructure-based approaches give the agent a proper query interface, but at the cost of engineering overhead and data that's never fully current.

What we built

We ran into this exact problem building an MCP server against DynamoDB. The agent could reason about the data well, but getting it out required knowledge of partition key layouts, GSI structures, and access patterns that wasn't realistic to expect the agent to have upfront.

So we built an SQL engine on top of DynamoDB and called it DynamoSQL. When the agent submits an ANSI SQL SELECT statement, DynamoSQL parses it, builds a query plan, and runs an optimizer that inspects the table's actual index metadata. The optimizer selects the best access path: a partition key Query if the WHERE clause supports it, a GSI Query if a matching index exists, or a Scan as a last resort. The agent doesn't configure any of this. It just writes SQL.

The execution layer handles what DynamoDB can't do natively. JOINs run across multiple DynamoDB API calls. GROUP BY, aggregates, subqueries, CTEs, UNION, ORDER BY, and HAVING all work in the engine. Results come back as rows. The agent doesn't need to know anything about the underlying data model.

The MCP server exposes a single run_sql tool. The agent writes a query, the engine executes it, and the result is a clean set of rows, the same interface you'd expect from any SQL database.

Where this leaves things

SQL databases and LLM agents are a natural fit. The interface is clean, the semantics are well-defined, and the tooling is mature. NoSQL databases are still mostly a compromise: a syntax layer that hits a ceiling, or export pipelines and secondary stores that require engineering overhead and never give the agent fully current data.

The fragmentation across NoSQL databases makes this hard to solve once and apply everywhere. But for teams using DynamoDB, the SQL layer approach works well in practice. The agent writes what it wants. The engine figures out how to fetch it efficiently.

The broader question of whether this is the right approach across all NoSQL databases is still open. But the cost of the status quo is real, and it deserves more attention than it's getting.

Why We Built an SQL Layer for DynamoDB

Camma Smith — Thu, 04 Jun 2026 14:35:28 +0000

The SQL vs. NoSQL debate has been running long enough that most engineers have stopped having it. The real question isn't which is better. It's which tradeoffs you're actually making when you choose one.

SQL databases give you a relational model, flexible ad-hoc queries, decades of tooling, and strong consistency guarantees. The cost is horizontal scale. Distributing a relational database across many nodes is possible, but it adds complexity, and query semantics get harder to reason about as you do it. At extreme read/write throughput, relational databases require significant engineering effort to keep performant.

NoSQL databases flip those tradeoffs. They're designed to distribute data across nodes predictably, which gives you horizontal scale and consistent latency. You gain the ability to handle massive throughput without complex sharding logic. You give up some of the richness of the relational model, particularly around ad-hoc querying.

DynamoDB is the clearest example of this tradeoff done deliberately and at scale.

Why teams choose DynamoDB

DynamoDB is a managed key-value and document store built by AWS. It delivers single-digit millisecond read and write latency at any scale, scales capacity automatically, requires no cluster management, and has a low operational burden compared to running your own database. For teams that don't want to operate infrastructure, it's a compelling default for production workloads.

The data model is built around partition keys. Every item in a table has a partition key, which determines which physical partition holds that item. An optional sort key allows range queries within a partition. Global Secondary Indexes (GSIs) let you define alternative partition and sort key layouts, enabling multiple read patterns without duplicating your full dataset.

For workloads where access patterns are known and stable, DynamoDB performs exceptionally well. An e-commerce platform with clear paths for "get order by ID", "list orders for customer", and "get product by SKU" can serve all of them from DynamoDB efficiently and cheaply. Each access pattern gets an index. Each query hits that index. Performance is predictable.

The catch is that this performance guarantee comes with a hard constraint: you must define your access patterns before you write your data.

The querying problem

DynamoDB's Query API requires a partition key value. You can filter on sort key attributes and apply additional filter expressions, but the partition key is always mandatory. Without it, the only option is Scan, which reads every item in the table and applies filters afterward.

Scans are expensive. They consume read capacity proportional to the data they touch, not the data they return. On a large table, a Scan that returns 100 rows might read millions of items to get there. That's costly in both time and money, and it gets worse as your table grows.

This creates a specific class of queries that DynamoDB handles poorly.

Aggregates. There's no native SUM, COUNT, AVG, or GROUP BY. To know how many orders were placed by region last week, you fetch the rows and compute the aggregate yourself. For small datasets that's workable. For anything large, you're pulling significant data across the wire and doing compute work in your application.

JOINs. DynamoDB has no cross-table JOIN. Single-table design, a common DynamoDB modeling pattern, works around this by denormalizing related entities into one table. But that requires anticipating every join at schema design time. It makes your data model harder to evolve and makes ad-hoc analysis across related entities painful.

Ad-hoc queries. Questions that weren't anticipated at schema design time have no efficient answer. The data exists. Getting it in an arbitrary shape requires either a full-table Scan or redesigning your access patterns, which often means a new GSI and a backfill.

These aren't edge cases. They're the normal requirements of anyone who needs to understand what's in their database beyond the access patterns the application was originally built around.

What teams do about it

Several approaches exist. Each involves real tradeoffs.

Application-level computation. Fetch the relevant rows from DynamoDB and compute aggregates or join logic in application code. This works at small data volumes. At larger volumes, you're moving significant data across the network and doing CPU-heavy work in your API layer. It also spreads data logic across application code rather than keeping it at the data layer.

Export to S3 and Athena. DynamoDB supports point-in-time exports to S3. Athena can then run SQL against those files. The tradeoff is freshness: exports aren't real-time, so your query results are as stale as your last export. For some reporting use cases that's acceptable. For anything that needs current data, it isn't. You also now have two systems to understand and operate.

DynamoDB Streams to a secondary store. Stream DynamoDB changes to Redshift, OpenSearch, or a relational database, and run queries against the replica. This can give you full SQL against a continuously synced copy of your data. The tradeoffs are real: operational complexity, eventual consistency between primary and replica, and the cost of maintaining both systems. When the pipeline works, it works. When it lags or fails, your results are wrong and diagnosing the problem requires understanding two systems at once.

PartiQL. AWS added PartiQL, an SQL-like syntax, to DynamoDB in 2020. PartiQL translates SQL-style statements into native DynamoDB API calls. A SELECT with a partition key condition becomes a Query. Without one, it becomes a Scan. There's no JOIN support, no GROUP BY, and no aggregates. PartiQL gives DynamoDB an SQL-style syntax. It doesn't change DynamoDB's underlying access model. It's a syntax layer, not a query engine.

Each of these approaches solves part of the problem. None of them gives you real-time SQL with full query capabilities directly against your DynamoDB tables.

What we built

That's what led us to build DynamoSQL, a read-only SQL engine that translates ANSI SQL SELECT statements into DynamoDB API calls and executes them directly against your tables, in real time.

When you submit an ANSI SQL SELECT statement to DynamoSQL, it parses the SQL into an AST, builds a logical query plan, and runs an optimizer that inspects your table's actual index metadata. The optimizer selects the best access path: a partition key Query if your WHERE clause supports it, a GSI Query if a matching index exists, or a Scan as a last resort. Then it executes the plan against DynamoDB and returns results as JSON rows.

The execution layer handles what DynamoDB can't do natively. JOINs are computed across multiple DynamoDB API calls. GROUP BY, aggregates (SUM, COUNT, AVG, MIN, MAX), subqueries, CTEs, UNION, INTERSECT, EXCEPT, ORDER BY, HAVING, and a wide function library all run in the engine. You write standard SQL. DynamoSQL figures out how to execute it efficiently.

This is different from the Athena approach because it's real-time. No export pipeline, no stale snapshots. It's different from streaming to a secondary store because there's no replica to maintain and no pipeline to monitor. It's different from PartiQL because it's an actual query engine, not a syntax translation layer.

It's also read-only. DynamoSQL supports SELECT only. DynamoDB's write model is precise by design: partition keys, sort keys, defined capacity allocations. Wrapping writes in SQL semantics would make the behavior harder to reason about, not easier. The read side is where the gap is, and that's what we're solving.

The MCP server

The SQL engine solves the querying problem for developers who write code. The MCP server extends that to a different kind of user: anyone interacting with an AI agent.

MCP, or Model Context Protocol, is the standard interface AI agents use to call external tools. When DynamoSQL is connected to an agent through its MCP server, the agent can query DynamoDB directly using plain SQL through a single tool call.

Here's what that looks like in practice. A developer or analyst asks the agent a question in natural language: "How many orders were placed in California last week?" The agent doesn't need to know anything about DynamoDB's partition key structure or which indexes exist on the table. It translates the question into SQL, calls run_sql, and gets back rows. DynamoSQL handles the rest: optimizing the query plan, selecting the right index, executing against DynamoDB, and returning structured results.

This matters because SQL is a reliable middle layer between natural language and a NoSQL database. LLMs write SQL well. They've been trained on large amounts of it, the syntax is unambiguous, and errors are easy to catch. Asking an agent to write DynamoDB SDK code to answer the same question is a different proposition: the code is longer, harder to verify, and requires the agent to know the table's internal structure upfront.

The SQL layer gives the agent a well-defined interface. The agent writes what it wants. DynamoSQL figures out how to fetch it. The result is more reliable than asking the agent to navigate DynamoDB's access model directly, and more accessible than expecting every user to write SQL by hand.

How it deploys

DynamoSQL runs as a managed SaaS. You connect your AWS account, register your DynamoDB tables under a schema, and run queries through the REST API or the MCP server. Your data stays in your AWS account. DynamoSQL assumes a read-only IAM role you create and control, runs queries, and returns results. We don't store your data and we don't have standing access to your tables.

If you're using DynamoDB and want real-time SQL read access against your tables, the product is live. AWS Marketplace Listing

Where this leaves you

DynamoDB is a good database. The teams that choose it make a deliberate call: predictable performance, no cluster management, strong operational guarantees at scale. The querying limitation isn't a reason to abandon it. It's a gap that can be filled without changing anything about how your data is stored or your tables are structured.

DynamoSQL sits on top of what you already have. You bring the DynamoDB tables. We bring the SQL engine, the query optimizer, and the MCP server. The access patterns you've already defined become inputs to the optimizer, not constraints you have to work around.

If you're hitting the querying wall and want to see how it works against your own tables, we'd like to hear from you. Dynamosql.com