Database connection: Difference between revisions

Revision as of 19:02, 7 December 2018

Revision as of 03:32, 9 December 2018

Line 1:

A '''Database connection''' is facility in [[computer science]] that allows [[Client (computing)|client]] software to talk to [[database server]] software, whether on the same machine or not. A '''connection''' is required to send [[command (computing)|commands]] and receive answers, usually in the form of a result set.

Connections are a key concept in [[data-centric]] programming. Since some DBMS engines require considerable time to connect [[connection pooling]] was invented to improve performance. No command can be performed against a database without an "open and available" connection to it.

Connections are built by supplying an underlying [[software driver|driver]] or [[data provider|provider]] with a [[connection string]], which is a way of addressing a specific [[database]] or [[server (computing)|server]] and instance as well as user authentication credentials (for example, '''''Server='''sql_box;'''Database='''Common;'''User ID='''uid;'''Pwd='''password;''). Once a connection has been built it can be opened and closed at will, and properties (such as the command time-out length, or [[Database transaction|transaction]], if one exists) can be set. The Connection String is composed of a set of key/value pairs as dictated by the data access interface and data provider being used.

Many databases (such as [[PostgreSQL]]) only allow one operation to be performed at a time on each connection. If a request for data (a [[SQL]] [[Select (SQL)|Select]] statement) is sent to the database and a result set is returned, the connection is open but not available for other operations until the client finishes consuming the result set. Other databases, like [[Microsoft SQL Server|SQL Server 2005]] (and later), do not impose this limitation. However, databases that provide multiple operations per connection usually incur far more overhead than those that permit only a single operation task at a time.

Line 9:

== Pooling ==

Database connections are [[finite]] and [[time complexity|expensive]] and can take a disproportionately long time to create relative to the operations performed on them. It is very inefficient for an application to create, use, and close a database connection whenever it needs to update a database.

[[Connection Pool]]ing is a technique designed to alleviate this problem. A pool of database connections can be created and then shared among the applications that need to access the database. When an application needs database access, it requests a connection from the pool. When it is finished, it returns the connection to the pool, where it becomes available for use by other applications.