[BUG]: In the Query API generated identifier exceeds 63-bytes length limit
Problem
What version of `drizzle-orm` are you using? 0.28.6 What version of `drizzle-kit` are you using? 0.19.13 Describe the Bug I have a query using Query API that is doing a lot of nested joins. I've chcecked the generated query and it appears that the generated identifier name is getting truncated because it's too long. From what I read Postgres has a limit of 63-bytes long identifiers. Here is the generated query (I've replaced params with actual values): [code block] And the error says: [code block] Expected behavior When identifier length is too long it should try to maybe generate acronyms of tables. Instead of `training_step_templates` maybe it should use `tst` or maybe even random names to avoid conflicts. Environment & setup _No response_
Error Output
ERROR: column microcycles_microcycleDays_trainingTemplate_trainingStepTemplat.exercise_id does not exist
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Implement Identifier Length Management in Query API
The Query API in drizzle-orm generates identifiers that exceed the 63-byte limit imposed by PostgreSQL when performing nested joins. This occurs because the generated identifiers concatenate table names and column names without considering their length, leading to truncation errors.
Awaiting Verification
Be the first to verify this fix
- 1
Modify Identifier Generation Logic
Update the identifier generation logic in the Query API to check the length of generated identifiers. If an identifier exceeds 63 bytes, create a shortened version using acronyms or random names.
typescriptfunction generateIdentifier(originalName) { const maxLength = 63; if (originalName.length > maxLength) { return createAcronym(originalName); } return originalName; } function createAcronym(name) { return name.split('_').map(word => word[0]).join(''); } - 2
Implement Unit Tests for Identifier Generation
Create unit tests to ensure that the new identifier generation logic works correctly and does not produce identifiers longer than 63 bytes. Test cases should include various combinations of table and column names.
typescriptdescribe('Identifier Generation', () => { it('should generate valid identifiers', () => { expect(generateIdentifier('training_step_templates')).toBe('tst'); expect(generateIdentifier('short_name')).toBe('short_name'); }); }); - 3
Update Documentation for Query API
Revise the documentation for the Query API to inform users about the new identifier generation behavior and provide guidelines on how to structure their queries to avoid long identifiers.
- 4
Deploy Changes and Monitor
Deploy the changes to the production environment and monitor for any new errors related to identifier lengths. Ensure that the application continues to function as expected without identifier-related issues.
Validation
Confirm that the application no longer throws errors related to identifier length when executing queries with nested joins. Review the generated SQL queries to ensure identifiers are within the 63-byte limit.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep