Skip to content

Instantly share code, notes, and snippets.

@alexturek

alexturek/method.md

Last active May 20, 2017 01:19
Show Gist options
  • Save alexturek/fa1e66143f40d32f3434b1e5cde0092b to your computer and use it in GitHub Desktop.
Save alexturek/fa1e66143f40d32f3434b1e5cde0092b to your computer and use it in GitHub Desktop.
Download ZIP
How I validated squel's typings (https://github.com/hiddentao/squel)
Raw
method.md

From the API examples, in Chrome console:

snippets = $('.syntaxhighlighter.js table')
bits = []; snippets.each((i, snippet) => bits.push(snippet.innerText));
console.log(bits.join('\n'));

After that, I ran ts-node against squel-test.ts until it compiled... and I gave up when I started hititng all the class bits.

There are several examples that aren't supported, check out any comment like // ERRORING

Raw
squel-test.ts
import * as squel from 'squel';
const log = console.log;
log( squel.VERSION );    /* version string */
const s = squel.select();
s.from("student");
log( s.toString() );    /* SELECT * FROM student */
log( squel.select().from("students").toString() );  /* SELECT * FROM students */
log( '' + squel.select().from("students") );  /* SELECT * FROM students */
log(
    squel.select({ separator: "\n" })
        .from("students")
        .field("name")
        .field("MIN(test_score)")
        .field("MAX(test_score)")
        .field("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')")
        .group("name")
        .toString(),
);
/*
    SELECT
        name,
        MIN(test_score),
        MAX(test_score),
        GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
    FROM
        students
    GROUP BY
        name
*/
log(
    squel.select()
        .from("students")
        .toString(),
);
/* SELECT * FROM students */
log(
    squel.select({ separator: "\n" })
        .from("students")
        .toString(),
);
/*
    SELECT
    *
    FROM students
*/
log(
    squel.select()
        .from("students")
        .from("lecturers", "l")
        .from("admins")
        .toString(),
);
/* SELECT * FROM students, lecturers `l`, admins */
log(
    squel.select()
        .from( squel.select().from('students'), 's' )
        .field('s.id')
        .toString(),
);
/*  SELECT s.id FROM (SELECT * FROM students) `s` */
log(
    squel.select()
        .from("students")
        .field("id")
        .field("students.name")
        .toString(),
);
/* SELECT id, students.name FROM students */
log(
    squel.select()
        .from("students")
        .field("id", "Id")
        .field("students.name", "Student Name")
        .toString(),
);
/* SELECT id AS "Id", students.name AS "Student Name" FROM students */
log(
    squel.select()
        .from("students", "s")
        .field("s.id")
        .field("s.test_score", "Test score")
        .field("DATE_FORMAT(s.date_taken, '%M %Y')", "Taken on")
        .toString(),
);
/*
    SELECT
        s.id,
        s.test_score AS "Test score"
        DATE_FORMAT(s.date_taken, '%M %Y') AS "Taken on"
    FROM
        students `s`
*/
log(
    squel.select()
        .from("students", "s")
        .field(squel.select().field("MAX(score)").from("scores"), 'score')
        .toString(),
);
/*
    SELECT
        (SELECT MAX(score) FROM scores) AS "score"
    FROM
        students `s`
*/
log(
    squel.select()
        .from("students")
        .field("id")
        .distinct()
        .toString(),
);
/*  SELECT DISTINCT id FROM students    */
log(
    squel.select()
        .from("students")
        .join("teachers")
        .toString(),
);
/*  SELECT * FROM students INNER JOIN teachers */
log(
    squel.select()
        .from("students")
        .join("teachers", "t")
        .outer_join("expelled")
        .toString(),
);
/*  SELECT * FROM students
        INNER JOIN teachers `t`
        OUTER JOIN expelled
*/
log(
    squel.select()
        .from("students")
        .join(
            squel.select().field('score').from('results'),
            't'
        )
        .outer_join("expelled")
        .toString(),
);
/*  SELECT * FROM students
        INNER JOIN (SELECT score FROM results) `t`
        OUTER JOIN expelled
*/
log(
    squel.select()
        .field("students.id")
        .from("students")
        .left_join("teachers", null, "students.id = teachers.student_id")
        .right_join("jailed", "j", "j.student_id = students.id")
        .toString(),
);
/*  SELECT students.id FROM students
        LEFT JOIN teachers ON (students.id = teachers.student_id)
        RIGHT JOIN jailed `j` ON (j.student_id = students.id)
*/
log(
    squel.select()
        .from('marks', 'm')
        .join( squel.select().from('students'), 's', 's.id = m.id' )
        .toString(),
);
/*  SELECT * FROM marks `m` INNER JOIN (SELECT * FROM students) `s` ON (s.id = m.id) */
log(
    squel.select()
        .field("id")
        .from("students")
        .where("name = 'Thomas'")
        .toString(),
);
/*  SELECT id FROM students WHERE (name = 'Thomas')  */
log(
    squel.select()
        .field("id")
        .from("students")
        .where("score = ?", squel.select().field('MAX(score)').from('scores'))
        .toString(),
);
/*  SELECT id FROM students WHERE (score = (SELECT MAX(score) FROM scores))  */
log(
    squel.select()
        .field("id")
        .from("students")
        .where("name = 'Thomas'")
        .where("age > 18")
        .toString(),
);
/*  SELECT id FROM students WHERE (name = 'Thomas') AND (age > 18) */
log(
    squel.select()
        .field("id")
        .from("students")
        .where("name = 'Thomas' OR age > 18")
        .where("id BETWEEN 200 and 300")
        .toString(),
);
/*  SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) AND (id BETWEEN 200 and 300) */
log(
    squel.select()
        .field("id")
        .from("students")
        .where(
            squel.expr().and("name = 'Thomas'").or("age > 18"),
        )
        .toString(),
);
/*  SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) */
log(
    squel.select()
        .field("id")
        .from("students")
        .order("id")
        .order("name")
        .toString(),
);
/*  SELECT id FROM students ORDER BY id ASC, name ASC */
log(
    squel.select()
        .field("id")
        .from("students")
        .order("id")
        .order("name", false)  /* using TRUE instead would be the same as omitting it. */
        .toString(),
);
/*  SELECT id FROM students ORDER BY id ASC, name DESC */
log(
    squel.select()
        .field("id")
        .from("students")
        .order("DIST(?, ?)", true, 1, 2)
        .toString(),
);
/*  SELECT id FROM students ORDER BY DIST(1, 2) ASC */
log(
    squel.select()
        .field("id")
        .from("students")
        .group("id")
        .toString(),
);
/*  SELECT id FROM students GR0UP BY id */
log(
    squel.select()
        .field("id")
        .from("students")
        .group("id")
        .group("students.name")
        .toString(),
);
/*  SELECT id FROM students GR0UP BY id, students.name */
log(
    squel.select()
        .field("id")
        .from("students")
        .group("id")
        .having("a = ?", 2)
        .toString(),
);
/*  SELECT id FROM students GR0UP BY id HAVING (a = 2) */
log(
    squel.select()
        .from("students")
        .limit(10)
        .toString(),
);
/*  SELECT * FROM students LIMIT 10 */
log(
    squel.select()
        .from("students")
        .limit(10)
        .limit(0)
        .toString(),
);
/*  SELECT * FROM students */
log(
    squel.select()
        .from("students")
        .offset(102)
        .toString(),
);
/*  SELECT * FROM students OFFSET 102 */
log(
    squel.select()
        .from("students")
        .offset(1)
        .offset(0)
        .toString(),
);
/*  SELECT * FROM students */
log(
    squel.select()
        .from("students")
        .union(
            squel.select()
                .from('scores')
        )
        .union(
            squel.select()
                .from('batches'),
        )
        .toString(),
);
/*  SELECT * FROM students UNION (SELECT * FROM scores) UNION (SELECT * FROM batches) */
log(
    squel.select()
        .function('1')
        .toString(),
);
/*  SELECT 1 */
log(
    squel.select()
        .function('MAX(?, ?)', 45, 87)
        .toString(),
);
/*  SELECT MAX(45, 87) */
log(
    squel.update()
        .table("students")
        .set("name", "Thomas")
        .toString(),
);
/*  UPDATE students SET name = "Thomas" */
log(
    squel.update()
        .table("students")
        .table("teachers", "t")
        .set("t.name", "Fred")
        .toString(),
);
/*  UPDATE students, teachers `t` SET t.name = "Fred" */
log(
    squel.update()
        .table("students")
        .set("name", "Fred")
        .set("age", 29)
        .set("score", 1.2)
        .set("graduate", false)
        .set("level", squel.select().field('MAX(level)').from('levels'))
        .toString(),
);
/*  UPDATE students SET name = "Fred", age = 29, score = 1.2, graduate = FALSE, level = (SELECT MAX(level) FROM levels) */
log(
    squel.update()
        .table("students")
        .set("time", "GETDATE()", {
            dontQuote: true,
        })
        .set("city", "London")
        .toString(),
);
/*  UPDATE students SET time = GETDATE(), city = "London" */
log(
    squel.update()
        .table("students")
        .set("name", "Fred")
        .set("age", 29)
        .set("nickname", null)
        .toString(),
);
/*  UPDATE students SET name = "Fred", age = 29, nickname = NULL */
log(
    squel.update()
        .table("students")
        .set("age = age + 1")
        .toString(),
);
/*  UPDATE students SET age = age + 1 */
log(
    squel.update()
        .table("students")
        .setFields({ age: 23, name: 'Fred' })
        .toString(),
);
/*  UPDATE students SET age = 23, name = 'Fred' */
log(
    squel.update()
        .table("students")
        .set("name", "Fred")
        .set("age", 29)
        .where("id > 5")
        .where("id < 102")
        .order("id", false)
        .limit(5)
        .toString(),
);
/*  UPDATE students SET name = "Fred", age = 29 WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */
log(
    squel.delete()
        .from("students")
        .toString(),
);
/*  DELETE FROM students */
log(
    squel.delete()
        .from("students", "s")
        .outer_join("marks", "m", "m.student_id = s.id")
        .toString(),
);
/*  DELETE FROM students `s` OUTER JOIN marks `m` ON (m.student_id = s.id) */
log(
    squel.delete()
        .from("students")
        .where("id > 5")
        .where("id < 102")
        .order("id", false)
        .limit(5)
        .toString(),
);
/*  DELETE FROM students WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */
log(
    squel.insert()
        .into("students")
        .set("name", "Thomas")
        .toString(),
);
/*  INSERT INTO students (name) VALUES ("Thomas") */
log(
    squel.insert()
        .into("students")
        .set("name", "Thomas")
        .set("age", 29)
        .set("score", 90.2)
        .set("graduate", true)
        .set("nickname", null)
        .set("level", squel.select().field('MAX(level)').from('levels'))
        .toString(),
);
/*  INSERT INTO students (name, age, score, graduate, nickname, level)
    VALUES ('Thomas', 29, 90.2, TRUE, NULL, (SELECT MAX(level) FROM levels)) */
