SQLite is a unique implementation of a database and it has some unique approaches that distinguish it from other databases. One of the greatest advantages of SQLite is that it can be run nearly anywhere. It has been ported to a variety of platforms like Windows, Mac OS, Linux, iOS, Android, and many other options. The applications that make use of the SQLite database don’t have to be written in a specific language that you would typically see with other databases. As long as there is some way to bind and work with the external libraries, it would work correctly. All of the source code for SQLite is public domain so it can be reused in other programs with no restrictions.
SQLite is very flexible when it comes to data types. We have seen with our PostgreSQL DB that we have very specific types of data and sizes. SQLite only has a few data types including REAL, BLOB, NULL, INTEGER, and TEXT. SQLite is quite forgiving of the type of data that you enter in. If a column has the datatype of an integer and you try to insert a text string into that column, SQLite will try to convert the text string into an integer first. For example, if the user enters a string of ‘9876’ into an integer column, that value is covered to an integer of 9876 and stored in the column. If you tried to insert a non-numeric string like ‘abcd’ into an integer column, most other databases will throw an error. However, SQLite will not and just store the actual string value in the column.
You may remember that many of our columns have a VARCHAR(40) that allows up to 40 character sizes. If we try to insert data into a column that has that size, many databases would either throw an error or truncate the string to the 40 characters. SQLite instead would just store the entire string without the loss of information. This is viewed as a feature of SQLite rather than a bug. This does make it very difficult for applications built using SQLite to be ported to other databases as many of those situations could create problems.
Booleans are another instance where SQLite does not store a separate data type. Instead, true and false are represented by integers of 0 and 1. Dates are also different in SQLite where the date is stored in a TEXT string. If it is stored as an integer, it stores the number of seconds since 1970. There are built-in date and time features to change between those values which should make it easier to follow. The tables could even be created without any data types at all which can be quite confusing for many. For example, you could run a command like
CREATE TABLE myTable (a, b, c);
This would create a table named myTable with the column names a, b, and c that have no data types defined. Anything could be stored in those columns.
Primary keys should be unique and cannot be empty. That has been a big piece of any database. However, with SQLite, a primary key can be a null value. This was a bug in the program but when the bug was identified, so many databases had already made use of that bug as a feature that the bug was kept as a feature.
When it comes to working with tables, after the tables have been created, in SQLite, we can only rename the table with RENAME TABLE, rename a column in the table using RENAME COLUMN and add a new column at the end of a table with ADD COLUMN. We cannot add any constraints to a table once the table has been created, drop a column, or alter a column. The joins between tables in SQLite can also be a bit of a challenge, especially with outer joins. The only outer join you can run is an OUTER LEFT join. In SQLite, they only support an INNER join, LEFT join, LEFT OUTER join, CROSS join and an OUTER join.
As you can see, even given a lot of the benefits of SQLite, there are many intricacies to be aware of when it comes to using the database.
Source: Authored by Vincent Tran