SQL queries using drizzle

Live queries are defined using the useLiveQuery hook from @electric-sql/pglite-react (for React).

useLiveQuery requires a raw SQL query and the parameters to apply on it.

Instead of writing raw SQL, our app uses drizzle-orm to build queries. We can extract an instance of drizzle-orm from the use-pglite-drizzle.ts context provider:

We implement a shared useQuery hook responsible for building queries and executing them, included validation with Schema.

export const useQuery = () => {
  const orm = usePgliteDrizzle();

  // ...
};

Since we want useQuery to be generic, the hook accepts a function that returns a Query object:

Query comes from drizzle-orm. It contains a raw SQL query and the parameters to apply on it.

Just what we need for useLiveQuery 🪄

export const useQuery = (
  query: (orm: ReturnType<typeof usePgliteDrizzle>) => Query
) => {
  const orm = usePgliteDrizzle();
  const { params, sql } = query(orm);
  
  // ...
};

The returned params and sql are passed to useLiveQuery to execute the query:

export const useQuery = (
  query: (orm: ReturnType<typeof usePgliteDrizzle>) => Query
) => {
  const orm = usePgliteDrizzle();
  const { params, sql } = query(orm);
  return useLiveQuery(sql, params);
};

With this implementation we can define other hooks to build queries and have access to reactive updates:

export const useFoods = () => {
  return useQuery(
    // 👇 Build a query using drizzle-orm, and convert it to `Query` using `toSQL`
    (orm) => orm.select().from(foodTable).toSQL(),
  );
};