# Public Postgres database In addition to [downloadable files](downloads.md), an [API](api.md), and the [text search](searching-rnacentral.md), RNAcentral provides a public [Postgres](https://en.wikipedia.org/wiki/PostgreSQL) 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](https://rnacentral.org). ## 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**: ```bash 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](https://www.docker.com/) to get started with a pre-configured Postgres image: ```bash 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](https://dbeaver.io) or [PgAdmin](https://pgadmin.org). ```{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](_static/img/rnacentral_latest_schema.png)](_static/img/rnacentral_latest_schema.png) ## 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](identifiers.md) | | `xref` | contains cross-references to [Expert Databases](expert-databases.md) | | `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: ```sql SELECT urs, -- RNAcentral URS identifier taxid, -- NCBI taxid ac -- external accession FROM xref WHERE ac IN ('OTTHUMT00000106564.1', 'OTTHUMT00000416802.1') ``` Example output: ```text 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](searching-rnacentral.md). Here's an example of how you can explore this service: 1. Create a file `query.sql`: ```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: ```bash 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: 4. Extract the sequences using [seqkit](https://bioinf.shenwei.me/seqkit/): ```bash 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](https://www.psycopg.org/) to connect to Postgres: ```bash pip install psycopg2-binary ``` ```python 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() ```