{"id":1120,"date":"2018-05-29T20:33:58","date_gmt":"2018-05-29T19:33:58","guid":{"rendered":"https:\/\/solidt.eu\/site\/?p=1120"},"modified":"2022-07-25T08:35:44","modified_gmt":"2022-07-25T07:35:44","slug":"knex-javascript-to-sql-query-generator","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/knex-javascript-to-sql-query-generator\/","title":{"rendered":"Knex: Javascript to SQL query generator"},"content":{"rendered":"<p>Source:\u00a0<a href=\"https:\/\/knexjs.org\/\">https:\/\/knexjs.org\/<\/a><\/p>\n<pre class=\"lang:js decode:true\">var knex = require('knex')({\r\n  dialect: 'sqlite3',\r\n  connection: {\r\n    filename: '.\/data.db'\r\n  }\r\n});\r\n\r\n\/\/ Create a table\r\nknex.schema.createTable('users', table =&gt; {\r\n  table.increments('id');\r\n  table.string('user_name');\r\n})\r\n\r\n\/\/ ...and another\r\n.createTable('accounts', table =&gt; {\r\n  table.increments('id');\r\n  table.string('account_name');\r\n  table.integer('user_id').unsigned().references('users.id');\r\n})\r\n\r\n\/\/ Then query the table...\r\n.then(() =&gt;\r\n  return knex.insert({user_name: 'Tim'}).into('users');\r\n})\r\n\r\n\/\/ ...and using the insert id, insert into the other table.\r\n.then((rows) =&gt;\r\n  return knex.table('accounts').insert({account_name: 'knex', user_id: rows[0]});\r\n})\r\n\r\n\/\/ Query both of the rows.\r\n.then(() =&gt;\r\n  return knex('users')\r\n    .join('accounts', 'users.id', 'accounts.user_id')\r\n    .select('users.user_name as user', 'accounts.account_name as account');\r\n})\r\n\r\n\/\/ .map over the results\r\n.map((row) =&gt;\r\n  console.log(row);\r\n})\r\n\r\n\/\/ Finally, add a .catch handler for the promise chain\r\n.catch((e) =&gt;\r\n  console.error(e);\r\n});<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Source:\u00a0https:\/\/knexjs.org\/ var knex = require(&#8216;knex&#8217;)({ dialect: &#8216;sqlite3&#8217;, connection: { filename: &#8216;.\/data.db&#8217; } }); \/\/ Create a table knex.schema.createTable(&#8216;users&#8217;, table =&gt; { table.increments(&#8216;id&#8217;); table.string(&#8216;user_name&#8217;); }) \/\/ &#8230;and another .createTable(&#8216;accounts&#8217;, table =&gt; { table.increments(&#8216;id&#8217;); table.string(&#8216;account_name&#8217;); table.integer(&#8216;user_id&#8217;).unsigned().references(&#8216;users.id&#8217;); }) \/\/ Then query the table&#8230; .then(() =&gt; return knex.insert({user_name: &#8216;Tim&#8217;}).into(&#8216;users&#8217;); }) \/\/ &#8230;and using the insert id, insert into the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[5,4,1],"tags":[],"class_list":["post-1120","post","type-post","status-publish","format-standard","hentry","category-javascript","category-programming","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/1120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=1120"}],"version-history":[{"count":3,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/1120\/revisions"}],"predecessor-version":[{"id":1123,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/1120\/revisions\/1123"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=1120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=1120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=1120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}