log(
    squel.insert()
        .into("students")
        .set("time", "GETDATE()", {
            dontQuote: true,
        })
        .set("city", "London")
        .toString(),
);
/*  INSERT INTO students (time, city) VALUES(GETDATE(), "London") */
log(
    squel.insert()
        .into("students")
        .setFields({ name: "Thomas", age: 29 })
        .toString(),
);
/*  INSERT INTO students (name, age) VALUES ('Thomas', 29) */
log(
    squel.insert()
        .into("students")
        .setFieldsRows([
          { name: "Thomas", age: 29 },
          { name: "Jane", age: 31 },
        ])
        .toString(),
);
/*  INSERT INTO students (name, age) VALUES ('Thomas', 29), ('Jane', 31) */
log(
    squel.insert()
        .into("students")
        .fromQuery(
            ['username'],
            squel.select().field('name').from('candidates'),
        )
        .toString(),
);
/*  INSERT INTO students (username) (SELECT name FROM candidates) */
log(
  squel.select()
    .from("students")
    .where("a = ? AND b = ?", "test", true)
    .order("CALC(?, ?)", true, 1.2, false)
    .limit(10)
    .offset(3)
    .toParam(),
);
 
/*
  {
    text:  SELECT * FROM students WHERE (a = ? AND b = ?) ORDER BY CALC(?, ?) ASC LIMIT ? OFFSET ?,
    values:  [ 'test', true, 1.2, false, 10, 3 ]
  }
*/
log(
  squel.select()
    .from("students")
    .where("a = ? AND b IN ?", "test", squel.select().field('score').from('results').where('c IN ?', [5, 6, 7]))
    .toParam(),
);
 
