Bubble Docs
  • Introduction
  • New? Start Here
  • What is Bubble?
  • The Glossary
  • User manual
    • Getting started
      • What is Bubble?
      • Building your first app
        • Planning features
        • Database structure
        • Design and UX
        • eCommerce and payments
          • Shopping cart
          • Checkout page
          • One-time payments
          • Subscriptions
          • Marketplace
      • Creating and managing apps
      • The Bubble editor
        • Tabs and sections
          • Design tab
            • The element tree
            • The property editor
          • Workflow tab
          • Data tab
          • Styles tab
          • Plugins tab
          • Settings tab
            • Application settings
              • Custom headers/body
              • Visual settings
              • Social media sharing
              • Translating your app
              • Email settings
              • Collaboration
            • Custom domain and DNS
          • Logs tab
        • Tools
          • Key features
          • The search tool
          • The Issue Checker
          • The element tree
          • The element property editor
          • The debugger
          • Notes
        • Previewing your app
      • Transitioning to Bubble from...
        • JavaScript
        • HTML and CSS
        • SQL
    • Design
      • Elements
        • The element hierarchy
          • The element tree
        • The page
        • Containers
          • Groups
          • Repeating groups
          • Table elements
          • Popups
          • Floating groups
          • Group focus
        • Visual elements
        • Input forms
          • Text and numbers
          • Dates and time
          • File uploads
          • Selection controls
        • Reusable Elements
      • Styling
        • Color variables
        • Font variables
        • Styles
        • Custom Fonts
      • Responsive design
        • Building responsive pages
        • Legacy articles
          • The Basics (Legacy)
          • Building Responsive Pages (Legacy)
          • Migrating Legacy Pages
          • Tips When Designing (Legacy)
      • Templates
      • The Component Library
      • Importing from Figma
    • Data
      • The database
        • Data types and fields
        • Creating, saving and deleting data
        • Finding data
        • Displaying data
        • Protecting data with privacy rules
        • The database editor
        • Export/import data
          • Exporting data
          • Importing data (CSV)
        • Working with location data
        • Using Algolia
        • Database structure by app type
          • Marketplace Apps
          • Directory & Listings Apps
          • Social Network Apps
          • SaaS Apps
          • Project Management Apps
          • CRM Apps
          • Professional Services Apps
          • On-demand Apps
          • Documentation/ CMS Apps
          • Applicant Tracking System (ATS) Apps
          • Portfolio Apps
          • Gallery Apps
          • Online Store / Ecommerce Apps
          • Blog Apps
          • Messaging App
          • Dashboards
          • Building Block Apps
          • Bubble as a backend
      • Files
      • Images
      • Static data
        • App texts (translations)
        • Option sets
      • Temporary data
        • Custom states
        • URL parameters
      • User accounts
        • Authentication plugins
          • Facebook plugin
          • Fitbit plugin
          • Google plugin
          • Instagram plugin
          • LinkedIn plugin
          • Pinterest plugin
          • Slack plugin
          • Wistia plugin
          • YouTube plugin
        • Cookies set by Bubble
      • Time, dates and time zones
    • Logic
      • The frontend and backend
      • Workflows
        • Events
          • Frontend events
            • Recurring workflows
            • Custom events
          • Backend events
            • Database trigger events
        • Actions
        • API Workflows
      • Dynamic expressions
      • Conditions
      • Navigation
        • Single-page applications (SPA)
        • Multi-page applications
        • Page slugs
    • Workload
      • Understanding workload
        • Activity types
        • The workload calculation
        • Client-side and server-side processing
      • Tracking workload
        • Measuring
          • Using App Metrics
        • Monitoring
          • Workload notifications
          • Infinite recursion protection
      • Optimizing workload
        • Optimization framework
        • Optimization checklist
          • Page load
          • Searches
          • Workflows and actions
          • Backend workflows
        • Agency showcases
          • Minimum Studio
          • Neam
          • Support Dept
    • Security
      • Bubble's security features
      • Planning app security
      • Client-side and server-side
      • Bubble account security
      • App security
      • Page security
      • Database security
      • API security
        • API Connector security
        • Data API security
        • Workflow API security
      • Flusk
        • Overview
        • Flusk plan features
        • Getting started with Flusk
        • Flusk security tools
          • The Issues Explorer
          • Issue details
          • Tools and settings
            • Pages rating
            • Database rating
        • Flusk FAQ
      • Cookies
      • Security checklist
    • Publishing your app
      • Web app
      • Native mobile app
        • Global native mobile settings
        • iOS App Store
        • Google Play Store
        • Publishing FAQ
    • AI
      • Generate apps with AI
        • About AI app generation
      • AI page designer
      • Connect to AI agents
    • Maintenance
      • Collaborators
      • Version control
        • Best practices: Version control
        • Transitioning from the legacy version control
        • Terminology: Version control
        • Version Control (legacy)
      • Commenting
      • Database maintenance
        • Copying the database
        • Restoring database backups
        • Bulk operations
          • Bulk operation methods compared
        • Wiping change history
      • Performance
        • Hard limits
        • Capacity Usage (legacy)
        • Notes on queries
      • SEO
        • Introduction to SEO
        • SEO: App
        • SEO: Page
      • Testing and debugging
        • Introduction to testing and debugging
        • The debugger
        • The server logs
        • Supported browsers
      • API workflow scheduler
    • Integrations
      • API
        • Introduction to APIs
          • What is a RESTful API?
        • The Bubble API
          • Bubble API terminology
          • Authentication
            • How to authenticate
            • No authentication
            • As a User
            • As an admin
          • The Data API
            • Data API Privacy Rules
            • Data API endpoints
            • Data API requests
          • The Workflow API
            • Workflow API privacy rules
            • Workflow API endpoints
            • API workflows
              • Creating API workflows
              • Scheduling API workflows
              • Recursive API workflows
              • API Workflow Scheduler
              • Case: Stripe notifications
        • The API Connector
          • Authentication
          • API Connector security
          • API guides
            • OpenAI
              • Authentication
              • Calls
                • ChatGPT
                  • Chat
            • Google Translate
              • How to setup Google API keys
          • Streaming API
        • API security
        • Plugins that connect to APIs
        • API Glossary
      • Plugins
        • What Plugins Can Do
        • Installing and using Plugins
        • Authentication plugins
        • Special Plugins
      • SQL Database Connector
      • Bubble App Connector
      • WorkOS
        • WorkOS SSO
        • WorkOS API
    • Infrastructure
      • Sub-apps
      • Bubble release tiers
      • Hosting and scaling
        • How Bubble hosting works
        • Scaling with Bubble
        • CDN (Cloudflare)
        • Bubble app names
        • Domain and DNS
      • Compliance
        • GDPR
        • SOC 2 Type II
        • HIPAA
        • Other frameworks and standards
    • Bubble for Enterprise
      • Hosting and infrastructure
        • Dedicated instance
          • The Dedicated editor experience
          • Technical specs
          • Main cluster dependencies
          • Customizable options
          • Migration process
            • Pre-migration
            • During migration
            • Post-migration
      • Security and compliance
        • Single sign-on (SSO)
        • GDPR
        • SOC 2 Type II
        • HIPAA
        • Other frameworks
        • Bubble's security features
      • Admin and collaboration
      • Priority support
      • Billing and Payment Guideline for Dedicated Instances
  • Core Reference
    • Using the core reference
    • Bubble's Interface
      • Design tab
      • Design tab (Legacy)
      • Workflow tab
      • Data tab
      • Styles tab
      • Styles tab (Legacy)
      • Plugins tab
      • Settings tab
      • Logs tab
      • Template tab
      • Toolbar
      • Top and context menu options
      • Deployment and version control
        • Deployment & Version Control Dropdown (legacy)
      • Notes
    • Elements
      • General properties
      • General properties (Legacy)
      • Styling properties
      • Styling Properties (Legacy)
      • Responsive Properties
      • Responsive Properties (Legacy)
      • Conditional formatting
      • States
      • Page Element
        • Page Element (Legacy)
      • Visual Elements
      • Containers
      • Container Layout Types
      • Containers (Legacy)
      • Input Forms
      • Reusable Elements
      • Element Templates (legacy)
    • Workflows
    • Events
      • General events
      • Element events
      • Custom events
      • Recurring event
      • Database trigger event
    • Actions
      • Account
      • Navigation
      • Data (things)
      • Email
      • Element
      • Custom
    • Data
      • Data Sources
      • Operators and comparisons
      • Search
      • Privacy
    • Styles
    • API
      • The Bubble API
        • The Data API
          • Authentication
          • Data API endpoints
          • Data API requests
        • The Workflow API
      • The API Connector
        • Authentication
        • Adding calls
    • Bubble-made Plugins
      • AddtoAny Share Buttons
      • Airtable
      • API Connector
      • Blockspring
      • Box
      • Braintree
      • Bubble App Connector
      • Chart.js
      • Circle Music Player
      • Draggable Elements
      • Dropzone
      • Facebook
      • Fitbit
      • Full Calendar
      • Google
      • Google Analytics
      • Google Optimize
      • Google Places
      • Ionic Elements
      • iTunes
      • Slidebar Menu
      • LinkedIn
      • Localize Translation
      • Mixpanel
      • Mouse & Keyboard Interactions
      • Multiselect Dropdown
      • Progress Bar
      • Rich Text Editor
      • Rich Text Editor (Legacy)
      • Screenshotlayer
      • SelectPDF
      • Slack
      • Segment
      • Slick Slideshow
      • SQL Database Connector
      • Star Rating
      • Stripe
      • Tinder-like Element
      • Twitter
      • YouTube
      • Zapier
    • Application Settings
      • App plan
      • General
      • Domain / email
      • Languages
      • SEO / metatags
      • API
      • Collaboration
      • Sub-apps
      • Versions
  • Account & Marketplace
    • Account and billing
      • Pricing and plans
        • Plans and billing
        • Billing cycle
        • FAQ: Pricing and Workload
      • Account Management
      • Building Apps for Others
      • Selling on the Marketplace
      • Plans & Billing (legacy)
    • Official Bubble Certification
      • Hiring certified developers
    • Building Plugins
      • The Plugin Editor
      • General Settings
      • Updating to Plugin API v4
      • Adding API Connections
      • Building Elements
      • Building Actions
      • Loading Data
      • Publishing and versioning
      • Github Integration
    • Building Templates
    • Application and data ownership
    • Marketplace policies
    • Bug reports
  • Beta features
    • About the Beta features section
    • Native mobile apps 🔒
      • Introduction
        • What is a native mobile app?
        • Native mobile vs. web development
        • Differences in native and web elements
        • Native mobile app terminology
      • Building
        • Views and navigation
        • Native mobile actions
        • Components and gestures
        • Device resources
          • Location services
          • Camera/photo library
      • Previewing
      • Publishing
