Public Postgres database

In addition to downloadable files, an API, and the text search, RNAcentral provides a public Postgres database that can be used to query the data using SQL syntax. The database is updated with every RNAcentral release and contains a copy of the data available through the RNAcentral website.

Connection details

Main database

Setting

Value

Hostname

hh-pgsql-public.ebi.ac.uk

Port

5432

Database

pfmegrnargs

User

reader

Password

NWDMCE5xdipIjRrp

Connecting to the database

To connect to the database using the command line:

psql postgres://reader:NWDMCE5xdipIjRrp@hh-pgsql-public.ebi.ac.uk:5432/pfmegrnargs

Pro tip: if you don’t have psql installed on your machine, consider using Docker to get started with a pre-configured Postgres image:

docker pull postgres
docker run -it postgres psql postgres://reader:NWDMCE5xdipIjRrp@hh-pgsql-public.ebi.ac.uk:5432/pfmegrnargs

Alternatively, you can use a Postgres client like DBeaver or PgAdmin.

Warning

If your computer is behind a firewall, please ensure that outgoing TCP/IP connections to the corresponding ports are allowed.

Database schema

The following diagram was generated based on the latest version of RNAcentral.

RNAcentral database schema

Main tables

The entire RNAcentral schema contains more than 300 tables, but the following tables are good starting points for exploring the data:

Table

Description

rna

contains RNA sequences and URS identifiers

xref

contains cross-references to Expert Databases

rnc_database

contains a list of Expert Databases

rnc_accessions

contains metadata associated with each cross-reference

rnc_rna_precomputed

contains RNA types and descriptions for all sequences

Example queries

Search by external accessions

Although the VEGA database has been archived and its identifiers are no longer searchable using the RNAcentral text search, you can still query RNAcentral using VEGA identifiers:

SELECT
  urs,     -- RNAcentral URS identifier
  taxid,   -- NCBI taxid
  ac       -- external accession
FROM xref
WHERE ac IN ('OTTHUMT00000106564.1', 'OTTHUMT00000416802.1')

Example output:

URS00000B15DA	9606	OTTHUMT00000106564.1
URS00000A54A6	9606	OTTHUMT00000416802.1

Example workflow to extract all bacterial rRNA sequences

Exporting millions of sequences via the public database can be faster than using the text search. Here’s an example of how you can explore this service:

  1. Create a file query.sql:

    SELECT
        precomputed.urs_taxid
    FROM rnc_rna_precomputed precomputed
    JOIN rnc_taxonomy tax
    ON
        tax.id = precomputed.taxid
    WHERE
        tax.lineage LIKE 'cellular organisms; Bacteria; %'
        AND precomputed.is_active = true    -- exclude sequences without active cross-references
        AND precomputed.rna_type = 'rRNA'
    
  2. Run the following command to execute the query:

    docker run -v `pwd`:/rnacentral -it postgres /bin/sh -c 'cd /rnacentral && psql -t -A -f query.sql postgres://reader:NWDMCE5xdipIjRrp@hh-pgsql-public.ebi.ac.uk:5432/pfmegrnargs > ids.txt'
    

    The command will create a file ids.txt with a list of RNAcentral identifiers.

  3. Download the following RNAcentral FASTA file:

    https://ftp.ebi.ac.uk/pub/databases/RNAcentral/current_release/sequences/rnacentral_species_specific_ids.fasta.gz

  4. Extract the sequences using seqkit:

    seqkit grep -f ids.txt rnacentral_species_specific_ids.fasta.gz > output.fasta
    

    The file output.fasta will contain the desired subset of RNAcentral sequences in FASTA format.

Example Python script

Requires psycopg2 to connect to Postgres:

pip install psycopg2-binary
import psycopg2
import psycopg2.extras

def main():
    conn_string = (
        "host='hh-pgsql-public.ebi.ac.uk' dbname='pfmegrnargs' "
        "user='reader' password='NWDMCE5xdipIjRrp'"
    )
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    # retrieve a list of RNAcentral databases
    query = "SELECT * FROM rnc_database"

    cursor.execute(query)
    for row in cursor:
        print(row)

if __name__ == "__main__":
    main()