Need something to conver back tauri-plugin-sql query result to drizzle.

[FrancoRATOVOSON]

Just keep in mind this part of the code:

tauri-drizzle-sqlite-proxy-demo/src/db/database.ts

Lines 40 to 42 in 37a05b3

	rows = rows.map((row: any) => {
	return Object.values(row);
	});

First of all

I want to thank you for this repos, it's exactly what I was looking for and the solution of a 3 days problem (still a junior 😅).
And secondly, sorry for the errors, I'm trying to be the most understandable but english is not my first language and I'm still improving.

The context

I will use the data format in the code base for the example.

Let's say we have this db schema:

tauri-drizzle-sqlite-proxy-demo/src/db/schema.ts

Lines 3 to 12 in 37a05b3

	export const users = sqliteTable("users", {
	age: integer("age").default(18),
	city: text("city").default("NULL"),
	created_at: text("created_at").default("CURRENT_TIMESTAMP"),
	deleted_at: text("deleted_at").default("NULL"),
	email: text("email").unique(),
	id: integer("id").primaryKey().unique(),
	name: text("name"),
	updated_at: text("updated_at").default("CURRENT_TIMESTAMP"),
	});

Let's run this query:

tauri-drizzle-sqlite-proxy-demo/src/App.tsx

Lines 28 to 29 in 37a05b3

	db.query.users
	.findMany()

Drizzle will convert the code into SQL query, send it to the tauri-plugin-sql, which will send it to the rust sqlx driver, and the result will go back from the rust sqlx driver, to tauri-plugin-sql, to drizzle and then back to our app.
So here's the flow:
Make request -> drizzle(convert query to sql) -> tauri-plugin-sql(get the sql query) -> sqlx(execute the query on the db)
So far, so good. But that's where the problems start. So let's keep going:
sqlite(sends the result back) -> sqlx -> tauri-plugin-sql (get the result, format & type it) -> the code above (change the object into an array of values) -> drizzle (get the array, format it again into an object type from the schema) -> get the result

The problem

I think you see the problem here, we have no idea how the data is ordered, there's no way for tauri-plugin-sql and drizzle to comunicate the format.

We can get this from tauri-plugin-sql:

{
  name: "John",
  updated_at: 1721509200,
  deleted_at: 1726952400,
  email: "random@email.com",
  id: 1,
  age: 20,
  created_at: 1719090000,
  city: "NY",
}

With the code above, will be changed to:

[ "John", 1721509200, 1726952400, "random@email.com", 1, 20, 1719090000, "NY"]

And drizzle will just:

{
  id: "John",
  name: 1721509200,
  age: 1726952400,
  created_at: "random@email.com",
  deleted_at: 1,
  updated_at: 20,
  city: 1719090000,
  email: "NY",
}

That's the issue.

Proposal

I mean, what I used to fix the issue, is a generic function that order the object given by tauri-plugin-sql, so I know exactly the order of the data in the array, so even if the object poperies are shuffeled, I can re-order it the way I want.

So the code above become:

rows = rows.map((row: any) => {
  return Object.keys(row)
      .sort()
      .map( key => row[key] )
})

Now that I know that the array elements are ordered to follow the alphabetical (or whatever sort function I used) order of my object type keys, all I need is a function to redo the process but give back a full object:

// Not sure for the name, pls suggest something better
// Didn't find a more correct term to tell what exactly the function does
function normalizeObjectKV<T extends object>(obj: T): T {
  const values = Object.values(obj)
  const keys = Object.keys(obj).sort()
  const newObj = new Map([])
  keys.forEach((key, index) => {
    newObj.set(key, values[index])
  })

  return Object.fromEntries(newObj.entries())
}

And when I query, I just need to use my function to transform my drizzle result to the correct format:

db.query.users
      .findMany()
      .execute()
      .then((results) => {
        const users = normalizeObjectKV(results);
        setUsers(results);
      });

And voilà 🥳

I mean...😅

I mean, yes, is there any better solution ? Or did I miss something ?

[tdwesten]

Hi @FrancoRATOVOSON,

Thanks for your detailed explanation! However, I want to ensure we can use native drizzle.

db.query.users
      .findMany()
      .execute()
      .then((results) => {
        const users = normalizeObjectKV(results); // This should not be needed
        setUsers(results);
      });

[FrancoRATOVOSON]

@tdwesten it's the best solution I found for now. But I totaly agree and am widely open to better and more elegant ways.

[tdwesten]

@FrancoRATOVOSON: I was looking for a way to find the index of the schema key value in the current query. Sorting the schema key alphabetically makes it work fine.

[FrancoRATOVOSON]

@tdwesten good for me.

[vladfaust]

So, Drizzle maps the columns in accordance to the schema definition order. 😬

An universal workaround until Drizzle provides more context to custom clients is to:

1. Define models' fields in alphabetical order (can be mitigated by wrapping the definition in some custom ordering function).
2. Use @FrancoRATOVOSON's "object -> sorted array" piece of code within the callback.

For example:

import { sql } from "drizzle-orm";
import { sqliteTable, text } from "drizzle-orm/sqlite-core";

export const games = sqliteTable(
  "games",
  sortObjectByAlphabeticalKeys({
    id: text("id").primaryKey().notNull(),
    screenshot: text("screenshot"),
    createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
    updatedAt: text("updated_at").default(sql`CURRENT_TIMESTAMP`),
  }),
);

function sortObjectByAlphabeticalKeys(
  obj: Record<string, any>,
): Record<string, any> {
  return Object.keys(obj)
    .sort()
    .reduce(
      (acc, key) => {
        acc[key] = obj[key];
        return acc;
      },
      {} as Record<string, any>,
    );
}

    // ...

    rows = rows.map((row: any) => {
      return Object.keys(row)
        .sort()
        .map((key) => row[key]);
    });

    // ...

[vladfaust]

Ugh, a relation is expected always be in the end of a row, after the columns. Unable to know if it is a relation from within a RemoteCallback. All we have is sql, params and method.

[OliveiraCleidson]

Hello everyone,

I’ve been following this issue, and in the meantime, I’ve implemented a workaround to manage the parser between Tauri and Drizzle after the else statement. While the solution isn’t particularly elegant, it has proven effective in my tests, especially in scenarios involving multiple joins and a large number of tables. However, it’s worth noting that this approach works best when SQL aliases are not present, and I haven’t yet tested it with returning statements.

Any feedback or suggestions for improving this approach would be greatly appreciated!

const columnsRegex = /SELECT\s+(.+)\s+FROM/i;
const columnsMatch = sql.match(columnsRegex);
let columns: string[] = [];
if (columnsMatch) {
  columns = columnsMatch[1].split(",");
  columns = columns.map((column) => column.trim().replace(/"/g, ""));
}

rows = rows.map((row) => {
  // Order the values in the row based on the order of the columns
  // This is necessary because the order of the values in the row is not guaranteed
  // when using the select method
  const orderedRow = {};
  columns.forEach((column) => {
    orderedRow[column] = row[column];
  });

  // The logic can be replaced for not use Object Values, but I worked in this 4 a.m.
  // I will refactor
  return Object.values(orderedRow);
});

[doolijb]

I appreciate the effort placed here. However the issue persists for nested/relational data.

const [test] = await db.query.tasks.findMany({
          with: {
            taskType: true,
          }
        });
// Before remapping: [{completed: 0, position: 4, type_id: 1, taskType: "[1,\"daily\"]", text: "test", …}]
// After mapping: test[0].taskType is undefined