Getting started with jasql

1. Install jasql:

jasql is available on npm, making installation as easy as:

npm install jasql

jasql is tested and verified on the latest releases of Node v4, v5, and v6. If you need to support another version of node, create an issue or even better a pull request!

One of the supported database drivers should also be installed!

• sqlite3 for Sqlite3 support
• pg for postres support
• mysql for MySQL support

The above dependencies are marked as optionalDependencies in the package.json. This means npm will try to install them all, and continue happily even if they fail. One is required, feel free to uninstall the dependencies ones you don’t use.

2. Import and initialize jasql

The jasql module exports a single class Jasql, we will need to instantiate and also initialize this class.

Instatiating the Jasql class with no parameters will result in a sqlite3 database stored in ./jasql.sqlite.

During the call to initialize, jasql will create it’s table in the database if it does not already exist.

var Jasql = require('jasql')
var jasql = new Jasql()

jasql.initialize()
  .then(() => console.log('jasql is ready to work!'))

3. Add some documents

Create some documents:

• 2 users, Cody and Brian
• 2 posts made by Cody

Below we will leverage the fact that jasql allows any string for the _id. However, if no id is provided, jasql will generate a random id, meaning the _id field is optional in the documents. Since we are prefixing our ids with type of the document, we will benifit by being able to query only the types of documents we want. Leveraging the id is an imporant concept that eliminates the need for multpile collection or tables. See Ids and Indexes for more discussion on how to use jasql’s powerful indexing.

  // first create 2 users
  .then(() => jasql.create({
    _id: 'users/Cody',
    name: 'Cody',
    title: 'Software Engineer'
  }))

  .then(() => jasql.create({
    _id: 'users/Brian',
    name: 'Brian',
    title: 'Quality Engineer'
  }))

  // next create 2 posts
  .then(() => jasql.create({
    _id: 'posts/Cody/' + new Date().toJSON(),
    title: 'How to use jasql',
    body: 'See documentation at http://github.chom/codyzu/jasql'
  }))

  .then(() => jasql.create({
    _id: 'posts/Cody/' + new Date().toJSON(),
    title: 'Why we may have to use relational databases',
    body: 'Often enterprises will want to leverage their existing infastructure, i.e. relational dbs.'
  }))

4. Read a stored document by id

Querying a single document by id is easy.

  .then(() => jasql.read('users/Cody'))

  .then((doc) => console.log('User Cody:', doc))

5. List documents, using wildcards

Magic time! Query our documents… by type!.

We can query documents by type (leveraging the fact we prefixed their id’s by type). jasql supports sql wildcards, so its easy to build interesting queries on the id. As a bonus, since we stored the date in the id of the posts, they will be retrieved in chronological order because jasql sorts the documents by id!

See the list function documentation for more details.

  .then(() => jasql.list({id: 'users/%'}))

  .then((docs) => console.log('All users:', docs))

  .then(() => jasql.list({_id: 'posts/Cody/%'}))

  // posts will be in chronological order
  // because they have the date in the id!
  .then((docs) => console.log('All posts by Cody:', docs))

6. Disconnect if needed

Don’t forget to cleanup if you want to application to exit cleanly.

This isn’t required for long running server applications, but may be useful if your tests seem to “hang” do to open connections to the database.

  .then(() => jasql.destroy())

  .then(() => console.log('jasql is disconnected'))

The jasql module exports a single class, Jasql.

This class exposes an intuitive API that makes CRUDL’ing (Create, Read, Update, Delete and List) documents a cinch. jasql is designed to easily integrate into applications serving REST API’s, but jasql’s easy API makes it suitable for all types of use cases.

Once instantiated, all of the methods of the Jasql class return a Promise.

constructor (opts)

The jasql constructor accepts has a single optional paratmeter, opts.

Database Options

The opts.db defines the storage driver used by jasql (internally it is passed to the initializer of knex, see the knex documentation for more details).

Below are some example using the various supported database technologies:

Sqlite3

Sqlite3 is a great way to use jasql out-of-the-box. No external databases or services required because Sqlite3 is a file based database. In fact, if you don’t specify any db options to the jasql constructor, it will default Sqlite3. Fast, easy, done.

Use jasql defaults, a Sqlite3 database, located at ./jasql.sqlite (the db will be created if it does not already exist):

var jasql = new Jasql()

Sqlite3 with a custom database file:

var jasql = new Jasql({
  db: {
    client: 'sqlite3',
    connection: {
      filename: 'path/to/db.sqlite'
    }
  }
})

Postgres

Postgres (or PostgreSQL) is a great free and open source database with an emphasis on standards compliance.

jasql is tested on PostgresSQL 9.4.

Configure jasql with Postgres:

var jasql = new Jasql({
  db: {
    client: 'pg',
    connection: {
      host: 'localhost',
      user: 'postgres',
      password: 'rootpass',
      database: 'dbname'
    }
  }
})

Mysql

MySQL is popular open source database option.

jasql is tested on MySQL 5.7.

Configure jasql with MySQL:

const jasql = new Jasql({
  db: {
    client: 'mysql',
    connection: {
      host: 'localhost',
      user: 'root',
      password: 'rootpass',
      database: 'dbname'
    }
  }
})

initialize ()

Initialize a Jasql instance, connecting to the database and creating the table if it does not exist.

Initialize should be called before executing any other methods on an instance of Jasql.

jasql.initialize()
  .then(() => console.log('jasql is ready to rock!'))

create (doc)

Creat a new document in the database.

The doc object can optionally include an _id field to identify the document. The id must be unique, if a document with the same id already exists, an error will be raised. If the doc does not include an _id, a random identifier will be automatically generated.

Create a document with a random id:

jasql.create({ name: 'Cody', title: 'Software Engineer' })

Create a document with a given id:

jasql.create({ _id: 'users/Cody', name: 'Cody', title: 'Software Engineer' })

See Ids and Indexes for more details about ids.

read (id)

Read an existing document from the database.

The id must refer to an existing document. An error will be raised if a document with the given id does not exist.

Read a document with id users/Cody:

jasql.read('users/Cody')

update (doc)

Update an existing document.

The doc should have an _id of a document existing in the database. The existing document will be replaced with the new doc.

Update a field in a document with id users/Cody:

jasql.read('users/Cody')
  .then((doc) => {
    doc.title = 'Senior Software Engineer'
    return jasql.update(doc)
  })

list (opts)

Lists some or all documents in the database.

If called with no parameters, all documents will be returned. If called with an id (optionally with wildcards), all documents matching the id will be returned.

For a list of valid wildcards, see sql wildcards.

List all documents:

jasql.list()

List all documents with ids starting with users/:

jasql.list({id:'users/%'})

del (idOrDoc)

Delete a document from the database.

The document to delete can either be specified by its id, or by passing an object that has an _id field that matches an existing document.

Delete a document by id:

jasql.del('users/Cody')

Read and then delete a document:

jasql.read('users/Cody')
  .then((doc) => jasql.del(doc))

destroy ()

Closes any open connections to the database

Calling destroy is not required for long running server applications. However, it can be useful in your tests if they seem to “hang” do to open connections to the database.

  jasql.destroy()