Skip to main content

ChEMBL DB on SQLite, is that even possible?

Short answer: Yes; Andrew Dalke did it in 2014 for ChEMBL 19 compounds but now it's officially supported by the ChEMBL team and covers the whole database.

One thing you can notice looking at the ChEMBL 21 FTP directory is a  new file called chembl_21_sqlite.tar.gz. What's that?

It's a binary SQLite database file containing all the ChEMBL 21 tables and data. If you don't know what the SQLite is, it's a very lightweight database system, that stores the entire database (definitions, tables, indices, and the data itself) as a single cross-platform file on a host machine. It's very popular as well, so if you have a Mac, Windows 10 or a Linux box, chances are that SQLite is already installed on your computer. Skype uses SQLite to store the local copy of conversation history and the Python language has SQLite bundled as a core library.

If it's so "lightweight", why is the SQLite ChEMBL 21 file 2.4GB, compared to less than 1.4GB for Oracle, MySQL and PostgreSQL dumps and is the largest file in the FTP directory? This is because the 'dumps' only contain raw data in a form of SQL statements, that yet have to be executed in order to create a database. In contrast, SQLite file IS a database already. This means that if you download a MySQL dump, for example, you need to install the MySQL server first. Most probably you will have to configure it as well. Once this is done, you install the MySQL client to create a new database and populate it with a data from the dump file. This can take several hours during which the engine will create tables and indexes. If you never done this before, the whole process can take you much longer and possibly you will need some help.

SQLite can make your life easier, all you have to do is to download the file from our FTP and uncompress it. The uncompressed file (named chembl_21.db ) will take about 12GB of your disk space. Once you done this, you can open a terminal and change the current directory to the one, which contains the file. Now all you have to do is to type:

sqlite3 chembl_21.db

If you have SQLite installed you will see the prompt ready to execute your SQL statements:

SQLite standard terminal shell has many useful commands making is extremely convenient for simple tasks such as exports. We use SQLite CLI ourselves to prepare chembl_21_chemreps.txt.gz file, which is a text file containing structure information of all the ChEMBL compounds used by the UniChem software. In order to create it, we execute the following commands:

OK, but I hate terminal, can I have a GUI instead? Yes, just install sqlitebrowser and open the file as you would with any other program. You will see ChEMBL 21 tables, you can browse data and execute SQL statements with autocompletion. Of course you can also use SQLite in your Python (or IPython notebooks) scripts or KNIME workflows.

Isn't ChEMBL a bit too large for SQLite capabilities? Of course SQLite has its limitations. It's good for quick hacking and prototyping but as it doesn't implement client-server architecture it doesn't scale well. If all you need is the ability to run a SQL query locally on your laptop or extract some data, then probably SQLite would be your best choice. It's exceptionally fast on SSD hard drives. The chemreps file we've described above was generated on the Mid 2014 MacBook Pro with SSD and it took less than a minute to prepare. Just keep in mind, that SQLite always does a full table scan for


It does not keep meta information on tables to speed this process up so this operation will always be slower than on other engines.

How about chemistry logic? Riccardo Vianello created a project called ChemicaLite which is a cheminformatic SQLite database extension. It can generate and store fingerprints, compute descriptors, run chemical queries so everything you would expect from a normal chemical database.


Popular posts from this blog

UniChem 2.0

UniChem new beta interface and web services We are excited to announce that our UniChem beta site will become the default one on the 11th of May. The new system will allow us to better maintain UniChem and to bring new functionality in a more sustainable way. The current interface and web services will still be reachable for a period of time at . In addition to it, the most popular legacy REST endpoints will also remain implemented in the new web services: Some downtime is expected during the swap.  What's new? UniChem’s current API and web application is implemented with a framework version that’s not maintained and the cost of updating it surpasses the cost of rebuilding it. In order to improve stability, security, and support the implementation and fast delivery of new features, we have decided to revamp our user-facing systems using the latest version of widely used and maintained frameworks, i

A python client for accessing ChEMBL web services

Motivation The CheMBL Web Services provide simple reliable programmatic access to the data stored in ChEMBL database. RESTful API approaches are quite easy to master in most languages but still require writing a few lines of code. Additionally, it can be a challenging task to write a nontrivial application using REST without any examples. These factors were the motivation for us to write a small client library for accessing web services from Python. Why Python? We choose this language because Python has become extremely popular (and still growing in use) in scientific applications; there are several Open Source chemical toolkits available in this language, and so the wealth of ChEMBL resources and functionality of those toolkits can be easily combined. Moreover, Python is a very web-friendly language and we wanted to show how easy complex resource acquisition can be expressed in Python. Reinventing the wheel? There are already some libraries providing access to ChEMBL d

LSH-based similarity search in MongoDB is faster than postgres cartridge.

TL;DR: In his excellent blog post , Matt Swain described the implementation of compound similarity searches in MongoDB . Unfortunately, Matt's approach had suboptimal ( polynomial ) time complexity with respect to decreasing similarity thresholds, which renders unsuitable for production environments. In this article, we improve on the method by enhancing it with Locality Sensitive Hashing algorithm, which significantly reduces query time and outperforms RDKit PostgreSQL cartridge . myChEMBL 21 - NoSQL edition    Given that NoSQL technologies applied to computational chemistry and cheminformatics are gaining traction and popularity, we decided to include a taster in future myChEMBL releases. Two especially appealing technologies are Neo4j and MongoDB . The former is a graph database and the latter is a BSON document storage. We would like to provide IPython notebook -based tutorials explaining how to use this software to deal with common cheminformatics p

ChEMBL 30 released

  We are pleased to announce the release of ChEMBL 30. This version of the database, prepared on 22/02/2022 contains: 2,786,911 compound records 2,157,379 compounds (of which 2,136,187 have mol files) 19,286,751 activities 1,458,215 assays 14,855 targets 84,092 documents Data can be downloaded from the ChEMBL FTP site: Please see ChEMBL_30 release notes for full details of all changes in this release: New Deposited Datasets EUbOPEN Chemogenomic Library (src_id = 55, ChEMBL Document ID CHEMBL4689842):   The EUbOPEN consortium is an Innovative Medicines Initiative (IMI) funded project to enable and unlock biology in the open. The aims of the project are to assemble an open access chemogenomic library comprising about 5,000 well annotated compounds covering roughly 1,000 different proteins, to synthesize at least

Multi-task neural network on ChEMBL with PyTorch 1.0 and RDKit

  Update: KNIME protocol with the model available thanks to Greg Landrum. Update: New code to train the model and ONNX exported trained models available in github . The use and application of multi-task neural networks is growing rapidly in cheminformatics and drug discovery. Examples can be found in the following publications: - Deep Learning as an Opportunity in VirtualScreening - Massively Multitask Networks for Drug Discovery - Beyond the hype: deep neural networks outperform established methods using a ChEMBL bioactivity benchmark set But what is a multi-task neural network? In short, it's a kind of neural network architecture that can optimise multiple classification/regression problems at the same time while taking advantage of their shared description. This blogpost gives a great overview of their architecture. All networks in references above implement the hard parameter sharing approach. So, having a set of activities relating targets and molecules we can tra