/*
  {
    text:  SELECT * FROM students WHERE (a = ? AND b IN (SELECT score FROM results WHERE c IN (?, ?, ?)))),
    values:  [ 'test', 5, 6, 7 ]
  }
*/
log(
  squel.select()
    .from("students")
    .where("a IN ?", squel.select().field('score').from('results').where('c = ?', 5))
    .union(
        squel.select().field('age').from('states').where('person = ?',
            squel.select().field('id').from('students').where('name = ?', 'John').limit(1),
        )
    )
    .toParam(),
);
 
/*
  {
    text:  SELECT * FROM students WHERE (a IN (SELECT score FROM results WHERE (c = ?))) UNION (SELECT age FROM states WHERE (person = (SELECT id FROM students WHERE (name = ?) LIMIT 1))),
    values:  [ 5, 'John' ]
  }
*/
log(
  squel.insert({ numberedParameters: true })
    .into("students")
    .set("a", "test")
    .set("b", 1)
    .set("c", null)
    .toParam(),
);
 
/*
  {
    text:  INSERT INTO students (a, b, c) VALUES ($1, $2, $3)
    values:  [ 'test', 1, null ]
  }
*/
log(
  squel.insert()
    .into("students")
    .set("a", "test")
    .set("b", 1)
    .set("c", null)
    .toParam({ numberedParameters: true, numberedParametersStartAt: 3 }),
);
 
