0.4.3 • Published 7 years ago

some-sql v0.4.3

Weekly downloads
-
License
MIT
Repository
github
Last release
7 years ago

Project Discontinued

Please switch to Nano-SQL as soon as it's easy. The API is essentially the same, it's faster and includes a turbo button.

The ReadME below is kept for anyone still using this lib as it is....

SomeSQL

RDBMS Immutable App Store with Undo, Redo & IndexedDB support.

npm TSlint

I was tired of doing all the work for my data store library (Redux) or letting my data store eat through half my production bundle (Lovefield and others). SomeSQL is small, fast and built specifically to work with modern frameworks like Angular2 and React while keeping RDBMS nicities around. As a bonus, you also get the performance perks of ImmutableJS in a lib half it's size.

Features

  • Run in Node, IE8+ & modern browsers.
  • Supports all common RDBMS actions.
  • All queries return immutable rows.
  • Super easy IndexedDB support.
  • Simple & elegant undo/redo.
  • Full Typescript support.
  • Runtime type casting.
  • Full events system.
  • Under 10Kb Gzipped.

Installation

npm i some-sql --save

Using in typescript project:

import { SomeSQL } from "some-sql";

Using in node:

var SomeSQL = require("some-sql").SomeSQL;

To use directly in the browser, just include the script file found inside the dist folder onto your page.

Simple Usage

1 minute minimal quick start:

SomeSQL('users') //  "users" is our table name.
.model([ // Declare data model
    {key:'id',type:'int',props:['pk','ai']}, // pk == primary key, ai == auto incriment
    {key:'name',type:'string'}
])
.connect() // Init the data store for usage.
.then(function(result, db) {
    // "db" holds the current SomeSQL var with the previous table still selected.
    return db.query('upsert',{ // Add a record
        name:"Billy",
    }).exec();
})
.then(function(result, db) {
    return db.query('select').exec(); // select all rows from the current active table
})
.then(function(result, db) {
    console.log(result) // <= [{id:1,name:"Billy",age:50}]
})

Detailed Usage

First you declare your models, connect the db, then you execute queries.

1. Declare Model & Setup

SomeSQL('users')// Table/Store Name, required to declare model and attach it to this store.
.model([ // Data Model, required
    {key:'id',type:'uuid',props:['pk']}, // This has the primary key value
    {key:'name',type:'string', default:"None"}, // This will cause inserts to always use "None" if no value is provided.
    {key:'age',type:'int'},
    {key:'balance',type:'float', default: 0},
    {key:'postIDs',type:'array'},
    {key:'meta',type:'map'}
])
.rowFilter(function(row) { // Optional, lets you control the row data going into the database.
    if(row.age > 99) row.age = 99;
    if(row.age < 12) row.age = 12;
    if(row.balance < 0) callOverDraftFunction();
    return row;
})
.actions([ // Optional
    {
        name:'add_new_user',
        args:['user:map'],
        call:function(args, db) {
            return db.query('upsert',args.user).exec();
        }
    }
])
.views([ // Optional
    {
        name: 'get_user_by_name',
        args: ['name:string'],
        call: function(args, db) {
            return db.query('select').where(['name','=',args.name]).exec();
        }
    },
    {
        name: 'list_all_users',
        args: ['page:int'],
        call: function(args, db) {
            return db.query('select',['id','name']).exec();
        }
    }                       
])

2. Connect the DB and execute queries

// Initializes the db.
SomeSQL().connect().then(function(result, db) {
    // DB ready to use.
    db.doAction('add_new_user',{user:{
        id:null,
        name:'jim',
        age:30,
        balance:25.02,
        postIDs:[0,20,5],
        meta:{
            favorteColor:'blue'
        }
    }}).then(function(result, db) {
        console.log(result) //  <- "1 Row(s) upserted"
        return db.getView('list_all_users');
    }).then(function(result, db) {
        console.log(result) //  <- single object array containing the row we inserted.
    });
});

Arbitrary Commands

You can execute a db command at any point from the SomeSQL object after the DB is connected.