Powered by GitBook
On this page
  • Installing the plugin
  • Setting up a connection
  • Finding the connection settings
  • Understanding the connection string
  • Encrypted connections
  • Testing the connection
  • Adding queries
  • Fetching data
  • Adding constraints
  • Dynamic constraints
  • Limiting the result
  • Sorting the results
  • Updating data
  • Creating a record
  • Updating a record
  • Deleting a record
  • Technical limitations

Was this helpful?

  1. User manual
  2. Integrations

SQL Database Connector

This section covers the SQL Database Connector plugin, which lets you connect with external SQL databases

Last updated 1 year ago

Was this helpful?

This is the in-depth manual article series on Bubble’s SQL Database Connector. If you want the shorter, technical reference, check out the Reference entry:

Reference:

Sometimes you'll need to connect to an external database to make the most of your Bubble app. The SQL Database Connector Plugin is a handy tool that allows you to connect to databases like:

  • Postgres

  • MySQL

  • Microsoft SQL

By running SQL queries from within Bubble, you can access information or trigger actions.

What is SQL?

SQL (Structured Query Language) is a language used for managing and manipulating relational databases.

It is primarily designed for querying, inserting, updating, and deleting data within a database, as well as creating and modifying database structures. SQL is widely used and supported by various database management systems, such as MySQL, PostgreSQL, and Microsoft SQL Server.

