Use as a fan in server from a parallel computation …

[adonoho]

Gentlefolk,

I'm a big fan of SQLite. I am designing an experiment management system and am considering using SQLite as the core storage engine. Part of the design requires a data URL to allow data sources/computational nodes to write intermediate and final results to the management server. As sqlite-jdbc looks like a natural network service for my architecture, I have a few questions and hope this community can provide me some guidance.

First a few notes/assumptions/observations:

• Starting my scaling journey by planning to use a single SQLite db per data source.
  • I can coalesce these databases later, if necessary. By keeping them separate, I get an interesting archive property.
• Assuming sqlite-jdbc utilizes Java threading to open SQLite DB files on unique threads.
  • This would allow multi-threaded writes to the server while preserving single threaded semantics to SQLite. Currently, no plan is needed for two data sources to write to the same database.
  • While most writes will be large dumps of computational data frames, one could easily imagine both diagnostic logging and realtime data capture being performed. While write conflicts would be minimal, write load could be large.
• Assuming sqlite-jdbc will open files created by a separate app at the location in the jdbc: URL.
  • This allows the management app to make partitioning decisions and the JDBC server to concentrate on serving and capturing data.

Now my questions:

1. Have people successfully used SQLAlchemy in Python to interact with sqlite-jdbc? Many data science applications use PANDAS, which uses SQLAlchemy.
2. Is my threading assumption above valid?
3. sqlite-jdbc looks like it could easily be deployed in a Docker/containerd system. Has this been done in production?
4. If it has been deployed in production, how are security and access control decisions made?

Thank you in advance for any information you may care to share.

Anon,
Andrew

[gotson]

• Have people successfully used SQLAlchemy in Python to interact with sqlite-jdbc? Many data science applications use PANDAS, which uses SQLAlchemy.

There's native Python libraries for SQLite. sqlite-jdbc is for JDBC, so Java.

• sqlite-jdbc looks like it could easily be deployed in a Docker/containerd system. Has this been done in production?

Docker is only a fancy packaging/ring-fencing system, it doesn't change how the application behaves.

4. If it has been deployed in production, how are security and access control decisions made?

This is completely irrelevant to this project. Security and access control depends on what you want, not the project. You should check what SQLite offers and what JDBC offers, and if that suits you.