Connection pooling is a technique that involves precreating and reusing a number of ever-open DB connections instead of opening connections on demand. Since opening and closing connections takes more time than passing open connections around, this technique is used to improve performance of web application under high load.
Norm offers a simple thread-safe connection pool implementation. It can be used with both Postgres and SQLite, and you even can create multiple pools if you need to.
Important Connection pooling requires
To use connection pool:
newPool creates a pool of a given size with connections of type
DbConn (either from
postgres). The params for the connections are taken from the environment, similar to how
withDb works (see Configuration from Environment).
import norm/[model, sqlite, pool] type Product = ref object of Model name: string price: float proc newProduct(): Product = Product(name: "", price: 0.0) putEnv("DB_HOST", ":memory:") var connPool = newPool[DbConn](10) withDb(connPool): db.createTables(newProduct())
CREATE TABLE IF NOT EXISTS "Product"(name TEXT NOT NULL, price FLOAT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
Pool Exhausted Policy
If the app requests more connections from the pool than it can give, we say the pool is exhausted.
There are two ways a pool can react to that:
- raise a
PoolExhaustedError; this is the default policy
- open an additional connection and extend the pool size
The policy is set during the pool creation by setting
poolExhaustedPolicy param to either
To reset the pool back to its default size after it has been extended, call
reset proc on it.
Manual Pool Manipulation
You can borrow connections from the pool manually by calling
Important If you choose to get connections from the pool manually, you must care about putting the borrowed connections back byb calling
let dbConn = connPool.pop() var product = newProduct() product.name = "Table" product.price = 123.45 dbConn.insert(product) connPool.add(dbConn)
INSERT INTO "Product" (name, price) VALUES(?, ?) <- @['Table', 123.45]
Closing the Pool
When you no longer need the pool, for example, when your app exits or crashes, to avoid leaving hanging connections, close the pool by calling
close. This proc closes all connections in the pool and sets its size to 0.
Custom Connection Provider
By default, new connections are added to the pool by calling
getDB, which takes the DB params from the environment.
But you can override that. For example, to get one pool connected to one DB and another one connected to another one.
To do that, pass a function that returns
DbConn to the Pool constructor:
func myDb: DbConn = open("mydb.db", "", "", "") var anotherPool = newPool[DbConn](10, myDb) assert anotherPool.size == 10 assert fileExists("mydb.db") close anotherPool removeFile("mydb.db")