Bubble uses PostgreSQL as the foundation for its database. By using PostgreSQL, Bubble takes advantage of a well-established, secure, and efficient database system that has been developed and refined over the years.

This also ensures compatibility with other databases and tools, making it easier to integrate and connect with external systems when needed.

Installing the plugin

The SQL Database Connector is not a built-in feature but an optional plugin that needs to be installed through the plugin store. To install it, search for "SQL Connector" and install the plugin below:

Click install to install it in your app.

Setting up a connection

After installing the plugin, you will see its settings where you can set up new connections. You can set up as many connections as you need, and you can set up as many queries as you need on each connection.

  1. First, click the Add a new connection button to create a new one.

  2. Then, provide a name for the connection. This is only for internal use and does not affect how the connection works.

  3. Next, we choose the Database type. Bubble officially supports connecting with Postgres, MySQL and Microsoft SQL.

  4. The next step is to set up the Connection string. This is where we set up the authentication and the URL of the SQL database we want to connect to. This information you set up here is sent encrypted from Bubble's server and is not visible to your app's users. The format of the Connection string is as below:

mysql://username:password@my-db-instance.endpoint.us-east-1.rds.amazonaws.com:PORT/db_name

Finding the connection settings

To find the connection settings for the SQL database, follow these general steps:

  1. Locate your database management system (DBMS) documentation: Refer to the documentation provided by your DBMS vendor (such as MySQL, PostgreSQL, Microsoft SQL Server, etc.) for specific instructions on locating connection settings.

  2. Access your DBMS management tool: Use the appropriate management tool provided by your DBMS vendor, like phpMyAdmin for MySQL, pgAdmin for PostgreSQL, or SQL Server Management Studio for Microsoft SQL Server.

  3. Find the connection details: In the management tool, look for the connection details, including the hostname or IP address, port number, database name, username, and password. These details may be found in the server configuration, connection properties, or user account settings.

