Thoughts on SQLite and VistaDB

I started working with VistaDB Express this spring as a data backend to use with BlogEngine.NET.  I wanted to make a new data provider for a session I was giving at code camp and wanted to get familiar with a database I didn’t know.  I ended up choosing VistaDB because it looked interesting and because I found so little information about using it.  I also wanted to know why Telligent picked it for Graffiti over something else. 

While I was considering what database to use, SQLite was recommended to me be two different people.  I had recently read a few articles on getting started with it.  It looked interesting and was the other choice I gave consideration to.  Since doing my talk and spending a nice amount of time working with VistaDB, I’ve also had a chance to work with SQLite a bit and thought I’d share my thoughts on the two databases. 

Both SQLite and VistaDB Express are lightweight file based databases that work very well.  They are similar but different enough that there are a few things to consider when thinking about them.  Both have .NET support via ADO.NET data providers and I’ve had no problems with either of them.  Both are small in size and in the small data sets I’ve worked with both have performed very well.  (I read somewhere that SQLite performs better with larger data sets, but I’ve never seen any real stats on the matter.)  SQLite and VistaDB both have nice GUI admin tools available to work with and both databases have been easy to use overall.  Both have small runtimes, work in mono, and support transactions, I believe as well.  Now onto the differences… 

Cost

SQLite is free to use for any purpose.  VistaDB is a commercial product and VistaDB Express is a free for non-commercial use product that requires a link back to the company.  This is a huge difference for some people and is the main strike I have against VistaDB.  If I had a paid commercial product however, the cost of VistaDB is minimal enough that I would not let it sway me too much.  For my personal consulting work, I’m not too excited about the $299 price.  VistaDB Express is a nice alternative for non-commercial use, but the required link isn’t ideal for every circumstance.  (You’ll notice I have the link in the footer of my blog.)

Data Types

I’ve found that VistaDB mirrors SQL Server’s data types very well.  Since I’ve done most of my database work using SQL Server, this suits me very well.  SQLite is more similar to MySQL as far as that goes.  I’m not a MySQL expert (or to be honest even a fan) so take that with a grain of salt.  There is nothing wrong with the data types being different, they just are and it is a distinction.  For me, I’m more comfortable with the SQL Server similarity, but I will freely admit it is a minor issue.

Scripting

Both SQLite and VistaDB support most of the standard SQL I’ve run against them.  I recently noticed that SQLite did not support the ability to change a column in a table via ALTER TABLE which I found to be a bit of a nuisance.  (It seems lame to tell BlogEngine.NET users they have to manually change a data type in a table in the new upgrade.)  Other than that however, I’ve had no issue with either one.

GUI differences

As I said earlier, both SQLite and VistaDB have nice GUI admin tools.  As an open source product with a big following, SQLite has a few GUI admin tools to choose from.  I personally have only ever tried SQLite Administrator.  I have no idea if there are better tools so what I say is based on my work with this tool.  From a functionality perspective, I’ve been able to do basically what I’ve needed to do in both interfaces.  I can honestly say they are both fine tools for the job.  That said, I much prefer my SQL Server Management Studio to both of them. 

Just as in the data types though, VistaDB’s Data Builder tool is a little more SQL Server like.  The look is more similar as is the organization.  It felt a little more comfortable to me as it was more like what I’ve been using for years.  I particularly liked the Data folder under each table for getting to my table’s data quickly. There are a few quirks however including an annoying laptop issue.  (When I have a second display on my laptop, I often move the VistaDB window to the secondary display and close it from there.  When I next open it when the second display is not connected, it opens off the screen and I need to edit the registry to bring back over.)

As I said, the SQLite admin tool was very nice too.  While the look and organization was a bit different, the Edit Data tab in the query area is very nice.  It is a handy way to edit and view/filter data.  There was also a possibly helpful export to excel option easily available.  The interface didn’t seem to have as many quirks but I have spent less time in it.  I found an oddity with editing a table, but it was very minor.

Encryption

One other thing of interest that I found was that VistaDB has an encryption option which will encrypt your database with a password.  In a file based database, this seems nice.  I didn’t see that SQLite had this built in, but I could be wrong about it.  I did see some commercial tools to help with this though.

Documentation

I’ve spent only a little bit of time searching for documentation, looking for examples, and reading forums for each of these products.  My little experience however tells me that SQLite has VistaDB beat in this area.  My google searches regarding SQLite usually brought me right to what I needed and likely had the answer below it as well.

I found a bunch of getting started guides for SQLite and nothing for VistaDB Express besides what comes in the download.

It wasn’t hard to get started with VistaDB however, but a bit more information would have been nice.

Summary

As you might guess from my notes above, I have a slight preference for VistaDB overall.  The cost of VistaDB is an issue and the express version is not an option in many situations you find yourself building software.  There were a number of small things I liked better about VistaDB though and they add up.  The information out there on the web about VistaDB is lacking however.  Hopefully, this will improve with time.

SQLite is very solid and other than the ALTER TABLE issue, I’ve really enjoyed using it too.  It has many things going for it.  I know I can pick up a mac admin tool and as I have a MacBook Pro, this might be a nice option down the road.

Both options are very good and I’ll likely be using both in the future.