How to integrate ts-sql-query with migrations?

[Motii1]

Hi, I am coming from ORMs background and I am wondering what is the best way to handle migrations with ts-sql-query?

What do you recommend? Maybe it is also a good question to add it to the FAQ

[juanluispaz]

Hi,

Good Idea, I will add it, meanwhile a quick summary.

If you are coming from an ORM it is common ORM offers you the possibility to create/migrate the database using an Object model as a starting point; that is useful in small and non-complex projects. If you want to use SQL, you want full power over SQL, especially if you need to migrate complex data between versions.

There are two different approaches you can use when you work with SQL first:

• SQL migration scripts first: For this you write your SQL migrations commands; I strongly recommend you to:

  • Write the SQL migration outside of your backend (but in the same code repository) because you will want (at some moment in time) to execute it using a database user with more privilege than the user used by your backend to connect to the database.
  • Write the SQL migration as SQL files instead of using some JS wrappers/builders; at this level, you want to take full advantage of your database; even if you start simple, you will want to allow you grow up, then you have the time to prepare yourself to use more advanced features provided by your database when you have the opportunity.

  Several amazing tools in the market manage the SQL migrations script for you. The one we use in our projects is Flyway, for starting you can use the community edition, and then it is easy to change to another tool (if you are not happy) because your scripts don't have any dependencies on this kind of tool (or even it is really easy to build a simple script to apply it). Doing a quick search in NPM I found db-migrate that supports the same script migration pattern; I never used db-migrate before.

• DB diagram first: This can be less flexible than the previous one, depending on the tool you use, but it can be very massively useful; I worked in this way on several important projects. I worked on important & critical projects where data quality (and control over it) is very important where they used ERwin data modeler, but this tool is insanely expensive; another alternative that I used (and I love) is Visual Paradigm, for DB diagrams you can use the community edition as starting point, but, if you want to apply the changes to the DB, you will need the Standard edition (19 USD/month per editor).

  When you have a complex project, a good DB Diagram is really important and makes it easy to understand the domain; going blind is not good, but you will not miss it if you are working on something small for not so long time. The good thing about this approach is you can ensure your diagram is your source of truth. Note: Visual Paradigm (standard edition) supports updating the diagram with the changes detected in a running database, allowing you to use SQL migration scripts and then update the diagram.

Hopefully, I have provided you with enough information in this text. Let me know any comments or questions, and I will gladly answer you.

[Motii1]

@juanluispaz Wow, that's a great answer. Thank you!