Considerations when using a database with a web application

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.Opening 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 Opening event handler, connect to the database:

Var 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

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)
Next
rs.Close

Protecting your database from hackers with 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. With Prepared Statements you supply the SQL statement and any user-provided data separately allowing the database engine to them determine if the data contains any SQL commands and reject it is that's the case.

The SelectSQL method has built-in support for Prepared Statements. However, there are two situations where they won't work. First, if you need to perform the same Select repeatedly (in a loop for example), then it's not efficient to use the Prepared Statements that are built-in to the SelectSQL method because your code would be setting them up every time it did a Select. The second would be if you need to access a special column type specific to a particular database engine. In either of these cases, use the Prepared Statement classes. They are a little more work to use but are far more efficient when quickly and repeatedly performing the same query over and over:

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

Try
  Var rs As RowSet = ps.SelectSQL("john", 20) // values for parameters
  For Each row As DatabaseRow in rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
      " Age: " + row.Column("Age").StringValue)
  Next
Catch error As DatabaseException
  MessageBox(error.Message)
End Try

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.WriteAheadLogging 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: