Skip to content

Instantly share code, notes, and snippets.

@tarex

tarex/json_postgres.js

Forked from lucdew/json_postgres.js
Created July 14, 2016 18:56
Show Gist options
  • Save tarex/aa96f91cfe0fba92fe268d92b43797e1 to your computer and use it in GitHub Desktop.
Save tarex/aa96f91cfe0fba92fe268d92b43797e1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @lucdew lucdew renamed this gist Sep 28, 2014. 1 changed file with 0 additions and 0 deletions.
    0 json_postgres → json_postgres.js
    View file
    File renamed without changes.
  2. @lucdew lucdew created this gist Sep 28, 2014.
    95 changes: 95 additions & 0 deletions json_postgres
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,95 @@
    var connectionString = 'postgres://localhost:5432/postgres';
    var Promise=require('bluebird');


    var knex = require('knex')({
    client: 'pg',
    connection: {
    user: 'postgres',
    database: 'postgres',
    port: 5432,
    host: 'localhost',
    password: 'admin'
    },
    debug: false,
    pool: {
    min: 1,
    max: 2
    }
    });


    var books = [
    { name: 'Da vinci', author: { 'first_name': 'Bob', last_name: "White" }},
    { name: '50 shades of blue', author: { 'first_name': 'Bob', last_name: "Heinzeberg" }}
    ];

    // Create table with json data column
    var schemaCreation =function() {

    return knex.schema.dropTableIfExists('books')
    .then(function() {
    return knex.schema.createTable('books',function(table){
    table.increments();
    table.timestamps();
    table.json('data').nullable();
    });
    });

    };


    // Insert the books
    var inserts = function() {
    var insertPromises = [];
    books.forEach(function(book) {

    insertPromises.push(
    knex('books')
    .insert({data: JSON.stringify(book), created_at : new Date(), updated_at: new Date() })
    );
    });

    return Promise.all(insertPromises);
    };



    // Perform some selects on json
    var selects = function() {

    return knex('books').select(knex.raw("data->'author' as author")).whereRaw("data->'author'->>'first_name'=? ",[books[0].author.first_name])
    .then(function(rows) {
    console.log("Found "+rows.length+" books with authors first_name "+books[0].author.first_name);
    rows.forEach(function(row){
    console.log(row);
    });
    })
    .then(function() {
    return knex('books').select(knex.raw("data->'author' as book")).whereRaw("data->'author'->>'last_name'=? ",[books[0].author.last_name])
    .then(function(rows) {
    console.log("Found "+rows.length+" book(s) with last_name "+books[0].author.last_name);
    console.log(rows[0]);
    });
    });

    };

    // Main flow
    schemaCreation()
    .then(function() {
    console.log('Table created');
    })
    .then(inserts)
    .then(function() {
    console.log('Inserts done');
    })
    .then(selects)
    .then(function() {
    process.exit(0);

    })
    .catch(function(error){
    console.log(error);
    });