NextDB.net Database Basics
First Steps
This documentation shows you how to use the NextDB JavaScript API
to add relational database capabilities to your website.
NextDB offers a full database programming environment using JavaScript
in the browser over the public internet. NextDB has solved security issues
that make it possible to do database programming in JavaScript.
The first step is to use the NextDB web based administrative pages to create your account
and define your database, tables, relationships and
queries. See:
NextDB Admin pages.
For a detailed walk through of the admin pages, please watch the introduction video
to using the NextDB admin pages
here.
The following examples cover all of the basic CRUD (create, read, update, delete)
operations using the NextDB system. We would suggest reading over these examples to
get familiar with the architecture and design of the system. You will not be able
to work with the API until you have created your account (database, tables, relationships
and queries) using the admin interface. The admin interface also offers some canned
databases that serve as a good starting point.
If you do not have an account please sign up for a free trial beta account on
nextdb.net.
Once you have an account up and running, you can add the lightweight (50k) hosted
JavaScript API to your webpage and start coding from the HTML page to the database
directly:
<script src="http://www.nextdb.net/api.js"></script>
The JavaScript API is lightweight, tightly namespaced, extremely flexible and it
plays well with all of the popular JavaScript libraries, such as
prototype,
jquery and
dojo to name a few.
Queries
Queries are used for retrieving data from your database.
Queries to your databases are made by referencing the queries that you developed
on the
NextDB admin pages.
For obvious security reasons, the query language syntax is not exposed at the
JavaScript level. Executing SQL from the browser would pose obvious security problems.
Rather, you reference a query by its name. The query request is sent
asynchronously to the server, and your callback function handles the response--
standard AJAX programming.
Behind the scenes we are using a communication mechanism that side
steps the 'server of origin' policy
of the XMLHTTPRequest. So you can access NextDB from a page loaded
from a server different than www.nextdb.net. This enables mashups in the true
sense of the word.
In this example the query
named "QUERY1" is being executed and an anonymous inline callback
function is handling the returned data. The data is returned in the
rows
variable to the callback function, it is an array of JavaScript Objects representing
the rows returned from the database. A typical return structure might be:
rows[0].TABLE_NAME.column_name.
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var query = new net.nextdb.Query("QUERY1");
conn.executeQuery(query,
function(rows,error){
if(error){
// handle error, for example:
net.nextdb.Util.print(error.toString());
} else {
// handle success, for example:
for(var i=0;i<rows.length;i++)
net.nextdb.Util.print(rows[i].USERS.first_name);
}
}
);
Queries can be designed to take runtime arguments (i.e. parameters). These 'bind
variables' are added to the query using the setParameters method in the Query class. The
setParameters method takes an object and parses out the name value pairs as the
parameters and their values. These parameters only represent values, there is
no query logic exposed on the client, it is all defined and compiled on the server
through the query builder interface on the admin pages, which ensures security.
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var query = new net.nextdb.Query("QUERY1");
query.setParameters( { email : "info@nextdb.net", date : "now" } );
conn.executeQuery(query,
function(rows,error){
if(error){
// handle error, for example:
net.nextdb.Util.print(error.toString());
} else {
// handle success, for example:
for(var i=0;i<rows.length;i++)
net.nextdb.Util.print(rows[i].USERS.first_name);
}
}
);
Inserts
A simple insert just takes the table name in the constructor, then an
object of name/value pairs as the data to insert into that table. The fields
of the object must correspond to the columns of the database. The values for
the fields are the values that will make up the new row. Your callback function is passed
an encrypted key (PK) referencing the row that you have inserted. A very simple insert
looks like this:
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var insert = new net.nextdb.Insert("TABLE1");
insert.setParameters( { email : "info@nextdb.net" , date : "now" } );
conn.executeInsert(insert,
function(rowId,error){
if(error){
// handle error, for example:
net.nextdb.Util.print(error.toString());
} else {
// handle success, for example:
net.nextdb.Util.print(rowId);
}
}
);
The insert above is simple but there is no security. NextDB offers two ways of securing
inserts against hackers. The first is by allowing tables to be configured with
captcha protection, which means that in order to perform an insert, the user needs to
supply the text on a human readable image. Captcha protection is a very common
method for disabling hackers and bots from flooding a user registeration page. In order to
add captcha protection you first captcha protect the table using the admin pages,
then add a captcha image to you page with the following utility:
net.nextdb.Util.sourceCaptchaImg(referenceToImgInDOM);
which will source a captcha image to the page.
Inserts take a plain old JavaScript object where the name is the column and the
value is what you inserting into the row for that column. The executeInsert
callback is passed the encrypted rowId for the row inserted, and any errors.
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var insert = new net.nextdb.Insert("TABLE1");
insert.setParameters( { email : "info@nextdb.net" ,
date : "now",
captcha : USER_SUPPLIED_VALUE} );
conn.executeInsert(insert,
function(rowId,error){
if(error){
// handle error, for example:
net.nextdb.Util.print(error.toString());
} else {
// handle success, for example:
net.nextdb.Util.print(rowId);
}
}
);
The second way of securing inserts is with relationships to other data (e.g. key constraints).
When data is inserted into a table it can be bound to data in other tables by
providing a key to the other row and naming the relationship that exists between the
tables. Relationships are defined using the admin pages. For example,
a relationship is between a USER and the CONTACTS associated with that USER. This
relationship has a 'one to many' cardinality, as a single (one) user can have multiple
(many) contacts. For public sites, it is a good idea to have one central table
(such as a USER table) which is captcha protected, then have all of the other tables
secured by reference to the captcha protected table using relationships. Inserts
without a valid key to the parent table will fail, and the key to the parent table
will only be granted by logging into the system.
This is a common design principle in databases to 'constrain' the data using
primary keys and foreign keys to protect the integrity of the database. Here is
an example of a query, then an insert and relate all in one shot:
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
// get a certain user to whom we will add a new contact
var query = new net.nextdb.Query("USER");
query.setParameters( { email : "info@nextdb.net" } );
conn.executeQuery(query,
function(rows,error){
if(error){
// handle error
} else {
// in the callback from the query we execute the insert
var insert = new net.nextdb.Insert("CONTACTS");
// set insert values
insert.setParameters( { cell_number : "888 888 8888" ,
home_number : "888 888 8888" } );
// get a specific encrypted key
var key=rows[0].USER.PK;
// set relationship between the key of the query results and the insert
insert.setRelationship(key, "USER_CONTACTS");
conn.executeInsert(insert,
function(rowId,error){
if(error){
// handle error
} else {
// success
}
}
);
}
}
);
Updates
Just as inserts pose security issues, so do updates. NextDB has a policy for
securing updates (and deletes). In order to execute an update, you need to have
first retrieved an temporary encrypted key for the row you want to update. In other words
you must first query for the row, and then you supply the encrypted row id back
with the update. The second aspect of this security policy is that the query
that is used to initially get the row id must have "FOR UPDATE" enabled. In
other words, some queries are read only, while others can allow either "FOR UPDATE"
and/or "FOR DELETE". Batch updates are not allowed, and row level security
is enabled at the query level with the "FOR UPDATE" flag.
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var query = new net.nextdb.Query("QUERY2");
var args={};
args["email"]="info@nextdb.net";
query.setParameters(args);
conn.executeQuery(query,
function(rows,error){
if(error){
// handle error
} else {
//get a specific encrypted key
var key=rows[0].TABLE1.PK;
var update = new net.nextdb.Update("TABLE1");
update.setParameters( { first_name : "edgar" , last_name : "codd" } );
update.setRowId(key);
conn.executeUpdate(update,
function(error){
if(error){
// handle error
} else {
// success
}
}
);
}
}
);
Deletes
The NextDB security policy for deleting data is the same as updates.
In order to execute a delete, you need to have
first retrieved a temporary encrypted key for the row you want to delete.
In other words
you must first query for the row, and then you supply the encrypted row id back
with the delete. The second aspect of this security policy is that the query
that is used to initially get the row id must have "FOR DELETE" enabled. In
other words, some queries are read only, while others can allow either "FOR UPDATE"
and/or "FOR DELETE". Batch deletes are not allowed, and row level security
is enabled at the query level with the "FOR DELETE" flag.
A delete looks like this:
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var query = new net.nextdb.Query("QUERY3");
conn.executeQuery(query,
function(rows,error){
if(error){
// handle error
} else {
//get a specific encrypted key
var key=rows[0].TABLE1.PK;
var del = new net.nextdb.Delete("TABLE1");
del.setRowId(key);
conn.executeDelete(del,
function(error){
if(error){
// handle error
} else {
// success
}
}
);
}
}
);
Working with Dates & Calendars
NextDB offers a calendar object for making date programming easy.
The calendar object has one function that
returns an HTMLInput object that you can insert
into the DOM. When the text field comes into
focus a Calendar interface appears allowing the
user to easily select a date. The date is converted
into the appropriate format for the server to you
can pass the value of the field to the server
wholesale. The calendar is completely customizable in terms of
interactivity and look & feel.
function start(){
var date = Date.today().next().friday().add(3).hour();
var colors={"border":"rgb(200, 200, 200)",
"font":"rgb(8, 8, 8)",
"background":"rgb(255, 255, 255)",
"selectedBackground":"rgb(0, 88, 0)",
"selectedFont":"rgb(255, 255, 255)",
"mouseoverBackground":"rgb(200, 200, 200)",
"mouseoverFont":"rgb(0, 0, 0)"};
// construct Calendar with optional date instance and color config
// the defaults are a new Date(), and the color scheme above
var calendar = new net.nextdb.html.Calendar(date, colors);
var input=calendar.getElement();
var button=document.createElement("input");
button.type="button";
button.value="add date";
button.onclick=function(){
if(input.value==""){
alert("click on text field");
return;
}
insert(input.value);
}
document.getElementById("somewhereinpage").appendChild(input);
document.getElementById("somewhereinpage").appendChild(button);
}
function insert(d){
var conn = new net.nextdb.Connection("YourAccountName","DatabaseWithinYourAccount");
var insert = new net.nextdb.Insert("DATE_TABLE"); // table to take insert
insert.setParameters( { date : d } );
conn.executeInsert(insert,
function(rowId,error){
if(error){
// handle error
} else {
// success
}
}
);
}
Working with Binary Data (images)
NextDB offers a very easy approach to working with binary data (images/files).
You can use our file uploader to insert binary data to your database,
and when you query for the binary data, you are given a URL, which you can
use to source the image. Very simple.
The file uploader is constructed with a net.nextdb.Connection object,
the name of the table for the insert and two callback functions.
The first callback is fired when the upload starts allowing you
to activate some progress indicator. The second callback is
passed a URL when the file has been loaded. You still need to
execute your insert inside the second callback function, you
use the URL for the insert into the binary column. There is a third
optional callback, which if provided will be passed an object about
once every 100 milliseconds to indicate the progress of the upload.
With this progress information you can give the user a real sense
of the upload time.
var uploader = new net.nextdb.html.FileUpload(conn,"USER_PIC",
function(){
// start loading callback - initialize progress indicator
},
function(url){
// end loading callback - remove progress indicator
// create insert for the uploaded image
var insert = new net.nextdb.Insert("PIC");
insert.map["pic"]=url;
conn.executeInsert(insert, function(key,error){
if(error){
// handle error
}else{
// handle success
}
}
);
},
function(progress){
// if supplied this function is passed
// a progress indicator every 100 milliseconds
var uploadedBytes = progress.rx;
var totalBytes = progress.totalRX;
var perct = (Math.round((uploadedBytes/totalBytes)*100))+"%";
}
);
// now append to the page
document.getElementById("somewhereinpage").appendChild(uploader.getElement());