JavaScript & MySQL With Jaxer

Wouldn’t it be cool if you could work with MySQL within your JavaScript code? Think about it, you wouldn’t have to spend extra time writing extra server-side code for connecting to, querying, and parsing results, you could just write a little bit more code in your JavaScript and be done with it. Of course, we wouldn’t want any of this SQL exposed to the end-user, as that would be a major security issue, but what if that problem was solved as well? You might also raise the point that you’d still need the ability to prepare your SQL statements that take dynamic input to prevent SQL injection attacks, but if that weren’t an issue, wouldn’t that be awesome as well?

Seriously, take a look over an existing AJAX app you may have written. You’ve probably got a bunch of different functions for making AJAX calls to PHP scripts (or something similar), which process the input, and deal with the database, then return either parsed results, or results to be parsed by JavaScript. Wouldn’t it be nice if you could consolidate all that stuff into some smaller, simpler JavaScript code? Fewer files, less overhead… sounds good to me.

That’s one of the great things about Jaxer. If you’ve done any reading, you probably know that Jaxer works with SQLite out of the box, and you might be aware that it’s also able to work with MySQL, but most of the people I’ve talked to have stopped there. Aside from the above objections (which I’ll smash shortly ), I’ve also heard that they simply don’t want to learn another complex API. Well, the time for silly excuses is over, and I’m going to show you how easy all this stuff can be. I would suggest that you first set up Jaxer with XAMPP so you can follow along, but if you want to use the stand-alone Jaxer and Apache, you’ll be just fine. Let’s get our hands dirty…

Setting up Jaxer to Use MySQL

This is actually pretty simple, and already documented in the Jaxer documentation, but I’ll go over it here as well. First, some background on how Jaxer works internally with databases. Whether you’re using SQLite or MySQL, Jaxer leverages a database for some of its own internal functionality (session management and other goodness). As such, it needs a database for its own stuff, which it will create if it doesn’t exist. We’re also going to need a default application database for Jaxer to connect to when it spins up, but it won’t be writing anything to it.

So, let’s tell Jaxer to use MySQL. If you’re set up according to my Jaxer+XAMPP tutorial, we’re going to edit:

/Applications/xampp/xamppfiles/local_jaxer/conf/config.js

If you’re working with the standalone, browse to wherever you’ve installed it and edit:

Path-to-Aptana_Jaxer/local_jaxer/conf/config.js

What we’re looking for is around line 44 (it should be commented out):

Config.DB_IMPLEMENATION = "MySQL";

Now, some more assumptions:

• You’ve got a database created for your app, and one for jaxer itself. For my “sandbox” testing, these are the same… I use “jaxer_db”
• You know what port MySQL is using (unless you’ve changed something, it’s 3306). If you’re using XAMPP, you’re good to go

OK, so let’s uncomment all the MySQL-specific stuff and change our default stuff. Here’s what I’ve got:

Config.DB_IMPLEMENTATION = "MySQL";
Config.DB_CONNECTION_PARAMS =
    {
        HOST: "127.0.0.1",
        PORT: 3306,
        NAME: "jaxer_db",
        USER: "root",
        PASS: "",
        CLOSE_AFTER_EXECUTE: false,
        CLOSE_AFTER_REQUEST: true
    };
    Config.DB_FRAMEWORK_IMPLEMENTATION = "MySQL";
    Config.DB_FRAMEWORK_CONNECTION_PARAMS =
    {
        HOST: "127.0.0.1",
        PORT: 3306,
        NAME: "jaxer_db",
        USER: "root",
        PASS: "",
        CLOSE_AFTER_EXECUTE: false,
        CLOSE_AFTER_REQUEST: true
    }; 

