tabler

Tabler: Generic Table Persistence

Introduction

Tabler lets Node.js developers interact with a variety of databases, both relational and NoSQL, using a simple SQL-inspired table interface. Using Tabler your application can define tables, specify column data types, and perform insert/select/update/delete operations in a generic manner. Store-specific details are abstracted away so that when you need to switch data stores, your application is ready.

Project goals:

• Provide a familiar table interface for non-relational data stores
• Provide data integrity checks for data stores that don't have this built in
• Make it easy to switch data stores

Although there are other projects that abstract away store-specific details for relational/SQL databases, these interfaces are generally too complex to implement in NoSQL datastores. The Tabler interface has been purposely kept very simple so that it can be implemented on top of many types of data stores.

Tabler is Unicode/UTF-8 safe, and so friendly to internationalized apps.

A Quick Example

See the short illustrative example here: https://github.com/aarong/tabler/blob/master/example.js

Available Backends

Amazon SimpleDB

• Transparently serializes all data types (offsets and zero-pads numbers, Iso8601 for dates, etc)
• Transparently partitions large columns into multiple SimpleDB attributes
• Transparently iterates on nextTokens for complex queries
• Transparently breaks large write operations into batches to bypass SimpleDB limits
• Limitations: Cannot sort on string columns longer than 1024 bytes because they are partitioned.

JSON File

• Convenient for local development

More to come

• MySQL
• SQLite
• CouchDb?
• MongoDb?
• Redis?
• Cassandra?

Getting Started

Install the module using NPM:

npm install tabler

Create a Tabler instance:

var Tabler = require('tabler'),
    tabler = new Tabler(
        'simpledb',
        {
            accessKeyId:        "123",
            secretAccessKey:    "456",
            domainNamespace:    "tabler" // All domains used by Tabler are prefixed with this string
        }
    );

or

var Tabler = require('tabler'),
    tabler = new Tabler(
        'jsonfile',
        {
            filename: 'myjsonfile.json'
        }
    );

All the Tabler code that you write after this is completely generic -- you will never have to change it even if you decide to move to a different backend.

Defining Your Tables

tabler.defineTable(
    tableName,
    fieldDefs,
    callback[error]
)

fieldDefs = {
    fieldName: fieldDef,
    fieldName: fieldDef,
    ...
}

where (for example)

fieldDef = {
    // String query conditions are case sensitive -- have the app create a lower-case
    // copy of the column to achieve case insensitivity.
    // For strings, passing the null byte \u0000 is the same as passing a null object.
    // About nulls (applies to all data types): For sorting, null is low. Null is not allowed by default.
    type:       'string',
    nullable:   true,
    byteLength: 50,     // Not the same as character length when using multi-byte UTF-8 characters
    pattern:    /.*/    // Regex pattern that must be matched (this one allows everything)
};

For convenience, you can use the following built-in patterns

tabler.PATTERNS.ALL - Allow any string tabler.PATTERNS.PRINTABLE_NO_BREAKS // Printable characters only, line breaks are not allowed tabler.PATTERNS.PRINTABLE_AND_BREAKS // Printable characters online, line breaks ARE allowed

... or (for example)

fieldDef = {
    // Numbers can hold any number that Javascript expresses without exponential notation (up to 1e20 roughly)
    type:       'number', 
    nullable:   true,
    min:        -100,
    max:        100,
    decPlaces:  2
};

or (for example)

fieldDef = {
    type:       'datetime',
    nullable:   false
};

or (for example)

fieldDef = {
    type:       'object',
    nullable:   true,
    byteLength: 1000, // Maximum serialized byte length (JSON is used)
    schema:     {} // Optional (http://tools.ietf.org/html/draft-zyp-json-schema-02)[JSON Schema object]
};

Important note: You need to use defineTable() every time you create your Tabler instance. Once you have written to a table, DO NOT change its definition in any way, as Tabler assumes that your current definition is valid for every row currently in the database and you will have all kinds of problems if it's not. Instead, create a new table and transfer the data (this will be improved in the future).

To remove a table and its data you can do:

tabler.deletetable( // Delete the table and all its data tableName, callback[error] )

Reading and Writing Rows

See below for definitions of method arguments and output variables. Note that Tabler assigns a unqiue id to every row (a UUID string).

The API is designed so that you can apply each operation (insert, select, update, select) to either one row specified by ids, multiple rows specified by an array of ids, or multiple rows specified by a query condtition.

Due to the limitations some of of the underlying data stores, these operations cannot be guaranteed to be atomic (i.e. if you are performing many writes, a failure could result in some writes taking place and others not).

Inserting

tabler.insertOne(
    tableName,
    rowWithoutId or rowWithId,
    callback[error, rowId]
)

tabler.insertMulti(
    tableName,
    rowsWithoutIdArray or rowsWithIdArray,
    callback[error, rowIdArray] // Output row IDs same order as input
)

Selecting and counting

tabler.selectOne(
    tableName,
    fieldNames, // Empty for no fields, null for all fields
    rowId,
    callback[error, rowWithoutId] // null if row not found
)

tabler.selectMulti(
    tableName,
    fieldNames,     // Empty array for no fields (checking existence), null for all fields
    rowIdArray,
    callback[error, rowsWithIdHash] // Rows not found are not included in the hash
)

tabler.selectQuery(
    tableName,
    fieldNames,     // Empty array for no fields (getting row IDs only), null for all fields
    condition,
    selectOptions,  // Optional
    callback[error, rowsWithIdArray]
)

tabler.countQuery(
    tableName,
    condition,
    callback[error, numRows]
)

Updating

tabler.updateOne( // Does not create a row if the id doesn't exist, but returns success
    tableName,
    rowWithoutId // Omitted fields are not altered
    rowId,
    callback[error]
)

tabler.updateMulti( // Does not create a row if the id doesn't exist, but returns success
    tableName,
    rowWithoutId, // The same value is applied to all rows, omitted fields are not altered
    rowIdArray,
    callback[error]
)

tabler.updateQuery(
    tableName,
    rowWithoutId, // The same value is applied to all rows, omitted fields are not altered
    condition,
    callback[error]
)

Deleting

tabler.deleteOne(
    tableName,
    rowId,
    callback[error]
)

tabler.deleteMulti(
    tableName,
    rowIdArray,
    callback[error]
)

tabler.deleteQuery(
    tableName,
    condition,
    callback[error]
)

Arguments and output details

fieldNames = [fieldName, fieldName, ...]

rowWithoutId = {
    fieldName: fieldValue,
    fieldName: fieldValue,
    ...
}

fieldValue = (for example) 'abc'        for string fields
                        or 123.4        for number fields
                        or new Date()   for datetime fields
                        or {a:1, b:2}   for object fields
                        or null         for any field type, if allowed

rowsWithoutIdArray = [rowWithoutId, rowWithoutId, ...]

rowWithId = {
    __id:       GUID,
    fieldName:  fieldValue,
    ...
}

rowsWithIdHash = {
    id1: rowWithId,
    id2: rowWithId,
    ...
}

rowsWithIdArray = [rowWithId, rowWithId, ...]

condition = [ // All conditions joined using AND
    [fieldName, comparisonOperator, fieldValue],
    [fieldName, comparisonOperator, fieldValue],
    ...
]

comparisonOperator = "=", "!=", "<", ">", "<=", or ">="

selectOptions = {
    orderBy:    'fieldName',    // Optional
    orderDir:   'desc',         // Optional, asc (default) or desc
    limit:      10              // Optional, default 0 (all matching rows)
}

error = {
    code: 'ERROR_CODE', // Always present
    (error-specific members)
}

More Interface Notes

Tables and field names are case sensitive, but you cannot define multiple tables or fields with the same lowercase name. This ensures that the interface can be implemented on data stores that have either case-sensitive or case-insensitive table names.

The string "__" is not allowed in field names, as it is reserved for Tabler's use. In particular, __id is used throughout the API to access the row Id.

Running the tests

cd tests
node stringtabler.js jsonfile
node stringtabler.js simpledb accesskeyid secretaccesskey
node tabler.js jsonfile
node tabler.js simpledb accesskeyid secretaccesskey

Looking Forward

This is just a braindump of my ideas for the project, in no particular order (some of these ideas need to be developed a bit).

• More backends
• Cannot generally guarantee that operations are ACID, so have a utility that can be used to check consistency of the data with with your table definitions, and list/update/delete any rows that violate your definitions.
• Store table definitions in a meta table, then you just define your tables once like in SQL databases
• Update the interface: createDatabase, useDatabase, dropDatabase, createTable, dropTable.
• Import/export utility (save/load your databases to/from JSON files or sometihng). Need to think about how to make exports reflect a single point in time as best possible (timestamp writes somehow?).
• Improve tests (test the entire table against an array each write so that all unintented writes are found).
• Allow the user to specify unique and foreign key constraints. Provide adequately informative error objects so that apps don't run a bunch of double-checks in order to generate various error codes/messages.
• Transactions (would be normal transactions on backends that support them, and the best you can do to replicate that functionality for other backends).
• Indexes (would only apply to some stores, but always best to specify them for future flexibility).
• Field definition option for case-insensitive string matching (right now you need to manually create a lower-case column)