Every query follows the same pattern: SomeSQL(#TABLE_NAME#).query(#ACTION#,#ARGS#)....optional filtering, sorting, etc...exec()

For example a query to get all rows from the users table looks like this: SomeSQL('users').query('select').exec()

Here are some more examples:

// Get all records but only return the name and id columns
SomeSQL('users').query('select',['name','id']).exec(); 

// only show rows where the name == "scott"
SomeSQL('users').query('select').where(['name','=','scott']).exec() 

// Compound where statement with AND
SomeSQL('users').query('select').where([['name','=','billy'],'and',['balance','>',20]]).exec();

// Compund where statement with OR
SomeSQL('users').query('select').where([['name','=','billy'],'or',['balance','>',20]]).exec();

// Order the results by name ascending, then age descending.
SomeSQL('users').query('select').orderBy({name:'asc',age:'desc'}).exec() 

// Limit and Offset
SomeSQL('users').query('select').limit(20).offset(10).exec();

// Filters (Must be supported by the database driver or supplied by the user)
SomeSQL('users').query('select',['age']).filter('average').exec();

// The Memory DB supports sum, min, max, average, and count

// combine any patterns as you'd like.
SomeSQL('users').query('select',['name']).where(['age','>',20]).orderBy({age:'desc'}).exec() 

// Where statements work on upserts as well.
SomeSQL('users').query('upsert',{name:"Account Closed"}).where(['balance','<',0]).exec() 

// Simple join
SomeSQL("users").query("select",["users.name","orders.title"]).where(["users.name","=","Jimmy"]).join({
    type:'inner',
    table:"orders",
    where:['orders.customerID',"=",'user.id']
}).orderBy({"users.name":"asc"}).exec();

Possible query commands are select, drop, upsert, and delete.

All calls to the exec() return a promise, with the first argument of the promise being the response from the database.

The second argument is always the SomeSQL var, making chaining commands easy...

SomeSQL('users').query('select').exec().then(function(result, db) {
    return db.query('upsert',{name:"Bill"}).where(['name','=','billy']).exec();
}).then(function(result, db) {
    return db.query('drop').exec();
})

Events

You can listen to any number of database events on any table or all tables.

SomeSQL("users").on('select',function(eventData) {}) // Listen to "select" commands from the users table
SomeSQL("*").on('change',function(eventData) {}) // Listen for any changes to any table in the database.

Possible events are change, delete, upsert, drop, select and error.

Multiple Tables

You can create a new table by selecting it and creating a new data model:

SomeSQL('newTable').model([
    {key:'name',type:'string'}
])

Keep in mind you MUST declare all your models and tables BEFORE calling the connect() command.

Multiple Data Stores

If you need more than one data store with a collection of separate tables, you can declare a completely new SomeSQL db at any point.

var myDB = new SomeSQL_Instance().table;

// And now use it just like you use the SomeSQL var.
myDB('users').query("select").exec()...

Keep in mind that the tables and models are completely separate for each instance; there is no shared data, events or anything else.

API Index

Possible commands are split into three groups, one group is used before you connect to the database.
The other group is used after you connect to the database, and it's used to query the database data.

All commands can be chained or return a promise unless otherwise noted.

Group 1: Setup Mode

CommandDefinition
.model()Declare database model, required.Examples
.views()Declare views to use.Examples
.actions()Declare actions to use.Examples
.config()Pass custom configuration options to the database driver.Examples
.addFilter()Add a filter that can be used on queries.Examples
.rowFilter()Add a filter function to be applied to every row being inserted.Examples
.connect()Complete setup mode and optionally connect to a specific backend, required.Examples

Group 2: Query Mode

Every database query looks like this: SomeSQL(#Table Name#).query(#Query Type#, #Query Args#)...Optional Query Modifiers...exec()

This gives each query three distinct sections, the query init section, the query modifier section, and the execute section.

Query Init

There is only one possible function to start a query, and it has several different possible arguments. Check out the examples to see those.

CommandDefinition
.query()Starts a database query.Examples

Query Modifiers

Each modifier can take up to two arguments and normally can only be used once. Check each example for usage.

CommandDefinition
.where()Adds a search component to the current query.Examples
.orderBy()Adds a order by component to the current query.Examples
.join()Combine multiple queries into one using a where statement.Examples
.offset()Offset the current query by a given value.Examples
.limit()Limits the current query by a given value.Examples
.filter()Applies a custom filter to the current query.Examples
.extend()Use a extend query modifier provided by the database driver.Examples

Query Execution

These come at the end of a query to execute it on the database.

CommandDefinition
.exec()Executes a pending query and returns a promise containing an array of objects.Examples
.toCSV()Executes the pending query and returns a promise containg a string CSV.Examples

Misc Commands

Events

Events can be called before or after setup mode, at any time.

CommandDefinition
.on()Listen to specific database events with a callback function.Examples
.off()Remove a listening function from being triggered by events.Examples

Mass Import Data

CommandDefinition
.loadJS()Loads json directly into the database.Examples
.loadCSV()Loads CSV files directly into the database.Examples

Actions & Views

These can be used in replacement of the query..exec pattern to execute a given view or action.

CommandDefinition
.getView()Gets a specific view, returns a promise.Examples
.doAction()Does a specific action, returns a promise.Examples

View Complete Official Docs

Memory Database API

Documentation here is specific to the built in memory database driver.

Memoization

SomeSQL will memoize all select queries automatically with no configuration needed.

Identical select statements will only require a single actual select on that table. Every subsequent select query after the first will draw from the cache, pointing to the same object allowing strict comparison checking ("===").

Once you modify a table, all memoized selects for that table are cleared, however the rows themselves are still immutable so once the query itself is recreated unchanged rows will still pass "===" checks.

Finally, using JOIN commands will cause multiple table caches to be invalidated on single table updates so expect degraded performance with joins.

IndexedDB

The memory database can optionally persist all of it's changes to the browser's indexed DB. Some features:

  • All changes automatically update to indexedDB.
  • The memory database is populated from the Indexed DB on connection, allowing you to resume the previous state with no effort.
  • Undo and redo states are not saved.
  • Each Indexed DB is attached to a specific data model. If you change your data model a new, blank IndexedDB will be used.

Enableing it is easy, just call config before connect with {persistent:true} passed in, like this:

SomeSQL().config({persistent:true}).connect().then....

You can declare models, views, actions and filters before calling config, but it must be called BEFORE connect().

Finally, you can clear the current indexedDB completely by calling this:

SomeSQL().extend("flush_db");

This will cause ALL active databases and tables to be removed from Indexed DB, but they will remain in memory until the page is refreshed.

Undo & Redo

The driver has built in "undo" and "redo" functionality that lets you progress changes to the database forward and backward in time.

There is no need to enable anything, the history is enabled automatically and requires no configuration.

Each Undo/Redo action represents a single Upsert, Delete, or Drop query.

The history is applied across all database tables, calling a specific table on an undo/redo command will have no affect.

Queries that did not affect any rows do not get added to the history.

Usage:

  • Undo: SomeSQL().extend("<")
  • Redo: SomeSQL().extend(">")

These commands will cascade change events to the affected tables.

Optionally, you can attach then to the end of either undo or redo to discover if a redo/undo action was performed.

SomeSQL().extend(">").then(function(response) {
    console.log(response) //<= If this is true, a redo action was done.  If false, nothing was done.
});

You can also request the state of the undo/redo system like this:

SomeSQL().extend("?").then(function(response) {
    console.log(response) // <= [0,0]
});

The query returns an immutable array with two numbers. The first is the length of change history, the second is the current pointer of the change history.

This lets you determine if an undo/redo action will do anything:

  1. If the history length is zero, undo and redo will both do nothing.
  2. If the pointer is zero, undo will do nothing.
  3. If the pointer is equal to history length redo will do nothing.

Since the history state is immutable you can perform strict "===" checks against it to see if the history state has changed. This is useful for deciding to adjust the rendering of undo/redo buttons.

Finally, performing changes to the database anywhere in history is completely allowed and automatically handled. You can play with the Todo example to see how this works.

View Complete Official Docs

0.4.3

7 years ago

0.4.2

7 years ago

0.4.1

7 years ago

0.4.0

7 years ago

0.3.4

7 years ago

0.3.3

7 years ago

0.3.2

7 years ago

0.3.1

7 years ago

0.3.0

7 years ago

0.2.9

7 years ago

0.2.8

7 years ago

0.2.7

7 years ago

0.2.6

7 years ago

0.2.5

7 years ago

0.2.4

7 years ago

0.2.3

7 years ago

0.2.2

7 years ago

0.2.1

7 years ago

0.2.0

7 years ago

0.1.9

7 years ago

0.1.8

7 years ago

0.1.7

7 years ago

0.1.6

7 years ago

0.1.5

7 years ago

0.1.4

7 years ago

0.1.3

7 years ago

0.1.2

7 years ago

0.1.1

7 years ago

0.1.0

7 years ago

0.0.2

7 years ago