astro db feature how do i self reference columns :

[errant nexus]

In this example below the references is giving me an issue and as per Drizzle Documenation I am supposed to use a type called AnySQLiteColumn

const Objective = defineTable({
columns: {
id: column.text({
primaryKey: true,
default: sqluuid(),
}),

parentObjectiveId: column.text({
  references: ():  => Objective.columns.id,
  optional: true,
}),

[sly night]

👋

Here you go my friend
https://docs.astro.build/en/guides/astro-db/#table-references

[errant nexus]

I have been through this documentation and the issue is that; when you use references within the same table ; I get a compile time error saying :

"'references' implicitly has return type 'any' because it does not have a return type annotation and is referenced directly or indirectly in one of its return expressions.ts(7023)"

does the link you have shared have something specific which addresses this ? Which i cannot see ?

[uneven blaze]

have you seen https://docs.astro.build/en/guides/integrations-guide/db/#foreignkeys ?

[errant nexus]

Yes I have tried both of them where a column has "self-reference" :

foreignKeys: [
{
columns: ["parentObjectiveId"],
references: () => [Objective.columns.id],
},
],

This is the same snippet moved to foreign keys and it gives me the same error "'references' implicitly has return type 'any' because it does not have a return type annotation and is referenced directly or indirectly in one of its return expressions."

As per Drizzle Documentation; it is a limitation of TypeScript and there is also a bug :

https://github.com/drizzle-team/drizzle-orm/issues/1607

and as per help it also mentions the following :

Note that when implementing self-references, you need to specify the correct column type (AnyPgColumn, AnyMySqlColumn, or AnySQLiteColumn) due to TypeScript limitations 2.

[errant nexus]

Any ideas / Workarounds ?

[uneven blaze]

<@&1129102257422610512> Anyone able to give input on this? Maybe @slender idol has some ideas?

My only real idea, is to simply use it as a column.text() without the reference, and just use a checker to verify if any entries in the DB match if there is or is not a parent...

[slender idol]

Specify the type, it's in the error message.
TS won't infer circular types

[minor totem]

I know it's a bit old but I had the same problem today.

const Category = defineTable({
   columns: {
      id: column.number({ primaryKey: true }),
      name: column.text(),
      parent: column.number({ references: () => Category.columns.id }), // 'references' implicitly has return type 'any' because it does not have a return type annotation and is referenced directly or indirectly in one of its return expressions. [7023]
   },
})

I tried explicitly specifying a bunch of return type to see if it works
Didn't work:
references: (): NumberColumn
references: (): Number

Works but code smell:
references: ():any => Category.columns.id

references: (): { type: "number"; schema: { primaryKey: true } } => Category.columns.id,

references: (): ReturnType<typeof column.number> => Category.columns.id
Is this last one recommended? 👆

---

This was the easiest one I could think of.

const categoryId = column.number({primaryKey: true})
const Category = defineTable({
   columns: {
      id: categoryId,
      name: column.text(),
      parent: column.number({ references: () => categoryId }),
   },
})

I'm gonna use this in production. This doesn't have any weird edge cases / known problems or anything, right?

[uneven blaze]

@slender idol can correct me if im wrong, but this option would actually not work correctly... 🤔 since your only satisfying the type, and not the actual requirement of the reference... (the reference is not actually referencing a specific table)

I would say the following is probably the safest option:

references: (): ReturnType<typeof column.number> => Category.columns.id

Note: Tables are converted to SQL... so references are actually important to be fully written out

[minor totem]

ah bummer since other one felt more type safe. thanks for letting me know! I'll make a switch and save my future self some trouble until Fryuni can share their 2 cents about this.

[slender idol]

defineTable will set some values inside the column definition that allows it to be used as a reference, like which table and column in it that obhect refers to.

IIRC it does so by mutating the object so your original code might work. But Adam's code works even if it is immutable and is also preferable for other reasons. It avoids the problem of your future self accidentally referring to a column that is not used in any table or to use the same column definition in more than one table.