SQLite is really quite different when compared to many other database systems. Before you decide to use it, there are a few things you definitely need to understand about it. Here we go.
| SQLite |
SQLite is a serverless
SQLite is not a database server, which means it is not a backend process running in your system somewhere. There is no SQLite server process.
While we are using PostgreSQL or MySQL, we have a database server process running somewhere to interact with us. We connect database server with IP address, database name, account and password. Then we send our SQL statements to the server through network, and get back the result from it. But this is not the case for SQLite.
Serverless also implies zero-config!
SQLite is an embeded database
Embeded database means you can incorporate SQLite into your application, while the database functionality is not necessary to be a separate piece of software. Actually, this is most likely the use case for SQLite. There are just so many scenarios you need use a file to store structured data and you also desperately hope to take the advantage of SQL language.
Embeded is the other side of the coin of serverless. Python has a builtin module called sqlite3 which you can import to use SQLite.
SQLite is a file-based
A database in SQLite is just a single file on your disk. That's it. So simple and neat. What you need to do is just to manipulate this database file by APIs offered by SQLite library.
Since a database is just a disk file in SQLite, concurrency need to be considered carefully and coordinated explicitly. It's somehow a little low-level and error-prone. Basically, you need make sure that there is only one writer at a time per database file.
SQLite is a dynamic typing (by default)
Dynamic typing (flexible typing) means that values stored in a column are not strictly bound to the column's declared data type. Basically, each column can store data of any type.
SQLite has five storage data types:
- NULL
- INTEGER
- REAL
- TEXT
- BLOB (binary)
Even if a column has declared data type as INTERGER, it can still store TEXT data if TEXT string is trying to insert into this column. There is no complaint raised!
So, what's the purpose for those declared data type in SQL statements in SQLite?
Declared data types are used to determine type affinity in SQLite! SQLite use type affinity to determine the preferred storage data type for each column. Remember that type affinity is only a type preference, or a recommendation, not a type declaration. SQLite parses the decleared data type, and use the specific key words to make decision of type affinity. Even though each column has a type affinity, it literally can store any data type!
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statements that work on statically typed databases
work the same way in SQLite. However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases. Flexible typing is a feature of SQLite, not a bug. ---- from SQLite.org
More information about the data type of SQLite, please go to: https://sqlite.org/datatype3.html
Since each column can actually store any type of data, the data type declaration is not really necessary. They are optional. And SQLite does not impose any length restrictions for each column.
>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> cur = conn.cursor()
>>> # create table with one column named a without type declaration
>>> cur.execute('create table test(a)')
>>> # insert 4 different types of data into column a
>>> # insert binary data needs to use X and single quote!
>>> cur.execute('insert into test values(1),("a"),(1.234),(null),(X\'ABCD\')')
>>> conn.commit()
>>> # all different types of data are good!
>>> cur.execute('select * from test').fetchall()
[(1,), ('a',), (1.234,), (None,), (b'\xab\xcd',)]
Foreign key constraint is OFF by default
According to official documents, this is for the compatibility of millions of legacy installations (Compatibility is really taken seriously for serious project!). Anyway, it's necessary to aware of it and it's easy to turn it on in your code. Or you can completely give up foreign key constraint. Not a big deal in many cases.
Turn on foreign key constraint, run this SQL statement after connecting a database: pragma foreign_keys=on
So far, I have list a few main things for SQLite you need to understand before using it in this post. For sure there are many other important differences between SQLite and other database systems. It's impossible for me list all of them here.
Many of these features are determined by the design philosophy of SQLite. It needs to be small, fast, self-contained, highly-reliable and full-featured. I think you will appreciate these quirks of SQLite gradually as you know it better and better.
Comments
Post a Comment