SQL Development? Structured Query Language and Apps

You know about using SQL with databases. How does SQL fit in with app development? Is there anything like SQL development? In this post, we look at the app development process, how it intersects with modeling a database, and what developers can do with SQL outside of their app.

Obviously, the beginning of this process is defining your app — what it does, who it is for, how it will function and look, etc. Let’s say you’ve done that work already and you’re preparing to start the more detailed stuff. At this point, you should begin thinking about the database that’s going to be underlying your app.

SQL Development? Define Your Data First

Let’s say you are going to build an app for a library. The problem domain is going to involve keeping records of books, library members, borrowing and returning dates, requests for books not in the library’s stock, and other things. When you look at this list, you can see many of the data entities you will have to define.

Now you need to decide what attributes each entity has. A book will have a title, an author, and a publishing date. A library member (which we will refer to as a user) will have a name, an address, and a phone number. And so on.

After the entities are defined, the next step is to model the data that will be stored in the database. It’s important to create tables in ways that eliminates duplicate information and separates unrelated data. This process is called normalization; you can read more about it and other data modeling matters in the Vertabelo Blog.

Then, Create the Front End

The front end is the part that the user sees and interacts with. This means all the windows, buttons, text boxes, etc. For a web application, this is the HTML, CSS and JavaScript that is sent to users’ browsers; in a desktop application, it’s the windows and controls defined in a GUI framework.

The information presented by the front end development services is generated by the back end, which is where we will connect to the database and execute SQL commands. In the back end, we’ll also define the things that can be done with our data — i.e. our business logic.

Next, Create the Back End

While the sole purpose of the front end is data presentation, the back end is for computing and data access.

First of all, we’ll need to create classes or records that describe our entities as we have defined them. These are called models. Then we will need a tool to connect to the database. In object-oriented programming, we would use object-relational mapping (ORM) to translate the database data into a form that can be used by our programming language. An example of ORM is creating a list of objects based on your model and filling their fields with data pulled from the database. There are many ORMs that let you program against a database without any knowledge of SQL, but that wouldn’t be any fun, would it?

Finally, Connect to the Database

This process looks a little different in every programming stack, but there’s usually a connection string that you pass into a method or a function that initializes the connection with the database. That’s where the so-called SQL development starts.

To start with the SQL application development, we must specify the server and the port in the connection string. The database is running on a server (a computer) which we identify with a hostname or an IP address. It’s listening on a port (a number from 1 to 65535) for incoming connections.

The database application usually has many logical databases inside. Each of those databases contains tables. So when we set up our connection, we should also specify the name of the database we want to access. And for security reasons, we must also provide a username and a password to be granted access to the database.

The database provider for your language will take the connection string, parse it, and open a TCP connection to the database. Each database has its own protocol for communication over the network. The provider will log you in and handle all of the low-level communication.

Once the connection is complete, you’ll be presented with an interface for executing queries and statements. You can now start your SQL development!

SQL Application Development Explained

1. Executing SQL Queries and Statements

At this point, you can use SQL development to start building your data access layer. This is a set of methods that will talk directly to the database. For instance, our library app’s book catalog will need methods like:

• get_all_books()
• get_books_with_author(Author author)
• get_books_with_title(string title)
• get_books_newer_than(Date date)

Each of those will have the definition of a SQL query string and will run it by the database. I’ve written some pseudo-code to give you an idea of what this would look like:

get_books_with_title(string title):
	query_string = “SELECT id, title, author_id, publish_date 
					FROM books WHERE title = @title”
	sql_command = new SqlCommand(query_string)
	sql_command.add_parameter(“@title”, title)
	return database.query(sql_command)

Note: you should generally avoid selecting all columns [*] and list only those you need.

Let’s also take a look at the user registration system. Here we’ll have some of our work done by the business logic layer first:

#business logic
register_user(User user, string password):
	validate(user)
	encrypted_pwd = encrypt(password)
	add_new_user(user, password)

#data access layer
add_new_user(User user, string encrypted_pwd):
	add_login(user.email, encrypted_pwd)
	add_user_data(user)

add_login(string email, string pwd):
	statement = “INSERT INTO login VALUES (@email, @pwd)”
	sql_command = new SqlCommand(statement)
	sql_command.add_parameter(“@email”, email)	sql_command.add_parameter(“@pwd”, pwd)
	database.execute(sql_command)

I’ll omit the add_user_data function because it won’t present anything new.

Now, let’s review what we can see in the code. First, we have a clear separation of business logic and database access.

Second, we’re using SQL command parameters as a security measure in our SQL development. They’re like placeholders for text and numbers in an SQL statement. I’ve marked the placeholders with `@`, but your database and programming language may require a different method. Command parameters protect us from SQL injections, which are essentially the malicious use of SQL code in user-input fields. SQL injections can release sensitive information and destroy your database; you should definitely learn more about them.

Third, our SQL strings are compiled with the code, so column and table names should never be parameterized.

Fourth and finally, you’ll usually see two different methods for executing your SQL. The query method returns values from the database, whereas the execute method returns the number of records affected by the change.

2. Using SQL Outside the Application

While you’re developing your application, it’s easier and quicker to test your queries and statements in the terminal or the DBMS. First, you will need some test data, which you’ll have to insert manually or via a script. Then you can write the query in the DBMS, test it, and refine it. When it is done, copy it into your code, remembering about command parameters and escape quotes.

After you’ve built your application, you may want to access the database in a “raw” manner to see how everything is doing in there. Depending on how you design your administrative panel, you may not have the functionality to do this. Or perhaps you just got an idea for a new statistic. Either way, you can use the SQL development method to look inside your database independently of your app.

So as you see, SQL does have a place in the app developer’s toolbox. Why not learn more about SQL development by checking out one of LearnSQL.com’s free courses?