/*
  {
    text:  INSERT INTO students (a, b, c) VALUES ($3, $4, $5)
    values:  [ 'test', 1, null ]
  }
*/
log(
    squel.insert()
        .into("students")
        .setFieldsRows([
          { name: "Thomas", age: 29 },
          { name: "Jane", age: 31 },
        ])
        .toParam(),
);
/*
  {
    text:  INSERT INTO students (name, age) VALUES (?, ?), (?, ?)
    values:  [ 'Thomas', 29, 'Jane', 31 ]
  }
*/
log(
    squel.select()
        .field("id")
        .from("students")
        .where("age IN ?", [18, 19, 20])
        .toParam(),
);
 
/*
  {
    text:  SELECT id FROM students WHERE (age IN (?, ?, ?)),
    values: [ 18, 19, 20 ]
  }
*/
log(
    squel.update()
      .table('students')
      .set('modified', 'NOW()')
      .toString(),
);
 
/*   UPDATE students SET modified = 'NOW()'   */
log(
    squel.update()
      .table('students')
      .set('modified', squel.str('NOW()'))
      .toString(),
);
 
/*  UPDATE students SET modified = (NOW())  */
log(
    squel.update()
      .table('students')
      .set('modified', squel.rstr('NOW()'))
      .toString(),
);
 
/*  UPDATE students SET modified = NOW()  */
log(
    squel.select()
        .from("students")
        .where("age IN ?", squel.str('RANGE(?, ?)', 1, 1.2))
        .toParam(),
);
 
/*
  {
    text:  SELECT * FROM students WHERE (age IN (RANGE(?, ?))),
    values: [ 1, 1.2 ]
  }
*/
log(
  squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true })
    .from("students", "s")
    .field("s.name", "Student name")
    .toString(),
);
 
