sqlite - In Memory Database

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• Diff
• Text

This page is about using in-memory SQLite databases.

---

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.

---

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?

---

---

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