If you’re going to use (and you should for production stuff) a different Jaxer internal database, change the Config.DB_FRAMEWORK_CONNECTION_PARAMS to reflect this appropriately. Now that we’ve changed everything, restart Jaxer, then restart Apache. If everything’s good, you should be able to browse to the Jaxer Unit Tests (located in the Server Diagnostics… usually located at http://localhost/aptana or the start page from the Jaxer control panel for stand-alone users), and run the “DB – test_DB_MySQL.html” test (just click on that link). If all is well, you’ll see a bunch of passes:

Jaxer MySQL Unit Tests

Getting a Few Things Set Up

Awesome, now that we’re up and running, let’s set up some test data to play around with. Let’s create a test table and populate it with some data… just copy and paste this code in your MySQL client of choice:

create table if not exists `users` (
user_id int(10) unsigned NOT NULL auto_increment,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
username varchar(50) NOT NULL,
date_created datetime NOT NULL default '0000-00-00 00:00:00',
date_updated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
primary key (`user_id`));
INSERT INTO users SET first_name='Ian', last_name='Selby', username='iselby', date_created=NOW();
INSERT INTO users SET first_name='John', last_name='Doe', username='jdoe', date_created=NOW();

Of course, you could certainly add more, but we’re not writing an app, so this should suffice just fine.

One last little bit of groundwork, let’s set up the page that’s going to contain some of our test code. Basically, it’s just going to be a sandbox page of sorts, so there’s nothing too tough here:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Sample Jaxer DB Stuff

	<script type="text/javascript" runat="server-proxy">
	// we'll add our sample code here
	</script>
</head>
<body>
	<div id="results"></div>
</body>
</html>

Great! Now we’re ready to start working with the Jaxer & MySQL API.

Getting Our Feet Wet… Connecting to MySQL and Basic API Functions

First thing we need to do (obviously), is to set up a connection to the database. While we have set up a default database in our Jaxer configuration, we can connect to any database that we want to. So, let’s set up that connection now. Place the following between our script tags:

var dbConnection = new Jaxer.DB.MySQL.Connection({
	'HOST': 'localhost',
	'PORT': 3306,
	'USER': 'root',
	'PASS': '',
	'NAME': 'jaxer_db',	// the name of the db to use
	'CLOSE_AFTER_EXECUTE': false
});

So far, pretty simple, no? Obviously, you can change any of these parameters to suit your needs. Remember, the end user will not see any of this as we’re running it at the server level (which we defined with the runat= in our script tags). Now that we’ve got a connection, we can start running queries. Before we build anything practical, let’s take a look at our basic API stuff.

Queries
As stated in the introduction to this article, there is, in fact, a way to prepare statements to prevent SQL injection. Let’s take a look at a basic example, then a prepared query:

// assuming we are using the above connection...
var query_string = 'SELECT * FROM user';
var result = dbConnection.execute(query_string);
// and now a prepared statement
var query_string = 'SELECT * FROM user WHERE first_name LIKE ? OR last_name LIKE ?';
var result = dbConnection.execute(query_string, ['%' + someInput + '%', '%' + someOtherInput + '%']);

It’s really that easy! Of course, if you aren’t interested in a result, you can omit the “var result =” (such as update or insert statements). Again, as long as your queries are in a runat=”server” or runat=”server-proxy” script block (or .js include), the end user will never see your code. So, everything’s nice and secure.
Result Sets
If you’re a PHP developer, you are probably familiar with the mysql_fetch_object() or mysql_fetch_array() functions for traversing result sets (shame on you for not using mysqli tho ). If you’re not, don’t worry, I’m not going to be referring to them here. Anyway, regarding those functions, you’re in luck! The fine folks who programmed Jaxer were kind enough to provide results similar to those of PHP (and other languages I would imagine). I’ll go over a sample result set in a moment, but here’s a quick look at what the “result” variable from the examples above would contain:

• columns – An array of column names for all rows in this resultSet.
• hasData – I’ll give you three guesses (true / false folks!)
• rows – The array of rows in the resultSet in the order retrieved from the database.
• rowsAsArrays – An alternate representation of the rows of the resultSet
• singleResult – The value in the first column of the first row of the resultSet (think SELECT COUNT(*) FROM…)

As you can see, it’s about as robust as you could possibly hope for. So, based on our sample data, and the sample SELECT * FROM user query above, what would a resultSet object look like in real life? It looks like this:

result =
{
	"rows":
	[
		{"user_id":1,"first_name":"Ian","last_name":"Selby","username":"iselby","date_created":"2008-02-23T17:46:24","date_updated":"2008-02-23T17:46:24","$values":[1,"Ian","Selby","iselby","2008-02-23T17:46:24","2008-02-23T17:46:24"]},
		{"user_id":2,"first_name":"John","last_name":"Doe","username":"jdoe","date_created":"2008-02-23T17:46:35","date_updated":"2008-02-23T17:46:35","$values":[2,"John","Doe","jdoe","2008-02-23T17:46:35","2008-02-23T17:46:35"]}
	],
	"rowsAsArrays":
	[
		[1,"Ian","Selby","iselby","2008-02-23T17:46:24","2008-02-23T17:46:24"],
		[2,"John","Doe","jdoe","2008-02-23T17:46:35","2008-02-23T17:46:35"]
	],
	"columns":
	[
		"user_id","first_name","last_name","username","date_created","date_updated"
	],
	"singleResult":1,
	"hasData":true
}

We’ve also got a bunch of functions we can execute on a result set. Rather than go over them in detail, you can check them out online or in your local instance of Jaxer’s API documentation:

• Online at Aptana
• Locally (If you installed according to my tutorial

OK, go take a look over that goodness (I suggest locally if you can, as it will be more up-to-date), and then let’s take a look at how we could do this in a more real-world environment.

Doing Something Practical

Alright, to do something practical, we’re going to need to wrap our stuff in some functions, so we can call them when we need them. So, go ahead and remove everything in our <script> block, and create the following function:

function fetchUsers(search_for)
{
	var dbConnection = new Jaxer.DB.MySQL.Connection({
		'HOST': '127.0.0.1',
		'PORT': 3306,
		'USER': 'root',
		'PASS': '',
		'NAME': 'jaxer_db', // the name of the db to use
		'CLOSE_AFTER_EXECUTE': false
	});

	// if we've been provided with search params
	if (search_for)
	{
		search_for = '%' + search_for + '%';
		var query_string = 'SELECT * FROM user WHERE first_name LIKE ? OR last_name LIKE ? ORDER BY last_name';
		var params = [search_for, search_for];

		var result = dbConnection.execute(query_string, params);
	}
	// default result set
	else
	{
		var query_string = 'SELECT * FROM user ORDER BY last_name';
		var result = dbConnection.execute(query_string);
	}

	return result.toHTML();
}

Pretty straight-forward, right? OK, one last thing, and we’ll actually have something that works. In order for this to execute on load, we can do one of two things: onload or onserverload. Basically, these are the same thing, except the onserverload is evaluated when the server-side DOM is created (essentially before it ends up on the client-side of things… where all our runat=”server” stuff is executed). So, let’s alter our tag:

<body onserverload="document.getElementById('results').innerHTML = fetchUsers();">

Save your page, reload it, and if everything works, you’ll have a really ugly HTML table of results! Pretty damn cool, right? If you’re sharp, you can see that we’re working our way towards a simple search, so let’s dive right into that. Rather than go over it step-by-step, I’ll just show you the final HTML (because this really isn’t rocket science ):

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Sample Jaxer DB Stuff

	<style type="text/css">
		body, td, p { font-family: Lucida Grande, Arial; font-size: 11px; }
		td { border: 1px solid #ccc; padding: 3px; }
	</style>

	<script type="text/javascript" runat="server-proxy">

	function fetchUsers(search_for)
	{
		var dbConnection = new Jaxer.DB.MySQL.Connection({
			'HOST': '127.0.0.1',
			'PORT': 3306,
			'USER': 'root',
			'PASS': '',
			'NAME': 'jaxer_db', // the name of the db to use
			'CLOSE_AFTER_EXECUTE': false
		});

		// if we've been provided with search params
		if (search_for)
		{
			search_for = '%' + search_for + '%';
			var query_string = 'SELECT * FROM user WHERE first_name LIKE ? OR last_name LIKE ? ORDER BY last_name';
			var params = [search_for, search_for];

			var result = dbConnection.execute(query_string, params);
		}
		// default result set
		else
		{
			var query_string = 'SELECT * FROM user ORDER BY last_name';
			var result = dbConnection.execute(query_string);
		}

		return result.toHTML();
	}

	</script>
	<script type="text/javascript" runat="client">
		function doSearch()
		{
			var search_for = document.getElementById('searchFor').value;
			document.getElementById('results').innerHTML = fetchUsers(search_for);
		}
	</script>
</head>
<body onserverload="document.getElementById('results').innerHTML = fetchUsers();">
	<label for="searchFor">Search For: </label><input name="searchFor" id="searchFor" />
	 <button onclick="doSearch();">Search</button>
	<div id="results"></div>
</body>
</html>

Most of this stuff should be pretty self-explanatory, but I’ll go over a few things of note. The first thing you should notice is that I’ve created a new client-side script tag. This is so I can create a function that will have access to the results div, as well as our server-side function. Remember, after the server-side DOM and functions are evaluated, that DOM goes away. So, the server-side functions will no longer have access to the DOM when we’re doing our search, regardless of the fact we proxy that function. All the proxy does is allow the functions to be executed from the client-side without exposing any of the server-side code (so our SQL statements won’t be seen or anything like that).

DB-Driven Search Using JavaScript… WOW! (Jaxer rules folks)

Wrapping Up

As you can see, it’s really darn easy to work with MySQL in Jaxer. It’s also perfectly safe, and there are no real causes for concern from a security standpoint. As Jaxer is still in its beta stage, there’s a lot of feature development currently taking place, but we (at Aptana) would love your feedback and suggestions. You can certainly provide feedback with comments here and I’ll pass them along, but the best place to do so is in the Aptana Forums. I’m currently cooking up a prototype database abstraction class that is essentially like a PHP class in order to obviously make things easier development-wise, but should also provide a bit of a familiar environment for PHP developers looking to make the jump to Jaxer.