SQLite Basics
Published on 21 Mar 2019
SQLite cheatsheet
Advantages
- Extremely lightweight
- Compatible SQL, not 100% but enough
- Ideal as in-app database
Disadvantages
- Lack of multi-user capabilities, roles.
- Writes are serialized, so it’s not suited for large databases.
- Concurrency not possible.
- Not good for large application.
Data Types
There are only 5 types in SQLite:
Type | Description |
---|---|
NULL | Not set |
INTEGER | Signed integer (1, 2, 3, 4, 6, or 8 bytes) |
REAL | Floating point value, stored as an 8-byte IEEE floating point number. |
TEXT | The value is a text string (UTF-8, UTF-16BE or UTF-16LE). |
BLOB | - |
Date and time
For storing date and time you can one of the following alternatives:
- TEXT. As ISO8601 strings
"YYYY-MM-DD HH:MM:SS.SSS"
. - REAL. The number of days since noon in Greenwich on November 24, 4714 B.C.
- INTEGER. As Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Updates - New features
2022
- The json_nextract() function.
- Binary Operators
->
and->>
. The -> operator works like the two-argument version of json_nextract() and the -» operator works like the two-argument version of json_extract().
Source: https://www.sqlite.org/datatype3.html
all tags
IDE architecture" cli crontab crypto csv database docker editor error exceptions fastapi ffmpeg filename git go history http iteration javascript linux logging me mysql numpy orm pdo pentaho php postgres python random rename rsync rust selenium server solana sql sqlite ssh typescript user w3m wordpress