In the last previous tutorial, we covered how to do create a database and do a succesfull database connection in two ways. In this tutorial, we are going to look at how to use the connection functions we created previously to create our table why it’s important to create your tables this way.

One of the most important things in software development is building scalable applications. Though as a beginner in software development you shouldn’t think of scalability, the real truth is you gonna have to know it at long last.

When writing tests for an application that uses a database without ORMs and you are required to use SQL, you need to have your create table queries in your code to ease execution of your app. This way, when your app is run, it goes ahead to create your tables.

Lets put this into practice!

Here are our two SQL queries for creating our tables

#creating users table
CREATE TABLE IF NOT EXISTS users (
    user_id serial PRIMARY KEY NOT NULL,
    name character varying(50) NOT NULL,
    username character varying(50) NOT NULL,
    email character varying(50),
    date_created timestamp with time zone DEFAULT ('now'::text)::date NOT NULL,
    password character varying(500) NOT NULL );

#creating posts table
CREATE TABLE IF NOT EXISTS incidents (
    post_id serial PRIMARY KEY NOT NULL,
    created_by character varying(20) NOT NULL,
    description character varying(200) NOT NULL,
    title character varying(50),
    created_on timestamp with time zone DEFAULT ('now'::text)::date NOT NULL
    );

To simplify this further, we are going to create a function called tables thats going to have these queries as a variable. We are going to then put the variable into a list and return the list.

def tables():
	users = """ CREATE TABLE IF NOT EXISTS users (
    user_id serial PRIMARY KEY NOT NULL,
    name character varying(50) NOT NULL,
    username character varying(50) NOT NULL,
    email character varying(50),
    date_created timestamp with time zone DEFAULT ('now'::text)::date NOT NULL,
    password character varying(500) NOT NULL );"""
	
	posts = """CREATE TABLE IF NOT EXISTS incidents (
    post_id serial PRIMARY KEY NOT NULL,
    created_by character varying(20) NOT NULL,
    description character varying(200) NOT NULL,
    title character varying(50),
    created_on timestamp with time zone DEFAULT ('now'::text)::date NOT NULL
    ); """

	queries = [posts, users]
	return queries

In our intit_db() we are going to call our connection() which will return for us the connection object, use it to create a cursor.

We will then call the tables() function, that will return for us the tables query lists. loop through the list using the for loop to create for us the tables. The function init_db() must return a connection object that we will use within our models.

def init_db():
	con = connection(uri)
	cur = con.cursor()
	queries = tables()

	for query in queries:
		cur.execute(query)
	con.commit()
	return con

Repeat the same for the init_test_db() function. The only difference between these two functions is that one is used for tests and one is used for the app.

The destroydb() is also gonna use the same methodology only that in this case, it will deleting the tables in the test database.

The final database_config.py file will look like this;

import pyscopg2

#url for databse connection
uri = os.getenv(['DATABASE_URL'])

#url for test databse connection
test_uri = os.getenv(['DATABASE_TEST_URL'])


#return connection
def connection(url):
	con = pyscopg2.connect(url)
	return con
	

#return connection and creates tables 
def init_db():
	con = connection(uri)
	cur = con.cursor()
	queries = tables()

	for query in queries:
		cur.execute(query)
	con.commit()
	return con


#return connection and creates tables (TDD)
def init_test_db(test_url):
	con = connection(test_uri)
	cur = con.cursor()
	queries = tables()

	for query in queries:
		cur.execute(query)
	con.commit()
	return con

#Deletes all tables after tests have been run
def destroydb():
	con = connection(test_uri)
	cur = con.cursor()

	posts = """ DROP TABLE IF EXISTS posts CASCADE; """
	users = """ DROP TABLE IF EXISTS users CASCADE;  """

	queries = [posts, users]

	for query in queries:
		cur.execute(query)
	con.commit()

#contain all table creation queries
def tables():
	users = """ CREATE TABLE IF NOT EXISTS users (
    user_id serial PRIMARY KEY NOT NULL,
    name character varying(50) NOT NULL,
    username character varying(50) NOT NULL,
    email character varying(50),
    date_created timestamp with time zone DEFAULT ('now'::text)::date NOT NULL,
    password character varying(500) NOT NULL );"""
	
	posts = """CREATE TABLE IF NOT EXISTS incidents (
    post_id serial PRIMARY KEY NOT NULL,
    created_by character varying(20) NOT NULL,
    description character varying(200) NOT NULL,
    title character varying(50),
    created_on timestamp with time zone DEFAULT ('now'::text)::date NOT NULL
    ); """

	queries = [posts, users]
	return queries

Thats all for this tutorial. In the next tutorial, we gonna create our models file and see how to put this to work.

Please checkout the video below for more;


Thank you for reading. let me know on the comment section how helpful it was.

If you like the Article you can give it a clap 👏.

If you feel like you owe me 🍺 after learning something valuable you can always leave a comment and share with your network 😃


1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here