/*   SELECT `s`.`name` AS "Student name" FROM `students` `s`  */
log(
  squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true })
    .from("students", "s")
    .field("s.name", "Student name", { ignorePeriodsForFieldNameQuotes: true })
    .toString(),
);
 
/*   SELECT `s.name` AS "Student name" FROM `students` `s`  */
log(
  squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true, nameQuoteCharacter: '|' })
    .from("students")
    .field("name", "Student name")
    .toString(),
);
 
/*   SELECT |name| AS "Student name" FROM |students|  */
log(
  squel.select({ tableAliasQuoteCharacter: '|', fieldAliasQuoteCharacter: '~' })
    .from("students", "s")
    .field("name", "Student name")
    .toString(),
);
 
/*   SELECT name AS ~Student name~ FROM students |s|  */
log(
  squel.select({ autoQuoteAliasNames: false })
    .from("students", "s")
    .field("name", "Student_name")
    .toString(),
);
 
/*   SELECT name AS Student_name FROM students s  */
log(
    squel.select()
        .from("students")
        .where("(id < 500 AND (id > 100 OR name <> 'Thomas') AND "
                + "(age BETWEEN 20 AND 25 OR (name <> RANDOMNAME(?)))) OR (nickname = 'Hardy')", 10)
        .toString(),
);
/*  SELECT * FROM students WHERE
        ((id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR (name <> RANDOMNAME(10))))
            OR (nickname = 'Hardy')) */
log(
    squel.expr()
        .and("id < ?", 500)
        .and(
          squel.expr()
            .or("id > ?", 100)
            .or("name <> ?", 'Thomas')
        )
        .and(
          squel.expr()
            .or("age BETWEEN ? AND ?", 20, 25)
            .or("name <> ?", squel.str('RANDOMNAME(?)', 10)),
        )
        .or("nickname = ?", 'Hardy')
        .toString(),
);
/*  id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR name <> (RANDOMNAME(10)))
        OR nickname = 'Hardy'
*/
log(
    squel.select()
        .field("s.id")
        .from("students", "s")
        .where(
            squel.expr()
                .and("s.name <> 'Fred'")
                .and(
                  squel.expr()
                    .or("s.id = 5")
                    .or("s.id = 6")
                )
        )
        .join("teachers", "t",
            squel.expr()
                .and("s.id = t.sid")
                .and("t.name = 'Frances'"),
        )
        .toString(),
);
/*
    SELECT s.id FROM students `s`
        INNER JOIN teachers `t` ON (s.id = t.sid AND t.name = 'Frances')
            WHERE (s.name <> 'Fred' AND (s.id = 5 OR s.id = 6))
*/
log(
    squel.update({
        stringFormatter: function(str) {
            return "u'" + str + "'";
        }
    })
        .table("students")
        .set("name", "Jack")
        .toString(),
);
/*  UPDATE students SET name = u'Jack' */
let myDate = new Date(2012, 4, 22);
let myDate2 = new Date(2013, 5, 30);
log(
    squel.update()
        .table("students")
        .set("start_date", myDate.getFullYear() + '-' + (myDate.getMonth() + 1) + '-' + myDate.getDate())
        .set("end_date", myDate2.getFullYear() + '-' + (myDate2.getMonth() + 1) + '-' + myDate2.getDate())
        .toString(),
);
/*  UPDATE students SET start_date = '2012-5-22', end_date = '2013-6-30' */
myDate = new Date(2012, 4, 22);
myDate2 = new Date(2013, 5, 30);
 
/* Tell Squel how to handle Date objects */
squel.registerValueHandler(Date, function(date) {
  return '"' + date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate() + '"';
});
 
log(
    squel.update()
        .table("students")
        .set("start_date", myDate)
        .set("end_date", myDate2)
        .toString(),
);
/*  UPDATE students SET start_date = ("2012-5-22"), end_date = ("2013-6-30") */
/* OOP Inheritance mechanism (substitute your own favourite library for this!) */
// ERRORING
// Function.prototype.inheritsFrom = function( parentClassOrObject ) {
//   this.prototype = new parentClassOrObject;
//   this.prototype.constructor = this;
//   this.prototype.parent = parentClassOrObject.prototype;
// };
 
