Hi, we are building a Spring Boot application using jOOQ and I ran into trouble with database table column orders during development (see below). I generated jOOQ classes using one schema (locally), but ran the application against a schema with a different column order in some database tables (test system) and then I get wrongly mapped data using jOOQ queries. These problems during development might be avoidable (with some annoyance), but unfortunately I also see potential problems in production. See below for details on that. If there's no (easy) fix, we probably would need to change our stack in one way or the other, so I'm glad about any advice or explanation you people have for us!
*tl;dr:* My main question is: Is there any way to use jOOQ if multiple instances of the application have different column orders in some database tables? Or do you have any ways to deal with the problems arising from different column orders? I'm happy for any hint. *Problem description in detail:* *1. Potential Problems in production* Our application will have multiple instances (hosted by our customers on their own) and these might end up with different column orders. Background: The number of customers may grow to 20+ and in the domain that we work in it's not unusual that customers want to stay on a stable release version and not update to the newest version. Then still, sometimes they want new features on their old stable release which might introduce new database columns. When they later update to a newer version, they will end up with a different column order than a different customer who always updated to the newest release quickly. With a different project, we saw this happen quite a few times. I know that this is not good practice, and believe me, me and my colleagues are very, very much annoyed by it, but unfortunately I cannot exclude the possibility that this might happen again with the new application. *2. Problems during development due to co-existence with Hibernate* Our main project uses jOOQ (and flyway to manage database initialization and updates), but we also have a dependency on a project which uses Hibernate (don't ask why, I found the project in this state when I joined). The corresponding database tables are thus created and managed by Hibernate. When new columns are added there and e.g. our test system gets updated, the new columns will be added at the end of the corresponding database table by Hibernate. But, in development I sometimes clear the database (i.e. I drop all tables using flywayClean) and then let it be re-instantiated by starting the application. Because Hibernate will then use the default order of creation (alphabetically or whatever systematics), the order will differ compared to the test system. This second problem stems from the fact that we use jOOQ and Hibernate (or rather jOOQ+flyway and Hibernate) at the same time. I see some ways around that, e.g. not dropping all tables in the database, but only emptying them (which might involve more manual steps). Otherwise, possible solutions to me seem to be to completely switch to Hibernate (would also solve Problem 1) or completely switch to jOOQ with both projects and just force customers to take the newest release, if they want a new feature. But that'd be a lot of work and I'd rather avoid that. So if you have any other ideas, please let me know! Thanks! Yafl -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a78e31b2-6e5d-480e-979f-b35d2880669en%40googlegroups.com.
