CODEWITHSUNDEEP
javasqlite
tmn
tmn

Reputation: 11559

SQLite- Read-only, low volume over network?

It is getting burdensome on my team to prototype tables in MySQL that back our Java business applications, so I'm campaigning to use SQLite to prototype new tables and test them in our applications.

These tables are usually lightweight parameters, holding 12 to 1000 records at most. When the Java applications use them they are likely to be doing so in a read-only capacity, and typically the data is ingested in memory and never read again.

Would I have a problem putting these prototype SQLite tables out on a network, as long as they are accessed via read-only and in small volume? Or should I copy them locally to everyone's machines? I know SQLite does not encourage concurrent access on a network, but I'd be surprised if more than one user would hit it a the same time given the number of users and the way our applications are architected.

Upvotes: 0

Views: 87

Answers (1)

Pau Carre
Pau Carre

Reputation: 471

If you are using a three-layer architecture, only the application server should have access to the database server. Therefore, you should have control over the connections (i.e. you can create a very small connection pool). Embedded databases are not suited for lots (hundreds) of concurrent connections. Nevertheless, having into account the amount of data and that you will only focus on read-only queries, I doubt that would be a problem.

A major problem I foresee is that you can have serious problems in terms of SQL dialects. Usually embedded databases use the ANSI SQL standard, but mySQL and others allow you to use their own SQL dialects which are incompatible. It's usually a good practice to have a unit test that runs all the SQL queries against an embedded database to guarantee that they are ANSI-compliant. This way, you have a guarantee that you can use your application (automatically or manually) with the embedded database.

Upvotes: 1

Related Questions