@sugarcoated/fondant-sequence

Face it, raw SQL strings are ugly. The longer one is, the uglier. Sequence attempts to solve this by implementing a chainable prototype (something that made jQuery famous) so that you can write SQL, in JavaScript, in the way it was meant to be written. This ranges from SELECT and INSERT statements, or even all JOIN types.

As it stands, Sequence has been written in an adaptable way. SQL varies throughout iterations (of which I've used MSSQL, MySQL, WebSQL & SQLite), therefore please report an issue if you encounter syntax differences that can be addressed.

• View the source at src/Modules/Sequence
• View the test file at test/Globals/testSequence
• View the NPM package at @sugarcoated/fondant-sequence

API Reference

new Sequence([identifier])

Creates a new Sequence instance.

• identifier is an optional parameter, expected as a String, that identifies the beginning of an SQL statement. When no identifier is given, it is defaulted to SELECT.

  new Sequence();
  new Sequence('CREATE')
  

Upon construction, you will have access to the following properties:

• modifier is a SequencePart representing the modifier of the SQL statement. This can be SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER`, etc.
• locator is a SequencePart representing the locator in the SQL statement. This is typically FROM or INTO.
• condition is a SequencePart representing the condition in the SQL statement; typically a WHERE.
• joins is a Collection containing a SequencePart for every joined table.
• group is a SequencePart representing the grouping in the SQL statement.
• order is a SequencePart representing the ordering in the SQL statement.
• use is a SequencePart representing an expression generated with another Sequence instance for use with creating tables and views.
• final is a SequencePart representing the final of the SQL statement. Currently the only possibility of a final is a LIMIT in MySQL.

.all()

Implements the * character from all SQL versions.

  new Sequence().all()
  new Sequence().distinct().all()

.distinct()

Implements the DISTINCT clause from all SQL versions.

  new Sequence().distinct()

.only(columns)

Implements the concept of specifying exact columns in a SELECT query.

• columns is expected as a Array.<Array.<String, String, String>> representing the type of selection to perform on a specific column for a group.

  const mySequence = new Sequence() .only([

  // SQL FUNCTION, COLUMN, ALIAS
  ['COUNT', 'myColumn', 'MC'],
  

  ])

.top(amount)

Implements the TOP clause from Microsoft SQL.

• amount is expected as a Number, representing an implication of the amount of records select in the query.

  new Sequence().top(10)

.view(name)

Implements the CREATE VIEW clause from all SQL versions.

• name is expected as a String, representing the name of a nonexistent view.

  new Sequence('CREATE').view('myView')

.update(table)

Implements the UPDATE clause from all SQL versions.

• table is expected as a String, representing the name of an existing table.

  new Sequence('UPDATE').update('myTable')

.from(table, [alias])

Implements the FROM clause from all SQL versions.

• table is expected as a String, representing the name of an existing table.

• alias is an optional parameter, expected as a String, representing the alias for that table. When no alias is given, the locator expression has no AS clause.

  new Sequence().all().from('myTable', 'MT')

.into(table, columns)

Implements the INTO clause from all SQL versions.

• table is expected as a String, representing the name of an existing table.

• columns is expected as an Array.<String>, representing the name of columns on that table.

  new Sequence('INSERT').into('myTable', ['myColumn'])

.where(column)

Implements the WHERE clause from all SQL versions.

• column is expected as a String, representing a column within the table.

  new Sequence().all().from('myTable').where('myColumn') new Sequence().all().from('myTable').where('myColumn').where('myOtherColumn')

.join(type, table, [alias])

Implements the JOIN clause from all SQL versions.

• type is expected as a String, representing the type of join. This can be INNER, OUTER, LEFT, RIGHT, or anything supported by your SQL version.

• table is expected as a String, representing the name of an existing table.

• alias is an optional parameter, expected as a String, representing the alias for that table. If no alias is provided, the JOIN clause has no AS appended.

  new Sequence().all().from('myTable').join('INNER', 'myOtherTable') new Sequence().all().from('myTable').join('INNER', 'myOtherTable', 'MOT')

.on(table, left_column, right_column)

Implements the ON clause from all SQL versions.

• table is expected as a String, representing the name of the table joined to.

• left_column is expected as a String, representing the column on the left.

• right_column is expected as a `String, representing the column on the right.

  new Sequence() .all().from('myTable') .join('INNER', 'myOtherTable') .on('myOtherTable', 'myOtherColumn', 'myExtraColumn')

.with(sequence)

Embeds an instance of Sequence within another instance. Used for creating tables and views.

• sequence is expected as a Sequence, representing the query to produce your view.

  new Sequence('CREATE').view('myVue').use(new Sequence().all().from('myTable'))

.groupBy(column, [ascending])

Implements the GROUP BY clause from all SQL versions.

• column is expected as a String, representing a column to group on.

• ascending is an optional parameter, expected as a Boolean, representing whether or not you want the results to be grouped from top to bottom or bottom to top. When no ascending is provided, the value is defaulted to false.

  new Sequence().all().from('myTable').groupBy('myColumn') new Sequence().all().from('myTable').groupBy('myColumn', true)

.orderBy(column, [ascending])

Implements the ORDER BY clause from all SQL versions.

• column is expected as a String, representing the column to order by.

• ascending is an optional parameter, expected as a Boolean, representing whether or not you want the results to be ordered from top to bottom or bottom to top. When no ascending is provided, the value is defaulted to false.

  new Sequence().all().from('myTable').orderBy('myColumn') new Sequence().all().from('myTable').orderBy('myColumn', true)

.limit([amount])

Implements the LIMIT clause from MySQL.

• amount is an optional parameter, expected as a Number, representing the amount of records to limit the query to. When no amount is provided, the value defaults to 1.

  new Sequence().all().from('myTable').limit() new Sequence().all().from('myTable').limit(10)