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.

    Code:
    sqlite> select date(datetime),count(*) from quote group by date(datetime);
    2019-02-28|139487
    2019-03-01|3335677
    2019-03-03|416006
    2019-03-04|3225579
    
    The database is around 1G right now.
     
    fan27 likes this.
  2. R1234

    R1234

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

    IAS_LLC

    Also a an sqlite(3) user....love 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

    2rosy

    agreed. i like it for tests where you can grab the schema/data from a large database and load into it http://odo.pydata.org/en/latest/. 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.