DreamFactory 2.0 Support For SQLite

Written by Terence Bennett | July 6, 2018

I have been blogging extensively about the highly scalable and enterprise class features in DreamFactory 2.0. We’ve benchmarked huge server installations and explored exciting new possibilities with Big Data. But now in a complete change of course, I want to talk about what may be the coolest new feature we have: DreamFactory 2.0 now supports SQLite. It may not be massively scalable, but SQLite is an extremely convenient way to create a database on the fly for development and testing. Production applications can also benefit from this nifty new feature.

First, A Little History

DreamFactory depends on some system tables in a default database. This is where we store the users, services, roles, and other information needed to run the platform. In the past, we exposed this database so that developers could build applications there. But with DreamFactory 2.0 we decided to make the default database more private. This made a lot of sense for security and scalability.

But we also wanted to make it really easy for developers to create applications. They might not have a legacy database handy to evaluate our product. And if they wanted to build a greenfield application, they would have to install a new database somewhere. So we decided to include MySQL and MongoDB in the Bitnami installation package for DreamFactory 2.0. This gives developers access to SQL, NoSQL, and file storage right out of the box.

Then one of the engineers pointed out a problem. We also host a Free Edition of DreamFactory on our website. Developers can go there and instantly try out the product. But now, with the new version of DreamFactory, there wasn’t a database available for them to use! We could create an extra SQL database for each developer, but that was going to really increase the number of files on the server, not to mention our hosting cost.

SQLite To The Rescue

As you may or may not know, SQLite is an almost full-featured SQL database that stores everything in a single file. The recent improvements to SQLite version 3.6 made it suitable for use as a backend database in DreamFactory 2.0. Aside from a few limitations discussed below, the REST API for SQLite is exactly the same as the other databases we support, including DB2, Oracle, MySQL, PostgreSQL, etc.

And so SQLite was the perfect solution to providing a developer database in our Free Hosted Edition. We simple generate a SQLite database with each new account, populate the database with the schema for our example applications, and bingo! Each developer now has a complete “dev and test” database that only consumes a single file on our server. As we looked into it deeper, we realized there were some other ways SQLite could help us.

Internet of Things Applications

DreamFactory 2.0 was designed for installation on cloud servers to support massive mobile and Internet of Things (IoT) deployments. But you can actually take this amazing product in the opposite direction, and install it on an individual IoT gateway. Here it can gather data from devices and send information to the cloud, all with RESTful interfaces. Our friend Janakiram MSV has written extensively about this capability.

As it turns out, this kind of usage of DreamFactory on a small gateway or router works great with – you guessed it – SQLite. The DreamFactory 2.0 default database can also be configured to use SQLite, and in that case the entire product is running on a single file database! This is perfect for “micro” installations of DreamFactory.

Instant Gratification

DreamFactory 2.0 goes even farther with SQLite support. You can go to the service tab and create any number of SQLite databases as needed. The engine will actually create the data file for you on the fly, and you can start using the database immediately without any other provisioning! This is super useful for “dev and test” scenarios or any time you need a small database.

What Doesn’t It Do?

Well of course SQLite should not be used when a mission critical highly scalable database is required. It does a great job of managing a small database, but there are real limits to what can be done with a single file. All that having been said, there are a lot of applications where a small or even a read only database is very useful.

There are also some differences in what SQLite can do compared to other SQL databases. In particular, SQLite does not support stored procedures. It can’t drop tables or fields once they are created, and it can’t edit a field once it has been created. You can use all of the normal tools in the admin console for editing schema or creating data in your SQLite database. If you try to do something that is prohibited you will get an error message to that effect.

Interface Tour

Here is a brief overview of working with SQLite in the admin console. Most of this is applicable to other SQL databases. First, you need to create the SQLite service. As mentioned earlier, DreamFactory will automatically create the data file for you. With a regular SQL database you would need an existing database somewhere and a connection string to hook up the service.

Next, you need to add some schema to the SQLite database. The Schema tab in the admin console works just fine for this purpose. Create a table and add some fields. As mentioned earlier, with SQLite you cannot change a field or drop it after it has been created.

Next, you might go to the Data tab in the admin console and populate your new database. This can also be done from a client application. Let’s say you need a list of names or other information from a client application. This would be a perfect place to create the list. Your application would dynamically get the names from the database as needed.

Lastly, you can explore the SQLite API in the API Docs tab. This will show you how to use the database form the client or anywhere else. You can see the JSON for the names I typed in, above.

Quick And Cool

And so there you have it. SQLite is a great new addition to the DreamFactory arsenal. Enterprise developers can create a quick database for any purpose, exercise all of the REST API commands, and then upgrade to any other SQL database at a later date. For small databases and “dev and test” scenarios SQLite can’t be beat.