Drizzle Team•15mo ago

No such table: main.__old_push_[TABLENAME]

Hello, I tried to setup Lucia Auth with DrizzleORM and Torso LibSQL. The database reads/writes work via drizzleORM but when i want to create a user via Lucia Auth i get the error from the title. Does anyone have an idea?

38 Replies

stan•15mo ago

Did you solve this?

louisOP•15mo ago

not yet do you have an idea? @stan

stan•15mo ago

Not at all, I'm not in production, so I just changed the db and now it's working The problem started after I made a migration

louisOP•15mo ago

i didnt even make an migration just rebuilt the db there where no entries so i didnt care#

stan•15mo ago

I did the same and is now working

louisOP•15mo ago

changed from neon.tech to turso.tech because i want tot migrate to cf d1 when its ready yeah i changed the credentials and the adapters and so on but its not working for lucia-auth. its only working in drizzle itself

stan•15mo ago

But I fear it may happen in production and to say that it would be bad is an understatement haha

louisOP•15mo ago

yes i think its weird that it only throws this error when using a lucia-auth function. when i use drizzle to query and so on it works. but in lucia even i use the same client its not working correctly

stan•15mo ago

For me I wasn't using Lucia auth, I made my own auth with jwt and passportjs and it was working fine

louisOP•15mo ago

i think i go back to neon.tech and then migrate to d1 when its ready idk its weird

stan•15mo ago

The problem came when I was trying to run this code const newStore = await db.insert(stores).values(store).returning(); And it makes no sense for me

louisOP•15mo ago

hm true

stan•15mo ago

Because I ran this one and it worked seamlessly: const newUser = await db.insert(users).values(user).returning(); I think the problem was specifically related to the stores table and the migration a made to it My full error looks like this: LibsqlError: SQLITE_UNKNOWN: SQLite error: no such table: main.old_push_users at mapHranaError (/home/stan/project-rusilla-nstc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-cjs/hrana.js:287:16) at HttpClient.execute (/home/stan/project-rusilla-nstc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-cjs/http.js:84:48) at processTicksAndRejections (node:internal/process/task_queues:95:5) at async PreparedQuery.all (/home/stan/project-rusilla-nstc/nodemodules/.pnpm/[email protected]@[email protected][email protected]/node_modules/src/libsql/session.ts:223:16) at async /home/stan/project-rusilla-nstc/src/routes/storeRoutes.ts:38:22 { code: 'SQLITE_UNKNOWN', [cause]: [ResponseError: SQLite error: no such table: main.old_push_users] { code: 'SQLITE_UNKNOWN', proto: { message: 'SQLite error: no such table: main.__old_push_users', code: 'SQLITE_UNKNOWN' } } } You can see it says old_push_users doesn't exist because it's related to the push migration I did

louisOP•15mo ago

what the fuck i what the fuck i dont get it okay so i deleted the databases in turso created them new deleted the migration dir in my project and generated and pushed fresh now its working i dont get it

stan•15mo ago

I did exactly the same But it does not make any sense for me

louisOP•15mo ago

exactly that a bit fucked up i cant just delete everything when there is a problem oH prOducTioN is NoT wOrKiNg? JuSt dEleTe It

stan•15mo ago

It was trying to use the main.__old_push_users table, that I guess is the name of the users table from before the push

louisOP•15mo ago

its some migration table when u edit a schema of a table it renames the old to this weird name creates the new and moves the data to the new and deletes the old afaik

stan•15mo ago

But why was it looking for it?

louisOP•15mo ago

exatly i didnt define the name as this like where did it get the name from

stan•15mo ago

And worst, did it loose it?

louisOP•15mo ago

Outside of the migration process

stan•15mo ago

XD! cReAtE A nEw oNe

louisOP•15mo ago

who cares about data

stan•15mo ago

There's no github issue about it, later I'm going to create one, I suggest you do the same so that they may fix it

louisOP•15mo ago

just dont use it alright send it then

daz.dev•13mo ago

Just run in to this same issue with Elysia and Lucia Auth. Hit it twice, only resolution is to delete the tables, which is not practical. Did you open a GitHub issue I can track/contribute to @stan?

louisOP•13mo ago

I pushed changes instead of migrated so there were some problems

Derock•12mo ago

@Louis / @stan Did either of you make an GitHub issue for this? I'm running into the same problem (https://discord.com/channels/1043890932593987624/1186793412549148753) and if you haven't made an issue yet, I'll go ahead and make one.

louisOP•12mo ago

No didnt made one

timmushen•12mo ago

I ran into the same thing. Looking at the schema, the issue for me was that the foreign keys on the user tables were creating an issue because the FK's were connected to the old table (main.__old). Removing the foreign keys fixed the issue where I could use Drizzle. :/ Hope this helps.

Mykhailo•10mo ago

Hello everyone! Could you please let me know if this issue is exclusive to authentication providers (like Lucia, Next) or if it can also happen with a standard workflow using SQLite? Additionally, could you provide a detailed description of your workflow to help reproduce this issue?

