13 ms·

A Minimalist Guide to SQLite

472 points by craigkerstiens 9y ago

CJefferson 9y ago
I feel this article misses out on "why". I tend to store stuff as csv or json, then slurp it into python to operate on it. It's not clear what benefit putting your csv into sqlite gets you, from this article.
- untog 9y ago
  For one, memory usage. You either have to load your entire CSV file into memory or read it line-by-line in order to get the result you're looking for. SQLite intelligently uses disk, memory and indexes for that purpose. Plus, you know, the whole SQL thing.
- liveoneggs 9y ago
  sounds wasteful and slow for anything larger than a few MB compared to the awesome (really) sqlite
- mannykannot 9y ago
  An explanation of the considerable benefits that relational databases bring to a wide class of problems is beyond the scope of an article on one particular implementation, and also beyond the scope of an HN comment.
- paulsutter 9y ago
  SQLite is when the data is a little too big for your approach, but not worth the hassle of installing/running/managing a database process. The flat files method may lead to partial or empty files in certain failure cases (crash during writes) that may or may not be more gracefully handled by SQLite.
- c_shu 9y ago
  So you can insert/delete/update records without rewriting the whole file. And you get many other features like transaction, spill cache pages, fflush & fsync behind the scenes.
- mythrwy 9y ago
  Sibling comments are on target but also the "relational" advantages of storing your data in a database.
- zackelan 9y ago
  "SQLite As An Application File Format" [0] and "What If OpenDocument Used SQLite?" [1], both by the author of SQLite, may help to answer why you'd use it over other file serialization formats, such as CSV, JSON, Python's pickle, etc. 0: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html 1: https://sqlite.org/affcase1.html https://sqlite.org/affcase1.html
- Avshalom 9y ago
  An sqlite db file is probably as light as your json or csv AND better formed/typed
  - hk__2 9y ago
    It’s easier to compare it to CSV than JSON, because the former is tabular while the latter can have deep imbrications that aren’t well represented in SQL(ite).
    - Avshalom 9y ago
      Can but probably won't.
      - hk__2 9y ago
        You don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; and one to join both.
        Avshalom 9y ago
        Not an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS
        voltagex_ 9y ago
        Yep, I can write an INNER JOIN faster than I can remember jq's arcane syntax. If you needed it all in one flat table for some reason, SQLite supports views.
        bklaasen 9y ago
        Speaking of which, I've not grokked jq's functional approach to composing queries. Can anyone suggest a good approach to understanding jq please?
- labster 9y ago
  I think this comment misses out on "why". I tend to store stuff in xlsx, then use Excel to operate on it. It's not clear what benefit Python or JSON gets you, from this post.
  - CJefferson 9y ago
    I never claimed to show advantages over excel. Excel is fine, enjoy it!
- danso 9y ago
  One advantage that may seem too obvious to mention: using SQLite allows you to work with data using SQL, which is far, far easier to learn (and be powerful with) than trying to learn Python or even R if you are new to languages. Even as an experienced programmer, I find SQL to almost always more elegant for expressing logic than Pandas or R. My workflow is usually having a script execute a SQL statement to do the heavy data work, and then importing the results of that SQL statement into another programming environment (e.g. R/ggplot2 for visualizations).
- ww520 9y ago
  Much easier to work on data with SQL. I can cleanse, deduplicate, fill in default, update, merge, join, and generate reports with a few SQL. I had an interest rate app where the backend data collection process consists of a shell script to run curl to download rates from various places as CSV, import CSV into Sqlite, runs a few SQL to clean up/merge/fix default/dedup the data. The process is idempotent and can add new data to existing data. At the end I got a long history of clean up-to-date interest rate data in a Sqlite table.
- geokon 9y ago
  and why SQLite and not something newer like datomic
