I am trying to integrate database schema versioning in an existing project where schema is not versioned at all.
Is it a good practice to create a tight mapping between application version and database schema version so much so that the application deployment will fail if the schema version is not the one expected by the app version? This is because the application has frequent schema changes.
What are the pitfall of using such a logic in application init code?
Honestly, you have several questions to unwrap and there is no perfect guidance. Database versioning is difficult at best. However, there is guidance that I have been successful with. In all cases, starting where you are as "version 0" is your best bet.
For your first statement, use one of the following methodologies (or a combination that works best):
EntityFramework Code First Migrations - Treat the current state of your schema as well as any reference tables as version 0.
DbUp and the DbUp Azure Pipelines tasks - This is a very simple, but effective way to get a "poor-man's" database migration methodology, if you follow some simple rules/conventions. There are also ways to integrate "running" DbUp migrations as part of the start-up of your application, too, but I've had success executing DbUp migrations via Azure Pipelines releases.
Red Gate Database Lifecycle Management (DLM) Automation Suite - This requires a bit more planning and comes with a cost, but Red Gate's tools integrate well and give you more of a complete DLM picture from integration of SSMS with Git version control, drift detection, Azure Pipelines tasks, and more.
As far as the question of should you couple versioning of the database with the application, you'll need to answer this question. Is the database shared between more than one application or is it dedicated to that application. If "dedicated" is your answer, then versioning of the database with the application is the right thing to do and should be done so within the same source code repository. If not, it will be difficult to decide which application "owns" the versioning of the database or if it needs to be versioned entirely independent of your apps. If it is versioned independent of your apps, then you better have strong unit testing in all of the apps.
Lastly, I will assume your "pitfalls of using such logic" refers to using your applications initialization/startup code to run an upgrade of the database to the intended version. If I've assumed correctly, there is no right/wrong way. What matters is that you understand where you need the automated process to fail to ensure your application does not incur a downtime because of a failed database migration. If you upgrade a DB during initialization/startup of an application, then your app better have a fallback mechanism where it can function with the previous version of the DB until which time it's fixed. This is where I'd recommend making failure of a DB upgrade part of the deployment process itself and not part of the application. That way, if the DB upgrade fails (and you're doing it correctly), reverting to the previous version of the app upon failure ensures that downtime is minimized.
And, when I refer to doing DB upgrades "the right way," I refer to using methods that ensure the state of the DB schema is only changed atomically and transactionally such that failures in the middle of a deployment can be completely rolled back.
I think having such check in place is a good thing, especially if running the app with the wrong schema would cause app failures or, even worse, leave room for potentially costly corruptions in the database.
The only pitfall I can think of would be dependent on the actual deployment process - if it stops the already deployed app version before deploying the new app version the additional check will increase the number and duration of app outages. Not necessarily a bad thing when taking into account the cost of running the app with the wrong schema version. But IMHO it should be possible to adjust the deployment process to avoid such outages by only stopping the existing app version after successful deployment of the new version, typically by switching traffic from one version to the other.
Another approach to this problem would be to always have the app capable of running in backwards-compatible manner, automatically handling the schema changes. If such capability exists then you shouldn't have such check in place.
