What can the database SQLite do for me as a Xojo developer?

While not every app needs robust data storage, many solutions require one. The database market began in the 1960s to solve the problem of file storage, taking the form of Integrated Data Store as introduced by Charles Bachman. In the 1970s, the earliest work on relational databases following a paper delivered by E.F. Codd from IBM arrived, differentiating it from the network model of databases. As a sub-industry, the database market began at the beginning of computing and, while taking on the appearance of a very mature market in which a single technology (and vendor) dominates, it instead evolved into a market where new databases arrived in order to address specific and evolving new forms of data.

What about SQLite?

SQLite came about to solve a market problem. Developers needed a database system that had a small, local footprint for embedding into applications with no real licensing complications. It was released August 17, 2000 by Dr. Richard Hipp. Among its features:

• SQL based. You can use SQL, the most popular language of databases, to work with SQLite.
• Free and Open Source. There are free implementations and, both being open source and widely available, it has become a solid and reliable platform.
• Availability. SQLite implementations are available on almost every platform, including all supported by Xojo.
• Simple. SQLite is a simple, single-file based platform.

SQLite wasn't designed for a multi-user environment. It supports a feature called Write-Ahead Logging, allowing for multi-user-like behavior.  To repeat Xojo's own warning: don't store a SQLite file on a network drive with the expectation that users will just open it like it was any other file. This can lead to serious corruption problems when there is more than one user attempting to open the file.

What about SQLite and Xojo?

All of the features mentioned lend themselves towards development with Xojo, and Xojo has entirely embraced the use of SQLite. That is, you can use SQLite with any level of product, including Xojo Lite.

With SQLite Week 2024: Xojo+SQLite, we will explore three solutions included in Omegabundle for Xojo 2024.

Serving SQLite with Valentina SQLite Server

A natural evolution of many applications is to migrate from being a single user solution with a local only data store, to a true multi-user solution. That doesn't necessarily mean you are giving up on a local data store. Rather, you can have both. But SQLite does not lend itself to this strategy right out of the box.

Valentina Server brings together several solutions that work together. Two internal server solutions work with both internally supported databases as well as external data sources, such as MySQL, Microsoft SQL Server and others.  The two internal database solutions are ValentinaDB Server and Valentina SQLite Server.

Omegabundle for Xojo 2024 includes a standalone license for Valentina Server Unlimited.

Learn more about Paradigma Software Valentina SQLite Server...

Extending SQLite with MBS Xojo SQLite Extension

Xojo supports SQLite right out of the box, and you can use SQLite with any level of Xojo, from Xojo Lite and upward. Xojo implements access to SQLite databases using the SQLiteDatabase Class, its properties and methods as well as its sub-classes such as DatabaseRow, SQLitePreparedStatement and RowSet. But you may want to do more.

How Extensible is SQLite?

Fortunately, SQLite itself supports Runtime-Loading Extensions. You can roll your own, but doing so means you are not working in Xojo and you have a lot to learn. Also, how these extensions work on different operating systems is another consideration. SQLite is extensible, but the learning curve can be well more than you want to address.

More Functions for SQLite with MBS Xojo SQLite Extension

Monkeybread Software knows your pain. In response, MBS Xojo SQLite Extension works like any other Xojo extension and adds a full 58 new functions that work on all supported platforms.

Learn more about Monkeybread Software's MBS Xojo SQLite Extension...

Creating, Analyzing and Managing SQLite with Valentina Studio Pro

If you have committed to using SQLite as a data source (including local, client-server with Valentina Server or both), then you need a tool for working with the databases themselves.  Fortunately, SQLite is a 'native' database type in Valentina Studio. Create new SQLite databases on your local computer or, on Valentina SQLite Server from the main menu.

Learn more about Valentina Studio PRO support for SQLite...

SQLite Server and SQLite Extension Together

Both Valentina SQLite Server (a part of Valentina Server) and MBS SQLite Extension were built with maximum compatibility in mind with SQLite.

Since both support the SQLite specification for Runtime-Loading Extensions, we have confirmed that the standardized extensions included with MBS SQLite Extension can be loaded by Valentina SQLite Server for use like any other extension by making calls using the MBS interface.