2 years ago

Translate SQL into Convex Queries

Here’s a cheatsheet with examples of conversions between SQL queries and Convex queries. This article is geared towards developers (and LLMs) who have familiarity with SQL and want to translate those familiar patterns into Convex queries. You'll learn how to UNION, JOIN, DISTINCT, GROUP BY, do WHERE clauses, and SELECT fields.

For this article we’ll imagine you’re building a Slack-like chat app, where “users” send “messages” in “channels”. The translation should work with any app you can imagine building on a relational database with regular BTree-backed indexes. See this article to help understand how databases are organized by indexes.

While each snippet will describe a single query, you can combine them however you like. In SQL you could use sub-queries, UNION ALL, or multiple statements in a transaction. In Convex you can compose the patterns as you would compose any TypeScript code.

About the snippets:

The SQL snippet describes what data you’re trying to fetch.

The query will return the whole data set but could be modified with additional LIMIT or OFFSET or extra WHERE clauses to read incrementally.
The SQL syntax should usually match PostgreSQL, although similar syntax is usually possible in MySQL or other variants.

Each Convex query snippet shows how to fetch equivalent data in Convex.

The Convex query will use equivalent indexes and have equivalent performance to the SQL query.
This translation will .collect all results, because yielding results incrementally is difficult without streams. In practice, .collect() should be avoided in favor of something like .take or .paginate that have bounds on how much data they will read.

The Convex QueryStream helper enables you to read results incrementally, without collecting them all.

The snippet shows how to call .paginate¹, although you can also choose to call .take, .first, .unique, or .collect.
To use QueryStreams you would import { stream, mergedStream } from "convex-helpers/server/stream". QueryStreams were added in convex-helpers version 0.1.72.

Why Translate?

While the Convex queries below may look more complicated, there are a lot of benefits that empower your Convex queries to do exactly what you want:

They’re guaranteed to be efficient. Unlike SQL, which can decide to stop using an index and do full table scans whenever it wants, your Convex queries use exactly the indexes you specify.
Using code gives you a lot more expressive power and ability to build abstractions.
Type-safety from your database schema that match runtime values, instead of templating a sql query string or hoping your ORM schema matches your database at runtime.
Convex-powered reactivity, where queries subscribe to changes.

For more SQL comparisons, see this article and this video.

Union

SQL

CREATE INDEX author ON messages (author, _creationTime);

SELECT * FROM messages WHERE author IN ('Alice', 'Bob')
ORDER BY _creationTime DESC;
-- or equivalently
SELECT * FROM messages WHERE author = 'Alice' OR author = 'Bob'
ORDER BY _creationTime DESC;
-- or equivalently
(SELECT * FROM messages WHERE author = 'Alice') UNION ALL
(SELECT * FROM messages WHERE author = 'Bob')
ORDER BY _creationTime DESC;

Convex

Collect results from the two queries, union the arrays, then sort the results².

messages: defineTable(...).index("author", ["author"])

async function authoredMessages(author: string) {
  return await ctx.db.query("messages")
    .withIndex("author", q => q.eq("author", author)).order("desc")
    .collect();
}

const allMessages = await Promise.all(["Alice", "Bob"].map(authoredMessages));
const messages = messages.flat()
  .sort((a, b) => b._creationTime - a._creationTime);

Convex QueryStreams

messages: defineTable(...).index("author", ["author"])

function authoredMessages(author: string) {
  return stream(ctx.db, schema).query("messages")
    .withIndex("author", q => q.eq("author", author)).order("desc");
}

const messages = mergedStream(
  ["Alice", "Bob"].map(authoredMessages),
  ["_creationTime"],
);
const results = await messages.paginate(args.paginationOpts);

Arbitrary Filter

Arbitrary filters in Convex let you run TypeScript checks, including async code and code imported from npm libraries. Since they don’t use an index, arbitrary filters end up scanning the entire table, although with pagination they can scan it incrementally.

Note filters can be combined with any other pattern in this article, and in particular filters are great to apply after narrowing down the query to a small index range.

SQL

SQL is very restrictive in the filters it supports, compared to Convex which can run any TypeScript code, including code from npm libraries. For this translation example we choose something that SQL can support: calculating the length of a string.

SELECT * FROM messages WHERE CHAR_LENGTH(body) <= 280;

Convex

This pattern is described in the Complex Query Filters article.

const allMessages = await ctx.db.query("messages").collect();
const messages = allMessages.filter((message) => message.body.length <= 280));

Convex QueryStreams

const messages = stream(ctx.db, schema).query("messages")
  .filterWith(async (message) => message.body.length <= 280);
const results = await messages.paginate(args.paginationOpts);

One-to-One Join

