Composing queries in EdgeDB (with EdgeQL-JS)

Composing database queries can be a challenge. In the past, using Postgres / SQL, GraphQL and Sanity.io’s GROQ, I’d usually write “fragments” that I’d insert in queries as needed - usually plain strings.

To use Gororobas as an example, let’s take the data needed to render a vegetable’s card/preview:

Here’s how I’d compose this in Supabase’s PostgREST query builder:

const VEGETABLE_CARD_FRAGMENT = `
	names,
	handle,
	photos ( url, id )
` as const // not type-safe - typos and schema changes are hard to track

// 💡 the photos ( url, id ) above is PostgREST simplified way of querying
// many-to-many relationships tables. It lets me focus on the example, but the same could be applied to raw SQL.
// @see https://postgrest.org/en/v12/references/api/resource_embedding.html#one-to-many-relationships

// Using it in the homepage:
async function getHomepageData() {
	const vegetables = await supabase
		.from('vegetables')
		.select(VEGETABLE_CARD_FRAGMENT)
		.limit(12)
	// ...
}

async function getVegetablesIndexData() {
	const vegetables = await supabase
		.from('vegetables')
		.select(VEGETABLE_CARD_FRAGMENT)
		.limit(24)
		// .eq and more for filtering...
}

By re-using VEGETABLE_CARD_FRAGMENT, I’m able to avoid repeating myself and ensure consistency. It’s not type-safe and there’s no way to directly extract type definitions from it, but it works. A similar mechanism could be applied to SQL, GraphQL (via the official fragments primitive), GROQ or most query languages.

However, what happens when you want to change a subset of a fragment? Let’s say that in the homepage, instead of getting all photos for a vegetable, you only need the first:

Now you’re forced to create 3 fragments:

const VEGETABLE_CARD_FRAGMENT_CORE = `
	names,
	handle,
` as const

const VEGETABLE_CARD_FRAGMENT_MULTIPLE_PHOTOS = `
  ${VEGETABLE_CARD_FRAGMENT_CORE}
	photos ( url, id )
`

const VEGETABLE_CARD_FRAGMENT_SINGLE_PHOTO = // wait... how?!

But wait, in SQL you can’t limit the count of returned rows in a JOIN in the body of a fragment. Instead, it needs to come in the LIMIT that comes after the SELECT. Here’s the solution with PostgREST and Supabase:

async function getHomepageData() {
	const vegetables = await supabase
		.from('vegetables')
		// Need to use the multiple-image fragment, the key will show up as
		// "photos", but it'll be a single photo returned. Confusing 🥴
		.select(VEGETABLE_CARD_FRAGMENT_MULTIPLE_PHOTOS)
		.limit(1, { referencedTable: 'photos' })
}

The description of your data is now split between fragment and an individual query, the lack of type safety gets more critical, and tracking every step gets harder. I’m no expert, but that doesn’t feel like good composability!

Enter EdgeDB and EdgeQL

EdgeDB is a production-ready database built on top of Postgres that dares to go beyond SQL. A “post-SQL” database, if you will.

Beyond having a super expressive schema definition language (SDL), its EdgeQL query language is powerful and user-friendly:

select Vegetable {
  name := .names[0], # native arrays and easy to select by index, concat, slice, etc.
  handle,
	photos: {
    id,
    url,
  },
}

But you don’t need to work with plain strings and a separate query language. Instead, EdgeDB compiles a custom Typescript query builder from your specific schema, with full type-inference - EdgeQL-js:

import e, { type $infer } from './your-edgeql-query-builder'

const vegetablesQuery = e.select(e.Vegetable, (vegetable) => ({
	handle: true, // ✅ fully typed object, get errors on typos
	name: vegetable.names.index(0), // ✅ run any EdgeQL operation
	
	// ✅ Follow links/references easily
	photos: {
		url: true,
		id: true,
	},
}))

📌 Take a look at the docs on EdgeDB’s typescript client for how to set the query builder in your project

This by itself is a cool trick, but doesn’t change our workflows much. However, the query builder makes it trivial to compose. Let’s take our vegetable cards with 1 vs. multiple images:

import e, { type $infer } from './your-edgeql-query-builder'

// 💡 "shapes" are re-usable selections of data
const photoForRendering = e.shape(e.Photo, () => ({
	url: true,
	id: true,
}))

const vegetableForCard = e.shape(e.Vegetable, (vegetable) => ({
	handle: true,
	name: vegetable.names.index(0),
	
	// ✅ use shapes to define what data to get in coresponding contexts
	// For every photo, use the `photoForRendering` shape, un-modified
	photos: photoForRendering,
}))

// ✅ Shapes can compose - build one from the other
const vegetableWithSinglePhoto = e.shape(e.Vegetable, (vegetable) => ({
	...vegetableForCard(vegetable),
	
	// ✅ When composing a shape, properties can be overwritten
	photos: (photo) => ({
		...photoForRendering(photo),
		limit: 1,
	})
}))

