SQLite is a serverless relational database. It is used notably by modern browsers like Chrome and Firefox to store data like history, cookies, saved passwords…
We are going to see how you can get around in the command line interface. To work with a database file you can start the program in this way:
|
1 2 |
sqlite3 data.db sqlite> |
What you get is a prompt similar to the mysql client. Now let’s see what tables are available:
|
1 2 |
sqlite> .tables machines services |
If we want to see column names we can use the .schema command:
|
1 2 |
sqlite> .schema machines CREATE TABLE machines (id integer PRIMARY KEY AUTOINCREMENT, ip varchar(20), host varchar(100), os varchar(50)); |
To customize the output format of query results you can use the .mode and .headers options, for example:
|
1 2 3 4 5 6 7 8 |
sqlite> .headers on sqlite> .mode column sqlite> select * from machines; id ip host os ---------- ------------ ---------- --------------------------------- 233 42.137.222.1 Cisco 827H ADSL router (IOS 12.2) 234 42.137.222.2 Cisco 1700-series router 235 42.137.222.3 Cisco 870 router or 2960 switch |
You can add these options to .sqliterc for permanent effect. Finally, if you want to exit just type .exit
-> Something annoying about sqlite is that the ALTER TABLE command is very limited. You can only rename a table or add a column, but you can’t modify or delete columns after they have been created. There is an official work-around for this, explained here: https://www.sqlite.org/lang_altertable.html
Using SQLite in Ruby: https://rubygems.org/gems/sqlite3
You might also like:
Redis: the blazing fast datastore
Leave a Reply