In praise of SQLite

Discussion in 'Programming' started by nooby_mcnoob, Mar 4, 2019.

  1. I know everyone likes to use the latest thing but I've got SQLite running for Spartan (my trading/backtest platform) where it is currently:

    1. Managing and adding to 7 million rows of quotes at the rate of about 1 million/day
    2. Allowing complex instantaneous queries from the same DB for backtesting
    3. Updating bar data

    All 3 of these are occuring at the same instant and SQLite barely hiccups.

    Don't knock it till you try it.

    sqlite> select date(datetime),count(*) from quote group by date(datetime);
    The database is around 1G right now.
    fan27 likes this.
  2. R1234


    SQLite user here. I agree - very lightweight/fast/stable. I use it linked up to Python.
    nooby_mcnoob likes this.
  3. IAS_LLC


    Also a an sqlite(3) it. Only issue that you may run into is concurrent read/writes,if you end up needing that...
    nooby_mcnoob likes this.
  4. You're right but I find that as long as you're not concurrently writing to the same table, you're fine. Which makes sense.
  5. Yup, it's all I use for my IB API apps as well. I use it mainly for storing my transaction logs for PnL tracking and risk graphs.
    nooby_mcnoob likes this.
  6. 2rosy


    agreed. i like it for tests where you can grab the schema/data from a large database and load into it also, the in memory option is good for embedding; saw it used in multiplayer games
    nooby_mcnoob likes this.
  7. Never heard of odo, very cool!
  8. I'm running 3 processes continuously: bar import, tick recording and backtesting from the same database. I ran into this issue today while backtesting as the backtester kept the connection open for the duration of the backtest. I had to refactor things to limit SQLite connection scope and everything is ticking along perfectly fine, and in fact, seems a lot faster. So just FYI, that's a potential workaround as well.
    IAS_LLC likes this.