With the information you find, you can generate the connection string mentioned earlier. Let's look at what each section of the string represents.

Understanding the connection string

The connection string consists of different parts that each serve a purpose in the connection:

  1. mysql://: This is the protocol prefix indicating that the connection string is for a MySQL database.

  2. username: The username for a database user that has access

  3. password: The password for that user

  4. @: This symbol separates the authentication credentials (username and password) from the rest of the connection string.

  5. my-db-instance.endpoint.us-east-1.rds.amazonaws.com: This is the hostname or address of the database server. In this case, it's an Amazon RDS instance located in the US East (N. Virginia) region.

  6. :PORT: The port number on which the MySQL server is listening for connections. Replace PORT with the actual port number (commonly 3306 for MySQL).

  7. /db_name: The name of the specific database you want to connect to

Formatting the password

For example, the password below is displayed with and without URL encoding:

❌ my password/U2n(
✅ %22my%20password%2FU2n%28%22

Encrypted connections

If you don't add the parameters below, the connection won't be encrypted, allowing data transmission to be intercepted and read as plaintext.

For sensitive information or production environments, we recommend you always use encrypted connections.

To set up an encrypted connection, you will need to add a parameter to the connection string. The parameter depends on the type of SQL database you are connecting to:

MySQL

Add the ?ssl={} parameter to the connection string. The resulting connection string would look like:

mysql://username:password@hostname:port/database_name?ssl={}

PostgreSQL

Add the ?ssl=true parameter to the connection string. The resulting connection string would look like:

postgres://username:password@hostname:port/database_name?ssl=true

Microsoft SQL Server

Add the ?encrypt=true parameter to the connection string. The resulting connection string would look like:

sql://username:password@hostname:port/database_name?encrypt=true

In some cases, you may need to provide additional parameters, such as the SSL certificate or the SSL key, depending on your DBMS and hosting provider's requirements.

Check your database vendor's documentation for detailed instructions on configuring encrypted connections.

Testing the connection

Once you've entered all the required connection details, click Test the connection to verify that it's functioning properly.

If there are any issues, Bubble will display an error message to help you identify and resolve the problem.

Adding queries

What is a database query?

A database query is a way to ask a database to find, add, change, or remove data.

They are a way to work with the data in an external database just like you would in the database of your Bubble app. Instead of using built-in actions like Do a search for, Create a new thing and Make changes to a thing, we set up queries that perform the same task in an external database.

Because Bubble uses the widely adopted PostgreSQL language, your app can communicate without problems with a lot of other database systems that use a similar language.

This article provides an introduction to SQL queries, but is not an exhaustive list of keywords. For a more in-depth guide to SQL queries, we recommend W3School's SQL tutorial section:

When the connection is set up and working, it's time to add the queries we want.

  1. First, we provide a name for the query. This is only for internal use and does not affect how the query works

  2. In the Connection dropdown, you can select which connection to use: this is the connection we set up earlier

  3. The Use as dropdown lets you specify whether you want to use the query in an action, as a data source or both. It doesn't affect the query itself, only where it becomes available in your app

  4. Finally, we set up the query itself

The terminology used in SQL is a little bit different from what's used in Bubble. When we talk about a table, the Bubble equivalent is data type. For example, the user table means we are working with data in the user data type.

Fetching data

Let's say first that we want to query a list of users from an external database. A simple query returning all users (with no constraints) would look like the following:

SELECT * FROM users;

This string is instructs a PostgreSQL database to return all the columns (*) and all the rows from the "users" table. The semicolon (;) at the end of the query indicates the end of the statement.

This example is basically the same as setting up Do a search for without adding any constraints.

Adding constraints

Now let's add some constraints to the query. We'll still search for users, but only:

  • With an age higher than 25 (number/integer)

  • From the country "India" (text/string)

SELECT * FROM users WHERE age > 25 AND country = 'India';

We're still saying we want entries FROM the users table, WHERE age > 25 and the country is called India.

If we compare this to a Bubble database, we are basically setting up the Do a search for below:

Dynamic constraints

Note on MySQL connections: MySQL formats parameters differently than Microsoft SQL and PostgreSQL. Instead of using $1, $2 and so forth, you use ? for both the first parameters and top separate multiple parameters.

In the example above, we set up constraints that will work just fine, but they have one drawback; they are static values (25 and India), and in many cases you will want to specify the constraints dynamically when you send the query.

To do this, we use parameters. Each parameters is written out in the SQL query as $1, where the number 1 represents a count of each parameter. In other words, for each parameter you add, you increase the number by 1: In our case, we would need two (age and country), making the key of these two parameters $1 and $2:

SELECT * FROM users WHERE age > $1 AND country = $2;

As you can see, we've replaced the static values (25 and India) with two parameters. Now we need to set those parameters up below to tell Bubble what kind of data to expect for the two parameters (number (age) and text (country)).

Note the section under Parameters. Bubble automatically creates a numbered identifier for each parameter you create ($1, $2 and so on).

  • Click Add parameter for each parameter you want to add

  • Provide a name: this is used internally in your app and doesn't affect the query

  • Provide a data type, such as number and text

  • Provide a test value: this value is only used in the initialization

  • Click Initialize this query to send a request to the external server and verify that it's working

Limiting the result

The queries above will ask the database to send back all the results that match. In many cases you won't need to send back that much data. Also, Bubble has a limit of 200 records returned in the SQL database connector.

You must add a limit to the number of results by using the LIMIT keyword; this limit must be 200 or less for main cluster apps. If no limit is included, the query will fail. Building further upon our query from above, we'll add a limit of 10 records.

The result should look like this:

SELECT * FROM users WHERE age > $1 AND country = $2 LIMIT 10;

Sorting the results

If you need to sort the results, you can use the ORDER BY keyword:

SELECT * FROM users WHERE age > $1 AND country = $2 ORDER BY age ASC LIMIT 10;

In the example above, we are using two keywords:

  • ORDER BY makes sure that we order by the user's age

  • ASC ensures that the sorting is ascending

Let's repeat what this full query does: it all columns (*) from the users table where the age is greater than the value represented by $1 and the country is equal to the value represented by $2.

The results are sorted by the age column in ascending order (ASC) and limited to 10 records.

Updating data

So far we've looked at how we can send a query to get some data back. SQL queries can also make changes to the database, such as creating, updating and deleting records.

Creating a record

To create a new record (similar to Create a new thing in Bubble), we use the INSERT INTO keyword. We'll need some additional information in that query:

  • Which table to add the record to (users)

  • Which fields to add data to

  • The value to save in those fields

For example, we could set up a query like this:

INSERT INTO users (first_name, last_name, age, country)
VALUES ('John', 'Doe', 28, 'USA');

Here we have two rows, where the first row specifies the table and fields, and the second row contains the values we want to save.

Just like in Bubble, an SQL database will automatically generate fields for unique ID, Created date and Modified date. Note that the unique ID does not necessarily have the same structure as in Bubble.

Updating a record

To update a record (similar to Make changes to a thing in Bubble), we use the UPDATE keyword. For this query, we need to supply the following information:

  • The table where we want to make the change

  • The change we want to make

  • The constraint to apply to the database search. In the example below we are using the ID field (similar to Unique ID in Bubble) to make sure we get the correct record.

UPDATE users
SET age = 29, country = 'Canada'
WHERE id = 123321;

In this example, we are changing the age and country fields on the user with the ID 123321.

The UPDATE keyword can also be used to change a list of things by changing the constraint specified in WHERE. For example, we could replace the id = 123321 with first_name = 'John' to make the change on all users named John.

To work on single records, we recommend always using the ID to ensure you are working on the correct record.

Deleting a record

Now let's try to delete a record. Again we'll specify the user with an ID, and we need to provide:

  • The table in which you want to perform the delete action

  • The constraint to find the right user(s)

DELETE FROM users
WHERE id = 123321;

This command deletes the user with the id 123321.

The DELETE keyword can also be used to delete a list of things by changing the constraint specified in WHERE. For example, we could replace the id = 123321 with first_name = 'John' to delete all users named John.

To work on single records, we recommend always using the ID to ensure you are working on the correct record.

Technical limitations

  • Bubble accepts a maximum of 200 lines per response. This limit can be increased for apps on an Enterprise - Dedicated plan by contacting their Technical Success Manager (TSM).

If your password contains special characters, they must be URL-encoded to ensure proper connection. For example, replace the '#' symbol with '%23'. A comprehensive list of URL encoding replacements can be found in this link. You can also use an automated converter like

External link:

www.urlencoder.io
W3School's SQL tutorial section
SQL Database Connector
The Bubble SQL Database Connector plugin lets you connect to external SQL databases
Make sure that the plugin you select has the By Bubble stamp in the lower right corner of the plugin entry.
The SQL query we just set up will produce the same result as this Do a search for, but pulling data from an external database.