3 Steps to Seamlessly Deploy SQLite Projects on Desktop, Web & iOS

This tutorial will show you how to deploy SQLite based projects on Desktop, Web and iOS, copying the SQLite database file to the right place on every target.

1. Adding the Database File

You probably created your SQLite database file using an external editor; so first add that file to your Xojo project. You can do that in several ways, but usually it’s best to add a Build Step. This way, the file will be automatically added to the folder every time you compile your app. Bonus: Doing this allows you to decide to use different paths when debugging or deploying your app.

Adding a Build Step in Xojo is the same for Desktop and Web projects. Select the target in the Build Settings then from the contextual menu choose the “Add to Build Settings > Build Step > Copy Files” option.

This gives you access to the Inspector Panel for the just-added item where you can enter a name for the Build Step, choose if the copy file action will be executed both for debugging or deployment and, most importantly, choose the location where the file should be copied when the app compiles.

The Destination menu contains a number of typical paths (or most relevant folders). For example, a good destination option would be “Resources Folder”. Don’t forget to add the database file itself using the buttons on the Build Editor toolbar.

On iOS, adding a Build Step is nearly the same as for Desktop and Web. The only difference is that you have to choose the icon with an iPhone picture in it in order to access the Build Step contextual menu> In addition to that, every added resource needs to be signed with a certificate.

2. Copying the Database File to a “Working” Folder

You may think that adding a Build Step is all you need to do, because if the database file is already copied to a well known path then you only need to provide that path as the DatabaseFile property on a new SQLiteDatabase instance. But this is not the case.

They are many reasons you shouldn’t do that, notably because the database would be open in read/write mode and if you write to that database file inside an application bundle, then you’d be modifying a resource and that would invalidate any certificate signature on your app.

The best thing to do is to check every time you run the app to see if the database file has already been copied from the app bundle (or folder) into a target folder that doesn’t have an access problem when it is time to use the database. The Application Support folder is a good place for Desktop apps, while the Documents folder is fine for Web and iOS apps.

For example, if our database file is named “EddiesElectronics.sqlite” and our app name is “Xojotest”, then we can add the following code fragment into the Opening Event Handler of a Desktop app. Also, make sure to add the pDatabase property to the App object in the Navigator:

Var source As FolderItem = SpecialFolder.Resource("EddiesElectronics.sqlite")
Var name As String = app.ExecutableFile.name.NthField(".", 1)

// Check if there is a folder with the App name in special Application Data
// if not, create it and copy the database file from Resources bundle/directory
If Not (SpecialFolder.ApplicationData.Child(name).Exists And SpecialFolder.ApplicationData.Child(name).IsFolder) Then SpecialFolder.ApplicationData.Child(name).CreateFolder

If Not SpecialFolder.ApplicationData.child(name).child(source.name).exists Then source.CopyTo(SpecialFolder.ApplicationData.Child(name))

Try
  // Create a SQLiteDatabase instance and try to open our database file from
  // the path
  pDatabase = New SQLiteDatabase
  pDatabase.DatabaseFile = SpecialFolder.ApplicationData.Child(name).Child("EddiesElectronics.sqlite")
  pDatabase.Connect

Catch e As DatabaseException
  MessageBox(e.Message)
End Try

For an iOS app, the code would be:

Var source As FolderItem = SpecialFolder.Resource("EddiesElectronics.sqlite")
// Check if our database file already copied on the Documents Sandbox folder
// if not, copy the database file from Resources bundle/directory

If Not SpecialFolder.Documents.Child("EddiesElectronics.sqlite").Exists Then
  source.CopyTo(SpecialFolder.documents)
End If

Try
  // Create a SQLiteDatabase instance and try to open database file from
  // the path
  pDatabase = New SQLiteDatabase
  Var f As FolderItem = SpecialFolder.Documents
  pDatabase.DatabaseFile = f.Child("EddiesElectronics.sqlite")
  Call pDatabase.Connect
  
Catch e As RuntimeException
  MessageBox(e.Message)
End Try

If you’re working on Xojo Cloud, the code is even shorter. First, make sure that the Copy File Build Step has the following values in the Inspector Panel:

Destination: Contents Folder
Subdirectory: Documents

Then, the code will be:

Try
  pDatabase = New SQLiteDatabase
  pDatabase.DatabaseFile = SpecialFolder.Documents.Child("EddiesElectronics.sqlite")
  pDatabase.Connect
