free4inno/sqlite-tunnel repository overview

SQLite, a lightweight database, is an ACID compliant relational database management system that is contained in a relatively small C library. SQLite is lightweight and all data is encapsulated in a db file, making it easy to back up and manage data.

However, because of this design, SQLite does not naturally support remote connections like MySQL does, so we had to find other ways to support remote connections to SQLite during development. The essence of remote connection of SQLite is to realize remote access to db files. Here, on the remote server, I used ntunnel_sqlite.php, provided in the navicat official package, to build the http tunnel for the remote connection, with navicat accepting and implementing the remote connection at the other end of the tunnel. In the process of setting up this tunnel, php, PHp-fpm and nginx environments need to be built on the target host to support the php tunnel script to achieve remote http access. The process of setting up the environment is very complex, so consider using a container solution.

By building the above environment in the container, the tunnel script is introduced (the container building process is not detailed). Start the container, mount the directory where the db file resides, and map the corresponding port to enable Navicat to remotely connect to SQLite.

The specific use of the container is as follows:

⁠1. Deploy sqlite-tunnel

⁠1.1. Docker

docker pull free4inno/sqlite-tunnel:1.0
docker run --name sqlite-tunnel -p 8011:8011 -v /db:/db -itd free4inno/sqlite-tunnel:1.0

⁠1.2. Kubernetes

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: sqlite-tunnel
  # namespace
  namespace: <my-ns>
spec:
  replicas: 1
  selector:
    matchLabels:
      app: sqlite-tunnel
  template:
    metadata:
      labels:
        app: sqlite-tunnel
    spec:
      # appoint node
      nodeName: hf-master-01
      containers:
        - name: sqlite-tunnel
          image: free4inno/sqlite-tunnel:1.0
          imagePullPolicy: IfNotPresent
          # use hostPort 8011
          ports:
            - containerPort: 8011
              hostPort: 8011
              protocol: TCP
          volumeMounts:
            - name: sqlite-db
              # mount /db
              mountPath: /db
      volumes:
        - name: sqlite-db
          hostPath:
            # mount sqlite db file Dirctory on host
            path: <path_to_sqlite>

⁠3. Connect remote SQLite by Navicat

Browser access：http://<ip>:8011/ntunnel_sqlite.php

Get the following page

Enter the container path to the db file in the input box and click test

If display Connection Success! That is available. Open Navicat and create a new sqlite connection:

When complete, you can realize a remote connection.