Contents
SQLite Management Tools : a comprehensive table for multiple operating systems but not updated since 2011.
The following table describes a few tried by one member of this wiki. The page SQLite Managers for Mac OS looks into some for the Mac.
| Win | Mac | Product | Review | Rating TomH |
|---|---|---|---|---|
| Y | N | SQLite Expert Personal | Free subset of the $59 Professional version. Excellent results display, sort, filter, thumbnails of images. BLOB viewer/editor. Copy/paste for results export. Save/load SQL files. Supports loadable extensions including most compatible fake RMNOCASE. Supports runtime parameters. Best at handling very large databases, large result sets and multiple open databases. Very frequently updated. 2021-07-18: current version is 5.3 | 8.8 |
| Y | N | SQLiteSpy alternative SQLite Spy download link | Free, basic, clean manager capable of bypassing the RMNOCASE obstruction by using a fake collation. Fastest display of table data; sort by column with both numerical and ASCII fields. Export results by copy to clipboard and paste to Excel or text editor. Export/Import SQL. Frequently updated. 1.9.0 supports loadable extensions with potential for RM date processing and other lengthy expressions to be made custom functions. Can bog down on very large intermediate or final results sets. 2021-07-18:: version 1.9.15 released 8 Feb 2021 | 8.5 |
| Y | N | SQLite Developer | Were it not for its $29 license, this one would tie or be first. Only one to have choice of user named UniCode collations, hence a substitute for RMNOCASE and no more COLLATE NOCASE overrides. Has good sorting (no numerical sort), filtering, bookmarking and exporting of results. Export/import SQL files. SQL structured formatting. NB: the free Lite version lacks the Collation and some other tools. Good support for bug fixes. 2021-07-18: latest release was 4.2 in 2016 | 8 |
| Y | Y | SQLiteman | Simple, reliable but no visual query builder. Good error handling. Can export and import SQL files and save SQL Views. Development appears to have stalled in 2010. 2021-07-18 new website; $1/mo subscription model “native UI” apps for Windows 10 and macOS. | 6 |
| Y | N | DBTools DBManager Standard | More professional, commercial. Seems complex and powerful but freeware version out-dated (2007) and restricted. 2021-07-18 no further development since 2007 | 6 |
| Y | N | SQLite2009 Pro | Attractive GUI. Fast visual query builder. Unreliable – crashed on some queries or views with poor error handling (Jan 2010). Version 3.7.6.3 as of 2011-05-20 still does not save or load a SQL file – must copy to/from clipboard; takes almost twice as long as SQLiteSpy to execute LifeLines-OO.sql on a large database (117MB). Current 3.8.3.1 (2014-02) has not been tested. 2021-07-18 website uninformative; Software Informer shows latest version as 3.8.3.1 released in 2016 | 5 |
| Y | Y | SQLiteStudio | Version 3 (2014-12) under review; promising. 2021-07-18: current version is 3.3.3 | 7? |
| Y | N | SQLite Administrator | Beta. Quirky column widths in results display. No sorting, filtering. No import/export of queries or data. Queries saved to db table. Not updated since 0.8.3.2 (2006). 2021-07-18 unchanged | 3 |
| Y | Y | DB Browser for SQLite | Very limited. A basic browser. (that is an old review…) 2021-07-18 current v.3.12.2 for Windows & macOS released 2021-05-18 | 3? |
I tend to use a combination of SQLite Expert Personal and SQLiteSpy when developing new queries. For using established queries, I rather prefer the look of the results tables from Spy but Expert handles user inputted parameters at run-time. For a new user, Spy is probably the easier of the two to start with. SQLite Expert Personal is better for its performance on very large databases, display of JPG blobs and the availability of a highly compatible substitute RMNOCASE collation.
Discussions & comments from Wikispaces site
Speed Reporting Complicated
14 January 2010 14:36:14
Evaluating speed has turned out to be more complicated than expected. The supposed speed regression after sqlite 3.6.17 is not exactly that. It seems that either the database design or the query design or both confuse the sqlite query optimiser into making sub-optimal choices of index files up to that version and different, worse ones after. That resulted in the necessity of using INDEXED BY and NOT INDEXED clauses to force the use of appropriate indexes, contrary to what the documentation instructs. As INDEXED was implemented with sqlite 3.6.3, older tools (SQLiteman, DBManager…) error out on the query but the later ones all execute at about the same speed (~3s).
KenCRoy
Anyone have a preference for a free SQLite manager
11 March 2010 18:13:19
Do any of you have a preference for a free SQLite Manager?
I really don’t want to spend any money until I really decide to convert to RootsMagic.
I tried SQLite Expert Personal — http://sqlite-expert-personal.software.informer.com/2.1/
but it won’t run the queries as posted on this forum.
ve3meo
11 March 2010 22:04:24
You can see by my ratings on this page that my rankings are:
1. SharpPlus SQLite Developer
2. SQLiteSpy
3. Tie between DBTools DBManager Standard and SQLiteman.
I would welcome others to add their ratings.
In practise, I use Developer the most because it is well-featured, robust, up-to-date (uses SQLite 3.6.22 – 23 just came out in the last couple of days), can sort and filter results on screen, etc., and may be the only one that will allow editing of all the tables. I use Spy often, because it shows results the fastest and maybe the nicest and is also pretty current (SQLite 3.6.21). Initially, my favourite was SQLiteman for easy, clean, fast with DBManager free edition as the candidate for development. Both of these are quite dated – the former no longer being actively supported and developed.
I have recently started exploring OpenOffice Base and Calc using an SQLite ODBC connection to the RootsMagic files. Using the latter is quite promising for filtering and sorting copied results and there is potential for formatted reporting, along the lines of what MarkVS id developing using MSAccess. I’m not so keen on using Base to develop complex queries as error reporting is pretty uninformative. But that may be just a reflection of being at the low end of the learning curve.
None of them are ideal and you may not want to pay the license fee for extra features. So I won’t recommend for you one over another but do advise you to avoid the ones I ranked low as I think you would waste your time trying them.
ve3meo
11 March 2010 15:45:33
I should qualify these speed tests further and update them to reflect my best understanding. RM4’s NameTable has an index on the IsPrimary field. This seems to be a rather useless index because the field has only two values and thus cannot provide any speed improvement over a full scan. On the other hand, most searching of NameTable is against the OwnerID field; the corresponding index provides a tremendous speedup over a full scan. When a query uses IsPrimary as a condition in combination with a lookup of OwnerID, the query optimiser chooses the idxNamePrimary index with adverse consequences. Since the above posting, I have learned to hide the IsPrimary field from the query optimiser thus obviating the need for the INDEXED clauses and rendering the query compatible (in this respect) with the older SQLite implementations. It’s accomplished simply by prefacing the IsPrimary field with the ‘+’ operator. For example,
SELECT
Surname, Given, EventTable.Date AS BirthDate
FROM
EventTable
LEFT JOIN NameTable USING( OwnerID )
WHERE
OwnerType = 0 AND +NameTable.IsPrimary = 1
;
With the ‘+’ operator, EXPLAIN QUERY PLAN reports:
order from detail
0 0 TABLE EventTable
1 1 TABLE NameTable WITH INDEX idxNameOwnerID
Without the ‘+’ operator (or the INDEXED BY clause), it reports:
order from detail
0 0 TABLE EventTable
1 1 TABLE NameTable WITH INDEX idxNamePrimary