Cold River Comments - Answer Overflow

Topics

Cold River

•Created by Nino on 5/6/2024 in #help

slow Database

this very likely has nothing to do with Drizzle, but rather the database provider you're using. They probably have a paid plan that never turns off the database.

2 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

you're welcome! good luck in your project!

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

you would normally do something like this: 1. start transaction 2. check how many reminders. if more than allowed, abort transaction 3. insert reminder 4. commit transaction

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

ok great good

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

and it's fine for learning if you're not doing this already, but you'll want to make sure you don't store passwords in plain text, but rather a cryptographic hash

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

i think the check is invalid, checks can only take into account the row being inserted or updated. From the documentation (https://www.postgresql.org/docs/current/ddl-constraints.html): Note PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and restore to fail. The restore could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions. If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/restore problem because pg_dump does not reinstall triggers until after restoring data, so that the check will not be enforced during a dump/restore.)

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

you probably want to use $updateFn instead of (or in addition to) default for updatedAt

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

and if you do need to do this in production, make sure to do it in a staging environment first because I and you probably missed something in that playbook 😛

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

in other words, try not to do it. just delete all the data and start over, if you're still in development.

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

for reference, to change the type of an ID when using PostgreSQL i think you should be able to: 0. get a lock on the target table and all tables that reference it 1. disable foreign key checks 2. alter the column, with a USING clause if needed 3. update all tables that have a foreign key relationship that references that ID column to use the new ID values 4. re-enable foreign key checks 5. release lock on the tables

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

ah right. i think what you said is needed in other databases anyway

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

to make 1-step instead of 4

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

@samson , why not use the USING clause in Postgres?

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

i wouldnt expect any ALTER statements in the first migration file

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

huh that's weird

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

im not sure about push:pg because im using the beta which includes a migrate command. but yes, you edit the .sql file immediately after generate:pg

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

as samson said, there's two options: 1. reset the whole migrations and lose all your data 2. edit the migration to work and then run it If you're doing 1, that's what dropping the migrations and then generating the new one, and then pushing the change would look like. If you're doing 2, that's when you would edit the migration file and put the ALTER TABLE ... USING ... statement. You'd put it in the last migration file, replacing the ALTER TABLE statement in there. It would have the largest number as a preifx.

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

i don't know how foreign keys will work though

82 replies

•Created by David L. Bowman on 5/6/2024 in #help

Changing `id` type from serial to uuid, causing error.

it's not well documented in Drizzle yet, but I think you're expected to modify the migrations if there's no way to do them automatically. In this case, a serial is an int, which cannot be automatically converted to UUID. The error you're getting seesm to be trying to convert "id", which was a serial/int, to a type uuid. You'll need to specify how to convert old values: https://stackoverflow.com/a/7162961 ALTER TABLE tbl_name ALTER COLUMN id TYPE uuid USING some_expression;

82 replies