For this example, we assume that users only have access to certain channels, through “channelMemberships”. Each channel membership has a user and a channelId. You can use the channelId to look up the single channel with that ID.

SQL

CREATE INDEX `user` ON channelMemberships (userId, channelId);

SELECT channelMemberships.*, channels.* FROM channelMemberships
  JOIN channels ON channelMemberships.channelId = channels._id
  WHERE channelMemberships.userId = 'Bob'
  ORDER BY channelMemberships.channelId;

Convex

channelMemberships: defineTable(...).index("user", ["userId", "channelId"])

const memberships = await ctx.db.query("channelMemberships")
  .withIndex("user", q => q.eq("userId", "Bob"))
  .collect();
const channels = (await Promise.all(memberships
  .map((membership) => {
    const channel = await ctx.db.get("channels", membership.channelId)!;
    return {...membership, ...channel};
  })
)).flat();

Convex QueryStreams

channelMemberships: defineTable(...).index("user", ["userId", "channelId"])

const channels = stream(ctx.db, schema).query("channelMemberships")
  .withIndex("user", q => q.eq("userId", "Bob"))
  .map(async (membership) => {
    const channel = await ctx.db.get("channels", membership.channelId)!;
    return {...membership, ...channel};
  });
const results = await channels.paginate(args.paginationOpts);

One-to-Many Join

This example extends the previous one, but instead of joining to get the channel details, we join to get the multiple messages in each channel that a user has access to.

SQL

CREATE INDEX channel ON messages (channelId, _creationTime);
CREATE INDEX `user` ON channelMemberships (userId, channelId);

SELECT channelMemberships.*, messages.* FROM channelMemberships
  JOIN messages ON channelMemberships.channelId = messages.channelId
  WHERE channelMemberships.userId = 'Bob'
  ORDER BY (messages.channelId, messages._creationTime);

Convex

messages: defineTable(...).index("channel", ["channelId"])
channelMemberships: defineTable(...).index("user", ["userId", "channelId"])

const memberships = await ctx.db.query("channelMemberships")
  .withIndex("user", q => q.eq("userId", "Bob"))
  .collect();
const messages = (await Promise.all(memberships.map(async (membership) => {
  const messagesInChannel = await ctx.db.query("messages")
    .withIndex("channel", q => q.eq("channelId", membership.channelId))
    .collect();
  return messagesInChannel.map((message) => ({...channel, ...message}));
}))).flat();

Convex QueryStreams

messages: defineTable(...).index("channel", ["channelId"])
channelMemberships: defineTable(...).index("user", ["userId", "channelId"])

const memberships = stream(ctx.db, schema).query("channelMemberships")
  .withIndex("user", q => q.eq("userId", "Bob"));

const messages = memberships.flatMap(async (membership) =>
  stream(ctx.db, schema).query("messages")
    .withIndex("channel", q => q.eq("channelId", membership.channelId))
    .map(async (message) => ({...membership, ...message})),
  ["channelId"],
);
const results = await messages.paginate(args.paginationOpts);

Distinct

If your table has a lot of rows but comparatively few unique values for a particular field, you can find these unique values with a DISTINCT query. And in particular you can find the first row for each distinct value of the field.

For our example, we have lots of messages, but they're distributed across few channels. So we can get the most recent message in each channel with a DISTINCT query.

SQL

CREATE INDEX channel ON messages (channelId, _creationTime);

SELECT DISTINCT ON (channelId) * FROM messages
ORDER BY channelId DESC, _creationTime DESC;
-- or equivalently
SELECT channelId, MAX(_creationTime) FROM messages
GROUP BY channelId ORDER BY channelId DESC;

Convex

This pattern is described in https://stack.convex.dev/select-distinct .

messages: defineTable(...).index("channel", ["channelId"])

const messages = [];
let message = await ctx.db.query("messages")
	.withIndex("channel").order("desc").first();
while (message !== null) {
  messages.push(message);
  message = await ctx.db.query("messages")
    .withIndex("channel", q => q.lt("channelId", message.channelId))
    .first();
}

Convex QueryStreams

messages: defineTable(...).index("channel", ["channelId"])

const messages = stream(ctx.db, schema).query("messages").withIndex("channel").order("desc")
  .distinct(["channelId"]);
const results = await messages.paginate(args.paginationOpts);

Group By

Grouping By can be thought of as a DISTINCT query combined with a JOIN, so that’s how you would construct it in Convex.

Note for this example we’re doing a COUNT of each group, which requires reading all of the rows, in both SQL and Convex. In Convex you can choose to use the Aggregate or Sharded Counter components to compute counts faster.

SQL

CREATE INDEX channel ON messages (channelId, _creationTime);

