This page is about using in-memory SQLite databases. ---- 2006-09-08: It appears that if you're doing a small number of transactions, that in-memory vs. on-disk doesn't make much difference. I have 138000 rows to insert, and as long as I do them all inside of one transaction, the run time is pretty much the same. However, if I don't use any transactions and just do a bunch of inserts, then the on-disk db is horribly slow compared to memory. Check out the note about transactions in PerformanceTuning. ---- 2006-06-28: The patch for SQLite 3.2.2 to add the dump feature is not working on SQLite 3.3.6 because the dirty list mechanism is changed. To work dumping again, substitute just one line in marking all pages dirty. pPg->dirty = 1; as makeDirty(pPg); ---- 2006-02-22: "If the name of the database is an empty string or the special name ":memory:" then a new database is created in memory. " NOTE: There is a difference between a database created with an empty string and a database created with string ":memory:"! For the one, created with "" there exists a temp file in the temp-folder under Windows. The performance of the ":memory:"-database is much better than that of the ""-database. This seems to be true on unix systems as well. A journal file will show up in /var/tmp with "" but not with ":memory:". ---- 2006-03-13: Is there a way to read a database into memory, perform operations on the in-memory copy, and then commit those changes back out to the hard copy? I suspect it has something to do with transactions? My objective is to vastly reduce the disk I/O operations. 2006-02-20: A simple TCL-Implementation for loading a DB into memory: proc loadDB {dbhandle filename} { if {$filename != ""} { #attach persistent DB to target DB $dbhandle eval "ATTACH DATABASE '$filename' AS loadfrom" #copy each table to the target DB foreach {tablename} [$dbhandle eval "SELECT name FROM loadfrom.sqlite_master WHERE type = 'table'"] { $dbhandle eval "CREATE TABLE '$tablename' AS SELECT * FROM loadfrom.'$tablename'" } #create indizes in loaded table foreach {sql_exp} [$dbhandle eval "SELECT sql FROM loadfrom.sqlite_master WHERE type = 'index'"] { $dbhandle eval $sql_exp } #detach the source DB $dbhandle eval {DETACH loadfrom} } } 2006-01-07: I want to read a database from file to memory and use it via :memory: - can I do that? (The DB is distributed on CD/DVD and it is accessed many times - reading it into memory would speed things up considerably.) ---- 2005-03-25: :memory: doesn't seem to work if using sqlite3_open16(), rather than sqlite3_open(). I assume this is a bug? ---- 2005-03-25: I want to create a database first in memory and then dump it on a file. How can I do this? 2005-07-03: I've written a patch for SQLite 3.2.2 to add the feature you asking for. ---- 2004-09-15: I've written an article about memory databases in combination with PHP: SQLite in- memory databases. ---- 2004-08-08: SQLite would be really useful for me if it supported shared memory databases, so that unlike ':memory', multiple processes could share the same in-memory database. I am currently using Turck MMcache to share data among PHP scripts; if I could use SQLite instead with similar performance it would be really good. 2005-11-02: If you are on Linux, perhaps you could use the shared memory filesystm (tmpfs). It looks just like a normal filesystem, except the Linux kernel stores it all in memory. Some distributions come out of the box with /dev/shm mounted in this way, so you can share memory just by sharing files in this directory. ---- 2003-04-15: The in-memory database is now in the CVS tree, though it is still mostly untested. 2003-05-09: In-memory databases are now a feature of the standard SQLite library. To open an in- memory database, use filename ":memory:". ---- _While connections to on-disk databases should not be carried across a Unix fork(), is there any reason not to do it with an in-memory database?_ (2003-10-21 by drh:) Not that I know of. DK: Of course, when you do the fork() you are creating a new copy of the in-memory db for the child process. So subsequent edits to the db in the child won't show up in the parents copy, and vice-versa. NgPS: Multiple connections to ":memory:" within a single process create a fresh database each time: * (defvar db (sql:connect '(":memory:") :database-type :sqlite :make-default nil :if-exists :new)) DB * (sql:execute-command "create table a (id integer primary key, x varchar)" :database db) T * (sql:execute-command "insert into a values (NULL, 'aaa')" :database db) T * (sql:execute-command "insert into a values (NULL, 'bbb')" :database db) T * (sql:execute-command "insert into a values (NULL, 'ccc')" :database db) T * (sql:query "select * from a" :database db) (("1" "aaa") ("2" "bbb") ("3" "ccc")) * (sql:query "select * from sqlite_master" :database db) (("table" "a" "a" "3" "create table a (id integer primary key, x varchar)")) * (defvar db2 (sql:connect '(":memory:") :database-type :sqlite :make-default nil :if-exists :old)) DB2 * (sql:query "select * from sqlite_master" :database db2) NIL