= Database Backend

Trac offers an easily extensible layer for database support, see [source:trunk/trac/db/api.py trac.db.api]. The following databases have been integrated in this layer:
 * '''[http://www.sqlite.org SQLite]''', using '''PySqlite''', the default database
 * '''[http://www.postgresql.org PostgreSQL]''', using the
   '''[http://initd.org/pub/software/psycopg/ psycopg2]''' bindings, support added as of Trac [milestone:0.9].[[br]]
   Note that psycopg1 is no longer supported and that [http://pypgsql.sourceforge.net/ pyPgSQL] also presents issues for some users, see #5096. [[br]]
   Finally, see #126 for historical notes.
 * '''[http://www.mysql.org MySQL]''', using MySqlDb, support added for Trac [milestone:0.10]; see [http://thread.gmane.org/gmane.comp.version-control.subversion.trac.devel/342/focus=348 status report] and #986 for historical notes.

There is work in progress for the following databases:
 * [http://www.actian.com/products/operational-databases/ingres/ Ingres], see #6235.
 * [http://www.oracle.com/index.html Oracle], see #1874.
 * [http://www.microsoft.com/en-us/server-cloud/products/sql-server/ Microsoft SQL Server], see #329.

== Database Installation Instructions

=== SQLite

Installed out-of-the-box, provided you have installed the PySqlite bindings.

The following parameters are supported:

 * `extensions`: Load the extensions, see also [wiki:TracIni#sqlite-section].
 * `timeout`: Set timeout to connect the database, `10.0` is used by default.
 * `cursor`: `eager` cursor pre-fetching all the rows is used by default. It can be turned off by specifying `cursor=` (#3446).
 * `journal_mode`: Set the [https://www.sqlite.org/pragma.html#pragma_journal_mode journal mode] to the database (since 1.0.5).
 * `synchronous`: Set the [https://www.sqlite.org/pragma.html#pragma_synchronous synchronous] flag (since 1.0.5).

Known issues: see [query:keywords=~sqlite&status=!closed pysqlite-related tickets].

=== Postgresql

Prerequisites:
 * Have a working copy of Postgresql.
 * Get the proper database driver for Python, see above.
 * Create a database for your Trac environment:
{{{
% createdb dbname
}}}
 * Run trac-admin to create a new Trac environment. When prompted for a Database connection string, use:
{{{
postgres://user:pass@localhost:5432/dbname?schema=schemaname
}}}

Notice that the port number might be different in your Postgresql installation, eg 5433. See postgresql.conf.

Alternatively on UNIX, if the database is a local one, you can use UNIX sockets instead of TCP/IP:
{{{
postgres://user:pass@/dbname?host=/path/to/unix/socket/dir&schema=schemaname
}}}
If you are using user Trac without a password to connect through UNIX sockets to database trac and have only one project (hence no need for different schemas), your connections string would be ''postgres://trac:@/trac''

 * Note: '?host=...' is optional. Check your postgresql.conf' unix_socket_directory option if you have connection problems.

See #4546 for more details.

See also #2441, which discusses the process of migration from SQLite to Postgresql.

For a Postgresql recipe tested on CentOS4 (Red Hat -EL4) see [wiki:PostgresqlRecipe].

Note: Since Trac [milestone:0.10 0.10] the psycopg1 Postgresql driver is no longer supported due to lack of Unicode support.

Known issues: see [query:keywords=~postgres&status=!closed postgres-related tickets]

=== MySQL

MySQL is supported by Trac since [milestone:0.10], but there are some caveats, documented in more details in the MySqlDb page. 

 * Run trac-admin to create a new Trac environment. When prompted for a database connection string, use:
{{{
mysql://dbuser:dbpass@host:port/dbname
}}}
 * or when you need more options:
{{{
mysql://dbuser[:dbpass]@host[:port]/dbname[?param1=value&param2=value]
}}}

The following parameters are supported since Trac [milestone:1.0]:

* `compress`: Enable compression (0 or 1)
* `init_command`: Command to run once the connection is created
* `named_pipe`: Use a named pipe to connect on Windows (0 or 1)
* `read_default_file`: Read default client values from the given file (also used for SSL configuration)
* `read_default_group`: Configuration group to use from the default file
* `unix_socket`: Use a Unix socket at the given path to connect

Known issues: see [query:keywords=~mysql&status=!closed mysql-related tickets].

Users have reported success in [wiki:SqLiteToMySql converting their database from SQLite to MySQL].

== Alternative Ideas for Database Independence

=== Object-Relational Mapper

An ORM could be used to provide a unified object interface to different RDBMS:
 * [http://modeling.sourceforge.net/ Modeling], which is another, more advanced OR-Mapper.
 * [http://adodb.sourceforge.net ADODB], the fast database abstraction layer with a version available for Python.
 * [http://www.sqlalchemy.org/ SQLAlchemy] is also quite advanced.
 * some people have been talking about using [http://sqlobject.org SQLObject] to accomplish this goal of database independence.

=== Store Tickets and Wiki pages directly in the Subversion repository

A compelling idea with many advantages. A page advocating this plan is TighterSubversionIntegration. There has also been a discussion on the Trac mailing list, of which the arguments are summarized on the page [wiki:WhySQLite WhySQLite].

----
See also: SqlAlchemy