Using uuid as primary key on tables breaks reference fields

[kfawcett]

Wappler Version: 5.3.1
Operating System: Windows 11
Server: Node.js
Database: Postgres

Expected behavior

When relating two tables with UUID primary keys using a reference field it should save without an error.

Actual behavior

A “Database Update Failed” message appears with the following error.

Error: alter table "users" add constraint "users_account_foreign" foreign key ("account") references "account" ("id") - foreign key constrain "Users_account_foreign" cannot be implemented.

How to reproduce

Create two tables. Each with UUID primary field, then add a reference field to one of the tables that references the other via the UUID field.

User table

Reference to Account table

Account table

[kfawcett]

I think line 5 could be part of the problem. I believe the table.integer needs to be changed to table.uuid since I do not have increment fields on the tables. I tried changing it to table.uuid('account'); but it appears it’s overwritten when I attempt to apply the changes.

[George]

Indeed both key and reference should be of the same type - uuid in your case.

I think if you are in advanced display mode you will see the reference type.

Maybe it is because the id wasn’t applied yet.

Will check it out

[kfawcett]

I thought about that so I applied id to each table before adding the reference field. Still received the error.

This is also a brand new users table. I deleted the original table that is added with the docker image.

[George]

Also what is your database type? As every database have different UUID implementation…

[kfawcett]

Postgres.

[George]

Are you sure you have selected UUID as table field, as with Postgres a lot more have to be done when using UUID’s and a generation code as much more complex:

and after tables with UUID as keys are created you can apply the UUID reference:

[kfawcett]

Yes, I am. Here’s a video. https://youtu.be/j6KcdhVefb0

Not sure why my knex looks nothing like yours.

[kfawcett]

Could it be because I’m reusing ‘users’ and that was a previous table with an integer primary key? Maybe when I deleted it Wappler didn’t remove all files and something is getting referenced?

[kfawcett]

A brand new table has same issue.

create new table u_users

[George]

well it is all about the account table which already exists, it seems not to have uuid as primary key

could you paste the creation change code fot it?

[kfawcett]

  return knex.schema

    .raw(knex.client.constructor.name.startsWith('Client_PG') ? 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"' : 'SELECT 1')

    .createTable('account', async function (table) {

      table.uuid('id').primary().unique().defaultTo(knex.client.constructor.name.startsWith('Client_PG') ? knex.raw('uuid_generate_v4()')

                : (knex.client.constructor.name.includes('MSSQL') ? knex.raw('NEWID()')

                  : (knex.client.constructor.name.includes('MySQL') ? knex.raw('(UUID())') : null)));

    })

    .table('users', async function (table) {

      table.renameColumn('company', 'account');

    })

};

exports.down = function(knex) {

  return knex.schema

    .table('users', async function (table) {

      table.renameColumn('account', 'company');

    })

    .dropTable('account')

};```

[scalaris]

Hi,

I’m simply sharing an opinion here, from experience, Wappler’s database manager isn’t the right place to create and manage Postgres tables, schemas, indices, etc…

It’s far too complex and nuanced a database. Use a proven tool such as PGAdmin for management and then Wappler at the applicaiton layer.

Experience is simply the name I give my mistakes.

[George]

I strongly disagree. Wappler database manager is exactly the simplest way to edit the database schema and specially the changes for redeploy.

Please try to add valuable contributions to the bug reports, concerning the specific bug report and avoid statements like - it is all bad.

[scalaris]

I didn’t say it’s all bad. I just talked about my experiences. Experience as a power user, more so than most others in the community, including the development team.

When was the last time you, Patrick, Teodor or anyone else actually sat down and tried to build something of real commercial value and complexity with Wappler? Only when you do so, will you realise the reality behind your marketing messages and my comments.

It’s absolutely ridiculous to believe that Wappler is the best Postgres database manager and the simplest way to edit Postgres schemas. These tools have had large teams and years of investment to get where they are - they’re built on real expertise in the specifics of that database. Not hubris.

I’m a huge fan of Wappler becuase I’ve fought the battles with all the issues it has. I also realise how great it could be one day if done the right way.

My experience has been a great education. But the fees were high

Don’t shoot the messenger.

[George]

As I said, please stick to the topic. Use coffee lounge for general discussions or if you have better ideas for improvements file a feature request.

[kfawcett]

@George, I restarted my computer and strangely the attributes on the table fields are different now. I’m not sure what the restart fixed, but I am able to create reference fields now.

I no longer see ‘unique’ and ‘allow null’ on the uuid fields.

When I start to create a reference field I do not see the ‘id’ field, only the ‘table’ attribute.

[George]

Strange indeed that is why I could replicate it and it was working fine to me. Maybe there was some old cached definition of your accounts table or the uuid wasn’t recognized completely because it is a custom field in Postgres

[kfawcett]

Could it have something to do with the docker image used when creating a project or is it all built at time of project creation?