Self Hosting on Linux/MacOS machines



Prerequisites

docker, docker compose, wget, and xz-utils (or equivalent for your specific OS) packages are required for this installation.


How to Install, the easy way

On a Linux or MacOS machine, open a terminal and run:

curl https://datasquirel.com/install.sh | bash

Preferred operating systems are:

  • Debian
  • Ubuntu
  • Fedora


How to Install, the hard way

To install Datasquirel open a terminal and follow these steps:

1. Create a new directory to house all Datasuirel instances

It is possible to have multiple datasquirel instances running on the same server. Datasquirel is essentially a tool to manage SQL databases, so you can have more than 1 SQL databases connected. For this instance, we will use a directory named $HOME/.dsql/main. If this directory doesn't exist, create it with:

mkdir -p $HOME/.dsql/main

Subsequently you can have multiple instances in the $HOME/.dsql directory, each with different names.

2. Download the package from the official repository

wget -O $HOME/.dsql/main/dsql.tar.xz https://s3-api.datasquirel.com/public/dsql.tar.xz


3. Extract the .xz file

change directory to the new installation folder and extract the compressed file

cd $HOME/.dsql/main && tar -xvf dsql.tar.xz

After extracting the file your $HOME/.dsql/main directory should look like this

$HOME/.dsql/main/
│── dsql.tar.xz # The downloaded Tarball
│── dsql-admin/ # Folder containing all package assets
│   ├── dsql-app/
│   │   ├── test.env
│   │   ├── test.docker-compose.yml
│   │   ├── ...
│   ├── dsql-data/
│   │   ├── db-config/
│   │   ├── db-load-balancer-config/
│   │   ├── static-config/
│   ├── .gitignore
│   ├── README.md

For now we are only concerned with 2 files $HOME/.dsql/main/dsql-admin/dsql-app/test.env and $HOME/.dsql/main/dsql-admin/dsql-app/docker-compose.yaml.


4. Set your .env variables

New we move into the $HOME/.dsql/main/dsql-admin/dsql-app/ directory, copy the test.env file to .env. And edit the file to your liking.

cd $HOME/.dsql/main/dsql-admin/dsql-app/ && \
cp test.env .env

Your .env file should look like this:

DSQL_HOST=http://localhost:7070
NEXT_PUBLIC_DSQL_HOST=http://localhost:7070
DSQL_STATIC_HOST=http://localhost:7072
NEXT_PUBLIC_DSQL_STATIC_HOST=http://localhost:7072
DSQL_SOCKET_DOMAIN=http://localhost:7070
DSQL_HOST_ENV=prod_prod
NEXT_PUBLIC_DSQL_HOST_ENV=prod_prod
DSQL_PORT=7070
DSQL_PRODUCTION_PORT=7070
DSQL_STATIC_SERVER_PORT=7072
DSQL_STATIC_SERVER_DIR=/static
DSQL_SITE_URL=
NEXT_PUBLIC_DSQL_REMOTE_SQL_HOST=172.72.0.24
DSQL_DB_TARGET_IP_ADDRESS=172.72.0.24

NEXT_PUBLIC_DSQL_LOCAL=true

DSQL_USER_DB_PREFIX=datasquirel_user_
DSQL_USER_DELEGATED_DB_COOKIE_PREFIX=datasquirelDelegatedUserDbToken_

DSQL_NETWORK_GATEWAY=172.72.0.1

DSQL_DB_HOST=172.72.0.24

# Don't Change
DSQL_DB_USERNAME=root
DSQL_DB_NAME=datasquirel
DSQL_DB_READ_ONLY_USERNAME=read_only
DSQL_DB_FULL_ACCESS_USERNAME=datasquirel_full_access

# Change
DSQL_DB_PASSWORD=password
DSQL_MARIADB_ROOT_PASSWORD=password
DSQL_DB_READ_ONLY_PASSWORD=password
DSQL_DB_FULL_ACCESS_PASSWORD=password
DSQL_DB_EXPOSED_PORT=3317

DSQL_ENCRYPTION_PASSWORD=
DSQL_ENCRYPTION_SALT=

DSQL_SU_EMAIL=
DSQL_USER_KEY=
DSQL_SPECIAL_KEY=

DSQL_GOOGLE_API_KEY=
NEXT_PUBLIC_DSQL_GOOGLE_CLIENT_ID=
DSQL_GOOGLE_CLIENT_ID=
DSQL_GOOGLE_CLIENT_SECRET=
DSQL_GMAIL_PASSWORD=

NEXT_PUBLIC_DSQL_FACEBOOK_APP_ID=
DSQL_FACEBOOK_SECRET=

DSQL_MAIL_HOST=
DSQL_MAIL_EMAIL=
DSQL_MAIL_PASSWORD=

NEXT_PUBLIC_DSQL_TINY_MCE_API_KEY=

DSQL_GITHUB_ID=
NEXT_PUBLIC_DSQL_GITHUB_ID=
DSQL_GITHUB_SECRET=

