db debugging and tasks - mar 24 - apr

laptop

merge pr to prod

manually copy the SequelizeMeta table to local dev and prod dbs

note to lucas w screenshot of sequelizeMeta

users table: duplicate users_email_key and users_appleAppAccountToken_key indexes

personalBlogSubscribers_email_key in personalBlogSubscribers table

aside

look through existing table indexes and let lucas know about duplicates that probably should be removed

spaces_collaboratorKey


CREATE TABLE IF NOT EXISTS "SequelizeMeta" (
    name VARCHAR(255) PRIMARY KEY
);
INSERT INTO "SequelizeMeta" (name)
VALUES 
('000-create-table-user.js'),
('001-create-table-box.js'),
('002-create-table-card.js'),
('003-create-table-connection.js'),
('004-create-table-connectionType.js'),
('005-create-table-journalDailyPrompt.js'),
('006-create-table-journalPrompt.js'),
('007-create-table-meta.js'),
('008-create-table-notification.js'),
('009-create-table-personalBlogComment.js'),
('010-create-table-personalBlogSubscriber.js'),
('011-create-table-sessionToken.js'),
('012-create-table-shouldNotSendToNonUser.js'),
('013-create-table-shipping.js'),
('014-create-table-space.js'),
('015-create-table-tag.js'),
('016-create-table-userAIGeneratedImage.js'),
('017-create-table-userFavoriteColor.js'),
('018-create-table-userSpace.js'),
('022-create-table-spaceCollaboratorUser.js'),
('023-create-table-notificationUnsubscribe.js'),
('024-create-table-userVisitSpace.js'),
('025-initial-foreign-key-associations.js'),
('025-initial-indexes.js');

prod

run migration after SequelizeMeta to ensure nothing runs

desktop

table structures

table names (check plurals)

eyeball test that tables, columns, associations, indexes matches prod

readme:

add info about how migrations are run

new migrations should match model.js and modelfiles

run migrations on blank db

server uses it

create blank db

recheck associations

add existing indexes

1 migration file per table

add associations

create all basic tables w basic columns

Run Sequelize Migration Programmatically

need v7?

Sequelize Migration

maybe there is a way to duplicate heroku dbs for prod testing

How to programmatically run sequelize migrations

on operation createConnectionType

mon 25, 4am GET /user/favorites

mar 25 12pm POST /space/multiple

on sign up / sign in err using prev anon user id instead of new one

where

this gets real slow eventually

w alter: true app starts by dropping all constraints and rebuilding thenm DROP CONSTRAINT followed by ALTERs

so the current live version has alter: false

return response/error on operations so client knows when db fails

long running process, from sync alter:true

ALTER TABLE "cards" ALTER COLUMN "x" DROP NOT NULL;
ALTER TABLE "cards" ALTER COLUMN "x" DROP DEFAULT;
ALTER TABLE "cards" ALTER COLUMN "x" TYPE INTEGER;

This is the script to find corrupted notifications:

select notifications.* from notifications
left outer join cards on notifications."cardId" = cards.id
where notifications."cardId" is not null and cards.id is null order by "createdAt" desc;
Now

CleanShot-2024-03-24-at-20.59.37.mp4.zip lucas’ walkthrough of the problem

looks promising

Document how to use migrations programmatically · Issue #326…

find a way to run npx sequelize-cli db:migrate on app start, runs new migrations

migration is a requirement before we can add new indexes?

edit readme w migration instructions

make a 2ndary blank local db

Migrations | Sequelize

🌱 operation createConnection SequelizeForeignKeyConstraintError: Key (connectionTypeId)=(rqRRanHIIPSdvWutc5LU5) is not present in table "connectionTypes

bug

fix operation: notifications saving w non-existing cardids SequelizeForeignKeyConstraintError:

bug

handles fails without removing failed items from queue?

maintains order

redo queue system to support multiple batches with max size

do this last

root problem is a slow migration

Once the script is complete we can figure out how to fake the migration log in the prod DB so it doesn’t start with the first initial migration.

does anything get deleted or overwritten ?

I would develop this script with a completely empty database.

And associations. Equalize assisiations now do nothing and can be removed from models.js

add new indexes

lucas

It shouldn’t because the migration script will usually use a ‘create table’ query which will fail and stop the whole migration.

sequelize CLI will create a new table where it will keep a log of all migrations it already executed. This will ensure that no migration is executed twice.

get user tags?

get user favs?

space.findDetailed

identify specific slow queries for perf investigation

if needed, may not be needed after sync success

started on the 17th, notification’s are created without proper card id

client side fix

bug

migration plan / questions

alter: false

add console.logs to each route

Remove cardid constraint

1. look into finding the clientside cause for notifications sending wrong cardids

investigate h12 timeouts

lucas

purge old notifications. scheduled task

see screenshot for stacktrace

add notification email w no recipients null soak

“I would recommend to initialize all the existing tables in your first script and from then on just use it to alter stuff”

4. i’ll rewatch the part of the vid where you cover how to spot the sync error so i can validate whether it actually synced without new errors

add time measurement to models.sequelize.sync

so the model files are like a bridge: the migrations change the actual db, the model files tell sequlize about the current state of the db

This is the script to create the foreign key constraint: ALTER TABLE “notifications” ADD FOREIGN KEY (“cardId”) REFERENCES “cards” (“id”) ON DELETE SET NULL ON UPDATE CASCADE; feel free to set ‘ON DELETE CASCADE’

check foreignkey constraints in connections and notification

so when you git pull for the first time you can just run the migrations and start developing

initialize all the existing tables in your first script and from then on just use it to alter stuff

write a migration that specifies all the existing table names, columns, contraints, and indexes

edit models to create new dbs

migrations needed for new columns

basically it’s just that extra migration step on top, but the model parts are the same as before right?

3. and then re-run the sync

heroku kills server if this doesn’t complete after 30s

https://github.com/kinopio-club/kinopio-server/pull/90

so i need to prevent this from happening

this allowed the server to start, and to properly enforce the connection type constraint

server crashed bc sync took very long, bc of foreign key constraints

the notification cardids foreignkey constraint was missing / not enforced

lucas wrote a script to look for connections with non-existent type. and then deleted those

there was also a similar error on the notifications table

which would cause crashes

so the belongsto relation between connection and connectionType wasn’t being enforced, so it was possible to assign a connection’s connectionType id to a type that didn’t exist in the db

models.sequelize.sync crashed server bc it didn’t have a catch

unique connectiontype id constraint wasn’t a problem bc it didn’t crash server

2. run the script and remove the busted notifications (can you send it?)