MehdiZonjy
commented
6 years ago Thanks for giving Json-transqlify a shot.
I'll try my best to give a general example but I might be able to help you better if you could share with me the structure of your Json, table schema and your current transqlify definition file.
Assuming you have an array of user records that look something like
{
"id": "<uuid>",
"firstName": "<string>",
"lastName": "<string>",
"age": "<number>"
}and maybe the table schema is something like
CREATE TABLE `users` (
`id` char(36) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
)The following yaml definition file utilizes Batch-Upsert loader and allows you to insert user JSON into the corresponding users table and overwrite any existing records that hold the same Id
Insert
version: 1.0
loaders:
- batchUpsert:
label: InsertUser
source: $entity
tableName: users
transform:
columns:
- column: id
value: $entity.id
- column: name
value: $entity.fistName + $entity.lastName
- column: age
value: $entity.ageall is left is to build a jsonTransqlifier based on the definition you just created
const createFactory = require('json-transqlify').createFactory;
const db = {
host: 'localhost',
user: 'root',
password: '',
database: 'json_transqlify_demos',
connectionLimit: 2
}
const factory = createFactory(db)
const transqlifier = factory.createTransqlifier('./upsert-users.yaml'); // the definition file from above
const main = async () => {
const users =[
{ id: '11d7781b-3f58-4080-9a78-063c14e2e71f', firstName: 'Dextar', lastName: 'Morgan', age: 35 },
{ id: '213e287e-d2c7-4014-aa11-699d98afabd1', firstName: 'Debra', lastName: 'Morgan', age: 30 },
{ id: '0cc26d2d-6dcd-4663-8d9d-d27fbe9825e1', firstName: 'James', lastName: 'Doakes', age: 39 }
]
await transqlifier(users)
factory.closePool()
}
main()just for the fun of it, let's insert this user manually to the db before running the script and see it get updated
INSERT INTO users (id, name, age) VALUES ( '11d7781b-3f58-4080-9a78-063c14e2e71f' , 'Harry Morgan', 59)when running the script, two new records will be inserted, and the existing one will get updated and the name will change from Harry Morgan to Dextar Morgan along with the age as well.
Please note there are currently a variety of loaders. The one i used here is batch-upsert-loader The generated SQL command is something along the lines of
INSERT INTO users (id,name,age) VALUES (?,?,?),(?,?,?),(?,?,?)
ON DUPLICATE KEY UPDATE id = VALUES(id),name = VALUES(name),age = VALUES(age)Let me know if this helps.
nyousefzai
Hi, I have a hard time '' updating records '' like your first example I have an array of objects I am able to insert it and when I do batch-upsert it gets created again.
if you can explain a little more in update area that will help. an example will be nice. Thanks for sharing your lovely work. nice job.