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 |
|
Port |
|
Database |
|
User |
|
Password |
|
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.
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 |
|---|---|
|
contains RNA sequences and URS identifiers |
|
contains cross-references to Expert Databases |
|
contains a list of Expert Databases |
|
contains metadata associated with each cross-reference |
|
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:
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'
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.txtwith a list of RNAcentral identifiers.Download the following RNAcentral FASTA file:
Extract the sequences using seqkit:
seqkit grep -f ids.txt rnacentral_species_specific_ids.fasta.gz > output.fasta
The file
output.fastawill 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()
