Support Using Uuid as Primary Key

[martinsodabi]

Description

Add support for using uuid as primary key and remove dependency on incremental id as primary key when generating models.

[jondot]

Hi, can you explain where exactly you bumped into it and what is the motivation?

we currently use both id (a logical ID for data integrity, which is owned and managed by the database, and should be only used internally) and pid (a physical ID, to be used by the external world, which is a UUID).

[mikosco4real]

@martinsodabi, thank you for your valuable suggestion regarding the use of UUIDs as the primary key (PK) in our loco-rs models. We understand that having flexibility in choosing the identifier type is important for many use cases, and we appreciate you bringing this discussion to the table.

As @jondot mentioned, our current architecture utilizes a dual-ID system: an internal id for data integrity and a pid (physical ID) as a UUID for external reference. This approach is designed to balance efficiency, performance, and external usability. Here's why this method is beneficial:

1. Data Integrity: Numeric IDs (id) are typically managed by the database and offer performance advantages, especially when it comes to indexing and joining tables. They are sequential and compact, which helps in maintaining a streamlined and efficient database operation.

2. External Flexibility: UUIDs (pid) provide a globally unique identifier that is ideal for public-facing interfaces. They reduce the risk of enumeration attacks and are not sequential, which can be an advantage for security and distributed systems.

3. Best of Both Worlds: By using both, we ensure that the system remains robust and efficient internally while also providing the necessary flexibility and security considerations for external interactions.

We understand that some users may prefer to use only UUIDs for all purposes, including internal logic. While this is a valid approach, it's also important to consider the potential trade-offs in terms of performance and complexity. However, we are always looking to improve and make loco-rs more adaptable to various needs.

Could you please provide us with more details about your specific use case or the challenges you've encountered with the current system? Your detailed feedback is crucial for us to understand the context and motivations better. This will help us in considering future enhancements and possibly providing configurable options that cater to different preferences.

We're committed to making loco-rs as user-friendly and flexible as possible while maintaining its integrity and performance. Your input is invaluable in this journey, and we look forward to more discussions that will lead to improvements for all users.

[martinsodabi]

@mikosco4real, thank you for the detailed response. I think a configurable option will be better, however, I understand the complexity given that SeaORM is being used to automatically manage table relationships.

The main reason to not use incremental ids is for a distributed database. I am open to suggestions on how to handle distributed databases with Loco's current approach, thanks.

[mikosco4real]

@martinsodabi, your point about distributed databases is well-taken. Incremental IDs can indeed pose challenges in such environments due to the potential for collisions and the need for coordination among nodes. Here's how our current system can be adapted and some thoughts on moving forward:

Adapting the Current System for Distributed Databases

• Partitioning: Even with an incremental ID system, partitioning strategies like sharding can be employed. Each database shard would handle a subset of the data, and the incremental IDs would be unique within that subset.
• Composite Keys: Another strategy might involve using a combination of the internal incremental ID and another identifier (like a node or shard ID) to ensure uniqueness across the distributed system.

Moving Forward with UUIDs

• Configurable Option: Introducing a configurable option for using UUIDs as the sole primary key is a significant change but could be beneficial for scenarios like yours. This would allow users to choose the ID strategy that best fits their use case.
• Performance Considerations: While UUIDs are excellent for distributed systems due to their uniqueness, they come with their trade-offs in performance and storage. We'd need to carefully consider and benchmark these impacts, especially on indexing and query performance.

I will leave this one upto @jondot and the core maintainers who have deep understanding of the architecture and vision for loco-rs to decide. I would suggest that we could develop a prototype feature for UUID-only primary keys and gather feedback on its performance and usability.

[fan-tastic-z]

@martinsodabi I would like to ask, now that pid exists, should I use pid or id when establishing foreign keys or many-to-many relationships?

[jondot]

I believe this discussion has concluded - thanks for everyone participating. I will summarize:

• Pid is used for external purposes. It is a "handle" or a "reference" to a record
• We keep integer IDs used in constraints: primary keys, foreign keys, these are internal IDs

This is a common practice to enjoy the best of both worlds: having database-native IDs for easy indexing, access, and maintenance, and having user-friendly IDs for working with the external world.

If anyone is interested in having just one kind of an ID, such as UUID as primary key, we see this as an optimization -- so feel free to ALTER table or create your own migration for it.

[fan-tastic-z]

I believe this discussion has concluded - thanks for everyone participating. I will summarize:

• Pid is used for external purposes. It is a "handle" or a "reference" to a record
• We keep integer IDs used in constraints: primary keys, foreign keys, these are internal IDs

This is a common practice to enjoy the best of both worlds: having database-native IDs for easy indexing, access, and maintenance, and having user-friendly IDs for working with the external world.

If anyone is interested in having just one kind of an ID, such as UUID as primary key, we see this as an optimization -- so feel free to ALTER table or create your own migration for it.

@jondot thanks