Over the last decade a number of free / open source database environments such as PostgreSQL and MySQL have emerged to challenge the traditional players like Microsoft SQL Server and Oracle.  Like PostgreSQL and MySQL, SQLite has found favour with lone developers using limited data sets or developing lightweight applications.  Even users of SQL Server Express Edition have moved to SQLite, where compatibility with the full edition of SQL Server isn’t a significant requirement.

In this article I will explain how to connect Mo.net to a SQLite database using the standard ODBC connectivity available in Mo.net Model Development Studio.

What is SQLite?

SQLite is a lightweight, self-contained, serverless SQL database engine.  It has the following key features:

• Serverless: Unlike traditional databases (like MySQL or PostgreSQL), SQLite doesn’t run as a separate server process. Instead, it’s just a library that applications link to.
• File-based: All the data is stored in a single file on disk (with a .sqlite or .db extension)
• Zero configuration: No installation, setup, or management of a server is required. It’s just a case of opening the database file, and you can start using SQL.
• Cross-platform: Works on Windows, macOS, Linux, mobile platforms (iOS, Android), and even embedded systems.
• Fast and lightweight: Despite its simplicity, it supports most of standard SQL (transactions, joins, triggers, views, etc.) and can handle surprisingly large datasets for its size.

Mo.net with SQLite Use Cases

White Mo.net users typically connect to enterprise databases or data warehouses holding policy data, market data, and model results, there are some use cases that are particularly suited to SQLite.

Local Caching of Input Data

Mo.net models often rely on large policy datasets, assumptions, and reference tables (e.g., yield curves, mortality rates).  Instead of repeatedly pulling from SQL Server, Oracle, or CSVs, it’s possible to cache subsets of data in SQLite locally for faster prototyping or testing.

Portable Scenario & Assumption Stores

SQLite’s single-file design makes it perfect for bundling assumptions / scenarios (e.g., economic stresses, lapse shocks) into a .db file.  Actuaries can easily share this file with colleagues, ensuring consistency of inputs.

Results Staging & Debugging

Instead of sending projection results straight into a big SQL Server or reporting system, it’s possible to stage them in SQLite for local testing / analysis.  This is useful when debugging a new model, since SQLite is quick to query and doesn’t need IT infrastructure.

Integration with Python or R Analytics

Many users wrap Mo.net runs with Python / R scripts for analysis and visualisation.  SQLite provides a common lightweight database that both Mo.net and Python / R can read and write to, avoiding format conversions.

Offline / Sand-boxed Modelling

SQLite enables self-contained modelling environments that don’t depend on corporate databases.  Useful for consultants, regulators, or developers working remotely on laptops without full database access.

Automated Testing Framework

It’s possible to use SQLite as a reference results store for unit / regression testing of models.

Connecting Mo.net Model Development Studio to a SQLite Database

There are a few stages to connect Mo.net Model Development Studio to a SQLite database.  These are outlined below. 

Note: This assumes that you already have already created a SQLite database and populated it with some data to be used by Mo.net.  If you haven’t created a SQLite database yet, I recommend downloading DB Browser (SQLite) from https://sqlitebrowser.org/.  This will allow the creation and population of a new SQLite database.

For this example I have loaded the Linked_Inputs.txt inputs file from the LinkedFundsDynamic sample project into a SQLite database called monet.

Downloading the SQLite ODBC Driver

The first step is to download an ODBC driver for SQLite.  An open source (BSD licence) driver is available from http://www.ch-werner.de/sqliteodbc/.  Ensure you download the 32-bit driver for use with Mo.net Model Development Studio.

Once downloaded, install the driver with the default options.

Configuring the Driver with ODBC Data Source Administrator

1. Launch the 32-bit ODBC Data Source Administrator application.
2. Add a new User or System DSN
3. Select SQLite3 ODBC Driver from the list and click Finish.

4. Give the data source a name – e.g. Mo.net SQLite – and browse to the SQLite .db file containing your data – in this case monet.db.  Click OK to create the data source.

With the ODBC connection created, we can now bring data into a Mo.net project.

Connecting Mo.net to the SQLite Database

1. Launch Mo,net Model Development Studio
2. Create a new LinkedFundsDynamic sample project
3. Select Tables and New ODBC Connection- called SQLite
4. Click the Source property and then scroll down and select the Mo.net SQLite connection created above.  This should list the tables of data available – such as the Linked_Inputs imported for this example.

5. Now create a New Table and select SQLite as the Source and enter Linked_Inputs as the Reference.  The contents of the database table should now be visible.

You can now use this data as the source for the Linked task by changing the Inputs Table Source, selecting Existing Data Connection, Selecting SQLite, and entering Linked_Inputs in the Reference.

Writing Results to a SQLite Database

In a future article I will explain how to write results from a Mo.net projection to a SQLite database table to create a fully integrated modelling solution.

I hope you’ve found this article useful.  If you have any questions or suggestions, please don’t hesitate to contact me or anyone else at Software Alliance.