I’ve encountered a problem when I had to create 2 columns in one table and then copy values to those columns from another table with relation to id from the first table. This sounds complex, but it appears to be an easy one. I’ve also encountered such problem at my first job. The saddest thing is that neither I nor my team lead was able to solve it. Encountering it again wasn’t a big pleasure, even though I have much more experience than I had. And I decided to solve it.
One of the reasons the solution came to me (I think) it’s because I’ve been trying to tackle this using knex (official site)- SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use. And that’s exactly what it turned out to be, and I couldn’t be happier.
At first, I’ll explain the problem and table schemas, then I’ll tell you how I handled it.
Problem
We have two related tables. The first has foreign key (let’s say event_id), the second primary key – mentioned id. We need to transfer data from the first table’s column to the second table, keeping event_id relation. Because the second table might have several records related to one event_id.
Motivation
I won’t explain why I decided to do this way and not the other, because the project where I’ve been working on had a certain architecture for certain requirements. But requirements changed, and we have to change architecture in order to have application working according to requirements.
Given
As I described above we have 2 tables:
events, withid,nameanddatecolumns;notifications, withid,textandevent_idcolumns;
As described in a problem section – we’re transferring one column with relation to foreign key, which in our case is event_id. We’ll transfer the date column.
Solution
As I mentioned above we use knex on a project and knex itself and postgres documentation helped me find a correct solution. Which is simple and elegant. But initially, I had wrong solutions which might have resulted in an excessive memory usage for running the script (both tables have tens of thousands records and constantly adding more). I uploaded my solution to github and separated it in a commits so that you can walk through it step by step. Also I added sql history dump file from https://sqliteonline.com/.
Let me tell you about https://sqliteonline.com/ a bit (this is not an advertisement). This is a cool and useful sql sandbox, where you can check different solutions or build tables. You can use different sql options and make lots of stuff like import/export sql, database or full query history in sql format. Because I cannot use project’s database and codebase, I decided to create dummy tables with desired structure online and this tool is the best for it from the top 5 search list (IMHO).
First step
knex – SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use.
And I highly agreed with it.
Knex is a good tool to work with SQL in JavaScript/TypeScript which allows to work with table schema and data really easily. It can run any query using knex.schema.raw or knex.raw. The first one used to work with database schema, the second – to work with data. It also gives us a possibility to use certain methods to work with information and schema.
Google helped me to get to know knex better and memories about unsolved task faded. Maybe because knex allows you to build a query chain using Promises, returning the result of the sql query if needed. So, theoretically, you can run a SELECT query with some condition, then use the result data inside the next query condition and so on.
knex('users')// Type of users is inferred as Pick[]
.select('id')
.select('age')
.then((users) => {
});
(example from knex documentation)
So I came up with the solution for my problem very quickly:
export function up(knex: Knex): Promise {// Create Date column in Notifications table.
return knex.schema.table('Notifications', (table) => {
table.specificType('Date', 'date').defaultTo('');
}).then(() => {
return knex.raw(`
SELECT ID, Date from Events;
`);
}).then((data) => {
ID: 1,
Date: ’05-22-2021′,
let query: string = '';
data.rows.forEach((row: Record) => {
query += `UPDATE Notifications(Date)
VALUES('${row.date')
WHERE EventID='${row.id}';`;
});
return knex.raw(query);
});
}
It seems to be a good one. Script runs and records all the data as needed. But this is not the most elegant solution, maybe the ugliest one. I thought about the case when we have 1000 records, then about the case with 1 million, then one billion. I hope you’ve got the point – it looks like a bottleneck. So I decided to make it a bit better.
Final solution
Searching for a more elegant solution I encountered postgres documentation on postgrespro.ru site:
UPDATE accounts SET contact_first_name = first_name,
contact_last_name = last_name
FROM salesmen WHERE salesmen.id = accounts.sales_id;
This is exactly what I need!
After changing the code to fit my needs I got this:
export function up(knex: Knex): Promise {
return knex.schema.table('Notifications', (table) => {
table.specificType('Date', 'date').defaultTo('');
}).then(() => {
return knex.raw(`
UPDATE Notifications
SET Date = Events.date,
FROM Events WHERE Event.ID = Notifications.EventID;
`);
});
}
Run the script, check the table – all works like a charm.
In case if you not use knex you can use SQL query chain (or one single UPDATE-SET-FROM). You can find SQL queries listing from site https://sqliteonline.com/ in my repo, along with the code from this article. You can easily import SQL query history file usign https://sqliteonline.com/, to check if everything works as intended and do some tests with the queries. I split the code into commits in order to you to being able to follow the solution step by step. Should you have any questions feel free to write me an email.
Conclusion
All you need to copy data from one table to another with relations is only 3 rows of SQL code.
For the whole of my programming career I was always amazed by the number of ways to solve probems. But the most amazing thing is that sometimes a complex task appears to be an easy one. You just need to dive deep into documentation…
