UserGuide:Databases and Web Apps

Using databases with web apps is not much different than using them with desktop apps, but there are a few things to keep in mind. The most important change is that a web app allows multiple users. This means you'll want your database connection to be unique for each user that connects to the web app, rather than global to the app itself (as is common in desktop apps). The simplest way to ensure this is to create the connection to your database in the WebSession.Open event handler, saving a reference to the connection as a property that you add to WebSession.

Here is an example. In the Session object, add this property:

DB As SQLiteDatabase

In the Open event handler, connect to the database:

Dim dbFile As FolderItem = FolderItem("MyDatabase.sqlite")
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
Try
db.Connect
Catch error As DatabaseException
// Display an error page and log the error
// You should not show specifics of the error to users
ErrorPage.Show
End Try

Although the above code is connecting to a SQLite database, you would use the same technique when you are connecting to a different database such as PostgreSQL or MySQL.

By having the DB property isolated to the session, you ensure that that database transactions are specific to each connected user's session and you reduce the risk of data being exposed to the wrong session.

Once you have your project set up like this, you can refer to the database like this:

Session.DB

Connecting to Databases

So to retrieve data, you would do something like this:

Var rs As RowSet
Try
rs = Session.DB.SelectSQL("SELECT * FROM Person")
Catch error As DatabaseException
// Save and log error in error.ErrorNumber and error.Message
// You should not display specifics of the error to your users
Return
End Try

For Each row As DatabaseRow In rs
ListBox1.AddRow(row.ColumnAt(1).StringValue)
Wend
rs.Close

Prepared Statements

Whether you are using SQLite or another database server, to avoid a security risk called SQL Injection you should always use Prepared Statements with any SQL statements that have parameters supplied by user input. There is a PreparedStatement class for each database type:

For example, to query the Person table for specific values you would use a PreparedStatement like this:

// Code for SQLiteDatabase
// SQL statement uses ? in place of parameters to be supplied later
ps = Session.DB.Prepare("SELECT * FROM Person WHERE Name = ? AND Age >= ?")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) // Name type
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER) // Age type

Dim rs As RecordSet = ps.SQLSelect("john", 20) // values for parameters
If rs <> Nil Then
While Not rs.EOF
MessageBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Wend
Else
If db.Error Then MessageBox(db.ErrorMessage)
End If

Using SQLite Instead of a Database Server

SQLite is a file-based database and can be a great alternative to a database server for small to medium web sites that do not have a significant amount of database writing. Because SQLite is file-based, multiple users connected to your web app cannot write to the file at the same time. If your database writes are infrequent, short and fast this may never be a problem.

One way to help mitigate this is to enable Write-Ahead Logging by setting the SQLiteDatabase.MultiUser property to True for your SQLite database. This improves performance for database writes.

The When to Use section of the SQLite web site has this to say about using SQLite with web sites:

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.

Permissions

With SQLite, the database file exists on the database server alongside your web app (or in a related folder). Be sure that you have the correct permissions applied to the database file or you will not be able to save changes to it. For most Linux web servers, you will typically use "755" permissions, but this can vary depending on the server. When you are connected to the server, this can be done with a terminal command like this:

   chmod 755 MyDatabase.sqlite

Learn More

To learn more about databases to use with web apps, refer to the topics below:

UserGuide

Databases and Web Apps

From Xojo Documentation

Contents

Prepared Statements

Using SQLite Instead of a Database Server

Permissions

Learn More