Domski•9mo ago

@Mykhailo I'm sorry to say I can't clear that part up but am constantly running into this same issue using Turso, Drizzle, Lucia auth as well. My workflow is as follows: - write my schema files - generate a migration file - push it to turso. The first time everything works just fine but when running a new migration I get that same error and the __old_push_ table is not deleted. My schemas are quite foreign-key heavy which I suppose is causing this problem.

Mykhailo•9mo ago

Hello, @Domski! We will investigate your issue tomorrow, but could you please share your schema code?

Massimo•9mo ago

Hi @Mykhailo, having the same issue (seems unrelated to my Clerk auth), here's my schema.ts if it can help.

import { createId } from "@paralleldrive/cuid2";
import { relations, sql } from "drizzle-orm";
import { sqliteTableCreator, text } from "drizzle-orm/sqlite-core";

export const createTable = sqliteTableCreator((name) => `myproject_${name}`);

export const users = createTable("users", {
  id: text("id", { length: 128 }).primaryKey(),
  name: text("name"),
});

export const usersRelations = relations(users, ({ many }) => ({
  lists: many(lists),
}));

export const lists = createTable("lists", {
  id: text("id", { length: 128 })
    .$defaultFn(() => createId())
    .primaryKey(),
  userId: text("userId", { length: 128 }).notNull(),
  name: text("name", { length: 64 }).notNull(),
  description: text("description", { length: 256 }),
  createdAt: text("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  readOnlyId: text("readOnlyId", { length: 128 }).$defaultFn(() => createId()),
});

export const listsRelations = relations(lists, ({ one }) => ({
  author: one(users, {
    fields: [lists.userId],
    references: [users.id],
  }),
}));

export const items = createTable("items", {
  id: text("id", { length: 128 })
    .$defaultFn(() => createId())
    .primaryKey(),
  listId: text("listId", { length: 128 })
    .references(() => lists.id, { onDelete: "cascade" })
    .notNull(),
  sourceLink: text("sourceLink", { length: 256 }),
  tagLink: text("tagLink", { length: 256 }),
  name: text("name", { length: 64 }).notNull(),
  description: text("description", { length: 256 }),
  createdAt: text("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

export const itemsRelations = relations(items, ({ one }) => ({
  author: one(lists, {
    fields: [items.listId],
    references: [lists.id],
  }),
}));

import { createId } from "@paralleldrive/cuid2";
import { relations, sql } from "drizzle-orm";
import { sqliteTableCreator, text } from "drizzle-orm/sqlite-core";

export const createTable = sqliteTableCreator((name) => `myproject_${name}`);

export const users = createTable("users", {
  id: text("id", { length: 128 }).primaryKey(),
  name: text("name"),
});

export const usersRelations = relations(users, ({ many }) => ({
  lists: many(lists),
}));

export const lists = createTable("lists", {
  id: text("id", { length: 128 })
    .$defaultFn(() => createId())
    .primaryKey(),
  userId: text("userId", { length: 128 }).notNull(),
  name: text("name", { length: 64 }).notNull(),
  description: text("description", { length: 256 }),
  createdAt: text("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  readOnlyId: text("readOnlyId", { length: 128 }).$defaultFn(() => createId()),
});

export const listsRelations = relations(lists, ({ one }) => ({
  author: one(users, {
    fields: [lists.userId],
    references: [users.id],
  }),
}));

export const items = createTable("items", {
  id: text("id", { length: 128 })
    .$defaultFn(() => createId())
    .primaryKey(),
  listId: text("listId", { length: 128 })
    .references(() => lists.id, { onDelete: "cascade" })
    .notNull(),
  sourceLink: text("sourceLink", { length: 256 }),
  tagLink: text("tagLink", { length: 256 }),
  name: text("name", { length: 64 }).notNull(),
  description: text("description", { length: 256 }),
  createdAt: text("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

export const itemsRelations = relations(items, ({ one }) => ({
  author: one(lists, {
    fields: [items.listId],
    references: [lists.id],
  }),
}));

To ""solve"" the problem, currently I have to comment my schema, push, reverse the change and push again (not a viable solution for production, but seems the only to workaround that works for the time being)

Mykhailo•9mo ago

Hello, @Massimo! You should update all your columns with default value CURRENT_TIMESTAMP to this

  .default(sql`(CURRENT_TIMESTAMP)`) // add ()

  .default(sql`(CURRENT_TIMESTAMP)`) // add ()

So, the problem is with default value and current_timestamp constant. It should be wrapped with parentheses. Otherwise, push command will detect changes every time and it will result in this issue.

Massimo•9mo ago

@Mykhailo unfortunately the issue seems to persist even with this change (same behaviour as before)

Mykhailo•9mo ago

Could you clarify your workflow please?

Gaming

Programming

No such table: main.__old_push_[TABLENAME]