Skip to main content

Database System Requirements

Supported Platforms​

Devii currrently supports the following SQL RDBMS platforms:

Connecting​

In order to allow Devii to connect to your database, you must allow connections from the Devii server's IP address: 34.70.42.197. All Devii traffic from the Devii SAAS installation will come from this IP.

Depending on your database platform and hosting (on-premises or cloud), the settings required may differ. Any firewall will need to allow connections to the database server from Devii's IP.

Microsoft Azure​

Microsoft Azure supports three different managed database systems to which Devii can connect: Azure SQL Server, Azure Database for MySQL, and Azure Database for PostgreSQL. These offferings have slightly different settings for network connectivity.

For Azure SQL Server, you will need to either allow connections from the Devii server IP, or from an Azure Virtual Network (VNet) that is set up with an Azure Firewall instance to route traffic from the Devii server IP to the database server. Azure SQL Server supports both scenarios in the same deployment.

Azure Database for MySQL and PostgreSQL, Flexible Server, are different in that they require you to choose between allowing public IP access, or access through a VNet. Once made, this choice cannot be changed. We recommend using public IP, or adding an Azure Firewall DNAT rule to route traffic to the server's private VNet IP.

Google Cloud SQL​

Google Cloud SQL supports MS SQL Server, MySQL, and PostgreSQL as managed database systems. All of these offer the same connectivity options for external access: public IP addresses with authorized networks, Cloud SQL Auth Proxy, and Cloud SQL Language Connectors.

At this time, Devii does not support the Cloud SQL Auth Proxy or Cloud SQL Language Connector options for connecting to databases, meaning that setting the Devii server IP as an authorized network address is the recommended method. Support for language connector and/or Cloud SQL Auth Proxy is planned for future releases.

Database Platform settings​

PostgreSQL uses host-based access control (HBA), which is controlled via the configuration file pg_hba.conf. An example of a permissive access grant for Devii is presented below.

info

PostgreSQL - Server Administration Documentation

# IPv4 local connections:
# host  DATABASE    USER    ADDRESS             METHOD      [OPTIONS]
host    all         all     34.70.42.197        md5

This may be more permissive than desired; you can list specific databases and database users.

Schema Requirements​

In order for Devii to work with a database schema, tables must conform to certain requirements. These are minimal, but include the following:

  • All tables MUST have primary keys. They do not need to be autogenerated numeric columns, and multi-column primary keys are supported, but without a primary key, Devii cannot export a table as part of a GraphQL API.

  • Table and column names MUST match the following regex: /^[_a-zA-Z][_a-zA-Z0-9]*$/. In other words, the starting character may only be an ASCII letter (either case) or underscore, and all other characters may only be an ASCII letter (either case), number, or underscore. In addition, a table must not have two or more underscores as a prefix: Devii skips over tables so named for internal reasons.

  • Table columns MUST match one of the following generic types: string types (including Character, Character Varying, and Text), Numeric (aka Decimal), Integer, BigInteger, floating-point types (Double Precision, Real), Boolean, Date, Time, DateTime, Interval, Enum types, BLOB types, and Money. XML column types are supported where the underlying database has such a type, otherwise XML documents may be stored as Text fields.

    For some database platforms additional types are available:

    • PostgreSQL's CIDR, INET, UUID, JSON, LTree, HStore, Bytea, and TSVector, as well as PostGIS's Geometry type.

    • MS SQL Server/Azure SQL Server's Bit, VarBinary, and UniqueIdentifier.

    • MySQL's TinyBlob, MediumBlob, and LongBlob.

Working With Existing Database Security​

For RDBMS platforms that have database security of any kind, which is all of the platforms supported by Devii, the Devii software will need credentials to be able to operate. The policy rules system is evaluated by Devii, before queries ever reach the database system; if a rule allows an operation, but the database user does not have the necessary privileges, the operation will still fail.

Good practice is for every database to have a separate database user, and this includes the Devii control database, used by Devii for internal settings. The Devii database user will need the appropriate privileges to perform all necessary operations on its own tables in its own database schema, including standard CRUD operations like SELECT, INSERT, UPDATE, or DELETE, and DDL operations. Tenant users will need CRUD privileges on all tables for which Devii will create GraphQL types.