Skillversitas

BioSQL Module

BioSQL is a database schema that stores sequence records in SQL databases (MySQL, PostgreSQL, SQLite) with a common object model. Biopython's Bio.BioSQL module lets you index GenBank/EMBL records into that schema and query them with SQL - useful when you have thousands of records and need fast filtering, joins, and reporting that flat files cannot give.

Setting Up a BioSQL Database

  1. Create an empty SQL database with a name you control.
  2. Apply the BioSQL schema DDL for your database (the official schema SQL ships with the BioSQL project).
  3. Note the connection details: host, user, password, database name, and the schema (table prefix) name.

Connecting with BioSQLDatabase

from Bio.BioSQL import DBUtils

db = DBUtils.connect("MySQLdb", user="bio", passwd="secret",
                     host="localhost", db="biosql",
                     storable_id="myschema")
print(db.dialect)
print(db.list_dbs())

The connect call takes your DB-API module name (MySQLdb, psycopg2, sqlite3), credentials, and the schema name registered in the database table.

Indexing Records into BioSQL

from Bio import SeqIO
from Bio.BioSQL import DBUtils

db = DBUtils.connect("sqlite3", db="biosql.db", storable_id="schema1")
adaptor = db.get_adaptor()
dbi = adaptor.lookup_database("myschema")

# or create a new database entry:
# dbi = adaptor.create_database("myschema", "description")

for rec in SeqIO.parse("records.gb", "genbank"):
    dbi.add_sequence(rec)

print(dbi.count())  # number of indexed sequences

Querying Indexed Sequences

# retrieve by accession:
rec = dbi.get_accession("NC_000913.3")
print(rec.id, len(rec))

# search by description keyword:
hits = dbi.search("Haemophilus", max_results=10)
for h in hits:
    print(h.id, h.description[:50])

Running SQL Directly

Because the data is in SQL, you can also query the schema tables directly with your DB-API driver for custom reports (counts by organism, length distributions), and join with other tables in the same database.

When to Use BioSQL (vs Just Files)

  • Use BioSQL when: the collection is large and changing, you need ad-hoc SQL filters, or multiple tools share one indexed store.
  • Use flat files when: the collection is small, static, and you only parse in Python - simpler is better.
  • BioSQL is a niche but stable tool; for modern large-scale stores, a columnar DB (DuckDB, Parquet) is a common alternative, but BioSQL remains the classic bioinformatics pattern.

Summary

BioSQL stores SeqRecords in SQL tables under a standard schema. Connect with DBUtils.connect using your DB-API driver, index records with the adaptor and database objects (dbi.add_sequence), and retrieve with get_accession or search. It earns its keep for large, query-heavy collections where SQL filtering beats re-parsing files.