DSQL_GITHUB_WEBHOOK_SECRET=
DSQL_GITHUB_WEBHOOK_URL=

DSQL_DEPLOY_SERVER_PORT=3092

DSQL_DOCKERFILE=Dockerfile

DSQL_VOLUME_APP=
DSQL_VOLUME_STATIC=
DSQL_VOLUME_STATIC_CONFIGURATION_FILE=
DSQL_VOLUME_DB=
DSQL_VOLUME_DB_CONFIG=
DSQL_VOLUME_DB_SETUP=
DSQL_VOLUME_DB_SSL=

DSQL_USER_LOGIN_KEYS_PATH=/app/apiKeys/allowed-logins
DSQL_API_KEYS_PATH=/app/apiKeys/allowed-signatures
DSQL_USER_DB_SCHEMA_PATH=/app/jsonData/dbSchemas/users

DSQL_DEPLOYMENT_NAME=dsql

DSQL_CONTACT_EMAIL=
DSQL_SSL_DIR=/ssl

# Cookies
DSQL_COOKIES_PREFIX=dsql_
DSQL_COOKIES_KEY_NAME=key

DSQL_SESSION_EXPIRY_TIME=36000000

DSQL_WEBSOCKET_PORT=7073
DSQL_WEBSOCKET_URL=ws://localhost:7070/dsql-websocket/

For now we'll concern ourselves with just a few variables:

  • DSQL_HOST

    This Variable determines the origin to be used through out the site. If you're serving this through a domain name, it should be changed appropriately. Also, the NEXT_PUBLIC_DSQL_HOST variable should have the same value. And the DSQL_WEBSOCKET_URL should have the same domain name.

    Example:

    DSQL_HOST             = https://example.com
NEXT_PUBLIC_DSQL_HOST = https://example.com
DSQL_WEBSOCKET_URL    = wss://example.com/dsql-websocket/

  • DSQL_STATIC_HOST

    This Variable determines the origin for static files. It also should be the same as the NEXT_PUBLIC_DSQL_STATIC_HOST variable.

    Example:

    DSQL_STATIC_HOST             = https://static.example.com
NEXT_PUBLIC_DSQL_STATIC_HOST = https://static.example.com

  • NEXT_PUBLIC_DSQL_REMOTE_SQL_HOST

    This Variable represents the hostname to use for external SQL connections. It could be the IP address of your server, or a domain name that points to your server IP address.

    Example:

    NEXT_PUBLIC_DSQL_REMOTE_SQL_HOST = 105.67.8.23
# NEXT_PUBLIC_DSQL_REMOTE_SQL_HOST = sql.example.com

  • Docker Compose Internal IP Addresses

    All env variables that have the IP pattern 172.72.0.* need to be consistent. This is because a single Datasquirel instance depends on a specific docker IP configuration. More on this will be explained in the docker-compose.yml section. But for now, all IPs that follow the 172.72.0.* pattern should be consistent.

    Example:

    DSQL_DB_TARGET_IP_ADDRESS = 172.21.0.24
DSQL_NETWORK_GATEWAY      = 172.21.0.1
DSQL_DB_HOST              = 172.21.0.24

  • DSQL_DB_PASSWORD

    This is the root password. It can be the same as DSQL_MARIADB_ROOT_PASSWORD, but is not mandatory.

  • DSQL_MARIADB_ROOT_PASSWORD

    This is the root password at database creation. Mariadb Docker image requires this to onboard the database server.

  • DSQL_DB_READ_ONLY_PASSWORD and DSQL_DB_FULL_ACCESS_PASSWORD

    Passwords for the read_only and datasquirel_full_access users.

  • DSQL_DB_EXPOSED_PORT

    Port to be exposed by the server instance. When connecting to Datasquirel from an external SQL client like MariaDB Client, you may need to set the Port variable to make sure you're htting the correct server. For multiple instances, this variable will come in handy.

  • DSQL_ENCRYPTION_PASSWORD and DSQL_ENCRYPTION_SALT

    Encryption keys. Make sure to use strong keys.

That's pretty much it on a basic level. As we move further, you can customize other aspects of an instance.


5. Set your docker-compose.yml file

Your test.docker-compose.yml file should look like this:

name: dsql