SELECT channelId, COUNT(*) FROM messages
GROUP BY channelId ORDER BY channelId DESC;

Convex

messages: defineTable(...).index("channel", ["channelId"])

const channelCounts = [];
let message = await ctx.db.query("messages").withIndex("channel").order("desc").first();
while (message !== null) {
  const messagesInChannel = await ctx.db.query("messages")
    .withIndex("channel", q => q.eq("channelId", message.channelId))
    .collect();
  channelCounts.push({
    channelId: message.channelId,
    count: messagesInChannel.length,
  });
  message = await ctx.db.query("messages")
    .withIndex("channel", q => q.lt("channelId", message.channelId))
    .first();
}

Convex QueryStreams

messages: defineTable(...).index("channel", ["channelId"])

const channelCounts = stream(ctx.db, schema).query("messages").withIndex("channel").order("desc")
  .distinct(["channelId"])
  .map(async (message) => {
    const messagesInChannel = await ctx.db.query("messages")
	    .withIndex("channel", q => q.eq("channelId", message.channelId))
	    .collect();
	  return { channelId: message.channelId, count: messagesInChannel.length };
	});
const results = await messages.paginate(args.paginationOpts);

Select fields

Note that in Convex, even with QueryStreams, database queries always read entire documents. They can remove or modify fields in code before returning them to the client. This is not much different from row-based SQL servers, which read the entire row from disk before returning selected fields to the SQL client.

NOTE: Reading entire rows from storage is usually fine because the bandwidth bottleneck is between the client and the server (SQL server or Convex server), not between the server and the underlying storage. However, if you want to isolate large, infrequently-read fields from small, frequently-read ones, you can store them in separate tables and do JOINs when needed.

SQL

SELECT body FROM messages;

Convex

const messages = await ctx.db.query("messages").collect();
const bodies = messages.map((message) => message.body);
// NOTE this one works with `.paginate` too: call `.map` on `results.page`.
// https://docs.convex.dev/database/pagination#transforming-results

Convex QueryStreams

const bodies = stream(ctx.db, schema).query("messages")
  .map(async (message) => message.body);
const results = await bodies.paginate(args.paginationOpts);

Filter on index fields

This is a straightforward example of Convex's .withIndex method, although it's worth reiterating that SQL is not guaranteed to use any index, even if the perfect index exists. I've had a simple lookup of a single row, specified by equality on all fields of the primary key, and Postgres decided to scan a massive table to find the row.

SQL

CREATE INDEX channel ON messages (channelName, _creationTime);

SELECT * FROM messages WHERE channelName = '#general' AND
	_creationTime > (CURRENT_TIMESTAMP - INTERVAL '1 DAY');

Convex

const messages = await ctx.db.query("messages")
	.withIndex("channel", q => q.eq("channelName", "#general"))
	.collect();
// .paginate also works here, even without QueryStreams.

Convex QueryStreams

const messages = stream(ctx.db, schema).query("messages")
	.withIndex("channel", q => q.eq("channelName", "#general"));
const results = await messages.paginate(args.paginationOpts);

Filter with index fields out of order

This is also known as an Index Skip Scan in SQL query planners.

Convex’s ctx.db.query().withIndex() will only read from a contiguous index range. So there’s a more complex pattern if the data you want isn’t contiguous within the index. See the QueryStream article for more description and examples.

SQL

CREATE INDEX priority ON messages (priority, _creationTime);

SELECT * FROM messages WHERE priority > 5 AND
  _creationTime > (CURRENT_TIMESTAMP - INTERVAL '1 DAY');

Convex

messages: defineTable(...).index("priority", ["priority"])

// Get distinct priorities >5
const priorities = [];
let priorityDoc = await ctx.db.query("messages")
  .withIndex("priority", q => q.gt("priority", 5))
  .first();
while (priorityDoc !== null) {
  priorities.push(priorityDoc.priority);
  priorityDoc = await ctx.db.query("messages")
    .withIndex("priority", q => q.gt("priority", priorityDoc.priority))
    .first();
}
// Get recent messages for each of these priorities
const messages = (await Promise.all((priority) =>
  ctx.db.query("messages").withIndex("priority", q =>
    q.eq("priority", priority).gt("_creationTime", Date.now() - 24*60*60*1000)
  ).collect()
)).flat();

Convex QueryStreams

messages: defineTable(...).index("priority", ["priority"])

const priorities = stream(ctx.db, schema).query("messages")
  .withIndex("priority", q => q.gt("priority", 5))
  .distinct(["priority"])
  .map(async (message) => message.priority);

