Direct Database Access

Direct Database Access

PartKeepr stores information in two places: in your database (MySQL or PostgreSQL) and in the data subdirectory of the PartKeepr tree. The database contains numerous tables which describe your parts, manufacturers, distributors, etc. The data subdirectory of the PartKeepr tree contains images and attachment files.

Perhaps you have reasons to bypass the PartKeepr web application and interact with the database directly. For example, you might have part information stored in another format, such as a spreadsheet, and you wish to import those parts in bulk rather than to enter them manually one at a time. Or perhaps you have thought of some other reason to access the underlying database directly. To do so, you must know some database concepts and be comfortable with SQL.

Warning: This page is very much a work in progress! Some suggestions are made and SQL statements are given as examples. Do not apply any of the steps described here without fully understanding what they do, how they work, and whether they are correct for your particular situation!

Warning: Bypassing PartKeepr and accessing the database comes with the risk of damaging information already stored there. Before you begin, be sure you have a valid, working, and up-to-date backup of your database. Better yet, you should create a separate PartKeepr installation (perhaps in its own virtual machine) where you can experiment without risking damage to your "real" PartKeepr data.

Note: This page assumes you have a MySQL-backed PartKeepr installation. We would like similar documentation for PostgreSQL. If you are proficient with databases and SQL and wish to contribute to this wiki, please contact the authors for a wiki account.

PartKeepr Database Schema

The easiest way to examine the structure of the PartKeepr database schema is with a graphical tool. When your PartKeepr installation is backed by a MySQL database, you can use MySQL Workbench.

Here are the steps to examine the schema graphically with MySQL Workbench (preliminary): Connect to the database instance that houses your PartKeepr data. In the menu bar, select "Database" -> "Reverse Engineer..." In the dialog box that appears, be sure to select the correct database instance (under Stored Connection) with the correct hostname, port, username, password, and other settings. Click Next and enter any required information until you are asked to "Select the schemas below you want to include." Be sure there is a check mark next to PartKeepr and click next. During this process you may be asked to enter the appropriate password once or twice. Enter it as required and continue clicking Next until the process is completed. At this point, you will be presented with physical schemas and EER Diagrams. Open the EER Diagram to view it and you will be presented with the PartKeepr schema in graphical form. Each box represents a database table and each item in a box represents a column of that table. By hovering the mouse over various items, you can see tool tips about those items. Relationships between tables are shown with dashed lines.