echlebek 9y ago
SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.
- hasenj 9y ago
  I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.
  - deleted 9y ago
    [deleted]
  - hardwaresofton 9y ago
    So I'm actually doing this right now, I write about it on my blog. None of the apps I've written have hitten any kind of crazy traffic peak, so I started wondering why the hell I was using postgres or other database types after reading https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html. Seeing how simple SQLite has been for me to use has inspired me to write a bunch of dead-simple good-enough approximations for other tools that exist. High up on my list is a Graylog competitor that just uses SQLITE FTS (https://sqlite.org/fts3.html/https://sqlite.org/fts5.html https://sqlite.org/fts3.html/https://sqlite.org/fts5.html), that just is super easy to start and handles that micro-to-mid-size case before you really need something like graylog or ELK. Blog post if anyone's interested: https://vadosware.io/post/adding-sqlite-powered-fts-search-to-a-servant-powered-haskell-app/ https://vadosware.io/post/adding-sqlite-powered-fts-search-t...
    - xellisx 9y ago
      How does SQLite FTS compare to things like Sphinx and Elastic Search?
      - 9y ago
mistercow 9y ago
> Data locality can be greatly improved by storing a SQLite 3 database in memory instead of on disk My understanding (although I can no longer find the page in the sqlite3 docs) was that because of caching, using :memory: is unlikely to make much difference in practice.
- dotancohen 9y ago
  Depends where your disk is. Many virtual machines, e.g. Amazon EC2, keep their persistent storage (EBS on AWS) on an external medium, so disk reads have a bit of latency and writes take forever. Keeping the database in memory is a huge benefit on these systems.
- beagle3 9y ago
  For reading, for a small database, after the cache is warm, that's likely true. In all other cases, there is no avoiding disk access - which, on spinning rust, is slow.
hardwaresofton 9y ago
https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html Once you're ready get yourself a cup of the SQLite koolaid
danso 9y ago
Very nice! I'm in the middle of writing a book based on SQLite (the overall topic is data analysis with SQL, but SQLite is the medium) while I'm teaching it to students. I used to teach MySQL but it was just so goddamned hard to get it configured correctly on people's computers (I always hated when work had to be done on pre-configured computers lab rather than my own laptop), nevermind the server/client/daemon aspects of MySQL and other variants. While SQLite lacks polished (and free) GUIs, it's easier to explain on a sysops level (with sqlite files being similar to XLS files) without burying students in irrelevant tech details on the way to learning the power of SQL queries.
- Something1234 9y ago
  Have you tried sqlitebrowser? It's fantastic, I use it a lot. http://sqlitebrowser.org http://sqlitebrowser.org
  - danso 9y ago
    Yep, that's what I use. Because it is open-source and cross-platform -- not to mention, pretty high quality -- it has significantly increased the attractiveness of teaching SQLite. Previously, me and other SQLite teachers I knew would point students to the SQLite Manager plugin for Firefox, which is also free and open source, but a bit clunkier: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage... The DB Browser (they changed it from sqlitebrowser at the request of sqlite IIRC) also has in my experience, a very helpful and responsive dev team: https://github.com/sqlitebrowser/sqlitebrowser/issues https://github.com/sqlitebrowser/sqlitebrowser/issues
    - justinclift 9y ago
      > ... also has in my experience, a very helpful and responsive dev team. Thanks. We definitely try. :D
  - justinclift 9y ago
    Thank you. :) (I'm one of the dev's on that project)
placebo 9y ago
>It's used in systems as important as the Airbus A350 so it comes as no surprise the tests for SQLite 3 are aviation-grade I stumbled upon SQLite around 2001 and have been using and admiring this gem of a software since that time, but hearing that it is aviation-grade is definitely a surprise to me...
- FridgeSeal 9y ago
  From what I've heard it has one of the most complete and comprehensive test suites of any database. There's tests that simulate sudden power loss as well which is pretty neat.
