Drizzle Team•9mo ago

SqliteError:no such table : main.__old_push_users

Hey I have been using Sveltekit , lucia for handling sessions, and drizzle with an sqLite Database for my project. I have encountered with an issue when I submit my register form action in sveltekit , I can successfully create the user in the database but when I want to create the session with Lucia it throws me the error above, i am so lost I have been trying to fix it for so long now. Hope you guys can provide me some help or let me know if you have met with this error before. Please me let know if I should provide some additional code snippets.

12 Replies

Mykhailo•9mo ago

Hey @KDave73🔱 ! If you have default value current_timestamp change it to (current_timestamp). This should help. if not, provide please your workflow

KDave73🔱OP•9mo ago

I had a default value of CURRENT_TIMESTAMP , should i change it to (CURRENT_TIMESTAMP) or the capitalization doesent matter?

Mykhailo•9mo ago

capitalization doesn't matter. Just wrap into parentheses (CURRENT_TIMESTAMP)

KDave73🔱OP•9mo ago

still not working here is the github repo

KDave73🔱OP•9mo ago

https://github.com/mrdkvcs/teal

GitHub

GitHub - mrdkvcs/teal

Contribute to mrdkvcs/teal development by creating an account on GitHub.

Mykhailo•9mo ago

thanks for the repo! Could you please clarify the workflow to repdroduce this issue?

KDave73🔱OP•9mo ago

yes , so in sveltekit at the /register route i have a +page.server.ts , which has a register form action. When I submit my form it fires. Everything seems good , the user is created in the database , but when it gets to the await createAndSetSession(lucia , userId , event.cookies) section it throws an error like this Have you found something? Because I have still no clue 😄

Mykhailo•9mo ago

Will take a look now

Mykhailo•9mo ago

Honestly, I got this error on the screenshot, but I think I know why do you face this issue again. This issue was caused by plenty of factors, but the main was current_timestamp. Due to current_timestamp drizzle kit every time detects changes when you run drizzle-kit push and try to do smth like this

ALTER TABLE `users` RENAME TO `__old_push_users`;
CREATE TABLE `users` (
        `id` text PRIMARY KEY NOT NULL,
        `name` text NOT NULL,
        `email` text NOT NULL,
        `password` text NOT NULL,
        `user_type` text NOT NULL,
        `created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO "users" SELECT * FROM "__old_push_users";
DROP TABLE `__old_push_users`;
ALTER TABLE `users_sessions` RENAME TO `__old_push_users_sessions`;
CREATE TABLE `users_sessions` (
        `id` text PRIMARY KEY NOT NULL,
        `user_id` text NOT NULL,
        `expires_at` integer NOT NULL,
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO "users_sessions" SELECT * FROM "__old_push_users_sessions";
DROP TABLE `__old_push_users_sessions`;

ALTER TABLE `users` RENAME TO `__old_push_users`;
CREATE TABLE `users` (
        `id` text PRIMARY KEY NOT NULL,
        `name` text NOT NULL,
        `email` text NOT NULL,
        `password` text NOT NULL,
        `user_type` text NOT NULL,
        `created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO "users" SELECT * FROM "__old_push_users";
DROP TABLE `__old_push_users`;
ALTER TABLE `users_sessions` RENAME TO `__old_push_users_sessions`;
CREATE TABLE `users_sessions` (
        `id` text PRIMARY KEY NOT NULL,
        `user_id` text NOT NULL,
        `expires_at` integer NOT NULL,
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO "users_sessions" SELECT * FROM "__old_push_users_sessions";
DROP TABLE `__old_push_users_sessions`;

Then you add a new column (from your migrations), you might get the error from the drizzle-kit during the push (insert less columns that actually exist in the table) and drizzle kit statements are not wrapped into transactions, so some of this commands were executed. When you run again drizzle-kit push it will firstly drop user_sessions and create it again, then it will do the same with users, but in this workflow we rename table, so we renamed users to __old_push_users and all references in foreign keys were renamed too. So, you have invalid references in foreign key constraints. You have to write migration manually to rename referenced table in foreign key constraint or delete the db and start again

Mykhailo•9mo ago

Also, what is your migration workflow? drizzle-kit generate and drizzle-kit push are different approches should not be used together

KDave73🔱OP•9mo ago

thank you very much , i recreated the db , and my migrations folder and it now works. So I used push and generate together , that was the mistake then?Something got messed up? if i understand that right , when i generate my database at the start , should I only continiously use either drizzle-kit push or drizzle-kit generate and not together?

Mykhailo•9mo ago

Super! You can findmore infor about these two commands here - https://discord.com/channels/1043890932593987624/1220264364666519572 If something is unclear - feel free to ask! In your case, issue was with current_timestamp and some workflows, which drizzle kit doesn't support now (e.g. rollback after error). Due to that in your foreign key constraint was incorrect reference.

Gaming

Programming

SqliteError:no such table : main.__old_push_users