Node.js

Table creation methods

sqlite

.sqlite()

Tells Tablespoon you want to create an in-memory sqlite database.

var ts = require('tablespoon').sqlite()

pgsql

.pgsql(dbConnectionString)

Connects Tablespoon to your PostgreSQL database, this is the default flavor of Tablespoon.

If no arguments are passed to .pgsql(), the connection defaults to pg://postgres:5432@localhost/. Read more about database connections.

var ts = require('tablespoon').pgsql('pg://mike:5432@localhost/tablespoon')

createTable .createTable(dataobject, tablename, [tableschema], [permanent])

Synchronously creates a table in your PostgreSQL database with the specified tablename and optionally a tableschema. By default, Tablespoon will attempt to read the datatypes in your object. By default, Tablespoon will create a temporary table that exists only for this session. To instead create a permanent table, pass in true as boolean to permanent.

ts.createTable(data, 'cities') // Creates a table named `cities`.
ts.createTable(data, 'cities', 'name TEXT, temp NUMERIC[], country TEXT') // Creates a table named `cities` with this schema.
ts.createTable(data, 'cities', 'name TEXT, temp NUMERIC[], country TEXT', true) // Creates a permanent table named `cities` with this schema.

createTableCommands

.createTableCommands(dataobject, [tablename], [tableschema], [permanent], [skipinsert])

Returns a json object with two keys create and insert that contain the sql commands to make the table from your data. This method does not actually create your table. Useful if you find datatypes or create and insert statements tedious. Used internally by .createTable.

To only print the CREATE commands, pass the boolean false to skipinsert.

var commands = createTableCommands(data)

console.log(commands)
/*
{ create: 'CREATE TEMP TABLE cities (uid INTEGER PRIMARY KEY,city TEXT,temp NUMERIC,country TEXT)',
  insert: 'INSERT INTO cities (city,temp,country) VALUES (\'New York\',27.2,\'USA\'),(\'Los A\'\'ngeles\',72,\'USA\'),(\'Paris\',34,\'F"rance\'),(\'Marseille\',43,\'F\'\'rance\'),(\'Lon"don\',33,\'UK\')' }*/

createEmptyTable

.createEmptyTable(dataobject, [tablename], [tableschema], [permanent])

Creates an empty table with either the given schema or the inferred schema from the data. Useful in conjunction with insert to make a table and then insert it with various data later.

insert

.insert(dataobject, [tablename])

Inserts a data object into an existing table.

Query methods

query

.query(queryString, function)

Queries the database and returns a json object with the query text and the resulting rows.

ts.query('SELECT * FROM cities LIMIT 2', function(result){
	console.log(result)
	/*
	{ query: 'SELECT * FROM cities LIMIT 2',
	  rows:
	   [ { uid: '1', city: 'New York', temp: [Object], country: 'USA' },
	     { uid: '2', city: 'Los Angeles', temp: [Object], country: 'USA' } 
     ] 
  }
  */
})

query.each

.query.each(queryString, function)

Same as .query except it returns the resulting rows one by one.

ts.query.each('SELECT * FROM cities LIMIT 2', function(row){
	console.log(row)
	/*
	{ uid: '1', city: 'New York', temp: [Object], country: 'USA' },
	{ uid: '2', city: 'Los Angeles', temp: [Object], country: 'USA' } 
  */
})

queries

.queries(list, function)

Takes a list of query strings, processes them synchronously and returns them in an array of objects. Each object has the same structure as the result object from .query

var queries = [
	'SELECT * FROM cities LIMIT 1',
	'SELECT * FROM cities LIMIT 1 OFFSET 1',
]
ts.queries(queries, function(result){
	console.log(result)
/*	[
  {
    "query": "SELECT * FROM cities LIMIT 1",
    "rows": [
      {
        "uid": "1",
        "city": "New York",
        "temp": [
          0,
          35
        ],
        "country": "USA"
      }
    ]
  },
  {
    "query": "SELECT * FROM cities LIMIT 1 OFFSET 1",
    "rows": [
      {
        "uid": "2",
        "city": "Los Angeles",
        "temp": [
          15,
          35
        ],
        "country": "USA"
      }
    ]
  }
]
*/

queries.each

.queries.each(list, function)

The same as .queries except the callback is invoked after each query is finished as opposed to waiting for them all to finish.