[New Feature] Database connector

Announcements New features
1

We just deployed a plugin that allows you to read/write data from databases (currently PostgreSQL and MySQL). See more info at https://bubble.io/reference#Plugins.dbconnector

12 Likes
2

How do you reference the connector within bubble?

3

You can publish it as a datasource, action or both.
To use it as an action, look under plugins when creating a new action.
To use it as a datasource, use the Get data from external API option and look for your query’s name in the list of options for it. Makes sense?

1 Like
4

Can you provide an example connection string? There are many variations (C.f., https://www.connectionstrings.com/) and none seem to work for a normal 3306 external mySQL 4.1 or 5.6 or 5.7 database. Thanks.

5

Thanks for asking - I updated the documentation (deploying soon) but here it is

postgres://user:password@server_address:port/database_name. For MySQL replace ‘postgres’ with ‘mysql’
Concrete example:
postgres://ricardo:secret_password@128.203.134.78:5432/employees

Now, your server port / configuration might differ. Here are the steps I took to test, generally going up the OSI stack (see https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/)

  1. Can I ping the remote server? (sometimes pings are disabled, check with your administrator)
  2. Can I telnet to the machine?
  3. Does the user have rights to connect remotely?
  4. Do I have the right password?

Lots of really good tutorials on how to test out there.

I set up a mysql server for y’all so you can test.
Here are the deets:
mysql://knockknock:whosthere@162.243.34.135:3306/employees

The database loaded on it:
https://dev.mysql.com/doc/employee/en/sakila-structure.html

(Please be kind to the DB, you have full rights to it. If someone renders it unusable, I have an image I can restore from so just ask :wink: )

Here’s a sample MySQL parameterized query and one way to map returned columns:


Screenshot 2016-03-14 01.39.03.png863×557 38.3 KB

And a PostgreSQL query with parameters:

And a full example in the forum app:

3 Likes
9

@georgeciobanu We want to use a database connector for a client - but his host requires adding the source IPs to a whitelist in order to allow access. What are the IP adresses (or a range of IPs) that Bubble use to access the data through the database connector?
Thanks,

10

We unfortunately can’t guarantee IP addresses right now, but we’ll be able to do this soon for users on the enterprise plan. One way is to allow connection from any IP and use a special, limited-access (read-only) user from the DB Connector.

1 Like
11

How about a range of IPs? For example 212.167.23.x or 212.167.x.x ?

12

@georgeciobanu is it possible to use server’s alias instead of an IP address in the connection string? for example like this
aa107oshby1g79j.cf234yrgtj6cf.us-east-1.rds.amazonaws.com

13

I don’t think we can guarantee the ip range at this stage (except for the enterprise plan).

14

Ok and is it possible to use server’s alias instead of an IP address in the connection string? for example like thisaa107oshby1g79j.cf234yrgtj6cf.us-east-1.rds.amazonaws.com

15

@emmanuel @georgeciobanu - guys can you please answer this question? it’s for the client
is it possible to use server’s alias instead of an IP address in the connection string? for example like thisaa107oshby1g79j.cf234yrgtj6cf.us-east-1.rds.amazonaws.com

16

Yes, you can, but we can’t guarantee that won’t change either.

Wcan discuss setting up a fixed IP for someone with dedicated hosting (enterprise plan).

17

I have a small issue. I connected to a MySQL database on AWS.
So I want to use a field called Reg No as a parameter. When I define the query string as:
select * from All_PL where Reg No=? limit 200 - it gives an error
when trying this
select * from All_PL where Reg_No=? limit 200 - it doesn’t find the field
when trying this
select * from All_PL where “Reg_No”=? limit 200 no error but in the run mode it doesn’t return any results when using this query.
When using a parameter with a field that doesn’t have spaces (for example “model”) everything works fine.
In the forum app shared by George, he uses “where emp_no=?” and in the parameter details it appears “emp no” and seems to work.
But in my case there is an issue with using a field that has a space.
Any suggestions?
Thanks,

18

Have you find the issue?

19

Did you try “Reg No” in quotes?

You also might try Reg%20No with no quotes.

FWIW, it’s best to NOT embed spaces in anything. Use underscores instead.

1 Like
20

Thanks, WIz, we solved it simply be removing the spaces in the field names. I think we tried using quotes and %20, as far as I remember it didn’t help.

Thanks,
Levon.

Founder at Bubblewits - Bubble Certified Partner

http://iambubble.com - one page Bubble demo
http://builtonbubble.com - Collection of apps built on Bubble
Dev.zeroqode.com - Reach out if you need help creating something on Bubble

1 Like
21

Let us know if the dot (and other special characters) is a recurring issue. if so we can try to do something about it.

1 Like
22

the problem was with the spaces. We have removed the spaces in the field names in our SQL database, but i guess that it may not be always possible, if the Bubble user doesn’t own the database. So if you could fix the spacing issue in the connector that might be helpful for future cases.

1 Like
23

Hello @georgeciobanu ,

Could you make an example of how to export the data to Mysql database?

I´ve been reading the forum but yet I can´t figure out how it´s possible.

Thanks a lot.