Catch e As RuntimeException
  MessageBox(e.Message)
End Try

What about a Web app that you host? That would mean you are in control of the folder/directory you want to use to store the app resources. Thus, it wouldn’t make much sense to automate this process (but it is certainly doable following the same principles).

3. Simplifying the Process

What we have just done works, but it means you need to change the database file name, probably for every app you build. It also means that you’ll be writing the same code snippet again and again in every new app. Wouldn’t it be great to be able to extend the SQLiteDatabase in order to simplify that?

Well, let’s do that! Start by adding a new Module to the example project (for example, one named “DatabaseExtensions”) with a couple of methods on it. The first method will be the one executed on our Desktop, Web and Console apps, because all of these targets use the SQLiteDatabase class.

Add a new method using the following signature in the just-created Module:

OpenDatabase(databaseName As String)

It extends the SQLiteDatabase class, adding a new method that takes as parameter the name of the file we want to copy on the “work” folder/directory.

The code you should type on this method is:

pDatabase = New SQLiteDatabase

#If TargetDesktop Or TargetConsole Or TargetWeb Then

  Var source As FolderItem = SpecialFolder.Resource(databaseName)
  Var name As String = app.ExecutableFile.name.NthField(".", 1)
  // Check if there is a folder with the App name in special Application Data
  // if not, create it and copy the database file from Resources bundle/directory
  If Not (SpecialFolder.ApplicationData.Child(name).Exists And SpecialFolder.ApplicationData.Child(name).IsFolder) Then
SpecialFolder.ApplicationData.Child(name).CreateFolder
If Not SpecialFolder.ApplicationData.Child(nam).Child(Source.name).Exists Then source.CopyTo(SpecialFolder.ApplicationData.Child(name))

  Try
    // Create a SQLiteDatabase instance and try to open database file from
    // the path
    pDatabase.DatabaseFile = SpecialFolder.ApplicationData.Child(name).Child(databaseName)
    pDatabase.Connect

  Catch e As DatabaseException
    MessageBox(e.Message)
  End Try

#ElseIf TargetXojoCloud
  Try
    pDatabase.DatabaseFile = SpecialFolder.Documents.Child(databaseName)
    pDatabase.Connect
  Catch e As RuntimeException
    MessageBox(e.Message)
  End Try
#EndIf

Of course, we need to add the “pDatabase” property to our Module too: pDatabase As SQLiteDatabase

Now, you’ll only need to use:

OpenDatabase("EddiesElectronics.sqlite")

With the method selected in the Project Browser, click on the Attributes section of the Inspector Panel (the Cog Wheel icon), and uncheck the “iOS 64” checkbox. This way, that method will not be included when compiling for iOS apps.

The second method is the one we will be using for iOS apps. The method signature would be:

OpenDatabase(databaseName As String)

Type the following fragment of code in the associated Code Editor:

pDatabase = New SQLiteDatabase

Var source As FolderItem = SpecialFolder.Resource(databaseName)

// Check if the database file is already copied on the Documents Sandbox folder
// if not, copy the database file from Resources bundle/directory

If Not SpecialFolder.Documents.Child(databaseName).Exists Then
  source.CopyTo(SpecialFolder.Documents)
End If

Try
  // Create a SQLiteDatabase instance and try to open database file from
  // the path
  
  Var f As FolderItem = SpecialFolder.Documents 
  pDatabase.DatabaseFile = f.Child(databaseName)
  pDatabase.Connect
  
Catch e As RuntimeException
  MessageBox(e.Message)
End Try

Lastly, and with the method item still selected in the Navigator, go to the Attributes section of the Inspector Panel and make sure that the “iOS 64” checkbox is the only one selected under the “Include In” section. This way, we make sure that the method will be compiled only on iOS targets.

To Summarize

The use of Modules in combination with OOP Class Extension is a good way to get more flexibility when developing your apps, no matter if you are working on Desktop, Web or iOS. And that leads to convenient reutilization of code and less code to maintain through all your projects!

(You can find this article in Spanish here)

Javier Menendez is an engineer at Xojo and has been using Xojo since 1998. He lives in Castellón, Spain and hosts regular Xojo hangouts en español. Ask Javier questions on Twitter at @XojoES or on the Xojo Forum.