What Is a .dbs File? The DbSchema Design Model Explained

You found a file ending in .dbs, maybe in a pull request, and want to know what it is. If DbSchema wrote it, it is a design model: an XML text file that holds a database schema and the diagrams drawn from it. Any text editor opens it. A DbSchema model starts with these two lines:

<?xml version="1.0" encoding="UTF-8" ?>
<project name="PostgreSQL" database="PostgreSQL" id="cf87f0ad-e674-41b6-afc1-25d3e371a8b2" >

The database attribute names the engine that the model is designed for. If your file starts another way, another program wrote it. FileInfo lists Gupta SQLBase databases and GAMBIT mesh files under the same extension.

What a .dbs file stores

Each schema element holds the tables, each with its columns, indexes, foreign keys and comments. The views, sequences, procedures, functions and triggers follow the tables, inside the same element. The diagrams come after the schemas. DbSchema saves everything you see in its window in the same file, down to the SQL Editor tabs that you left open.

DbSchema keeps the model apart from the database. To make a model from a database, choose File → New Model and connect. DbSchema reads the schema into the model and changes nothing in the database. While DbSchema is connected, an edit that you confirm in a dialog runs in the database at once. Offline, the edit stays in the model until you synchronize the model with the database.

File → Save Model to File writes the .dbs file and needs DbSchema Pro. A saved model opens without any database. Model → Convert Model converts the model to another of the databases that DbSchema supports.

An excerpt from a real .dbs file

DbSchema 10.5.2 saved this model for a PostgreSQL schema named shop. The <!-- … --> lines mark cuts, and the indentation is shortened.

<?xml version="1.0" encoding="UTF-8" ?>
<project name="PostgreSQL" database="PostgreSQL" id="cf87f0ad-e674-41b6-afc1-25d3e371a8b2" >
  <schema name="shop" catalogname="shopdb" >
    <table name="addresses" row_count="200" spec="" >
      <column name="address_id" type="integer" mandatory="y" >
        <defo><![CDATA[nextval('shop.addresses_address_id_seq'::regclass)]]></defo>
      </column>
      <!-- … -->
      <column name="country_code" type="char" length="2" mandatory="y" />
      <index name="addresses_pkey" unique="PRIMARY_KEY" >
        <column name="address_id" />
      </index>
      <!-- … -->
      <fk name="addresses_country_code_fkey" to_schema="shopdb.shop" to_table="countries" options="" >
        <fk_column name="country_code" pk="country_code" />
      </fk>
    </table>
    <!-- … -->
    <table name="countries" row_count="10" spec="" >
      <comment><![CDATA[ISO 3166 countries the shop ships to]]></comment>
      <column name="country_code" type="char" length="2" mandatory="y" />
      <column name="name" type="varchar" length="80" mandatory="y" />
      <index name="countries_pkey" unique="PRIMARY_KEY" >
        <column name="country_code" />
      </index>
    </table>
    <!-- … -->
  </schema>
  <diagram_group name="Default" id="7bd4cdef-571c-4fc0-bcbb-0ad85862a025" >
    <diagram name="Main Diagram" id="912d6936-d5a8-4e7a-a202-084b720918ad" show_relation="columns" >
      <entity schema="shopdb.shop" name="addresses" color="BED3F4" x="228" y="57" />
      <entity schema="shopdb.shop" name="countries" color="BED3F4" x="57" y="57" />
      <!-- … -->
    </diagram>
  </diagram_group>
</project>

A table's columns, indexes and foreign keys sit inside its table element, in that order. A foreign key sits in the child table, whose column points at the parent table. So addresses_country_code_fkey is inside addresses, and its to_table attribute names the parent, countries. To find every table that points at countries, search the file for to_table="countries".

The attributes map onto SQL as below. The UNIQUE_KEY and NORMAL rows come from other tables in the same file:

The primary key is an index element, not a flag on the column. Only the index below address_id says that address_id is the key. row_count is the number of rows that DbSchema found when it read the table, so it describes the data, not the design.

The diagrams in the same file

Each diagram element lists one entity per table box, with the box's position and header color. The diagram is stored apart from the schema, so moving a box changes only its x and y. One model can hold many diagrams, and a table can sit on several of them.

Older DbSchema versions named this element layout. DbSchema still reads those files and writes diagram when it saves them, so the first save after an upgrade rewrites those lines.

Reading a change to a .dbs file

In DbSchema 10.5.2, we widened customers.phone in that model and made it required, renamed shipments.carrier, added a virtual foreign key from shipments to orders, and saved. The file changed like this:

-      <column name="phone" type="varchar" length="30" />
+      <column name="phone" type="varchar" length="40" mandatory="y" />
 …
-      <column name="carrier" type="varchar" length="40" mandatory="y" />
+      <column name="carrier_name" prior="carrier" type="varchar" length="40" mandatory="y" />
 …
+      <fk name="shipments_order_id_vfk" virtual="y" to_schema="shopdb.shop" to_table="orders" >
+        <fk_column name="order_id" pk="order_id" />
+      </fk>

Each change sits on the line of the object it changes. Look closest at a new mandatory="y". Once the new NOT NULL reaches the database, the database rejects a NULL in phone.

The renamed column keeps its old name in the prior attribute. From prior, DbSchema knows that the column was renamed, not dropped and added again. When you synchronize the model with the database, DbSchema runs ALTER TABLE ... RENAME COLUMN and keeps the column's data. A drop and a create would lose the data in the column.

virtual="y" marks a virtual foreign key, a foreign key that exists only in the model. DbSchema never creates it in the database, and the Query Builder and Data Explorer of DbSchema Pro follow it like a real one. To add one, drag the child column onto the parent column and tick Virtual in the dialog.

A diff that changes only x, y and color inside a diagram element moves or recolors boxes on a diagram. No table changes.

The .dbs file in Git and against the database

Commit the .dbs file next to the code that uses the database. The schema then gets the same history and review as the code. In DbSchema, File → Git Collaboration commits and pushes the model file, and Model History lists the tables that each commit changed. If you and a teammate change the same part of the file, your pull ends in a conflict, and the Git dialog offers Ours, Theirs and Merge... for the file.

Database → Refresh Model from Database compares the model with the database, and Review Differences lists each difference in the Synchronize Model and Database dialog. On each row, choose the side that changes, then press Apply to the model, Commit in the database, or Generate SQL script. Model → Compare Model with Other Model From File compares the model with a second .dbs file, such as the version on another branch, and runs nothing in a database. Schema synchronization needs DbSchema Pro.

For more, read database design tools with Git and designing offline in DbSchema.

To see your own schema as a .dbs file, download DbSchema and connect to your database. The free Community Edition reads the schema from the database and draws it as diagrams. Saving the model to a .dbs file, synchronizing it with the database and exporting HTML5, PDF or Markdown documentation of any diagram need DbSchema Pro.