Frappe database connection design pattern

ahmed-madi · October 25, 2018, 7:05am

Hello, community,
Is there any design pattern used to handle the connection to a database such as an object pooling or Singleton design patterns if no, what is the purpose to don’t use a design pattern that could decrease the database connections.

thank you,

ahmadRagheb · October 25, 2018, 1:04pm

hi madi
can you add links from github to point it out … what is the current state of code and what are you referring to or should be ?

tmatteson · October 26, 2018, 1:25am

So the Frappe ORM/ Database API is being refactored to support Postgres. You’ll want to have a clear idea about what version you’re working with so that you don’t try to use pattern that used to exist or doesn’t yet exist. The only source for this kind of information until the new API is finalized is searching the repos.
Foe access to single doctypes, use the get_single_** family of APIs. The data is stored as a blob and has to be translated in and out.

MariaDB [site1.local]> describe tabSingles;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| doctype | varchar(255) | YES  | MUL | NULL    |       |
| field   | varchar(255) | YES  |     | NULL    |       |
| value   | text         | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Good luck!

ahmed-madi · October 26, 2018, 12:30pm

What I meant is this line executed to make a new connection to database in each query in case of Mariadb or Postgres ??

github.com

frappe/frappe/blob/b1e0fc5ec9cf92f90ad9c48e99248b2671ac9f70/frappe/database/mariadb/database.py#L83


      
          		if PY2:
          			conversions.update({
          				TimeDelta: conversions[binary_type]
          			})
          
          
		if usessl:
          			conn = pymysql.connect(self.host, self.user or '', self.password or '',
          				charset='utf8mb4', use_unicode = True, ssl=ssl_params,
          				conv = conversions, local_infile = frappe.conf.local_infile)
          		else:
          			conn = pymysql.connect(self.host, self.user or '', self.password or '',
          				charset='utf8mb4', use_unicode = True, conv = conversions,
          				local_infile = frappe.conf.local_infile)
          
          
		# MYSQL_OPTION_MULTI_STATEMENTS_OFF = 1
          		# # self._conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_OFF)
          
          
		if self.user != 'root':
          			conn.select_db(self.user)
          
          
		return conn

ahmadRagheb · October 27, 2018, 4:15pm

@ahmed-madi @tmatteson you want to implement pooling so each hit on db take a connection from the pool and not initiate new one

ahmed-madi · October 27, 2018, 6:49pm

Yeah exactly, this what is known as object pooling pattern, this design pattern will guarantee that you are making a limited number of connection instances to database and these object instances can be released when it is done and back to availability on the pool and then will be reused once again when a new connection is requested so that, don’t let the database connection instances reach to number that could break database down.