/* Base type */
const ClassA = function() { this.a = 1; };
 
/* Sub-type */
// ERRORING
// let ClassB = function() { this.a = 2; };
// ClassB.inheritsFrom(ClassA);
 
/* Register base type */
squel.registerValueHandler(ClassA, function(obj) {
  return obj.a;
});
 
// ERRORING
// log(
//     squel.update()
//         .table("students")
//         .set("value", new ClassB())
//         .toString(),
// );
/*  UPDATE students SET value = 2 */
squel.registerValueHandler('boolean', function(v) {
  return v ? 'YES' : 'NO';
});
 
log(
    squel.update()
        .table("students")
        .set("value", true)
        .toString(),
);
/*  UPDATE students SET value = (YES) */
squel.registerValueHandler('boolean', function(v) {
  return {
    value: v ? 'YES' : 'NO',
    rawNesting: true,
  }
});
 
log(
    squel.update()
        .table("students")
        .set("value", true)
        .toString(),
);
/*  UPDATE students SET value = YES */
/* Global handler */
squel.registerValueHandler(Date, function(date) {
  return date.getFullYear();
});
 
log(
  squel.update()
    .registerValueHandler(Date, function(date) {
      return '"[' + date.getFullYear() + ']"';
    })
    .table('students')
    .set('value', new Date(2013,5,1))
    .toString(),
);
/*  UPDATE students SET value = ("[2013]") */
myDate = new Date(2012, 4, 22);
myDate2 = new Date(2013, 5, 30);
 
/* Tell Squel how to handle Date objects */
squel.registerValueHandler(Date, function(date) {
  return date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate();
});
 
log(
    squel.select()
        .from("students")
        .where("start_date >= ?", myDate)
        .where("end_date <= ?", myDate2)
        .toParam(),
);
 
/*
{
  text:  SELECT * FROM students WHERE (start_date >= ?) AND (end_date <= ?),
  values: [ '2012-5-22', '2013-6-30' ]
}
*/
/* We create a convenience method to make it easy to instantiate our customized UPDATE builder */
// ERRORING
// squel.myupdate = function(options) {
//   return squel.update(options, [
//     new squel.cls.StringBlock(options, 'UPDATE'),
//     new squel.cls.UpdateTableBlock(options),
//     new squel.cls.SetFieldBlock(options),
//     new squel.cls.WhereBlock(options),
//     new squel.cls.OrderByBlock(options),
//     new squel.cls.OffsetBlock(options),
//     new squel.cls.LimitBlock(options),
//   ]);
// };
//  
// log(
//   squel.myupdate()
//     .table('students')
//     .set('status', 'active')
//     .limit(10)
//     .offset(2)
//     .toString(),
// );
 
/* UPDATE students SET status = 'active' OFFSET 2 LIMIT 10 */
/*
NOTE: All methods prefixed with '_' are internal and not exposed via the
query builder.
 */
 
// ERRORING
// class CreateTableBlock extends squel.cls.Block {
//   /** The method exposed by the query builder */
//   table (name) {
//       this._name = name;
//   }
//  
//   /** The method which generates the output */
//   _toParamString (options) {
//     return {
//         text:   this._name,
//         values: [],  /* values for paramterized queries */
//     };
//   }
// }
//  
// class CreateFieldBlock extends squel.cls.Block {
//   constructor (options) {
//     super(options);
//     this._fields = [];
//   }
//  
//   /** The method exposed by the query builder */
//   field (name, type) {
//     this._fields.push({
//       name: name, type,
//     });
//   }
//  
//   /** The method which generates the output */
//   _toParamString (options) {
//     const str = this._fields.map((f) => {
//       return `${f.name} ${f.type.toUpperCase()}`;
//     }).join(', ');
//  
//     return {
//       text: `(${str})`,
//       values: [],   /* values for paramterized queries */
//     };
//   }
// }
//  
// class CreateTableQuery extends squel.cls.QueryBuilder {
//   constructor (options, blocks) {
//     super(options, blocks || [
//       new squel.cls.StringBlock(options, 'CREATE TABLE'),
//       new CreateTableBlock(options),
//       new CreateFieldBlock(options),
//     ]);
//   }
// }
//  
// /** Convenience method */
// squel.create = function(options) {
//   return new CreateTableQuery(options);
// };
//  
// /* Try it out! */
//  
// log(
//   squel.create()
//     .table("pet")
//     .field("name", "varchar(20)")
//     .field("owner", "varchar(20)")
//     .field("species", "varchar(20)")
//     .field("sex", "char(1)")
//     .toString(),
// );
 