krallin 9y ago
I love SQLite (and use it in a large number of places in our application stack!), but I feel that it suffers from a case of bad defaults. To name a few: - Foreign key checking (and cascading deletion for that matter) are turned off by default. You need to enable them using `PRAGMA foreign_keys = ON;`. - There are practically no downsides (and a number of upsides) to using the WAL journalling mode (at least for "use a local database and store it on the disk" use cases). The main one being that reads will conflict with writes if you don't enable it! (which is a problem in a multi-threaded environment) Unfortunately, that's another feature you must remember to enable: `PRAGMA journal_mode = WAL;` (for obvious reasons, this one "stays enabled" after you turn it on). - Full auto-vacuum cannot be enabled after you start writing to the database unless you enabled incremental auto-vacuum. If you're unsure, it's a good idea to enable incremental auto-vacuum to keep that option open. But, here again, that's not the default: you need `PRAGMA auto_vacuum = INCREMENTAL`. This tends to be explicitly problematic if you have to perform one or more ad-hoc queries using the SQLite command line on an app's database, and forget to apply the relevant PRAGMAs! I wish there was a way to add "default" PRAGMAs on a sqlite database file to avoid this. (note: some of these defaults are configurable when compiling sqlite from scratch, but if you're dynamically linking with an OS-provided instance of the library, you can't really do that).
- shoeb_uddin 9y ago
  nice http://ictset.com/skin-care/ http://ictset.com/skin-care/
- shoeb_uddin 9y ago
  http://ictset.com/skin-care/ http://ictset.com/skin-care/
- crest 9y ago
  The disadvantage of enabling WAL is that your database just turned into two files and you have to recover the database to read it. Recovery is fast but it requires write access.
ilitirit 9y ago
Sqlite is great as data store for storing JSON. We receive realtime data from different sources in JSON format. I dump the raw data into MongoDB, but it's still not very easy to query. This is where Sqlite comes in handy. I import the data straight into Sqlite from MongoDB which then enables me to run SQL queries using the JSON1 extension. Currently I'm using it to store data that drive feed simulators. We can extract the data using queries like: SELECT json_extract(RawData, '$.sourceId') SourceId , json_extract(RawData, '$.messageId') MessageId , json_extract(RawData, '$.message') Message , json_extract(RawData, '$.message.parties[0].name') Party , json_extract(RawData, '$.timestamp') Timestamp FROM RawFeed JOIN Source ON SourceId = Source.Id WHERE Source.Name = 'Foo Company' ORDER BY Timestamp
- zepolen 9y ago
  Don't get me wrong, I was hating mongo before it was cool to do so, but your example is one place which makes zero sense, just learn to write a mongo query already or use SQL straight up.
- rockostrich 9y ago
  You could also just structure your data in MongoDB in a way that creates an index on source id and name? This just makes it seem like you're trying to fit a square peg in a round hole.
microtherion 9y ago
One of my favorite sqlite features is the vtable mechanism: https://sqlite.org/vtab.html https://sqlite.org/vtab.html It makes it possible to expose a custom, application specific database format as a sqlite table, and suddenly you can run SQL queries on your data!
- masklinn 9y ago
  Postgres also has that concept, though calls it Foreign Data Wrappers: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers And it goes beyond single tables e.g. PG-Strom uses FDW to implement GPGPU scans, joins, aggregations & projections (and provides a pg/CUDA while at it)
