A key advantage of an Ajax server is access to shared databases. The Jaxer DB API attempts to strike a balance between extreme ease of use and enough power to get almost any job done.

It's important to note that the Jaxer DB API does not, today, have any true Object Relational Mapping (ORM) capabilities, or any other abtract persistence layer above raw SQL (except for session-type containers). We decided to leave that out of the Jaxer beta on purpose, to encourage the community to help define this aspect of Jaxer: should it commit to a certain ORM approach? Should it support multiple approaches? how much should be built-in versus available as extensions?

Jaxer offers two types of DB access:

1. Super simple, pre-configured static access
2. Pretty simple, more dynamic object-oriented access

The static functionality is designed to let you execute queries and get data back in a single line without thinking about connections, connection strings, reuse, etc. If you need a bit more than that, it's quite easy to new up a connection or multiple connections and use them to do your bidding.

Managed Access

The easiest way to access the DB functionality is with the pre-configured, static (non-object-oriented) API. It's meant to work "out of the box." Jaxer ships with the SQLite3 relational database built in, and with a SQLite3 "driver". The default configuration of Jaxer uses this SQLite3. So when you fire up Jaxer, all you need to do to access the DB is to execute SQL: Jaxer.DB.execute("CREATE TABLE IF NOT EXISTS booktest (id INTEGER PRIMARY KEY AUTO_INCREMENT, title TEXT)"); Jaxer.DB.execute("INSERT INTO booktest (title) VALUES (?)", "Mary Poppins"); Jaxer.DB.execute("INSERT INTO booktest (title) VALUES (?)", "Robinson Crusoe"); Jaxer.DB.execute("INSERT INTO booktest (title) VALUES (?)", "Harry Potter"); var resultset = Jaxer.DB.execute("SELECT * FROM booktest"); The API is extremely simple:

Jaxer.DB.execute(sql, params) returns Jaxer.DB.ResultSet

The sql string is mandatory. You can optionally use "?" to indicate prepared statement parameters: placeholders for data. This is much better in many cases than embedding parameter values directly in the sql string, especially if the values come from user data, since that opens your code up to sql injection attacks. If you use "?" in your sql, you need to give a second argument to the execute() function: the params object. If you have multiple "?" in your sql, the params should be an array of values, in the same order as the "?" in your sql. If you have a single "?" you can just use it as the value of params, without wrapping it in an array.

Jaxer.DB.lastInsertId (or its synonym Jaxer.DB.lastInsertRowId) returns a number

When you INSERT a row that has an AUTO_INCREMENT-type column, the value of that column in this row is automatically set by the database. To know what it was set to (an integer), retrieve the lastInsertId right after you execute the INSERT statement. Under other circumstances, the behavior of lastInsertId depends on the database implementation; for example, SQLite always returns the rowid of the last successfully inserted row, whether or not it had an INTEGER PRIMARY KEY AUTO_INCREMENT column.

Under the covers, Jaxer creates a single (per Jaxer process) connection to the database specified in config.js, and uses it to execute your queries and return any data as a Jaxer.ResultSet. It's called "managed access" because the connection is managed for you: it's automatically opened when needed, and closed according to the configuration parameters either after each query or after each page or only when the server shuts down. Since the connection itself holds essentially no data (other than lastInsertId or lastInsertRowId), it's safe to share it. If for some reason you need to access this default connection itself, you can get to it as Jaxer.DB.defaultConnections.DEVELOPER.

