MySQL
Learn how to build an app in Noloco around your MySQL database
Last updated
Learn how to build an app in Noloco around your MySQL database
Last updated
Overview
In this guide we are going to walkthrough how easy it is to connect your MySQL database(s) to your Noloco app. Once connected, this will allow your team, customers &/or third party stakeholders to read, update and create records in your MySQL instance directly from your Noloco app.
Add your MySQL data source Navigate to the data tab in your Noloco app and click to add a new data source. From the list, choose MySQL.
2. Name your data source It's best practice to call the data source the same name as your database to help you keep track.
3. Enter the server connection information The hostname is the URL that you access your server on and the port is the port you use to connect. By default MySQL uses port 3306 so if you're not sure try that.
4. Enter the database information We need the name of the database on the server that you want us to connect to. The database will be something that you named.
5. Enter the login details
You will need to provide a MySQL user's login details for us to use. Specifically we need a the username and password for a user with SELECT, INSERT, UPDATE and DELETE permissions for tables within the database you are importing to Noloco.
We support connecting to your database with only SELECT permissions, however you need to toggle this setting on in the last step of the new data source form.
We support connecting to your database with SSL, just toggle it on in the last step of the new data source form.
If you restrict connections to your MySQL database by IP, you can whitelist our three static IP addresses that we might connect from.
Noloco will import all tables from your MySQL database that have a primary key and whose name has some alpha-numeric or emoji characters. So for instance a table with just punctuation in the name would be ignored, as would a table with a valid name but no primary key.
We do not support MySQL tables with composite primary keys.
We only support regular tables and do not import views.
We categorise built-in data types of columns into three buckets; fully supported, partially supported and unsupported. When importing a column from a table, you will only see it in Noloco if its type is fully or partially supported.
Data types that Noloco fully supports will be imported into your Noloco project with full read-write capabilities to be updated as well as displayed.
bigint
INTEGER
bool
BOOLEAN
boolean
BOOLEAN
char
TEXT
date
DATE
datetime
DATE
dec
DECIMAL
decimal
DECIMAL
double
DECIMAL
double precision
DECIMAL
enum
SINGLE_OPTION
float
DECIMAL
int
INTEGER
integer
INTEGER
longtext
TEXT
mediumint
INTEGER
mediumtext
TEXT
smallint
INTEGER
text
TEXT
time
DURATION
timestamp
DATE
tinyint
INTEGER (except for tinyint(1) which is BOOLEAN)
tinytext
TEXT
varchar
TEXT
Other data types are partially supported which means that we will import them to your Noloco project and display them, but we do not support any write operations to them so you may not update them from Noloco.
JSON
TEXT
YEAR
TEXT
Any table with a foreign key constraint on one or more of its columns to another synced table will have those foreign key(s) interpreted as a Noloco relationship field(s) when synced.
We do not support composite foreign keys. Only foreign key constraints on single columns will be synced as relationships in Noloco.
The type of relationship that will be created in Noloco depends on other constraints on the foreign key constrained column. However because MySQL requires all columns referenced by a foreign key to have a unique constraint, they will always be one of the two relationships below.
Yes
ONE_TO_ONE
No
MANY_TO_ONE
These relationships are fully functional Noloco relationships and any updates to the relationship values in Noloco will be propagated to the foreign key on your MySQL database.
As documented above, all MySQL foreign keys must reference a column with a unique constraint. This means that from one foreign key alone you cannot create ONE_TO_MANY or MANY_TO_MANY relationships in MySQL. The way that these are typically created are with join tables with multiple foreign keys that sit between two tables you want to relate. Noloco offers some limited support to interpret such join tables as multi-relationships.
Consider the following example:
The Join Table allows a MANY_TO_MANY relationship to be described between Table A and Table B by storing a normalised mapping of a_id and b_id.
If a table in your database matches all of these criteria, then we will classify it as a join table:
It has exactly two foreign keys (pointing to separate tables)
Either one or neither of the foreign keys has a unique constraint
All columns in the table are either in the primary key or in one of the foreign keys
Join tables are not synced as their own data type like the other tables. Instead they will be synced as a relationship field on each side of the relationship they point to. The type of Noloco relationship that is created for a join table depends on whether there are any unique constraints in the join table for the two foreign keys.
0
MANY_TO_MANY
1
ONE_TO_MANY
2
N/A - not classified as join table
These relationships are fully functional Noloco relationships and any updates to the relationship values in Noloco will be propagated to the join table on your MySQL database.
The following (and any case variants) are reserved field names in Noloco:
createdAt
id
updatedAt
uuid
If your MySQL table has an id column we will still import it to Noloco, however it will be renamed to [Table Name] Id to avoid conflicting with our own id.
If you have createdAt or updatedAt field(s) and they are of type date, datetime or timestamp, we will map your column(s) to our own field(s). Otherwise we will manage the created/updated times ourselves.
If you have a uuid column it will not be imported.
We support syncing columns containing a URL as a Noloco FILE field. Just open up the corresponding collection in your data table and click on the field then change its type to FILE. After the collection next has a data sync you will see your files at those URLs appearing in Noloco.
In some cases importing the exact tables that exist on your MySQL database might not be as useful to you as importing the results of a query that curates your data in a certain way. Noloco supports creating a collection from a query by opening up the menu for your data source:
This will take you the the collection query editor where you can set up the query that will build your collection.
At the top of this page is an input for you to name your collection. On the left you can see an overview of this data source from Noloco and on the right is a query editor for you to develop and test your query over time. The results of testing your query will appear in the section at the bottom of the page. To save your query you must have a successful test of it.
Any errors in your query will be surfaced in the section at the bottom of the page.
After saving the query you will be able to see and use it like any other data type in Noloco. It will also be kept in sync with the upstream database both in schema and in data. If you want to edit the query at any time you can do so by clicking on the edit button from the data table.
