How To Use Full-text Search In Sqlite3 Database In Django?

I am working on a django application with sqlite3 database, that has a fixed database content. By fixed I mean the content of the db won't change over time. The model is something

Solution 1:

SQLite's FTS engine is based on tokens - keywords that the search engine tries to match.

A variety of tokenizers are available, but they are relatively simple. The "simple" tokenizer simply splits up each word and lowercases it: for example, in the string "The quick brown fox jumps over the lazy dog", the word "jumps" would match, but not "jump". The "porter" tokenizer is a bit more advanced, stripping the conjugations of words, so that "jumps" and "jumping" would match, but a typo like "jmups" would not.

In short, the SQLite FTS extension is fairly basic, and isn't meant to compete with, say, Google.

As for Django integration, I don't believe there is any. You will likely need to use Django's interface for raw SQL queries, for both creating and querying the FTS table.

Solution 2:

I think that while sqlite is an amazing piece of software, its full-text search capabilities are quite limited. Instead you could index your database using Haystack Django app with some backend like Elasticsearch. Having this setup (and still being able to access your sqlite database) seems to me the most robust and flexible way in terms of FTS.

Elasticsearch has a fuzzy search based on the Levenshtein distance (in a nutshell, it would handle your "egsample" queries). So all you need is to make a right type of query:

from haystack.forms import SearchForm
from haystack.generic_views import SearchView
from haystack import indexes


class QScriptIndex(indexes.SearchIndex, indexes.Indexable):
    v = indexes.CharField(document=True)

    def get_model(self):
        return QScript


class QScriptSearchForm(SearchForm):
    text_fuzzy = forms.CharField(required=False)    

    def search(self):        
        sqs = super(QScriptSearchForm, self).search()

        if not self.is_valid():
            return self.no_query_found()

        text_fuzzy = self.cleaned_data.get('text_fuzzy')
        if text_fuzzy:
            sqs = sqs.filter(text__fuzzy=text_fuzzy)

        return sqs


class QScriptSearchView(SearchView):        
    form_class = QScriptSearchForm

Update: As long as PostgreSQL has the Levenshtein distance function, you could also leverage it as the Haystack backend as well as a standalone search engine. If you choose the second way, you'll have to implement a custom query expression, which is relatively easy if you're using a recent version of Django.