/*
  CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1))
*/
const select1 = squel.select().from('students');
 
const select2 = select1.clone().field('id').where('name = "david"');
 
log( select1.toString() );
 
/* SELECT * FROM students */
 
log( select2.toString() );
 
/* SELECT id FROM students WHERE (name = "david") */
const ins = squel.insert().into('students').set('name', 'David');
 
const insMathew = ins.clone().set('name', 'Mathew');
 
const insMark = insMathew.clone().set('name', 'Mark');
 
log( insMathew.toString() );
 
/* INSERT INTO students (name) VALUES('Mathew') */
 
log( insMark.toString() );
 
/* INSERT INTO students (name) VALUES('Mark') */
const squelPostgres = squel.useFlavour('postgres');
log(
  squelPostgres.insert()
    .into('table')
    .set('field', 5)
    .returning('*')
    .toParam(),
);
 
/*
    {
        text: 'INSERT INTO table (field) VALUES ($1) RETURNING *'
        values: [5]
    }
*/
 
log(
  squelPostgres.insert()
    .into('table')
    .set('field', 5)
    .with('someAlias', squelPostgres.insert().into('otherTable').set('otherField', 3))
    .toParam(),
);
 
/*
    {
        text: 'WITH someAlias AS (INSERT INTO otherTable (otherField) VALUES ($1)) INSERT INTO table (field) VALUES ($2)'
        values: [3, 5]
    }
*/
log(
  squelPostgres.select()
    .distinct('field1', 'field2')
    .from('table')
    .order('field1')
    .order('field2')
    .order('field3')
    .toString(),
);
 
/*   SELECT DISTINCT ON (field1, field2) * FROM table ORDER BY field1 ASC, field2 ASC, field3 ASC */
const squelMysql = squel.useFlavour('mysql');
 
log(
  squelMysql.insert()
    .into('table')
    .setFields({
        field1: 'abc',
        field2: 3,
    })
    .onDupUpdate('field1', 'upd')
    .toParam(),
);
 
/*
    {
        text: 'INSERT INTO table (field1, field2) VALUES (?, ?) ON DUPLICATE KEY UPDATE field1 = ?'
        values: ['abc', 3, 'upd']
    }
*/
const squelMssql = squel.useFlavour('mssql');
 
log(
  squelMssql.insert()
    .into('table')
    .setFields({
        field1: new Date('2012-12-12T04:30:00Z'),
    })
    .toString(),
);
 
/*
    INSERT INTO table (field1) VALUES(('2012-12-12 4:30:0'))
*/
log(
  squelMssql.select()
    .from('table')
    .field('field')
    .top(10)
    .toString(),
);
 
/*
    SELECT TOP (10) field FROM table
*/
 
log(
  squelMysql.select()
    .from('table')
    .field('field')
    .limit(10)
    .offset(5)
    .toString(),
);
 
/*
    SELECT field FROM table OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY
*/
 
log(
  squelMssql.update()
    .table('table')
    .output('id', 'blah')
    .set('field', 1)
    .toString(),
);
 
/*
    UPDATE table SET field = 1 OUTPUT INSERTED.id AS blah
*/
 
log(
  squelMssql.insert()
    .into('table')
    .output('id')
    .set('field', 1)
    .toString(),
);
 
/*
    INSERT INTO table (field) OUTPUT INSERTED.id VALUES (1)
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment