4 ms·

Side note: Sqlite mem-only DBs are amazing for working with dataset you want to load then get counts and groups and other fun things. Loops and other stuff in y

by edoceo 5y ago

Side note: Sqlite mem-only DBs are amazing for working with dataset you want to load then get counts and groups and other fun things. Loops and other stuff in your code get replaced by (cleaner?) SQL. It's also way easier to see what you were doing 6mo later and the performance is not terrible.

pletnes 5y ago
I’ve heard this before, but what do you get over pandas, which has groupby, filtering etc? Skip one dependency? Usage in other languages than python?
- atoav 5y ago
  Sqlite has probably much wider (and more stable) support with other languages than pandas. Also it is a different thing. Pandas is very nice to do data analytics or crunch numbers on reocurring data, but I wouldn't replace a database with it.
  - pletnes 5y ago
    I agree that it can’t replace a database, for most use cases. But an in-memory database I’ve yet to find a «real» usecase for.
- dgb23 5y ago
  If pandas gives you a full relational API with arbitrary data then in isolation it doesn’t make much of a difference. SQL is more portable so to speak, but a library like that may introduce less friction. Pragmatism is advised here. The big idea here is to use relational logic programming to express data transformation outside of storage access. The paper „Out of the Tar Pit“ proposed this as a way to reduce accidental complexity.
- selimnairb 5y ago
  I recently started using pandas to do groupby and aggregation. It’s nice to have, but the whole time I kept wishing it would just let me run an SQL query without adding another dependency. Having learned SQL long ago, I find it to be much more intuitive and expressive. I guess it’s all just what you know.
  - pletnes 5y ago
    Pandas does have read/write to sqlite. You can dump a dataframe to sqlite, transform, then load from sql again. If it’s worth it depends on your case I guess.
bob1029 5y ago
We use SQLite in-memory databases for executing 100% of our business logic these days. Letting the business write all the rules in SQL is the biggest win of my career so far. Also, if you think SQLite might be too constrained for your business case, you can expose any arbitrary application function to it. E.g.: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite... The very first thing we did was pipe DateTime into SQLite as a UDF. Imagine instantly having the full power of .NET6 available from inside SQLite. Note that these functions do NOT necessarily have to avoid side effects either. You can use a procedural DSL via SELECT statements that invokes any arbitrary business method with whatever parameters from the domain data. The process is so simple I am actually disappointed that we didn't think of it sooner. You just put a template database in memory w/ the schema pre-loaded, then make a copy of this each time you want to map domain state for SQL execution. You can do conditionals, strings, arrays of strings, arrays of CSVs, etc. Any shape of thing you need to figure out a conditional or dynamic presentation of business facts. Oh and you can also use views to build arbitrary layers of abstraction so the business can focus on their relevant pieces.
- jiofih 5y ago
  What data size are you dealing with? I imagine this gets prohibitive in both performance and cost as it gets into the high GBs.
  - bob1029 5y ago
    Very small data sizes. Each database is ephemeral and only scoped to a specific user's session of work. We didn't even bother with any indexing, because there are very few tables where we would exceed 100 rows.
  - simonw 5y ago
    My rule of thumb at the moment is that for anything up to 10GB of data SQLite basically Just Works. For 10-100GB it will work if you design your indexes carefully. Above 100GB gets harder - my hunch is that there are various tricks and optimizations I've not yet discovered to help make that work OK, but switching to PostgreSQL is probably easier for that kind of scale.

by edoceo 5y ago

pletnes 5y ago

I’ve heard this before, but what do you get over pandas, which has groupby, filtering etc? Skip one dependency? Usage in other languages than python?

atoav 5y ago
Sqlite has probably much wider (and more stable) support with other languages than pandas. Also it is a different thing. Pandas is very nice to do data analytics or crunch numbers on reocurring data, but I wouldn't replace a database with it.
- pletnes 5y ago
  I agree that it can’t replace a database, for most use cases. But an in-memory database I’ve yet to find a «real» usecase for.
dgb23 5y ago
If pandas gives you a full relational API with arbitrary data then in isolation it doesn’t make much of a difference. SQL is more portable so to speak, but a library like that may introduce less friction. Pragmatism is advised here. The big idea here is to use relational logic programming to express data transformation outside of storage access. The paper „Out of the Tar Pit“ proposed this as a way to reduce accidental complexity.
selimnairb 5y ago
I recently started using pandas to do groupby and aggregation. It’s nice to have, but the whole time I kept wishing it would just let me run an SQL query without adding another dependency. Having learned SQL long ago, I find it to be much more intuitive and expressive. I guess it’s all just what you know.
- pletnes 5y ago
  Pandas does have read/write to sqlite. You can dump a dataframe to sqlite, transform, then load from sql again. If it’s worth it depends on your case I guess.

bob1029 5y ago

We use SQLite in-memory databases for executing 100% of our business logic these days. Letting the business write all the rules in SQL is the biggest win of my career so far. Also, if you think SQLite might be too constrained for your business case, you can expose any arbitrary application function to it. E.g.: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite... The very first thing we did was pipe DateTime into SQLite as a UDF. Imagine instantly having the full power of .NET6 available from inside SQLite. Note that these functions do NOT necessarily have to avoid side effects either. You can use a procedural DSL via SELECT statements that invokes any arbitrary business method with whatever parameters from the domain data. The process is so simple I am actually disappointed that we didn't think of it sooner. You just put a template database in memory w/ the schema pre-loaded, then make a copy of this each time you want to map domain state for SQL execution. You can do conditionals, strings, arrays of strings, arrays of CSVs, etc. Any shape of thing you need to figure out a conditional or dynamic presentation of business facts. Oh and you can also use views to build arbitrary layers of abstraction so the business can focus on their relevant pieces.

jiofih 5y ago
What data size are you dealing with? I imagine this gets prohibitive in both performance and cost as it gets into the high GBs.
- bob1029 5y ago
  Very small data sizes. Each database is ephemeral and only scoped to a specific user's session of work. We didn't even bother with any indexing, because there are very few tables where we would exceed 100 rows.
- simonw 5y ago
  My rule of thumb at the moment is that for anything up to 10GB of data SQLite basically Just Works. For 10-100GB it will work if you design your indexes carefully. Above 100GB gets harder - my hunch is that there are various tricks and optimizations I've not yet discovered to help make that work OK, but switching to PostgreSQL is probably easier for that kind of scale.