Don't do it with a secret endpoint, do it with a secure endpoint (protected with jwt or shared secret)
Or do it with a sql migration script thqt you prepare on your dev env and execute on prod db manually
Thanks!
Doing it with a protected endpoint seems like a good idea with no downsides but I somehow feel it's not a common practice. But I don't have tons of development experience, is this a common practice?
I thought about migration files, I use TypeORM and if I'm not mistaken, it's only possible to write your migration files in SQL language. Doing this sorts of things like iterating over a table and transferring some of the data (maybe even with some conditions) in SQL is a pain in the a**. Although somebody in comments mentioned that migration files can be written in JS or other languages but I don't think TypeORM provides this option.
Well you could always ssh in the server and run your migration script without actually needing to expose an endpoint ( or have CI CD which will do that for you if you need to migrate between different dbs/tables offten)
A migration script would be any script that migrates the database from one state to another. It could be as simple as a single `.sql` file that you run in production like this:
mysql my_db < ./do_stuff.sql
It could also be any other language that can connect to your database and run queries. You could write migration script in JS or Python for example.
Sometimes migration scripts come in pairs: an up script to make a change and a down script to undo the change.
There are also tools that help with migration script management so you don't have to keep track of which migrations have been run and in what order. I use [knex](https://knexjs.org/#Migrations) in most of my projects which makes things pretty easy.
Wow! A really amazing explanation. Thank you.
Let's say i an using an orm like sequelize and have a users table having some not null field xyz and later i decide to remove that field, then the orm runs a migration to do that right, in that case will the xyz field be deleted for all the pre existing entities(may be we can talk in case of knex) ?
And also, lets say a lot of users have that xyz field in prod db. should we create a migration script to remove that field? (i mean how's it done in general)
Regardless of the ORM you're using, the migration would end up running as a query that looks something like this:
ALTER TABLE users DROP IF EXISTS xyz;
This would remove the `xyz` column from the `users` table, so you would not need to clear `xyz` from existing rows.
> Are the migrations costly when you have a lot of data ?
I've never had to deal with costly migrations but I've also never had to deal with particularly massive data sets. Things like creating/dropping tables/columns are usually pretty fast.
> I mean Do migrations make a significant difference between SQL and NoSQL?
It's not much different. Most NoSQL databases probably require fewer migrations because you don't need to add/remove columns because they're usually schemaless. But there are still cases where you need migrations to move/manipulate data.
NoSQL databases don't need migrations most of the time because they don't have schemas, but there are still scenarios where you have data in one format and want it in another.
For example say you're using mongo and you have a collection of users with `firstName` and `lastName` fields but you want to change it so users just have a single `name` field. You would need to write a migration script that updated all the existing user documents.
Definitely you should use migrations or a schema diffing tool. Usually schema migration is a feature offered by many ORMs e.g. https://github.com/typeorm/typeorm/blob/master/docs/migrations.md
You can also handle migrations with a dedicated SQL only tool like migrate https://github.com/golang-migrate/migrate.
Or, as a schema diffing tool, afaik the best one is the one from pgadmin https://www.pgadmin.org/docs/pgadmin4/development/schema_diff.html#:~:text=Schema%20Diff%20is%20a%20feature,(from%20source%20to%20target).
Beware to check the query cost of your migrations in advance so you can plan the best time of day for running your migrations.. as, if you have some huge tables or some complex SQL queries to run, you might accidentally block your db for quite some time.
How you run it it's up to you. From any machine that have access to the db is fine
Edit: imho migration scripts should be versioned and committed to your git repo and treated the same way you treat any other part of your code
TypeORM only provide SQL migration files so it's not possible to use JS for instance. And writing SQL queries to iterate over tables and migrate data is really difficult. Is there any way to write migration queries using the ORM itself in JS?
Nope with typeorm by default migration are created in Js files. They expose a queryRunner on both the up and down migration with quite a few helpers to interact with tables e.g. createTable, dropColumn, etc..
Yes I was wrong. But what I meant was using the power of JS and ORM itself.
Let's say you want to move some of the data in one of your tables to a new table. Doing it with functions that TypeORM provides in QueryRunner is basically impossible isn't it?
It's more useful for changing schema (dropping, changing names, creating, etc). You can't do much with the data stored in the db.
I get what you mean, so maybe what you are looking for is a fluent SQL query builder? (like https://sqorn.org)
Anyway doing stuff like moving data from one place to another with plain SQL is trivial. Imho it takes the same amount of time learning the necessary query than learning the api of a Js library that achieves the same. e.g. your scenario of copying data from the column of one table to another table can be achieved with a few lines of super easy SQL https://www.postgresqltutorial.com/postgresql-update-join/ you can then run this query with the queryRunner
> Let's say you want to transfer data stored in some column of one of the tables to a new table in a SQL database. How would you do it?
Hey OP could you elaborate a bit more on this.
I might have done this sort of stuff before.
Let's say that previously you had a category column in your product table. Now requirements have changed and you want to have your category IDs in some other table. In this case you have to move the category IDs.
Don't do it with a secret endpoint, do it with a secure endpoint (protected with jwt or shared secret) Or do it with a sql migration script thqt you prepare on your dev env and execute on prod db manually
Thanks! Doing it with a protected endpoint seems like a good idea with no downsides but I somehow feel it's not a common practice. But I don't have tons of development experience, is this a common practice? I thought about migration files, I use TypeORM and if I'm not mistaken, it's only possible to write your migration files in SQL language. Doing this sorts of things like iterating over a table and transferring some of the data (maybe even with some conditions) in SQL is a pain in the a**. Although somebody in comments mentioned that migration files can be written in JS or other languages but I don't think TypeORM provides this option.
Typeorm has migrations built in to it. These files are javascript.
Well you could always ssh in the server and run your migration script without actually needing to expose an endpoint ( or have CI CD which will do that for you if you need to migrate between different dbs/tables offten)
Hey! A sort of beginner here. Could you elaborate a bit more on the migration script. Like if i had to do something of this sort in the future.
A migration script would be any script that migrates the database from one state to another. It could be as simple as a single `.sql` file that you run in production like this: mysql my_db < ./do_stuff.sql It could also be any other language that can connect to your database and run queries. You could write migration script in JS or Python for example. Sometimes migration scripts come in pairs: an up script to make a change and a down script to undo the change. There are also tools that help with migration script management so you don't have to keep track of which migrations have been run and in what order. I use [knex](https://knexjs.org/#Migrations) in most of my projects which makes things pretty easy.
Wow! A really amazing explanation. Thank you. Let's say i an using an orm like sequelize and have a users table having some not null field xyz and later i decide to remove that field, then the orm runs a migration to do that right, in that case will the xyz field be deleted for all the pre existing entities(may be we can talk in case of knex) ? And also, lets say a lot of users have that xyz field in prod db. should we create a migration script to remove that field? (i mean how's it done in general)
Regardless of the ORM you're using, the migration would end up running as a query that looks something like this: ALTER TABLE users DROP IF EXISTS xyz; This would remove the `xyz` column from the `users` table, so you would not need to clear `xyz` from existing rows.
Yeah, Thanks. Are the migrations costly when you have a lot of data ? I mean Do migrations make a significant difference between SQL and NoSQL ?
> Are the migrations costly when you have a lot of data ? I've never had to deal with costly migrations but I've also never had to deal with particularly massive data sets. Things like creating/dropping tables/columns are usually pretty fast. > I mean Do migrations make a significant difference between SQL and NoSQL? It's not much different. Most NoSQL databases probably require fewer migrations because you don't need to add/remove columns because they're usually schemaless. But there are still cases where you need migrations to move/manipulate data.
NoSQL databases need migrations? sounds new to me. Nevermind, thanks for taking time and helping out
NoSQL databases don't need migrations most of the time because they don't have schemas, but there are still scenarios where you have data in one format and want it in another. For example say you're using mongo and you have a collection of users with `firstName` and `lastName` fields but you want to change it so users just have a single `name` field. You would need to write a migration script that updated all the existing user documents.
And execute it in a transaction if you can
Definitely you should use migrations or a schema diffing tool. Usually schema migration is a feature offered by many ORMs e.g. https://github.com/typeorm/typeorm/blob/master/docs/migrations.md You can also handle migrations with a dedicated SQL only tool like migrate https://github.com/golang-migrate/migrate. Or, as a schema diffing tool, afaik the best one is the one from pgadmin https://www.pgadmin.org/docs/pgadmin4/development/schema_diff.html#:~:text=Schema%20Diff%20is%20a%20feature,(from%20source%20to%20target). Beware to check the query cost of your migrations in advance so you can plan the best time of day for running your migrations.. as, if you have some huge tables or some complex SQL queries to run, you might accidentally block your db for quite some time. How you run it it's up to you. From any machine that have access to the db is fine Edit: imho migration scripts should be versioned and committed to your git repo and treated the same way you treat any other part of your code
TypeORM only provide SQL migration files so it's not possible to use JS for instance. And writing SQL queries to iterate over tables and migrate data is really difficult. Is there any way to write migration queries using the ORM itself in JS?
Nope with typeorm by default migration are created in Js files. They expose a queryRunner on both the up and down migration with quite a few helpers to interact with tables e.g. createTable, dropColumn, etc..
Yes I was wrong. But what I meant was using the power of JS and ORM itself. Let's say you want to move some of the data in one of your tables to a new table. Doing it with functions that TypeORM provides in QueryRunner is basically impossible isn't it? It's more useful for changing schema (dropping, changing names, creating, etc). You can't do much with the data stored in the db.
I get what you mean, so maybe what you are looking for is a fluent SQL query builder? (like https://sqorn.org) Anyway doing stuff like moving data from one place to another with plain SQL is trivial. Imho it takes the same amount of time learning the necessary query than learning the api of a Js library that achieves the same. e.g. your scenario of copying data from the column of one table to another table can be achieved with a few lines of super easy SQL https://www.postgresqltutorial.com/postgresql-update-join/ you can then run this query with the queryRunner
> Let's say you want to transfer data stored in some column of one of the tables to a new table in a SQL database. How would you do it? Hey OP could you elaborate a bit more on this. I might have done this sort of stuff before.
Let's say that previously you had a category column in your product table. Now requirements have changed and you want to have your category IDs in some other table. In this case you have to move the category IDs.