October 11, 2013

Visualize Sqlite database with schemaSpy

WORDS BY   Aleš Černivec

POSTED IN   tips & tricks | database | tips and tricks


I had trouble getting resources for viewing my sqlite db schema for a documentation I was writing. It turns out all you need is:

My system:

[Fri Oct 11][12:00:05][ales@~/Downloads]
$ uname -a
Linux ales-desktop-1 3.2.0-54-generic #82-Ubuntu SMP Tue Sep 10 20:08:42 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
[Fri Oct 11][12:00:08][ales@~/Downloads]
$ cat /etc/issue
Ubuntu 12.04.3 LTS

You need to create your own sqlite.properties since the the downloaded JDBC driver is a bit different:

description=SQLite-Xerial
driver=org.sqlite.JDBC
driverPath=sqlite-jdbc-3.7.2.jar
connectionSpec=jdbc:sqlite:/<db>
db=path to database or :memory:</blockquote>

Put the sqlite.properties file under the same location where schemaSpy jar file resides. Now, run:

$ java -jar schemaSpy_5.0.0.jar \
    -t ./sqlite.properties \
    -dp ./sqlite-jdbc-3.7.2.jar \
    -db <path to sqlite db> \
    -u <some name> \
    -o <path to output dir>

You should get this output (or smth. similar):

Using database properties: ........
Gathering schema details............(0sec)
Writing/graphing summary............(0sec)
Writing/diagramming details.........(0sec)
Wrote relationship details of 9
tables/views to directory '.' in 0 seconds.
View the results by opening ./index.html

Now, point your browser to the index.html. The result:

Result: beautiful :)

Result: beautiful :)

Cheers,

A