// ✅ Make "free objects" at will (arbitrary selections)
const homepageQuery = e.select({
	featured_vegetables: e.select(e.Vegetable, (vegetable) => ({
		...vegetableForCard(vegetableWithSinglePhoto),

		// ✅ pair shapes with filters & limits as needed
		filter: e.op('exists', vegetable.photos),
		limit: 16,
	})),
	// ...
})

And what’s more, you can infer precise and up-to-date Typescript types from each individual shape: type VegetableCardData = $infer<typeof vegetableForCard>[number]

Which I can then use in my components, backend, forms…

import type { VegetableCardData } from '@/queries'

export default function VegetableCard({
	vegetable,
}: { vegetable: VegetableCardData }) {
	const { photos = [] } = vegetable
	// ...
}

Let’s hop into some interesting examples to drive the point home:

Excluding unneeded properties to avoid excessive data fetching a Note’s created_by property when in the owner’s profile page

const userProfileForAvatar = e.shape(e.UserProfile, () => ({
	name: true,
	handle: true,
	photo: imageForRendering,
	// By default, we display the location
	location: true,
}))

const noteForCard = e.shape(e.Note, (note) => ({
	title: true,
	body: true,
	handle: true,
	published_at: true,
	types: true,
	created_by: (userProfile) => ({
		...userProfileForAvatar(userProfile),
		// ✅ when composing shapes, you can remove properties to prevent
		// excessive data fetching
		location: false,
	}),

	// ✅ Shapes can also include filters
	// Return only public notes by default
	filter: e.op(note.public, '=', true),
}))

Overwriting a shape’s filter

// params.handle coming from the URL: `/profile/[handle]`
export const userProfilePageQuery = e.params(
	{
		handle: e.str,
	},
	(params) =>
		e.select(e.UserProfile, (profile) => ({
			filter_single: e.op(profile.handle, '=', params.handle),

			...userProfileForAvatar(profile),
			bio: true,
			is_owner: e.op(profile.id, '=', e.global.current_user_profile.id),

			notes: e.select(e.Note, (note) => ({
				...noteForCard(note),
				created_by: false, // no need to display the user in the profile page

				// ✅ In the user profile page, if user is the owner also show private notes:
				filter: e.op(
					e.op(note.created_by, '=', profile),
					'and',
					e.op(
						e.op(note.public, '=', true),
						'or',
						e.op(note.created_by, '=', e.global.current_user_profile),
					)
				),
			})),
		})),
)

Building complex filters

export const vegetablesIndexQuery = e.params(
	{
		strata: e.optional(e.array(e.str)),
		planting_methods: e.optional(e.array(e.str)),
		edible_parts: e.optional(e.array(e.str)),
		lifecycles: e.optional(e.array(e.str)),
		uses: e.optional(e.array(e.str)),
		offset: e.int32,
	},
	(params) =>
		e.select(e.Vegetable, (vegetable) => {
			const filters = [
				{ field: vegetable.strata, values: params.strata, type: e.Stratum },
				{
					field: vegetable.planting_methods,
					values: params.planting_methods,
					type: e.PlantingMethod,
				},
				{
					field: vegetable.edible_parts,
					values: params.edible_parts,
					type: e.EdiblePart,
				},
				{
					field: vegetable.lifecycles,
					values: params.lifecycles,
					type: e.VegetableLifeCycle,
				},
				{ field: vegetable.uses, values: params.uses, type: e.VegetableUsage },
			] as const
			
			// ✅ Dynamic filters, type-validated
			const filterOps = filters.map(({ field, values, type }) =>
				e.op(
					// Either the param doesn't exist
					e.op('not', e.op('exists', values)),
					'or',
					// Or the vegetable has at least one of the values
					e.op(
						e.count(
							e.op(
								field,
								'intersect',
								e.array_unpack(e.cast(e.array(type), values)),
							),
						),
						'>',
						0,
					),
				),
			)

			const finalFilter = filterOps.reduce((finalFilter, op, index) => {
				if (finalFilter === null) return op
				return e.op(finalFilter, 'and', op)
			})

			return {
				...vegetableForCard(vegetable),

				filter: finalFilter,

				limit: VEGETABLES_PER_PAGE,
				offset: params.offset,
				order_by: [
					{
						expression: e.count(vegetable.photos),
						direction: e.DESC,
						empty: e.EMPTY_LAST,
					},
					{
						expression: vegetable.handle,
						direction: e.ASC,
					},
				],
			}
		}),
)

---

There are plenty more examples I can think of, but these are some I’m actually using in production: in my queries.ts file I have 7 re-used instances of the vegetableForCard shape, let alone imageForRendering, sourceForCard, userProfileCard… Can you imagine how many more lines of code I’d need to write without this?

And wow, the feeling of evolving my schema and being able rely on the Typescript compiler for spotting type errors in my entire app is amazing. Over my 25+ schema modifications to-date, this has been invaluable - what a great foundation to build upon 🌱