Connecting to SQL

From DreamFactory
Jump to: navigation, search
DreamFactoryTutorialsConnecting to SQL
(→‎Other SQL)
 
(22 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
With DreamFactory you can REST-enable and connect to any SQL database in just a few easy steps. This article explains how to configure DreamFactory to make connections to various types of SQL databases.  
+
DreamFactory makes it easy to REST-enable and connect to any SQL database in just a few easy steps. This tutorial explains how to configure DreamFactory to make connections to a SQL database.
  
−
## SQLite
+
== SQLite ==
  
−
==Step 1 - Configure the database as a SQL service on your DreamFactory instance==
+
All DreamFactory instances now come with a pre-created SQLite service named 'db' that you can use for development. To create a new SQLite service follow these steps.
  
−
In your web browser, go to the admin console for your DreamFactory instance and select the ‘Services’ tab. You’ll see a list of all services currently configured on your instance.
+
*Go to the Services tab in the admin console.
 +
*Click Create.
 +
*From the Service Type menu select Database then ''SQLite.'
 +
*Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
 +
*Go to the Config tab for the new service.
 +
*Set the file name in the connection string to something unique. The entire database is stored in this file, which can be found in /storage/databases of your DreamFactory installation.
 +
*No credentials are required, just click Create Service to save your new service.
 +
*Go to the API Docs tab in the admin console to test your new service.
  
−
-screenshot-
+
== Other SQL==
  
−
===Using the Default SQLite Database===
+
Connecting to other SQL types is similar to SQLite, except for the connection string, which varies depending on which database type you are targeting. Bitnami installs come with a pre-configured MySQL database that you can access without any additional set up.
  
−
Most DreamFactory instances will have a default database service named ‘db’.  This is a SQLite database that uses the local file system for storage. It’s great for “kicking the tires” and learning how to use the DreamFactory REST API.  If you don’t have the SQLite service named ‘db’, or want to add another SQLite service, see the next section ‘Adding a SQLite Service’. If you want to use the default SQLite database you can skip ahead to step 2.
+
*Go to the Services tab in the admin console.
−
 
+
*Click Create.
−
===Adding a SQLite Service===
+
*From the Service Type menu select Database then the SQL db type you want.
−
 
+
*Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
−
To add a SQLite service, click ‘Create’ on the left sidebar. Select ‘SQL DB’ for service type. Enter a name, label, and description. We recommend a short alphanumeric name in all lower case with no spaces, since it will be part of the REST URL for accessing the service.
+
*Go to the Config tab for the new service.
−
 
+
*Enter the appropriate connection information (host, port, db name, user, password, etc.)
−
-screenshot-
+
*Click Create Service to save your new service. If the proper PHP driver is not installed, an error will be displayed with information on what is missing.
−
 
+
*Go to the API Docs tab in the admin console to test your new service.
−
Click the ‘Config’ tab and select ‘SQLite’ for Driver. Set Connection String to ‘sqlite:db.sq.test’, or something different from all of the other SQLite services on your instance. With that connection string the database file will be located at <instance root>/storage/databases/db.sq.test. The part after the ‘:’ must be unique for each SQLite service.
+
**A good test is to do a GET on {service name}/_table with the refresh dropdown set to true
−
 
+
−
-screenshot-
+
−
 
+
−
Click ‘Create Service’ and your new SQLite database is ready to use. You’re ready for step 2.
+
−
 
+
−
 
+
−
==Step 2 - Make REST calls to your database service==
+
−
 
+
−
You can use any REST client to make calls to your database service. Here are some simple examples. If your service name is not ‘db’ you should change the service name in the URLs to match your service.
+
−
 
+
−
Retrieve a list of tables
+
−
 
+
−
```GET <instance_url>/api/v2/db/_schema```
+
−
 
+
−
or
+
−
 
+
−
```GET <instance_url>/api/v2/db/_table```
+
−
 
+
−
Retrieve all records (up to configured max number) from the todo table
+
−
 
+
−
```GET <instance_url>/api/v2/db/_table/todo```
+
−
 
+
−
If you go to <instance_url>/test_rest.html in your browser you’ll get a simple interface for making REST calls. Other tools like Postman work great too. If you use HTTP Basic Auth you can make API calls without a session token. This can be helpful when testing or debugging. See the tutorial ‘Authentication’ for details.
+
−
 
+
−
See the tutorial ‘Using the API Docs’ for details on these and other REST calls. It will show you how to use the API Docs to create tables and perform basic CRUD (Create-Read-Update-Delete) operations on those tables.
+
−
 
+
−
 
+
−
## MySQL
+
−
 
+
−
## PostgreSQL
+
−
 
+
−
## Microsoft SQL Server
+
−
 
+
−
## IBM DB2
+
−
 
+
−
## Oracle
+
−
 
+
−
## SAP/Sybase
+

Latest revision as of 13:00, 2 September 2016

DreamFactory makes it easy to REST-enable and connect to any SQL database in just a few easy steps. This tutorial explains how to configure DreamFactory to make connections to a SQL database.

SQLite

All DreamFactory instances now come with a pre-created SQLite service named 'db' that you can use for development. To create a new SQLite service follow these steps.

  • Go to the Services tab in the admin console.
  • Click Create.
  • From the Service Type menu select Database then SQLite.'
  • Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
  • Go to the Config tab for the new service.
  • Set the file name in the connection string to something unique. The entire database is stored in this file, which can be found in /storage/databases of your DreamFactory installation.
  • No credentials are required, just click Create Service to save your new service.
  • Go to the API Docs tab in the admin console to test your new service.

Other SQL

Connecting to other SQL types is similar to SQLite, except for the connection string, which varies depending on which database type you are targeting. Bitnami installs come with a pre-configured MySQL database that you can access without any additional set up.

  • Go to the Services tab in the admin console.
  • Click Create.
  • From the Service Type menu select Database then the SQL db type you want.
  • Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
  • Go to the Config tab for the new service.
  • Enter the appropriate connection information (host, port, db name, user, password, etc.)
  • Click Create Service to save your new service. If the proper PHP driver is not installed, an error will be displayed with information on what is missing.
  • Go to the API Docs tab in the admin console to test your new service.
    • A good test is to do a GET on {service name}/_table with the refresh dropdown set to true
Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Tutorials/Connecting_to_SQL&oldid=33230"