• #database #sql

You see a lot about SQLite all over the place, but finding a good place to start with how to use and interact with it is a pain. For a long time I never understood how to create, update, edit, etc. a sqlite Database. It turned out to be easier than I thought. Therefore this a quick getting started. One thing to note is to remember that SQLite is a subset of SQL* most basic commands will work, but you might need to look up the nuances.

Download SQLite

If you are on linux or OS X you might already have it. Open up command line and try to type:

sqlite3 –version

If you get a version number you are good, jump to next step.

If not you need to visit http://sqlite.org/download.html and get your relevent version. For windows get the “Precompiled Binaries”.

If you want you can go ahead and extract the executable and add the .exe to your path, or just run command-line from that folder.

Create a Database

Creating a SQLite database was always the most confusing thing for me what to do, but I found it was pretty simple. Most of the time applications use a sqlite library so this is done for the application, but doing it manually was confusing. If you have SQLite executable installed you simply type:

sqlite3 test.db

This will create the db file and get you into console mode ready to edit the database. Usually I like to try to find a GUI to edit the db, but for some reason I can’t find a very nice SQLite GUI that will create a db file so I have had to do it manually. Unfortunately, when I try to create the db file if I don’t create a table before I exit it doesn’t actually create and save the file for use; so I usually create a small little table.

Create Tables

This is actually really simple since it is ANSI SQL

create table test_table(id INTEGER NOT NULL, name TEXT);
  • “Don’t forget the semi-colon at the end!”

See Schema

Use the command line tool to view the design/layout of your database. Use sqlite3.exe db_filename.extension on an existing SQLite db file and you are ready to go editing and viewing the file.

.schema

This command actually outputs the commands issued for the created tables. So it will look just like the command you input above. This is how you will view your database structure.

Exit and Save

Finally comes time to quit. This is quite simple.

.exit

Now you shouldn’t be stuck trying to close the file for 5 minutes because you don’t know what is going on like I did with the stupid period before the word “exit”, it is like trying to quit vim.

Conclusion

SQLite is a really useful and portable database file to use, and was pretty “scary” to figure out when I didn’t know anything about it at all. I was so confused by it I didn’t want to mess with it, or anything relating to it. However, considering more and more things I work on seem to be dealing with SQLite db’s it was time to learn it, most notably are RoR and iPhone/OS development. I also like that it works with NHibernate if needed.

I think if this is all you ever learn about SQLite then this is enough to make you comfortable with SQLite when you run across it in the future.

*As noted by Jay R. Wren in the comments. I originally said it was based on T-SQL. This was my confusion because every one I talk to about SQL always says T-SQL or SQL. I never knew the history behind it. SQLite technically uses a subset of ANSI SQL which was formalized by ANSI in 1986, aka SQL-86/7, it has progressed since then and T-SQL is a Microsoft SQL Server and Sybase variant of SQL, sorry for any confusion on this. For more information feel free to read the wiki article on SQL I truely found it interesting. SQL – Wikipedia