[BUG]: useLiveQuery doesn't see change when querying joined tables

[lvolkmann]

What version of drizzle-orm are you using?

0.31.2

What version of drizzle-kit are you using?

0.22.5

Describe the Bug

I'm not positive if this is a bug or a feature request, but the docs around useLiveQuery should probably mention this if it's not currently supported.

1. Setup a live query

   const { data } = useLiveQuery(
   db
     .select()
     .from(booksToAuthors)
     .leftJoin(booksTable, eq(booksToAuthors.bookId, booksTable.id))
     .leftJoin(authors, eq(booksToAuthors.authorId, authors.id))
   );

2. Make some update to one of the joined tables

   await db
   .update(booksTable)
   .set({ title: "newTitle" })
   .where(eq(books.id, "someBookId"))
   .execute();

Actual behavior: data is not updated, but I can see the change if I force quit and relaunch.

Other details: The only workaround I've really found has been to abandon joins in the query and instead set up separate live queries for each table. Then I can do the lookup join in Javascript with a reduce function, but I'd really love to be able to just use the sql methods here.

Expected behavior

useLiveQuery would see that one of the tables that informs the query has changed, and data would be updated accordingly.

Environment & setup

package.json

"expo": "~51.0.0",
"react-native": "0.74.2",
"expo-sqlite": "^14.0.3",

schema.ts

export const books = sqliteTable('books', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
});

export const booksRelations = relations(books, ({ many }) => ({
  booksToAuthors: many(booksToAuthors),
}));

export const authors = sqliteTable('authors', {
  id: integer('id').primaryKey(),
  name: text('name').notNull().unique(),
});

export const authorsRelations = relations(authors, ({ many }) => ({
  booksToAuthors: many(booksToAuthors),
}));

export const booksToAuthors = sqliteTable(
  'books_to_authors',
  {
  bookId: text('book_id').notNull().references(() => books.id),
  authorId: integer('author_id').notNull().references(() => authors.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.bookId, t.authorId]})
  })
);

export const booksToAuthorsRelations = relations(booksToAuthors, ({ one }) => ({
  authors: one(authors, {
    fields: [booksToAuthors.authorId],
    references: [authors.id],
  }),
  books: one(books, {
    fields: [booksToAuthors.bookId],
    references: [books.id],
  }),
}));

[KirillSaltykov]

Faced the same issue today

[doughsay]

By reading the source here: https://github.com/drizzle-team/drizzle-orm/blob/64b3c85953d35bf37342ba8cb2b48f2ad08ee7a8/drizzle-orm/src/expo-sqlite/query.ts#L39 I think the conclusion is that it only subscribes to changes for the main table queried. Does that look right? And how hard would it be to add listeners for all joined tables? And what about relational style queries, not sql-like, could we also add change listeners to each nested table being queried?

[doughsay]

I don't know how to dynamically determine all tables involved in a given query. So as a stop-gap I just copied the existing hook and modified it to require a passed in list of tables to watch for changes:

import { is } from "drizzle-orm";
import { type AnySQLiteSelect } from "drizzle-orm/sqlite-core";
import { SQLiteRelationalQuery } from "drizzle-orm/sqlite-core/query-builders/query";
import { addDatabaseChangeListener } from "expo-sqlite";
import { useEffect, useState } from "react";

export const useLiveTablesQuery = <
  T extends
    | Pick<AnySQLiteSelect, "_" | "then">
    | SQLiteRelationalQuery<"sync", unknown>,
>(
  query: T,
  tables: string[],
  deps: unknown[] = [],
) => {
  const [data, setData] = useState<Awaited<T>>(
    (is(query, SQLiteRelationalQuery) && query.mode === "first"
      ? undefined
      : []) as Awaited<T>,
  );
  const [error, setError] = useState<Error>();
  const [updatedAt, setUpdatedAt] = useState<Date>();

  useEffect(() => {
    let listener: ReturnType<typeof addDatabaseChangeListener> | undefined;

    const handleData = (data: any) => {
      setData(data);
      setUpdatedAt(new Date());
    };

    query.then(handleData).catch(setError);

    listener = addDatabaseChangeListener(({ tableName }) => {
      if (tables.includes(tableName)) {
        query.then(handleData).catch(setError);
      }
    });

    return () => {
      listener?.remove();
    };
  }, deps);

  return {
    data,
    error,
    updatedAt,
  } as const;
};

usage:

const { data: user } = useLiveTablesQuery(
    db.query.users.findFirst({
      columns: {
        name: true
      },
      where: eq(schema.users.id, 123),
      with: {
        posts: {
          columns: { title: true },
        },
      },
    }),
    ["users", "posts"],
  );

Would be great if the hook just "figured it out" for you, but like I said, I don't know how to introspect the query to find all the tables.