services:
    setup:
        build:
            context: ./docker/setup
            dockerfile: Dockerfile
        env_file: .env
        container_name: dsql-setup
        hostname: dsql-setup
        volumes:
            - .:/app
            - ${DSQL_VOLUME_DB_SSL:-../dsql-data/ssl}:/ssl
            - ${DSQL_VOLUME_SSH:-../dsql-data/ssh}:/ssh
        environment:
            - DSQL_SSL_DIR=/app/ssl
            - DSQL_DB_SCHEMA_DIR=/app/jsonData/dbSchemas
            - DSQL_APP_DIR=/app

    cron:
        build:
            context: ./docker/cron
            dockerfile: Dockerfile
        env_file: .env
        networks:
            datasquirel:
                ipv4_address: 172.72.0.27
        container_name: dsql-cron
        hostname: dsql-cron
        volumes:
            - ${DSQL_VOLUME_STATIC:-../dsql-data/static}:/static
            - ${DSQL_VOLUME_APP:-.}:/app
        environment:
            - DSQL_SSL_DIR=/app/ssl
            - DSQL_DB_SCHEMA_DIR=/app/jsonData/dbSchemas
            - DSQL_APP_DIR=/app
        restart: on-failure:${DSQL_WEB_APP_FAIL_COUNTS:-3}
        depends_on:
            setup:
                condition: service_completed_successfully
            db:
                condition: service_started

    reverse-proxy:
        image: nginx:alpine
        env_file: .env
        depends_on:
            setup:
                condition: service_completed_successfully
        networks:
            datasquirel:
                ipv4_address: 172.72.0.34
        container_name: dsql-reverse-proxy
        ports:
            - ${DSQL_PORT:-7070}:80
        restart: always
        volumes:
            - ./docker/reverse-proxy/conf.d:/etc/nginx/conf.d
    webapp:
        build:
            context: .
            dockerfile: ${DSQL_DOCKERFILE:-Dockerfile}
        env_file: .env
        networks:
            datasquirel:
                ipv4_address: 172.72.0.35
        container_name: dsql-site
        hostname: dsql-site
        volumes:
            - ${DSQL_VOLUME_STATIC:-../dsql-data/static}:/static
            - ${DSQL_VOLUME_APP:-.}:/app
        environment:
            - DSQL_SSL_DIR=/app/ssl
            - DSQL_DB_SCHEMA_DIR=/app/jsonData/dbSchemas
            - DSQL_APP_DIR=/app
        restart: on-failure:${DSQL_WEB_APP_FAIL_COUNTS:-1}
        depends_on:
            setup:
                condition: service_completed_successfully
            db:
                condition: service_started

    websocket:
        build:
            context: ./docker/web-socket
            dockerfile: Dockerfile
        env_file: .env
        container_name: dsql-websocket
        hostname: dsql-websocket
        volumes:
            - ${DSQL_VOLUME_STATIC:-../dsql-data/static}:/static
            - ${DSQL_VOLUME_APP:-.}:/app
        networks:
            datasquirel:
                ipv4_address: 172.72.0.36
        depends_on:
            reverse-proxy:
                condition: service_started
        restart: always
        environment:
            - DSQL_SSL_DIR=/app/ssl
            - DSQL_DB_SCHEMA_DIR=/app/jsonData/dbSchemas
            - DSQL_APP_DIR=/app

    static:
        image: nginx:alpine
        env_file: .env
        container_name: dsql-static
        volumes:
            - ${DSQL_VOLUME_STATIC:-../dsql-data/static}:/static
            - ./docker/static/conf.d:/etc/nginx/conf.d
        ports:
            - ${DSQL_STATIC_SERVER_PORT}:80
        restart: always
        user: root

    db:
        image: mariadb:11-jammy
        env_file: .env
        networks:
            datasquirel:
                ipv4_address: 172.72.0.32
        depends_on:
            setup:
                condition: service_completed_successfully
        container_name: dsql-site-database
        volumes:
            - ${DSQL_VOLUME_DB:-../dsql-data/db}:/var/lib/mysql
            - ${DSQL_VOLUME_DB_SSL:-../dsql-data/ssl}:/ssl
            - ./docker/mariadb/master/conf.d:/etc/mysql/conf.d
            - ./docker/mariadb/.bash_history:/root/.bash_history
        environment:
            - MARIADB_ROOT_PASSWORD=${DSQL_MARIADB_ROOT_PASSWORD:-password}
        restart: always

    db-load-balancer:
        image: nginx:alpine
        env_file: .env
        networks:
            datasquirel:
                ipv4_address: 172.72.0.24
        depends_on:
            db:
                condition: service_started
        container_name: dsql-db-load-balancer
        hostname: dsql-db-load-balancer
        volumes:
            - ${DSQL_DB_LOAD_BALANCER_CONFIGURATION_FILE:-../dsql-data/db-load-balancer-config/nginx.conf}:/etc/nginx/nginx.conf
        ports:
            - ${DSQL_DB_EXPOSED_PORT:-33600}:3306
        restart: always

networks:
    datasquirel:
        driver: bridge
        ipam:
            config:
                - subnet: 172.72.0.0/24
                  gateway: 172.72.0.1

You need to copy this file to docker-compose.yml. You can edit this file to your liking. For multiple instances, make sure to change the name: and networks: fields.

NOTE: All IP addresses that match the 172.72.0.* pattern must be consistent


6. Fire up your instance

With all setup complete run these commands:

cd $HOME/.dsql/main/dsql-admin/dsql-app


docker compose up -d --build

To stop the instance, from the same directory, run

docker compose down