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());