- chrisweekly 9y ago
  Yes, this! Vtables are how [lnav](http://lnav.org http://lnav.org) works its magic. Embedded SQLite makes so much sense for such use cases.
sigzero 9y ago
What does "aviation-grade" mean in a software context?
- otterpro 9y ago
  It means that software is robust enough to be safely deployed on airplanes or vehicles so that safety is not compromised. Imagine if there were some bugs in the software that could potentially endanger safety of plane full of people.
jokoon 9y ago
Only thing that I miss in a database software is some sort of spatial indexing implementation. I wish sqlite would have one by default.
- CaptainDecisive 9y ago
  The SpatiaLite extension (https://www.gaia-gis.it/fossil/libspatialite/index https://www.gaia-gis.it/fossil/libspatialite/index) handles spatial data and includes indexing. It's solid and we've been using it in production for years. The only real downside I've experienced is that the SpatiaLite-gui browser is a bit flaky.
vram22 9y ago
I wonder why the Firebird database is not mentioned more often in such threads. It is a somewhat lightweight RDBMS that still has many advanced features that SQLite may not (by design, of course). It may be positioned somewhere between SQLite and heavyweight databases like MySQL, PostgreSQL, Oracle, DB2, etc. I've used Firebird, though only lightly, with Python. It also has different (lighter/heavier) versions for different needs. https://en.wikipedia.org/wiki/Firebird_(database_server) https://en.wikipedia.org/wiki/Firebird_(database_server) https://firebirdsql.org/ https://firebirdsql.org/ A few excerpts from this page: Get to know Firebird in 2 minutes: https://www.firebirdnews.org/docs/fb2min.html https://www.firebirdnews.org/docs/fb2min.html [ Firebird is derived from Borland InterBase 6.0 source code. It is open source and has no dual license. Whether you need it for commercial or open source applications, it is totally FREE! Firebird technology has been in use for 20 years, which makes it a very mature and stable product. Don’t be fooled by the installer size! Firebird is a fully featured and powerful RDBMS. It can handle databases from just a few KB to many Gigabytes with good performance and almost free of maintenance! Below is a list of some of the Firebird’s major features: Full support of Stored Procedures and Triggers Full ACID compliant transactions Referential Integrity Multi Generational Architecture Very small footprint Fully featured internal language for Stored Procedures and Triggers (PSQL) Support for External Functions (UDFs) Little or no need for specialized DBAs Almost no configuration needed - just install and start using! Big community and lots of places where you can get free and good support Optional single file embedded version - great to create CDROM catalogs, single user or evaluation versions of applications Dozens of third party tools, including GUI administrative tools, replication tools, etc. Careful writes - fast recovery, no need for transaction logs! Many ways to access your database: native/API, dbExpress drivers, ODBC, OLEDB, .Net provider, JDBC native type 4 driver, Python module, PHP, Perl, etc. Native support for all major operating systems, including Windows, Linux, Solaris, MacOS, HP-UX and FreeBSD. Incremental Backups 64bits builds available Full cursor implementation in PSQL Monitoring tables Connection and Transaction Triggers Temporary Tables TraceAPI - know what happens in your server ] Disclaimer: I'm not associated with them in any way. Just have used the product a bit, and think it is somewhat good, based on that.
- vram22 9y ago
  Also, from: https://www.firebirdsql.org/en/features/ https://www.firebirdsql.org/en/features/ [ Firebird is free for commercial and educational usage: no license fees, installation or activation restrictions. No double licensing - Firebird license is based on Mozilla Public License. The mix of high performance, small footprint, supreme scalability, silent and simple installation and 100% royalty-free deployment make Firebird a highly attractive choice for all types of software developers and vendors. It is used by approximately 1 million of software developers worldwide. ]

I feel this article misses out on "why". I tend to store stuff as csv or json, then slurp it into python to operate on it. It's not clear what benefit putting your csv into sqlite gets you, from this article.

untog 9y ago
For one, memory usage. You either have to load your entire CSV file into memory or read it line-by-line in order to get the result you're looking for. SQLite intelligently uses disk, memory and indexes for that purpose. Plus, you know, the whole SQL thing.
liveoneggs 9y ago
sounds wasteful and slow for anything larger than a few MB compared to the awesome (really) sqlite
mannykannot 9y ago
An explanation of the considerable benefits that relational databases bring to a wide class of problems is beyond the scope of an article on one particular implementation, and also beyond the scope of an HN comment.
paulsutter 9y ago
SQLite is when the data is a little too big for your approach, but not worth the hassle of installing/running/managing a database process. The flat files method may lead to partial or empty files in certain failure cases (crash during writes) that may or may not be more gracefully handled by SQLite.
c_shu 9y ago
So you can insert/delete/update records without rewriting the whole file. And you get many other features like transaction, spill cache pages, fflush & fsync behind the scenes.
mythrwy 9y ago
Sibling comments are on target but also the "relational" advantages of storing your data in a database.
zackelan 9y ago
"SQLite As An Application File Format" [0] and "What If OpenDocument Used SQLite?" [1], both by the author of SQLite, may help to answer why you'd use it over other file serialization formats, such as CSV, JSON, Python's pickle, etc. 0: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html 1: https://sqlite.org/affcase1.html https://sqlite.org/affcase1.html
Avshalom 9y ago
An sqlite db file is probably as light as your json or csv AND better formed/typed
- hk__2 9y ago
  It’s easier to compare it to CSV than JSON, because the former is tabular while the latter can have deep imbrications that aren’t well represented in SQL(ite).
  - Avshalom 9y ago
    Can but probably won't.
    - hk__2 9y ago
      You don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; and one to join both.
      - Avshalom 9y ago
        Not an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS
        voltagex_ 9y ago
        Yep, I can write an INNER JOIN faster than I can remember jq's arcane syntax. If you needed it all in one flat table for some reason, SQLite supports views.
        bklaasen 9y ago
        Speaking of which, I've not grokked jq's functional approach to composing queries. Can anyone suggest a good approach to understanding jq please?
labster 9y ago
I think this comment misses out on "why". I tend to store stuff in xlsx, then use Excel to operate on it. It's not clear what benefit Python or JSON gets you, from this post.
- CJefferson 9y ago
  I never claimed to show advantages over excel. Excel is fine, enjoy it!
danso 9y ago
One advantage that may seem too obvious to mention: using SQLite allows you to work with data using SQL, which is far, far easier to learn (and be powerful with) than trying to learn Python or even R if you are new to languages. Even as an experienced programmer, I find SQL to almost always more elegant for expressing logic than Pandas or R. My workflow is usually having a script execute a SQL statement to do the heavy data work, and then importing the results of that SQL statement into another programming environment (e.g. R/ggplot2 for visualizations).
ww520 9y ago
Much easier to work on data with SQL. I can cleanse, deduplicate, fill in default, update, merge, join, and generate reports with a few SQL. I had an interest rate app where the backend data collection process consists of a shell script to run curl to download rates from various places as CSV, import CSV into Sqlite, runs a few SQL to clean up/merge/fix default/dedup the data. The process is idempotent and can add new data to existing data. At the end I got a long history of clean up-to-date interest rate data in a Sqlite table.
geokon 9y ago
and why SQLite and not something newer like datomic

echlebek 9y ago

SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.

hasenj 9y ago
I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.
- deleted 9y ago
  [deleted]
- hardwaresofton 9y ago
  So I'm actually doing this right now, I write about it on my blog. None of the apps I've written have hitten any kind of crazy traffic peak, so I started wondering why the hell I was using postgres or other database types after reading https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html. Seeing how simple SQLite has been for me to use has inspired me to write a bunch of dead-simple good-enough approximations for other tools that exist. High up on my list is a Graylog competitor that just uses SQLITE FTS (https://sqlite.org/fts3.html/https://sqlite.org/fts5.html https://sqlite.org/fts3.html/https://sqlite.org/fts5.html), that just is super easy to start and handles that micro-to-mid-size case before you really need something like graylog or ELK. Blog post if anyone's interested: https://vadosware.io/post/adding-sqlite-powered-fts-search-to-a-servant-powered-haskell-app/ https://vadosware.io/post/adding-sqlite-powered-fts-search-t...
  - xellisx 9y ago
    How does SQLite FTS compare to things like Sphinx and Elastic Search?
    - 9y ago

mistercow 9y ago

> Data locality can be greatly improved by storing a SQLite 3 database in memory instead of on disk My understanding (although I can no longer find the page in the sqlite3 docs) was that because of caching, using :memory: is unlikely to make much difference in practice.

dotancohen 9y ago
Depends where your disk is. Many virtual machines, e.g. Amazon EC2, keep their persistent storage (EBS on AWS) on an external medium, so disk reads have a bit of latency and writes take forever. Keeping the database in memory is a huge benefit on these systems.
beagle3 9y ago
For reading, for a small database, after the cache is warm, that's likely true. In all other cases, there is no avoiding disk access - which, on spinning rust, is slow.

hardwaresofton 9y ago

https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html Once you're ready get yourself a cup of the SQLite koolaid

danso 9y ago

Very nice! I'm in the middle of writing a book based on SQLite (the overall topic is data analysis with SQL, but SQLite is the medium) while I'm teaching it to students. I used to teach MySQL but it was just so goddamned hard to get it configured correctly on people's computers (I always hated when work had to be done on pre-configured computers lab rather than my own laptop), nevermind the server/client/daemon aspects of MySQL and other variants. While SQLite lacks polished (and free) GUIs, it's easier to explain on a sysops level (with sqlite files being similar to XLS files) without burying students in irrelevant tech details on the way to learning the power of SQL queries.

Something1234 9y ago
Have you tried sqlitebrowser? It's fantastic, I use it a lot. http://sqlitebrowser.org http://sqlitebrowser.org
- danso 9y ago
  Yep, that's what I use. Because it is open-source and cross-platform -- not to mention, pretty high quality -- it has significantly increased the attractiveness of teaching SQLite. Previously, me and other SQLite teachers I knew would point students to the SQLite Manager plugin for Firefox, which is also free and open source, but a bit clunkier: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage... The DB Browser (they changed it from sqlitebrowser at the request of sqlite IIRC) also has in my experience, a very helpful and responsive dev team: https://github.com/sqlitebrowser/sqlitebrowser/issues https://github.com/sqlitebrowser/sqlitebrowser/issues
  - justinclift 9y ago
    > ... also has in my experience, a very helpful and responsive dev team. Thanks. We definitely try. :D
- justinclift 9y ago
  Thank you. :) (I'm one of the dev's on that project)

placebo 9y ago

>It's used in systems as important as the Airbus A350 so it comes as no surprise the tests for SQLite 3 are aviation-grade I stumbled upon SQLite around 2001 and have been using and admiring this gem of a software since that time, but hearing that it is aviation-grade is definitely a surprise to me...

FridgeSeal 9y ago
From what I've heard it has one of the most complete and comprehensive test suites of any database. There's tests that simulate sudden power loss as well which is pretty neat.

krallin 9y ago

I love SQLite (and use it in a large number of places in our application stack!), but I feel that it suffers from a case of bad defaults. To name a few: - Foreign key checking (and cascading deletion for that matter) are turned off by default. You need to enable them using `PRAGMA foreign_keys = ON;`. - There are practically no downsides (and a number of upsides) to using the WAL journalling mode (at least for "use a local database and store it on the disk" use cases). The main one being that reads will conflict with writes if you don't enable it! (which is a problem in a multi-threaded environment) Unfortunately, that's another feature you must remember to enable: `PRAGMA journal_mode = WAL;` (for obvious reasons, this one "stays enabled" after you turn it on). - Full auto-vacuum cannot be enabled after you start writing to the database unless you enabled incremental auto-vacuum. If you're unsure, it's a good idea to enable incremental auto-vacuum to keep that option open. But, here again, that's not the default: you need `PRAGMA auto_vacuum = INCREMENTAL`. This tends to be explicitly problematic if you have to perform one or more ad-hoc queries using the SQLite command line on an app's database, and forget to apply the relevant PRAGMAs! I wish there was a way to add "default" PRAGMAs on a sqlite database file to avoid this. (note: some of these defaults are configurable when compiling sqlite from scratch, but if you're dynamically linking with an OS-provided instance of the library, you can't really do that).

shoeb_uddin 9y ago
nice http://ictset.com/skin-care/ http://ictset.com/skin-care/
shoeb_uddin 9y ago
http://ictset.com/skin-care/ http://ictset.com/skin-care/
crest 9y ago
The disadvantage of enabling WAL is that your database just turned into two files and you have to recover the database to read it. Recovery is fast but it requires write access.

ilitirit 9y ago

Sqlite is great as data store for storing JSON. We receive realtime data from different sources in JSON format. I dump the raw data into MongoDB, but it's still not very easy to query. This is where Sqlite comes in handy. I import the data straight into Sqlite from MongoDB which then enables me to run SQL queries using the JSON1 extension. Currently I'm using it to store data that drive feed simulators. We can extract the data using queries like: SELECT json_extract(RawData, '$.sourceId') SourceId , json_extract(RawData, '$.messageId') MessageId , json_extract(RawData, '$.message') Message , json_extract(RawData, '$.message.parties[0].name') Party , json_extract(RawData, '$.timestamp') Timestamp FROM RawFeed JOIN Source ON SourceId = Source.Id WHERE Source.Name = 'Foo Company' ORDER BY Timestamp

zepolen 9y ago
Don't get me wrong, I was hating mongo before it was cool to do so, but your example is one place which makes zero sense, just learn to write a mongo query already or use SQL straight up.
rockostrich 9y ago
You could also just structure your data in MongoDB in a way that creates an index on source id and name? This just makes it seem like you're trying to fit a square peg in a round hole.

microtherion 9y ago

One of my favorite sqlite features is the vtable mechanism: https://sqlite.org/vtab.html https://sqlite.org/vtab.html It makes it possible to expose a custom, application specific database format as a sqlite table, and suddenly you can run SQL queries on your data!

masklinn 9y ago
Postgres also has that concept, though calls it Foreign Data Wrappers: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers And it goes beyond single tables e.g. PG-Strom uses FDW to implement GPGPU scans, joins, aggregations & projections (and provides a pg/CUDA while at it)
chrisweekly 9y ago
Yes, this! Vtables are how [lnav](http://lnav.org http://lnav.org) works its magic. Embedded SQLite makes so much sense for such use cases.

sigzero 9y ago

What does "aviation-grade" mean in a software context?

otterpro 9y ago
It means that software is robust enough to be safely deployed on airplanes or vehicles so that safety is not compromised. Imagine if there were some bugs in the software that could potentially endanger safety of plane full of people.

jokoon 9y ago

Only thing that I miss in a database software is some sort of spatial indexing implementation. I wish sqlite would have one by default.

CaptainDecisive 9y ago
The SpatiaLite extension (https://www.gaia-gis.it/fossil/libspatialite/index https://www.gaia-gis.it/fossil/libspatialite/index) handles spatial data and includes indexing. It's solid and we've been using it in production for years. The only real downside I've experienced is that the SpatiaLite-gui browser is a bit flaky.

vram22 9y ago

I wonder why the Firebird database is not mentioned more often in such threads. It is a somewhat lightweight RDBMS that still has many advanced features that SQLite may not (by design, of course). It may be positioned somewhere between SQLite and heavyweight databases like MySQL, PostgreSQL, Oracle, DB2, etc. I've used Firebird, though only lightly, with Python. It also has different (lighter/heavier) versions for different needs. https://en.wikipedia.org/wiki/Firebird_(database_server) https://en.wikipedia.org/wiki/Firebird_(database_server) https://firebirdsql.org/ https://firebirdsql.org/ A few excerpts from this page: Get to know Firebird in 2 minutes: https://www.firebirdnews.org/docs/fb2min.html https://www.firebirdnews.org/docs/fb2min.html [ Firebird is derived from Borland InterBase 6.0 source code. It is open source and has no dual license. Whether you need it for commercial or open source applications, it is totally FREE! Firebird technology has been in use for 20 years, which makes it a very mature and stable product. Don’t be fooled by the installer size! Firebird is a fully featured and powerful RDBMS. It can handle databases from just a few KB to many Gigabytes with good performance and almost free of maintenance! Below is a list of some of the Firebird’s major features: Full support of Stored Procedures and Triggers Full ACID compliant transactions Referential Integrity Multi Generational Architecture Very small footprint Fully featured internal language for Stored Procedures and Triggers (PSQL) Support for External Functions (UDFs) Little or no need for specialized DBAs Almost no configuration needed - just install and start using! Big community and lots of places where you can get free and good support Optional single file embedded version - great to create CDROM catalogs, single user or evaluation versions of applications Dozens of third party tools, including GUI administrative tools, replication tools, etc. Careful writes - fast recovery, no need for transaction logs! Many ways to access your database: native/API, dbExpress drivers, ODBC, OLEDB, .Net provider, JDBC native type 4 driver, Python module, PHP, Perl, etc. Native support for all major operating systems, including Windows, Linux, Solaris, MacOS, HP-UX and FreeBSD. Incremental Backups 64bits builds available Full cursor implementation in PSQL Monitoring tables Connection and Transaction Triggers Temporary Tables TraceAPI - know what happens in your server ] Disclaimer: I'm not associated with them in any way. Just have used the product a bit, and think it is somewhat good, based on that.

vram22 9y ago
Also, from: https://www.firebirdsql.org/en/features/ https://www.firebirdsql.org/en/features/ [ Firebird is free for commercial and educational usage: no license fees, installation or activation restrictions. No double licensing - Firebird license is based on Mozilla Public License. The mix of high performance, small footprint, supreme scalability, silent and simple installation and 100% royalty-free deployment make Firebird a highly attractive choice for all types of software developers and vendors. It is used by approximately 1 million of software developers worldwide. ]