I tried this out but I dont know what's going wrong. Here is what I am doing.

I am using sqlite-jdbc. I am getting a connection as below. (Please dont look at the syntax since I have typed it out here as an example)

SQLiteConfig sqliteConfig = new SQLiteConfig();
sqliteConfig.setMaxPageCount(100);
Connection connection = DriverManager.getConnection("jdbc:sqlite:", sqliteConfig.toProperties()); // No file name in the url so that a temporary database is created

Now when I insert certain number of records into a table an error is getting thrown saying that database or disk is full. My understanding is that sqlite is supposed to write the overflowing data into disk in a temp file.

Nothing is wrong except your expectations.

see https://sqlite.org/c3ref/open.html

"If the filename is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed."

You are asking for a private, temporary on-disk database with no more than 100 pages. SQLite is telling you that you need more space to store the number of records you desire.

"If the filename is ":memory:", then a private, temporary in-memory database is created for the connection. This in-memory database will vanish when the database connection is closed."

This would locate the private, temporary database in memory (the same effect might be achieved if you add SQLITE_OPEN_MEMORY to the flags parameter of sqlite3_open_v2() by whatever method the wrapper you are using requires). It does not overflow to disk if it exceeds the max page count you set.

Maybe you are actually trying to limit the amount of process memory that SQLite uses. You need to read:

https://sqlite.org/c3ref/c_config_covering_index_scan.html 
https://sqlite.org/pragma.html#pragma_hard_heap_limit
https://sqlite.org/pragma.html#pragma_soft_heap_limit

Thanks for the reply. I understand that the temp file will be deleted once the connection is closed. What I am trying to understand is how to achieve the below scenario where-in sqlite will flush to disk which is mentioned in the official documentation.

"The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if the database becomes large or if SQLite comes under memory pressure"

My understanding is that I can configure sqlite such that it can only consume X gb of memory and if I try to insert data which exceeds X gb of memory then sqlite will flush that data to disk in a temp file. Please let me know if my understanding is correct.

And if my understanding is correct what are the supposed configurations? In one of the answers max_page_count was mentioned and that is what I was trying to test.

The max page count limits the size of the database (irrespective of where it is stored).

It seems you are equating "memory use" with "database size". This is incorrect. Limiting the size of a database only indirectly limits the amount of memory used by SQLite, as it is not possible to cache more pages from a database (file or memory) than exist in that database.

For systems where memory use is constrained you can:

- use sqlite3_hard_heap_limit() (or invoke the corresponding pragma) to set a barrier for ALL memory used by SQLite

- use sqlite3_config(SQLITE_CONFIG_MALLOC,...) to replace the memory allocation routines with your own implementation.

- use sqlite3_config(SQLITE_CONFIG_PAGECACHE,...) to configure the size of the standard page cache. This is a per-connection attribute. Each connection will allocate its own page cache (usually...)

- use sqlite3_config(SQLITE_CONFIG_HEAP,...) to configure the memory available for general use by SQLite outside of the page cache

- use sqlite3_config(SQLITE_CONFIGE_PCACHE2,...) to replace the page cache routines with your own implementation

I am not familier with sqlite-jdbc. I don't know if it supports calling sqlite3_config.

There is no setting to limit the amount of memory to use for a specific attached database.

I suspect that you may falling into the "premature optimization" trap. Get your application working first, bother about memory usage if/when the issue arises.

Since you have neglected to provide any infomation on the OS you are running on, I can only refer you to your OS documentation on how to monitor memory usage of a process.

Sorry for not providing complete information. But you are right. I am looking to restrict memory usage. I will go back and do some POCs. Thanks for the information.

Answer to my question so that it helps others who are looking for the same thing.

Till now I have not been able to find anything which suggests that there is some kind of configuration which limits memory usage and persists the overflowing data in temporary file in disk (and most probably there is no configuration of such sort). But SQLite does this on its own. To test this I wrote a simple program (java) with a for loop to insert data into a table in temporary database and at the same time analyzing the memory usage using a tool (visualvm). No matter how many records were inserted the memory consumption didnt go up beyond the memory allocated to the running application.

Your solution is probably a result of external constraints on memory usage, i.e. calls to malloc failing if they attempt to exceed "memory allocated to the running application".