A Few SQLite Tips

 

 SQLite http://sqlite.org/ is an open source database that is written in C.  It is similar to say an access .mdb file or a Microsoft SQL Server CE file only databases in that it is a file and runs in the same process (well, some caviots here with newer mods to SQLite but I'm not familiar enough there to comment) as your deployed program.  It is used by Firefox, Chrome, a lot of phone app developers, and now by me.  SQL Server is overkill for many of the little web applications I write and although I like SQL Server Compact Edition it looks like it is dead in the water [LINK].

1) Once you have created a table in SQLite, you cannot add a new column to that table with the alter command that has a default value [LINK].

2) It seems like time for SQLite is defaulted to GMT, which in my opinion is a good thing.  For default dates in tables, use CURRENT_TIMESTAMP.

3) SQLite-Net looks really cool, but I haven't been able to hack it to work for web development.  If you are using Windows 8 and developing APPS or Desktop applications, take a look at it. [LINK].

4) A really great and free GUI manager for SQLite is a plugin for Firefox called SQLite Manager. Look for it in the Firefox Plugins.  More info [LINK].

5) It looks like the main .Net SQLite Data Connection enabler is System.Data.Sqlite.  The full Nuget package contains Linq and Entity Framework support.  More info [LINK].

6) There is a version of SQLite written in managed .net code, but it looks like it is no longer being revised.  Maybe Microsoft will pick it up.  Info [LINK].  You might be able to hack SQLite-net to use this version, as it looks like it was originally intended to use it.  I tried for a few hours but gave up.  Maybe I'll take another stab at it.

7) There are no stored procs with SQLite (although I believe there is a fork in development that supports that, but I'm not sure how stable it is).  You can however create user defined functions.

8) When to use SQLite [LINK].

9) Interestingly, if you try to connect to a SQLite DB that doesn't exist the SQLite Connection object will create a new blank DB file for you with the name that you were looking for.  This actually caused me some confusion as has an extra character in my connection string and suddenly I couldn't query any of my tables...duh, they didn't exist because I was querying a new database.

10) If you are familiar with Transact SQL and Microsoft SQL Server (or MySQL, Postgress, Oracle) I don't think you will have any problems using SQLite.

11) Data types on columns are more recommendations than set and fast.  I believe if you try you can save strings in int fields and mix and match.  This might have changed with the 3.x version but from what I have been reading this at lest used to be very true.  Who knows in some cases this could be a good thing, but in most if you aren't careful this might cause havoc.

12) For bulk inserts you want to use SQLite transactions else SQLite will be very slow [LINK]

13) SQLite select queries are CASE SENSITIVE by default.  You can get around this by using COLLATE NOCASE after your where condition like select * from mytable where colum1 = @somevalue COLLATE NOCASE.  A post explaining the particulars can be found [HERE].

14) There is no "Select top * or Select top 10" in SQLite.  Instead use limit. Example: "Select blah from mytable where blah order by blah limit 10"

I got my start in web development using classic ASP and Access MDB databases.  I could crank stuff out FAST.  I think now for some of my projects using MVC (thought without most of the MVC, just using Razor pages almost like development with WebMatrix) and SQLite might be a sweet spot for me as far as making development fun and productive.  I plan to update this page as I find more little quirks using SQLite with ASP.Net.  I also will be posting some source code examples.