const messages = priorities.flatMap(async (priority) =>
  stream(ctx.db, schema).query("messages").withIndex("priority", q =>
    q.eq("priority", priority).gt("_creationTime", Date.now() - 24*60*60*1000)
  )
);
const results = await messages.paginate(args.paginationOpts);

Composing Patterns

Putting it all together, let's see how you would compose the above patterns to translate a complicated query.

This query gets the message body and distinct emoji reactions for each non-deleted message in channels the user has access to.

This example has many of the above patterns:

a one-to-many join from channelMemberships to messages
an arbitrary filter on 'deleted'
an Index Skip Scan for the _creationTime filter
selecting only the body field of messages
a DISTINCT query for emoji reactions

SQL

CREATE INDEX channel ON messages (channelId, _creationTime);
CREATE INDEX user ON channelMemberships (userId, channelId);
CREATE INDEX message ON reactions (messageId, _creationTime);

SELECT 
  messages._id,
  messages.body,
  messages._creationTime,
  (SELECT ARRAY_AGG(DISTINCT emoji)
   FROM reactions
   WHERE reactions.messageId = messages._id
   ORDER BY reactions._creationTime DESC) AS emojis
FROM messages
JOIN channelMemberships ON channels._id = channelMemberships.channelId
WHERE channelMemberships.userId = $1
  AND messages._creationTime >= $2
  AND messages.deleted = FALSE
ORDER BY messages.channelId DESC, messages._creationTime DESC;

Convex

messages: defineTable(...).index("channel", ["channelId"])
channelMemberships: defineTable(...).index("user", ["userId"])
reactions: defineTable(...).index("message", ["messageId", "emoji"])

async function getEmojis(messageId) {
	const emojis = [];
	let reactionDoc = await ctx.db.query("reactions")
		.withIndex("message", q => q.eq("messageId", messageId))
		.first();
	while (reactionDoc !== null) {
		emojis.push(reactionDoc.emoji);
		reactionDoc = await ctx.db.query("reactions")
			.withIndex("message", q => q.eq("messageId", messageId).gt("emoji", reactionDoc.emoji))
			.first();
	}
	return emojis;
}

const memberships = await ctx.db.query("channelMemberships")
  .withIndex("user", q => q.eq("userId", args.userId))
	.order("desc")
	.collect();
const allMessages = await Promise.all(
	memberships.map((membership) => ctx.db.query("messages")
		.withIndex("channel", q => q.eq("channelId", membership.channelId).gte("_creationTime", args.creationTime))
		.order("desc")
		.collect()
	)
);
const nonDeletedMessages = allMessages.flat().filter((message) => !message.deleted);
const messages = await Promise.all(nonDeletedMessages.map(
	async (message) => ({
		_id: message._id,
		_creationTime: message._creationTime,
		body: message.body,
		emoji: await getEmojis(message._id),
	})
);

Convex QueryStreams

messages: defineTable(...).index("channel", ["channelId"])
channelMemberships: defineTable(...).index("user", ["userId"])
reactions: defineTable(...).index("message", ["messageId", "emoji"])

const memberships = stream(ctx.db, schema).query("channelMemberships")
	.withIndex("user", q => q.eq("userId", args.userId))
	.order("desc");
const messages = memberships.flatMap(async (membership) =>
	stream(ctx.db, schema).query("messages")
		.withIndex("channel", q => q.eq("channelId", membership.channelId).gte("_creationTime", args.creationTime))
		.order("desc")
).filterWith(async (message) => !message.deleted)
.map(async (message) => {
	const emojis = await stream(ctx.db, schema).query("reactions")
		.withIndex("message", q => q.eq("messageId", message._id))
		.distinct("emoji")
		.map(async (reaction) => reaction.emoji)
		.collect();
	return {
		_id: message._id,
		_creationTime: message._creationTime,
		body: message.body,
		emoji: reaction?.emoji,
	};
});
const results = await messages.paginate(args.paginationOpts);

Recap

Anything that you can query with a SQL database, you can write into a Convex query. The patterns may be trickier to figure out, and for incremental paginated results you may need to use helpers like Query Streams, but that's because Convex is pushing you towards efficient query plans, to ensure your queries are as fast and cheap as they can be.

Footnotes

If you want to use .paginate, make sure to check out the pagination warnings ↩
You can hypothetically merge these results faster, because each array is already sorted. But improving O(n log n) CPU time to O(n) is unlikely to matter when the bottleneck is probably on the storage fetches. And, for comparison, the SQL engine is likely going to materialize the entire union on disk before sorting, which is much worse. ↩

All gas, no breakages

Convex is the reactive backend platform that keeps up with you and your agents. Database, functions, workflow, sync, search, file storage, and more. All TypeScript, zero glue.

Get started