If you'd like this managed access to be configured differently, edit the config.js file in your local_jaxer folder. Jaxer ships with two "drivers": one for SQLite3 and one for MySQL 5. Here's how you configure for SQLite3: Config.DB_IMPLEMENTATION = "SQLite"; Config.DB_CONNECTION_PARAMS = { PATH: "c:\\path\\to\\my\\data.sqlite", // change to the path to your data file; it will be created if it isn't there already CLOSE_AFTER_EXECUTE: false, // optional: whether to close the connection after every call to execute() CLOSE_AFTER_REQUEST: true, // optional: whether to close the connection at the end of every request MAX_NUM_TRIES: 100, // optional: if locking is detected, retry up to this many times MILLIS_BETWEEN_TRIES: 37 // optional: if locking is detected, sleep this many milliseconds between tries }; And here's the configuration for MySQL 5: Config.DB_IMPLEMENTATION = "MySQL"; Config.DB_CONNECTION_PARAMS = { HOST: "127.0.0.1", PORT: 4417, // This uses a port different than the default of 3306, to minimize conflicts with existing installs NAME: "demos", // The name of the database instance USER: "root", PASS: "", CLOSE_AFTER_EXECUTE: false, // optional: whether to close the connection after every call to execute() CLOSE_AFTER_REQUEST: true, // optional: whether to close the connection at the end of every request }; For further information on using Jaxer with MySQL 5, see the FAQ entry.

Dynamic, Configurable Access

If you need more flexibility than a single, pre-configured, managed connection for database access, you can use the dynamic Jaxer DP API. You simply create your own connection objects and call their methods: var connectionParams = {PATH: "c:\\myData\\myApp.sqlite"}; // Uses the default settings for MAX_NUM_TRIES and MILLIS_BETWEEN_TRIES Jaxer.DB.SQLite.createDB(connectionParams); // Only needed if the DB file doesn't exist yet var conn = new Jaxer.DB.SQLite.Connection(connectionParams); conn.open(); // This is optional: executing a query automatically opens the connection if needed conn.execute("CREATE TABLE IF NOT EXISTS booktest (id INTEGER PRIMARY KEY AUTO_INCREMENT, title TEXT)"); conn.execute("INSERT INTO booktest (title) VALUES (?)", "Mary Poppins"); var resultset = conn.execute("SELECT * FROM booktest"); conn.close(); Similarly, for MySQL: var connectionParams = { HOST: "127.0.0.1", PORT: 4417, // This uses a port different than the default of 3306, to minimize conflicts with existing installs NAME: "demos", // The name of the database instance USER: "root", PASS: "", }; Jaxer.DB.SQLite.createDB(connectionParams); // Only needed if the DB instance doesn't exist yet var conn = new Jaxer.DB.SQLite.Connection(connectionParams); conn.open(); // This is optional: executing a query automatically opens the connection if needed conn.execute("CREATE TABLE IF NOT EXISTS booktest (id INTEGER PRIMARY KEY AUTO_INCREMENT, title TEXT)"); conn.execute("INSERT INTO booktest (title) VALUES (?)", "Mary Poppins"); var resultset = conn.execute("SELECT * FROM booktest"); conn.close(); Since you're managing when these connections open and close, there's no need to specify the CLOSE_AFTER_EXECUTE and CLOSE_AFTER_REQUEST parameters. Also, remember that the names of the connectionParams properties are case-sensitive.

Queries That Return Data: The ResultSet

To retrieve data from the database, you execute a sql SELECT query, and the execute() method returns a new instance of a Jaxer.DB.ResultSet objec. The resultset contains all the data returned from the query, so you can use it after the connection is closed. It also will not change even if the database data changes after you created it.

A resultset offers various ways of getting at its data. A common one is through its rows property, which is an Array of objects, one per returned row. Each row is a simple JavaScript object with a property per column in the query, whose value is the value of the row's cell for that column: var resultset = Jaxer.DB.execute("SELECT * FROM booktest"); var firstTitle = resultset.rows[0].title; // equals "Mary Poppins" Use rows.length to get a count of the returned rows. For each row, you may alternatively want all the cell values in one array, in the order returned by the query: each row has a property called $values that holds an Array of its cells' values.

If you'd prefer to get all the data as a rectangular array (a table), you can use the resultset's rowsAsArrays property. This is an Array of the rows returned by the query, but now each row is itself an Array, containing the values of the row's cells (the same as $values, above). var resultset = Jaxer.DB.execute("SELECT * FROM booktest"); resultset.rowsAsArrays.forEach(function(row) { print(row.join(" | ")); });

If you know your query returned a single result, or you only care about the first cell in the first row, you can use the resultset's singleResult property: var numBooks = Jaxer.DB.execute("SELECT COUNT(*) FROM booktest").singleResult; This will always return a null if there is no data to return. To test whether the resultset has any data, or to distinguish a singleResult that's null because the database had a NULL in that cell versus returned no data from the query, you can use the resultset's hasData property.

The resultset's columns property contains metadata about the query's results: it's an Array of the names of the columns returned, in the order returned by the query. If you know a column's name, you can also find its index using the resultset's indexOfColumn() function.

If you prefer to use the Google Gears DB API, the Jaxer.DB.ResultSet also supports its access methods: var resultset = Jaxer.DB.execute("SELECT * FROM booktest"); var numFields = resultset.fieldCount(); var header = []; for (var i=0; i<numFields; i++) { header.push(resultset.fieldName(i)); } print(header.join(" | ")); while (resultset.isValidRow()) { var line = []; for (var i=0; i<numFields; i++) { line.push(resultset.field(i)); } print(line.join(" | ")); resultset.next(); } resultset.close(); In the Jaxer Shell in Aptana Studio this results in:

id | title
1 | Mary Poppins
2 | Robinson Crusoe
3 | Harry Potter

Data Types

The execute() method attempts to preserve JavaScript datatypes and data across trips to and from the database, for all supported database implementations (currently SQLite 3 and My SQL 5). If you store a JavaScript Date value in a table with a compatible column type, and retrieve it, you will get back the same JavaScript Date value. The details of how this works vary with the implementation.

The SQLite 3 database is fundamentally untyped: unlike most other relational databases, you can store any SQLite data type in any column, regardless of its declared data type. For more information, see the SQLite 3 datatype documentation. JavaScript values are stored as follows:

• numbers are stored as double-precision values, except for NaN values which are stored as special string codes;
• booleans are stored as 1 or 0 integer values;
• strings are stored as strings;
• Dates are stored as 64 bit integers corresponding to the number of milliseconds since midnight 1/1/1970;
• null and undefined are stored as database NULL values;
• anything else is stored as a database NULL value after a warning message is logged.

When values are retrieved from SQLite, they are converted to JavaScript data types based on the SQLite data type and the declared type of that column (without regard to case):

• If the declared type has "bool" in it, the value is returned as a boolean;
• If the declared type has "date" or "time" in it, and the SQLite data type is integer, the value is treated as the number of milliseconds since midnight 1/1/1970 and returned as a Date;
• If the value is a string and matches one of the special string codes representing NaN values, it's returned as that number value
• Otherwise, SQLite integer, float (double), text, and blob values are returned as number, number, string, and string values, respectively.

The MySQL 5 database is a more traditional, strongly-typed relational database. This limits just a bit the storage of some JavaScript values, relative to SQLite:

• numbers are stored as double-precision values, except for NaN values which are stored as database NULLs after logging a warning message;
• booleans are stored as 1 or 0 integer values;
• strings are stored as (UTF8) strings;
• Dates are stored as MySQL DateTime values corresponding to the number of seconds since midnight 1/1/1970 (since MySQL DateTimes amazingly only have a precision of a second);
• null and undefined are stored as database NULL values;
• anything else is stored as a database NULL value after a warning message is logged.

When retrieving values from MySQL, the strong typing of MySQL columns removes the need for heuristics based on declared types, as was the case for SQLite:

• NULLs are returned as JavaScript nulls;
• numbers of any type are returned as JavaScript numbers (always double-precision);
• DateTimes are returned as JavaScript Date values, to a precision of 1 second;
• Dates are returned as JavaScript Date values with the time portion set to midnight;
• Times are returned as JavaScript Date values with the date portion set to the configurable settings Jaxer.DB.TIME_COLUMN_YEAR, Jaxer.DB.TIME_COLUMN_MONTH, Jaxer.DB.TIME_COLUMN_DAY (defaulting to 1/1/1970);
• TEXT and BLOB values of any type are returned as JavaScript strings.